[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_MVT_STATS_PROC
Source
1 PACKAGE BODY INV_MGD_MVT_STATS_PROC AS
2 -- $Header: INVSTATB.pls 120.13.12010000.2 2008/10/01 12:00:31 ajmittal ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVSTATB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Body of INV_MGD_MVT_STATS_PROC |
13 --| |
14 --| PROCEDURE LIST |
15 --| Process_Transaction |
16 --| Process_INV_Transaction |
17 --| Process_SO_Transaction |
18 --| Process_TwoLeOneCntry_Txn |
19 --| Process_Triangulation_Txn |
20 --| Process_PO_Transaction |
21 --| Process_RMA_Transaction |
22 --| Update_Invoice_Info |
23 --| Update_PO_With_Correction |
24 --| Process_Pending_Transactions |
25 --| Process_IO_Arrival_Txn |
26 --| Update_PO_With_RTV |
27 --| Update_SO_With_RMA |
28 --| |
29 --| HISTORY |
30 --| 07-Nov-06 nesoni Process_SO_Transaction method modified for bug |
31 --| 5440432 to calculate invoice for SO Arrival |
32 --| 16/04/2007 nesoni Bug 5920143. Added support for Include |
33 --| Establishments.
34 --| 02/08/2008 ajmittal Bug 7165989 - Movement Statistics RMA |
35 --| Triangulation uptake. |
36 --| Modified procs:Process_IO_Arrival_Txn, |
37 --| Process_RMA_Transaction |
38 --| New procedure : Process_RMA_Triangulation |
39 --+=======================================================================
40
41 --===================
42 -- CONSTANTS
43 --===================
44 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_MGD_MVT_STATS_PROC';
45 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_MGD_MVT_STATS_PROC.';
46
47 --===================
48 -- GLOBAL VARIABLES
49 --===================
50 g_records_processed NUMBER := 0;
51 g_records_inserted NUMBER := 0;
52
53 --===================
54 -- PRIVATE PROCEDURES
55 --===================
56
57 /* 7165989 - New procedure added to process RMA Triangulation transactions */
58 --========================================================================
59 -- PROCEDURE : Process_RMA_Triangulation PRIVATE
60 -- PARAMETERS: x_return_status status flag
61 -- p_legal_entity_id Legal Entity ID
62 -- p_start_date Transaction start date
63 -- p_end_date Transaction end date
64 -- p_transaction type RMA
65 -- COMMENT : This processes all the RMA triangulation txn for the specified
66 -- legal entity where the RMA is booked
67 --========================================================================
68
69 PROCEDURE Process_RMA_Triangulation
70 ( p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
71 , p_stat_typ_transaction IN INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
72 , x_return_status OUT NOCOPY VARCHAR2
73 )
74 IS
75 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
76 l_api_name CONSTANT VARCHAR2(30) := 'Process_RMA_Triangulation';
77 l_error VARCHAR2(600);
78 x_msg_count NUMBER;
79 x_msg_data VARCHAR2(2000);
80 l_insert_status VARCHAR2(10);
81 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
82 l_return_status VARCHAR2(1);
83 BEGIN
84 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
85 THEN
86 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
87 , G_MODULE_NAME || l_api_name || '.begin'
88 ,'enter procedure'
89 );
90 END IF;
91
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93 l_movement_transaction := p_movement_transaction;
94 l_stat_typ_transaction := p_stat_typ_transaction;
95
96 INV_MGD_MVT_UTILS_PKG.Mvt_Stats_Util_Info
97 ( p_stat_typ_transaction => l_stat_typ_transaction
98 , x_movement_transaction => l_movement_transaction
99 , x_return_status => l_return_status
100 );
101
102 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
103 THEN
104 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
105 THEN
106 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
107 , G_MODULE_NAME || l_api_name
108 || '.Failed when call mvt_stats_util_info'
109 ,'Failed'
110 );
111 END IF;
112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
113 ELSE
114 l_movement_transaction.customer_vat_number :=
115 INV_MGD_MVT_UTILS_PKG.Get_Cust_VAT_Number
116 (l_movement_transaction.bill_to_site_use_id);
117
118 IF l_movement_transaction.invoice_id IS NULL
119 THEN
120 l_movement_transaction.invoice_quantity := NULL;
121 l_movement_transaction.financial_document_flag := 'MISSING';
122 ELSE
123 l_movement_transaction.financial_document_flag
124 := 'PROCESSED_INCLUDED';
125 END IF;
126
127 /* Set the parameters for the RMA Dispatch transaction */
128 l_movement_transaction.movement_type := 'D';
129 l_movement_transaction.dispatch_territory_code :=
130 INV_MGD_MVT_UTILS_PKG.Get_Org_Location(p_warehouse_id => l_movement_transaction.sold_from_org_id);
131 l_movement_transaction.destination_territory_code :=
132 INV_MGD_MVT_UTILS_PKG.Get_Org_Location(p_warehouse_id => l_movement_transaction.organization_id);
133 /* triangulation country would be the country where the RMA was created/booked */
134 l_movement_transaction.triangulation_country_code := l_movement_transaction.dispatch_territory_code;
135
136 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
137 (p_api_version_number => 1.0
138 ,p_init_msg_list => FND_API.G_FALSE
139 ,x_movement_transaction => l_movement_transaction
140 ,x_msg_count => x_msg_count
141 ,x_msg_data => x_msg_data
142 ,x_return_status => l_insert_status
143 );
144 END IF;
145
146
147
148 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
149 THEN
150 COMMIT;
151 END IF;
152 g_records_processed := g_records_processed +1;
153 g_records_inserted := g_records_inserted +1;
154 l_movement_transaction := p_movement_transaction;
155
156 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
157 THEN
158 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
159 , G_MODULE_NAME || l_api_name || '.end'
160 ,'exit procedure'
161 );
162 END IF;
163
164 EXCEPTION
165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
167 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
168
169 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
170 THEN
171 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
172 , G_MODULE_NAME || l_api_name||'. Unexpected exception'
173 , l_error
174 );
175 END IF;
176
177 WHEN NO_DATA_FOUND THEN
178 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
179 THEN
180 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
181 , G_MODULE_NAME || l_api_name||'. No data found exception'
182 , l_error
183 );
184 END IF;
185 RAISE;
186
187 WHEN OTHERS THEN
188 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
189 THEN
190 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
191 , 'Others exception in '||l_api_name
192 );
193 END IF;
194
195 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
196 THEN
197 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
198 , G_MODULE_NAME || l_api_name||'. Others exception'
199 , l_error
200 );
201 END IF;
202 RAISE;
203
204 END Process_RMA_Triangulation;
205 --========================================================================
206 -- PROCEDURE : Process_Transaction PRIVATE
207 -- PARAMETERS: p_api_version_number known api version
208 -- p_init_msg_list FND_API.G_TRUE to reset list
209 -- x_return_status return status
210 -- x_msg_count number of messages in the list
211 -- x_msg_data message text
212 -- p_legal_entity_id Legal Entity ID
213 -- p_start_date Transaction start date
214 -- p_end_date Transaction end date
215 -- p_transaction type Transaction type (SO,PO etc)
216 -- COMMENT :
217 -- This processes all the transaction for the specified legal
218 -- entity that have a transaction date within the specified
219 -- date range.
220 --========================================================================
221
222 PROCEDURE Process_Transaction
223 ( p_api_version_number IN NUMBER
224 , p_init_msg_list IN VARCHAR2
225 , p_legal_entity_id IN NUMBER
226 , p_start_date IN DATE
227 , p_end_date IN DATE
228 , p_source_type IN VARCHAR2
229 , x_return_status OUT NOCOPY VARCHAR2
230 , x_msg_count OUT NOCOPY NUMBER
231 , x_msg_data OUT NOCOPY VARCHAR2
232 )
233 IS
234 l_return_status VARCHAR2(1);
235 l_msg_count NUMBER;
236 l_msg_data VARCHAR2(100);
237 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
238 x_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
239 l_api_name CONSTANT VARCHAR2(30) := 'Process_Transaction';
240 l_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.Value('AFLOG_ENABLED'),'N');
241 l_error VARCHAR2(600);
242 BEGIN
243 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
244 THEN
245 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
246 , G_MODULE_NAME || l_api_name || '.begin'
247 ,'enter procedure'
248 );
249 END IF;
250
251 x_return_status := FND_API.G_RET_STS_SUCCESS;
252
253 -- Get the setup info to determine if the transactions are to be
254 -- processed further.
255
256 l_movement_transaction.entity_org_id := p_legal_entity_id;
257
258 -- Process the INV transaction
259 IF p_source_type IN ('ALL','INV') THEN
260
261 Process_INV_Transaction
262 ( p_movement_transaction => l_movement_transaction
263 , p_start_date => p_start_date
264 , p_end_date => p_end_date
265 , p_transaction_type => p_source_type
266 , x_return_status => l_return_status
267 );
268
269 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
270 THEN
271 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
272 THEN
273 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
274 , G_MODULE_NAME || l_api_name
275 || '.Failed in process_inv_transaction'
276 ,'Failed'
277 );
278 END IF;
279
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281 END IF;
282
283 END IF;
284
285 IF p_source_type IN ('ALL','SO','PO','RMA','RTV')
286 THEN
287 -- Update the invoice info before retrieving new records.
288 Update_Invoice_Info
289 ( p_movement_transaction => l_movement_transaction
290 , p_start_date => p_start_date
291 , p_end_date => p_end_date
292 , p_transaction_type => p_source_type
293 , x_return_status => l_return_status
294 );
295
296 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
297 THEN
298 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
299 THEN
300 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
301 , G_MODULE_NAME || l_api_name
302 || '.Failed in Update_Invoice_Info'
303 ,'Failed'
304 );
305 END IF;
306
307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
308 END IF;
309
310 --Process pending transactions
311 Process_Pending_Transaction
312 ( p_movement_transaction => l_movement_transaction
313 , p_start_date => p_start_date
314 , p_end_date => p_end_date
315 , p_transaction_type => p_source_type
316 , x_return_status => l_return_status
317 );
318
319 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
320 THEN
321 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
322 THEN
323 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
324 , G_MODULE_NAME || l_api_name
325 || '.Failed in Process_Pending_Transactions'
326 ,'Failed'
327 );
328 END IF;
329
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
331 END IF;
332 END IF;
333
334 -- Process the SO transaction
335 IF p_source_type IN ('ALL','SO')
336 THEN
337 Process_SO_Transaction
338 ( p_movement_transaction => l_movement_transaction
339 , p_start_date => p_start_date
340 , p_end_date => p_end_date
341 , p_transaction_type => p_source_type
342 , x_return_status => l_return_status
343 );
344
345 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
346 THEN
347 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
348 THEN
349 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
350 , G_MODULE_NAME || l_api_name
351 || '.Failed in Process_SO_Transaction'
352 ,'Failed'
353 );
354 END IF;
355
356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
357 END IF;
358
359 Process_Triangulation_Txn
360 ( p_movement_transaction => l_movement_transaction
361 , p_start_date => p_start_date
362 , p_end_date => p_end_date
363 , p_transaction_type => p_source_type
364 , x_return_status => l_return_status
365 );
366
367 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
368 THEN
369 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
370 THEN
371 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
372 , G_MODULE_NAME || l_api_name
373 || '.Failed in Process_Triangulation_Txn'
374 ,'Failed'
375 );
376 END IF;
377
378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
379 END IF;
380 END IF;
381
382 IF p_source_type IN ('ALL','IO') THEN
383
384 l_movement_transaction.document_source_type := 'IO';
385 Process_SO_Transaction
386 ( p_movement_transaction => l_movement_transaction
387 , p_start_date => p_start_date
388 , p_end_date => p_end_date
389 , p_transaction_type => p_source_type
390 , x_return_status => l_return_status
391 );
392
393 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
394 THEN
395 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
396 THEN
397 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
398 , G_MODULE_NAME || l_api_name
399 || '.Failed in Process_SO_Trangsaction - IO'
400 ,'Failed'
401 );
402 END IF;
403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
404 END IF;
405
406 Process_IO_Arrival_Txn
407 ( p_movement_transaction => l_movement_transaction
408 , p_start_date => p_start_date
409 , p_end_date => p_end_date
410 , p_transaction_type => p_source_type
411 , x_return_status => l_return_status
412 );
413
414 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
415 THEN
416 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
417 THEN
418 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
419 , G_MODULE_NAME || l_api_name
420 || '.Failed in Process_IO_Arrival_Txn'
421 ,'Failed'
422 );
423 END IF;
424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
425 END IF;
426
427 END IF;
428
429
430 -- Process the PO transaction
431 IF p_source_type IN ('ALL','PO','RTV') THEN
432 Update_PO_With_Correction
433 ( p_legal_entity_id => p_legal_entity_id
434 , p_start_date => p_start_date
435 , p_end_date => p_end_date
436 , p_transaction_type => p_source_type
437 , x_return_status => l_return_status
438 );
439
440 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
441 THEN
442 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
443 THEN
444 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
445 , G_MODULE_NAME || l_api_name
446 || '.Failed in Update_PO_With_Correction'
447 ,'Failed'
448 );
449 END IF;
450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
451 END IF;
452
453 Process_PO_Transaction
454 ( p_movement_transaction => l_movement_transaction
455 , p_start_date => p_start_date
456 , p_end_date => p_end_date
457 , p_transaction_type => p_source_type
458 , x_return_status => l_return_status
459 );
460
461 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
462 THEN
463 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
464 THEN
465 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
466 , G_MODULE_NAME || l_api_name
467 || '.Failed in Process_PO_Transaction'
468 ,'Failed'
469 );
470 END IF;
471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
472 END IF;
473
474 END IF;
475
476
477 -- Process the RMA transaction
478 IF p_source_type IN ('ALL','RMA') THEN
479 Process_RMA_Transaction
480 ( p_movement_transaction => l_movement_transaction
481 , p_start_date => p_start_date
482 , p_end_date => p_end_date
483 , p_transaction_type => p_source_type
484 , x_return_status => l_return_status
485 );
486
487 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
488 THEN
489 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
490 THEN
491 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
492 , G_MODULE_NAME || l_api_name
493 || '.Failed in Process_RMA_Transaction'
494 ,'Failed'
495 );
496 END IF;
497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
498 END IF;
499 END IF;
500
501 /*INV_MGD_RPT_GENERATOR_PROC.Print_Transaction_Proxy_Stats;*/
502
503 IF l_debug = 'Y'
504 THEN
505 FND_FILE.put_line
506 ( FND_FILE.log
507 , '< ***** Records Processed: '||g_records_processed
508 );
509
510 FND_FILE.put_line
511 ( FND_FILE.log
512 , '< ***** Records Inserted: '||g_records_inserted
513 );
514 END IF;
515
516 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
517 THEN
518 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
519 , G_MODULE_NAME || l_api_name || '.end'
520 ,'exit procedure'
521 );
522 END IF;
523 EXCEPTION
524
525 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
527 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
528
529 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
530 THEN
531 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
532 , G_MODULE_NAME || l_api_name||'. Unexpected exception'
533 , l_error
534 );
535 END IF;
536
537 WHEN NO_DATA_FOUND THEN
538 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
539 THEN
540 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
541 , G_MODULE_NAME || l_api_name||'. No data found exception'
542 , l_error
543 );
544 END IF;
545 RAISE;
546
547 WHEN OTHERS THEN
548 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
549 THEN
550 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
551 , 'Others exception in '||l_api_name
552 );
553 END IF;
554
555 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
556 THEN
557 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
558 , G_MODULE_NAME || l_api_name||'. Others exception'
559 , l_error
560 );
561 END IF;
562 RAISE;
563
564 END Process_Transaction;
565
566 --========================================================================
567 -- PROCEDURE : Process_Transaction OVERLOADED
568 -- PARAMETERS:
569 -- x_return_status return status
570 -- p_movement_transaction Movement Transaction record
571 -- COMMENT :
572 -- This procedure is overloaded so that the form can use
573 -- this proceure to directly enter data in the mvt stats
574 -- table.
575 --========================================================================
576
577 PROCEDURE Process_Transaction
578 ( p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
579 )
580 IS
581 l_return_status VARCHAR2(1);
582 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
583 l_msg_count NUMBER;
584 l_msg_data VARCHAR2(100);
585 l_api_name CONSTANT VARCHAR2(300) := 'Process_Transaction (OVERLOADED)';
586 BEGIN
587 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
588 THEN
589 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
590 , G_MODULE_NAME || l_api_name || '.begin'
591 ,'enter procedure'
592 );
593 END IF;
594
595 l_movement_transaction := p_movement_transaction;
596
597 IF l_movement_transaction.document_source_type = 'INV' THEN
598
599 Process_INV_Transaction
600 ( p_movement_transaction => l_movement_transaction
601 , p_start_date => l_movement_transaction.transaction_date
602 , p_end_date => l_movement_transaction.transaction_date
603 , p_transaction_type => l_movement_transaction.document_source_type
604 , x_return_status => l_return_status
605 );
606
607 ELSIF l_movement_transaction.document_source_type='PO' THEN
608
609 Process_PO_Transaction
610 ( p_movement_transaction => l_movement_transaction
611 , p_start_date => l_movement_transaction.transaction_date
612 , p_end_date => l_movement_transaction.transaction_date
613 , p_transaction_type => l_movement_transaction.document_source_type
614 , x_return_status => l_return_status
615 );
616
617 Process_RMA_Transaction
618 ( p_movement_transaction => l_movement_transaction
619 , p_start_date => l_movement_transaction.transaction_date
620 , p_end_date => l_movement_transaction.transaction_date
621 , p_transaction_type => l_movement_transaction.document_source_type
622 , x_return_status => l_return_status
623 );
624
625 ELSIF l_movement_transaction.document_source_type='SO' THEN
626 Process_SO_Transaction
627 ( p_movement_transaction => l_movement_transaction
628 , p_start_date => l_movement_transaction.transaction_date
629 , p_end_date => l_movement_transaction.transaction_date
630 , p_transaction_type => l_movement_transaction.document_source_type
631 , x_return_status => l_return_status
632 );
633 ELSE
634 NULL;
635
636 END IF;
637
638 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
639 THEN
640 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
641 , G_MODULE_NAME || l_api_name || '.end'
642 ,'exit procedure'
643 );
644 END IF;
645 EXCEPTION
646
647 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
648 RAISE;
649
650 WHEN NO_DATA_FOUND THEN
651 RAISE;
652
653 WHEN OTHERS THEN
654 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
655 THEN
656 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
657 , 'Process_Transaction (OVERLOADED)'
658 );
659 RAISE;
660 END IF;
661
662 END Process_Transaction;
663
664 --========================================================================
665 -- PROCEDURE : Process_INV_Transaction PRIVATE
666 -- PARAMETERS: x_return_status status flag
667 -- p_legal_entity_id Legal Entity ID
668 -- p_start_date Transaction start date
669 -- p_end_date Transaction end date
670 -- p_transaction type INV
671 -- COMMENT :
672 -- This processes all the INV transaction for the specified legal
673 -- entity that have a transaction date within the specified
674 -- date range.
675 --========================================================================
676
677 PROCEDURE Process_INV_Transaction
678 ( p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
679 , p_start_date IN DATE
680 , p_end_date IN DATE
681 , p_transaction_type IN VARCHAR2
682 , x_return_status OUT NOCOPY VARCHAR2
683 )
684 IS
685 inv_crsr INV_MGD_MVT_DATA_STR.invCurTyp;
686 setup_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
687 ref_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
688 l_material_transaction INV_MGD_MVT_DATA_STR.Material_Transaction_Rec_Type;
689 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
690 l_movement_transaction_outer INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
691 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
692 x_msg_count NUMBER;
693 x_msg_data VARCHAR2(2000);
694 l_insert_flag VARCHAR2(1);
695 l_insert_status VARCHAR2(10);
696 l_movement_id NUMBER;
697 l_subinv_code VARCHAR2(10);
698 l_transfer_subinv VARCHAR2(10);
699 l_subinv_terr_code VARCHAR2(2);
700 l_transfer_subinv_terr_code VARCHAR2(2);
701 l_org_terr_code VARCHAR2(2);
702 l_transfer_org_terr_code VARCHAR2(2);
703 l_le_terr_code VARCHAR2(2);
704 l_return_status VARCHAR2(1);
705 l_api_name CONSTANT VARCHAR2(30) := 'Process_INV_Transaction';
706 l_error VARCHAR2(600);
707 BEGIN
708 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
709 THEN
710 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
711 , G_MODULE_NAME || l_api_name || '.begin'
712 ,'enter procedure'
713 );
714 END IF;
715
716 x_return_status := FND_API.G_RET_STS_SUCCESS;
717
718 l_movement_transaction := p_movement_transaction;
719
720 -- Get all the Inventory Transactions between the specified date ranges
721 INV_MGD_MVT_INV_MDTR.Get_INV_Transactions
722 ( inv_crsr => inv_crsr
723 , p_movement_transaction => l_movement_transaction
724 , p_start_date => p_start_date
725 , p_end_date => p_end_date
726 , x_return_status => l_return_status);
727
728 IF l_return_status = 'Y'
729 THEN
730 <<l_outer>>
731 LOOP
732 --Reset the movement record for each transaction
733 l_movement_transaction := p_movement_transaction;
734 l_movement_id := NULL;
735
736 FETCH inv_crsr INTO
737 l_movement_transaction.mtl_transaction_id
738 , l_material_transaction.transaction_type_id
739 , l_material_transaction.transaction_action_id
740 , l_movement_transaction.transfer_organization_id
741 , l_movement_transaction.transaction_date
742 , l_movement_transaction.organization_id
743 , l_movement_transaction.transaction_quantity
744 , l_subinv_code
745 , l_transfer_subinv;
746
747 EXIT WHEN inv_crsr%NOTFOUND;
748
749 SAVEPOINT INV_Transaction;
750
751 --Timezone support, convert server transaction date to legal entity timezone
752 l_movement_transaction.transaction_date :=
753 INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Server
754 ( p_trxn_date => l_movement_transaction.transaction_date
755 , p_le_id => l_movement_transaction.entity_org_id
756 );
757
758 -- Get the setup info from the stat type usages table for the
759 -- specified legal entity.
760 INV_MGD_MVT_SETUP_MDTR.Get_Setup_Context
761 ( p_legal_entity_id => l_movement_transaction.entity_org_id
762 , p_movement_transaction => l_movement_transaction
763 , x_return_status => l_return_status
764 , setup_crsr => setup_crsr
765 );
766
767 --Back up the movement statistics record
768 l_movement_transaction_outer := l_movement_transaction;
769
770 <<l_inner>>
771 LOOP
772 --Reset movement transaction record, fix bug 2888046
773 l_movement_transaction := l_movement_transaction_outer;
774
775 FETCH setup_crsr INTO
776 l_movement_transaction.zone_code
777 , l_movement_transaction.usage_type
778 , l_movement_transaction.stat_type
779 , l_stat_typ_transaction.reference_period_rule
780 , l_stat_typ_transaction.pending_invoice_days
781 , l_stat_typ_transaction.prior_invoice_days
782 , l_stat_typ_transaction.triangulation_mode;
783
784 EXIT l_inner WHEN setup_crsr%NOTFOUND;
785
786 INV_MGD_MVT_SETUP_MDTR.Get_Reference_Context
787 ( p_legal_entity_id => l_movement_transaction.entity_org_id
788 , p_start_date => p_start_date
789 , p_end_date => p_end_date
790 , p_transaction_type => p_transaction_type
791 , p_movement_transaction => l_movement_transaction
792 , x_return_status => l_return_status
793 , ref_crsr => ref_crsr
794 );
795
796 -- Reset the movement_id before fetching the transaction
797 l_movement_transaction.movement_id := NULL;
798
799 -- Bug:5920143. Added new parameter include_establishments in result.
800 FETCH ref_crsr INTO
801 l_movement_transaction.zone_code
802 , l_movement_transaction.usage_type
803 , l_movement_transaction.stat_type
804 , l_stat_typ_transaction.start_period_name
805 , l_stat_typ_transaction.end_period_name
806 , l_stat_typ_transaction.period_set_name
807 , l_stat_typ_transaction.period_type
808 , l_stat_typ_transaction.weight_uom_code
809 , l_stat_typ_transaction.conversion_type
810 , l_stat_typ_transaction.attribute_rule_set_code
811 , l_stat_typ_transaction.alt_uom_rule_set_code
812 , l_stat_typ_transaction.start_date
813 , l_stat_typ_transaction.end_date
814 , l_stat_typ_transaction.category_set_id
815 , l_movement_transaction.set_of_books_period
816 , l_stat_typ_transaction.gl_currency_code
817 , l_movement_transaction.gl_currency_code
818 , l_stat_typ_transaction.conversion_option
819 , l_stat_typ_transaction.triangulation_mode
820 , l_stat_typ_transaction.reference_period_rule
821 , l_stat_typ_transaction.pending_invoice_days
822 , l_stat_typ_transaction.prior_invoice_days
823 , l_stat_typ_transaction.returns_processing
824 , l_stat_typ_transaction.kit_method
825 , l_stat_typ_transaction.include_establishments;
826
827 IF ref_crsr%NOTFOUND
828 THEN
829 --the transaction is not inside of start period and end period
830 --so not create transaction
831 CLOSE ref_crsr;
832 ELSE
833 INV_MGD_MVT_STATS_PVT.Init_Movement_Record
834 ( x_movement_transaction => l_movement_transaction);
835
836 --Get subinventory location fix bug 2683302
837 l_subinv_terr_code :=
838 INV_MGD_MVT_UTILS_PKG.Get_Subinv_Location
839 ( p_warehouse_id => l_movement_transaction.organization_id
840 , p_subinv_code => l_subinv_code);
841
842 l_transfer_subinv_terr_code :=
843 INV_MGD_MVT_UTILS_PKG.Get_Subinv_Location
844 ( p_warehouse_id => l_movement_transaction.transfer_organization_id
845 , p_subinv_code => l_transfer_subinv);
846
847 --Get organization location
848 l_org_terr_code :=
849 INV_MGD_MVT_UTILS_PKG.Get_Org_Location
850 (p_warehouse_id => l_movement_transaction.organization_id);
851
852 l_transfer_org_terr_code :=
853 INV_MGD_MVT_UTILS_PKG.Get_Org_Location
854 (p_warehouse_id => l_movement_transaction.transfer_organization_id);
855
856 --Get legal entity location
857 -- Bug: 5920143. Calculate LE Territory code only when
858 -- user has selected Include Establishments as No
859 IF(l_stat_typ_transaction.include_establishments = 'N')
860 THEN
861 l_le_terr_code :=
862 INV_MGD_MVT_UTILS_PKG.Get_LE_Location
863 (p_le_id => l_movement_transaction.entity_org_id);
864 END IF;
865 -- For every record fetched get the dispatch and destination territory
866 -- codes.
867 IF ((l_material_transaction.transaction_type_id = 12 AND
868 l_material_transaction.transaction_action_id = 12)
869 OR
870 (l_material_transaction.transaction_type_id IN (2,3) AND
871 l_material_transaction.transaction_action_id IN (2,3) AND
872 l_movement_transaction.transaction_quantity > 0))
873 THEN
874 l_movement_transaction.dispatch_territory_code :=
875 NVL(l_transfer_subinv_terr_code, l_transfer_org_terr_code);
876
877 l_movement_transaction.destination_territory_code :=
878 NVL(l_subinv_terr_code, l_org_terr_code);
879 ELSE
880 l_movement_transaction.dispatch_territory_code :=
881 NVL(l_subinv_terr_code, l_org_terr_code);
882
883 l_movement_transaction.destination_territory_code :=
884 NVL(l_transfer_subinv_terr_code, l_transfer_org_terr_code);
885 END IF;
886
887 -- If the stat type is ESL ignore the INV transactions.
888 -- Bug: 5920143 Validation that LE Territory Code and
889 -- Shipping Org Territory Code should be same, is needed only when
890 -- user has selected Include Establishments as No.
891 IF ((UPPER(l_movement_transaction.stat_type) = 'ESL' AND
892 UPPER(l_movement_transaction.usage_type) = 'INTERNAL')
893 OR
894 ((l_stat_typ_transaction.include_establishments = 'N')
895 AND (l_le_terr_code <> NVL(l_subinv_terr_code, l_org_terr_code))))
896 THEN
897 l_insert_flag := 'N';
898 ELSE
899 l_insert_flag := INV_MGD_MVT_SETUP_MDTR.Process_Setup_Context
900 ( p_movement_transaction => l_movement_transaction);
901 END IF;
902
903 -- Process the inventory transaction
904 IF l_insert_flag = 'Y'
905 THEN
906 INV_MGD_MVT_INV_MDTR.Get_INV_Details
907 ( x_movement_transaction => l_movement_transaction
908 , x_return_status => l_return_status
909 );
910
911 IF l_return_status = 'Y'
912 THEN
913 INV_MGD_MVT_UTILS_PKG.Mvt_Stats_Util_Info
914 ( p_stat_typ_transaction => l_stat_typ_transaction
915 , x_movement_transaction => l_movement_transaction
916 , x_return_status => l_return_status
917 );
918
919 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
920 THEN
921 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
922 THEN
923 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
924 , G_MODULE_NAME || l_api_name
925 || '.Failed when call mvt_stats_util_info'
926 ,'Failed'
927 );
928 END IF;
929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
930 ELSE
931
932 --Get legal entity vat number stored in customer_vat_number
933 l_movement_transaction.customer_vat_number :=
934 INV_MGD_MVT_UTILS_PKG.Get_Org_VAT_Number
935 ( p_entity_org_id => l_movement_transaction.entity_org_id
936 , p_date => l_movement_transaction.transaction_date);
937
938 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
939 ( p_api_version_number => 1.0
940 , p_init_msg_list => FND_API.G_FALSE
941 , x_movement_transaction => l_movement_transaction
942 , x_msg_count => x_msg_count
943 , x_msg_data => x_msg_data
944 , x_return_status => l_insert_status
945 );
946
947 --yawang fix bug 2268875 only insert record when successfully
948 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
949 THEN
950 l_movement_id := l_movement_transaction.movement_id;
951 g_records_inserted := g_records_inserted +1;
952 END IF;
953 END IF;
954 END IF; --< end of IF from Get_INV_Details >
955 END IF;
956 CLOSE ref_crsr;
957 END IF;
958 END LOOP l_inner;
959 CLOSE setup_crsr;
960
961 -- If the insert procedure did not error out, update the transactions and
962 -- set the flag to PROCESSED.
963 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
964 THEN
965 l_movement_transaction.movement_id := l_movement_id;
966
967 INV_MGD_MVT_INV_MDTR.Update_INV_Transactions
968 ( p_movement_transaction => l_movement_transaction
969 , x_return_status => l_return_status );
970
971 COMMIT;
972 ELSE
973 ROLLBACK TO SAVEPOINT INV_Transaction;
974 END IF;
975
976 l_movement_transaction := p_movement_transaction;
977 g_records_processed := g_records_processed +1;
978 END LOOP l_outer;
979 CLOSE inv_crsr;
980 END IF;
981
982 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
983 THEN
984 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
985 , G_MODULE_NAME || l_api_name || '.end'
986 ,'exit procedure'
987 );
988 END IF;
989
990 EXCEPTION
991 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
992 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
993 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
994
995 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
996 THEN
997 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
998 , G_MODULE_NAME || l_api_name||'. Unexpected exception'
999 , l_error
1000 );
1001 END IF;
1002
1003 WHEN NO_DATA_FOUND THEN
1004 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1005 THEN
1006 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1007 , G_MODULE_NAME || l_api_name||'. No data found exception'
1008 , l_error
1009 );
1010 END IF;
1011 RAISE;
1012
1013 WHEN OTHERS THEN
1014 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1015 THEN
1016 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1017 , 'Others exception in '||l_api_name
1018 );
1019 END IF;
1020
1021 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1022 THEN
1023 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1024 , G_MODULE_NAME || l_api_name||'. Others exception'
1025 , l_error
1026 );
1027 END IF;
1028 RAISE;
1029
1030 END Process_INV_Transaction;
1031
1032
1033 --========================================================================
1034 -- PROCEDURE : Process_SO_Transaction PRIVATE
1035 -- PARAMETERS: x_return_status status flag
1036 -- p_legal_entity_id Legal Entity ID
1037 -- p_start_date Transaction start date
1038 -- p_end_date Transaction end date
1039 -- p_transaction type SO
1040 -- COMMENT :
1041 -- This processes all the SO transaction for the specified legal
1042 -- entity that have a transaction date within the specified
1043 -- date range.
1044 --========================================================================
1045
1046 PROCEDURE Process_SO_Transaction
1047 ( p_movement_transaction IN
1048 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1049 , p_start_date IN DATE
1050 , p_end_date IN DATE
1051 , p_transaction_type IN VARCHAR2
1052 , x_return_status OUT NOCOPY VARCHAR2
1053 )
1054 IS
1055 l_api_name CONSTANT VARCHAR2(30) := 'Process_SO_Transaction';
1056 l_error VARCHAR2(600);
1057 so_crsr INV_MGD_MVT_DATA_STR.soCurTyp;
1058 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1059 l_shipment_transaction INV_MGD_MVT_DATA_STR.Shipment_Transaction_Rec_Type;
1060 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
1061 l_movement_transaction_outer INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1062 setup_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
1063 ref_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
1064 l_insert_flag VARCHAR2(1);
1065 x_msg_count NUMBER;
1066 x_msg_data VARCHAR2(2000);
1067 l_so_le_id NUMBER;
1068 l_insert_status VARCHAR2(10);
1069 l_so_le_terri_code VARCHAR2(10);
1070 l_shipping_le_terri_code VARCHAR2(10);
1071 l_shipping_org_terri_code VARCHAR2(10);
1072 l_customer_terri_code VARCHAR2(10);
1073 l_movement_id NUMBER;
1074 --l_trans_date DATE;
1075 l_cross_le_status VARCHAR2(20);
1076 l_mvt_stat_status VARCHAR2(20);
1077 l_return_status VARCHAR2(1);
1078
1079 --Added for bug4185582, 4238563
1080 l_item_type_code VARCHAR2(30);
1081 l_link_to_line_id NUMBER;
1082 l_line_id NUMBER;
1083 l_need_create_kit VARCHAR2(1);
1084 l_kit_record_status VARCHAR2(1);
1085 l_parent_item_type_code VARCHAR2(30);
1086 l_shipped_qty NUMBER;
1087
1088 CURSOR l_parent
1089 IS
1090 SELECT
1091 item_type_code
1092 , NVL(shipped_quantity, fulfilled_quantity)
1093 FROM
1094 oe_order_lines_all
1095 WHERE line_id = l_link_to_line_id;
1096 BEGIN
1097 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1098 THEN
1099 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1100 , G_MODULE_NAME || l_api_name || '.begin'
1101 ,'enter procedure'
1102 );
1103 END IF;
1104
1105 x_return_status := FND_API.G_RET_STS_SUCCESS;
1106
1107 l_movement_transaction := p_movement_transaction;
1108
1109 -- Call the transaction proxy which processes all the transactions.
1110 INV_MGD_MVT_SO_MDTR.Get_SO_Transactions
1111 ( so_crsr => so_crsr
1112 , p_movement_transaction => l_movement_transaction
1113 , p_start_date => p_start_date
1114 , p_end_date => p_end_date
1115 , x_return_status => l_return_status);
1116
1117 IF l_return_status = 'Y'
1118 THEN
1119 <<l_outer>>
1120 LOOP
1121 --Reset the movement record for each picking line
1122 l_movement_transaction := p_movement_transaction;
1123 l_movement_id := NULL;
1124
1125 FETCH so_crsr
1126 INTO l_movement_transaction.picking_line_detail_id
1127 , l_movement_transaction.organization_id
1128 , l_movement_transaction.ship_to_site_use_id
1129 , l_movement_transaction.transaction_date
1130 , l_movement_transaction.order_line_id
1131 , l_movement_transaction.order_number
1132 , l_movement_transaction.bill_to_site_use_id
1133 , l_item_type_code
1134 , l_link_to_line_id;
1135
1136 EXIT WHEN so_crsr%NOTFOUND;
1137
1138 SAVEPOINT SO_Transaction;
1139
1140 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1141 THEN
1142 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1143 , G_MODULE_NAME ||'.The SO num,ln id,pk line id,txn date are '
1144 ||l_movement_transaction.order_number
1145 ||','||l_movement_transaction.order_line_id
1146 ||','||l_movement_transaction.picking_line_detail_id
1147 ||','||l_movement_transaction.transaction_date
1148 ,'debug msg');
1149 END IF;
1150
1151 --Timezone support, convert server transaction date to legal entity timezone
1152 l_movement_transaction.transaction_date :=
1153 INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Server
1154 ( p_trxn_date => l_movement_transaction.transaction_date
1155 , p_le_id => l_movement_transaction.entity_org_id
1156 );
1157
1158 INV_MGD_MVT_SETUP_MDTR.Get_Setup_Context
1159 ( p_legal_entity_id => l_movement_transaction.entity_org_id
1160 , p_movement_transaction => l_movement_transaction
1161 , x_return_status => l_return_status
1162 , setup_crsr => setup_crsr
1163 );
1164
1165 --Back up the movement statistics record
1166 l_movement_transaction_outer := l_movement_transaction;
1167
1168 <<l_inner>>
1169 LOOP
1170 --Reset movement transaction record
1171 l_movement_transaction := l_movement_transaction_outer;
1172
1173 FETCH setup_crsr INTO
1174 l_movement_transaction.zone_code
1175 , l_movement_transaction.usage_type
1176 , l_movement_transaction.stat_type
1177 , l_stat_typ_transaction.reference_period_rule
1178 , l_stat_typ_transaction.pending_invoice_days
1179 , l_stat_typ_transaction.prior_invoice_days
1180 , l_stat_typ_transaction.triangulation_mode;
1181
1182 EXIT l_inner WHEN setup_crsr%NOTFOUND;
1183
1184 IF NVL(l_stat_typ_transaction.reference_period_rule,'SHIPMENT_BASED')
1185 = 'INVOICE_BASED'
1186 AND NVL(l_movement_transaction.document_source_type,'SO') = 'SO'
1187 THEN
1188 IF l_movement_transaction.document_source_type IS NULL
1189 THEN
1190 l_movement_transaction.document_source_type := 'SO';
1191 END IF;
1192
1193 l_line_id := l_movement_transaction.order_line_id;
1194
1195 --For included item, use parent invoice info
1196 IF l_item_type_code = 'INCLUDED'
1197 THEN
1198 l_movement_transaction.order_line_id := l_link_to_line_id;
1199 END IF;
1200
1201 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
1202 ( p_stat_typ_transaction => l_stat_typ_transaction
1203 , x_movement_transaction => l_movement_transaction
1204 );
1205
1206 INV_MGD_MVT_FIN_MDTR. Get_Reference_Date
1207 ( p_stat_typ_transaction => l_stat_typ_transaction
1208 , x_movement_transaction => l_movement_transaction
1209 );
1210
1211 l_movement_transaction.transaction_date :=
1212 l_movement_transaction.reference_date;
1213
1214 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1215 THEN
1216 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1217 , G_MODULE_NAME ||'.The reference txn date is '
1218 ||l_movement_transaction.transaction_date
1219 ,'debug msg');
1220 END IF;
1221
1222 --Set back the included item line id
1223 l_movement_transaction.order_line_id := l_line_id;
1224 END IF;
1225
1226 INV_MGD_MVT_SETUP_MDTR.Get_Reference_Context
1227 ( p_legal_entity_id => l_movement_transaction.entity_org_id
1228 , p_start_date => p_start_date
1229 , p_end_date => p_end_date
1230 , p_transaction_type => p_transaction_type
1231 , p_movement_transaction => l_movement_transaction
1232 , x_return_status => l_return_status
1233 , ref_crsr => ref_crsr
1234 );
1235
1236 --Reset the movement_id before fetching the transaction
1237 l_movement_transaction.movement_id := NULL;
1238
1239 -- Bug:5920143. Added new parameter include_establishments in result.
1240 FETCH ref_crsr INTO
1241 l_movement_transaction.zone_code
1242 , l_movement_transaction.usage_type
1243 , l_movement_transaction.stat_type
1244 , l_stat_typ_transaction.start_period_name
1245 , l_stat_typ_transaction.end_period_name
1246 , l_stat_typ_transaction.period_set_name
1247 , l_stat_typ_transaction.period_type
1248 , l_stat_typ_transaction.weight_uom_code
1249 , l_stat_typ_transaction.conversion_type
1250 , l_stat_typ_transaction.attribute_rule_set_code
1251 , l_stat_typ_transaction.alt_uom_rule_set_code
1252 , l_stat_typ_transaction.start_date
1253 , l_stat_typ_transaction.end_date
1254 , l_stat_typ_transaction.category_set_id
1255 , l_movement_transaction.set_of_books_period
1256 , l_stat_typ_transaction.gl_currency_code
1257 , l_movement_transaction.gl_currency_code
1258 , l_stat_typ_transaction.conversion_option
1259 , l_stat_typ_transaction.triangulation_mode
1260 , l_stat_typ_transaction.reference_period_rule
1261 , l_stat_typ_transaction.pending_invoice_days
1262 , l_stat_typ_transaction.prior_invoice_days
1263 , l_stat_typ_transaction.returns_processing
1264 , l_stat_typ_transaction.kit_method
1265 , l_stat_typ_transaction.include_establishments;
1266
1267 IF ref_crsr%NOTFOUND
1268 THEN
1269 -- the transaction is not inside of start period and
1270 -- end period so not create transaction
1271 CLOSE ref_crsr;
1272 ELSE
1273 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1274 THEN
1275 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1276 , G_MODULE_NAME ||'.The usg,stat type,currency,tri mode,ref rule is '
1277 ||l_movement_transaction.usage_type
1278 ||','||l_movement_transaction.stat_type
1279 ||','||l_stat_typ_transaction.gl_currency_code
1280 ||','||l_stat_typ_transaction.triangulation_mode
1281 ||','||l_stat_typ_transaction.reference_period_rule
1282 ,'debug msg');
1283 END IF;
1284
1285 INV_MGD_MVT_STATS_PVT.Init_Movement_Record
1286 (x_movement_transaction => l_movement_transaction);
1287
1288 --Get legal entity where this SO is created
1289 l_so_le_id := INV_MGD_MVT_UTILS_PKG.Get_SO_Legal_Entity
1290 (p_order_line_id => l_movement_transaction.order_line_id);
1291
1292 --Find out the territory code
1293 l_so_le_terri_code :=
1294 INV_MGD_MVT_UTILS_PKG.Get_LE_Location
1295 (p_le_id => l_so_le_id);
1296
1297 l_shipping_le_terri_code :=
1298 INV_MGD_MVT_UTILS_PKG.Get_LE_Location
1299 (p_le_id => l_movement_transaction.entity_org_id);
1300
1301 l_shipping_org_terri_code :=
1302 INV_MGD_MVT_UTILS_PKG.Get_Org_Location
1303 (p_warehouse_id => l_movement_transaction.organization_id);
1304
1305 l_customer_terri_code :=
1306 INV_MGD_MVT_UTILS_PKG.Get_Site_Location
1307 (p_site_use_id =>l_movement_transaction.ship_to_site_use_id);
1308
1309 -- If cross legal entity transaction,the destination
1310 -- territory code is depend on if it's invoiced based
1311 -- triangulation mode
1312 IF (l_so_le_id IS NOT NULL
1313 AND l_so_le_id <> l_movement_transaction.entity_org_id
1314 AND NVL(l_stat_typ_transaction.triangulation_mode,
1315 'INVOICE_BASED') = 'INVOICE_BASED')
1316 THEN
1317 l_movement_transaction.dispatch_territory_code := l_shipping_le_terri_code;
1318 l_movement_transaction.destination_territory_code := l_so_le_terri_code;
1319 ELSE
1320 -- Regular SO or shipment based cross legal entity transaction
1321 l_movement_transaction.dispatch_territory_code := l_shipping_org_terri_code;
1322 l_movement_transaction.destination_territory_code := l_customer_terri_code;
1323 END IF;
1324
1325 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1326 THEN
1327 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1328 , G_MODULE_NAME
1329 ||'.The so le,shp le,shp org,cust,dest,disp terr code are '
1330 ||l_so_le_terri_code||','||l_shipping_le_terri_code
1331 ||','||l_shipping_org_terri_code||','||l_customer_terri_code
1332 ||','||l_movement_transaction.destination_territory_code
1333 ||','||l_movement_transaction.dispatch_territory_code
1334 ,'debug msg');
1335 END IF;
1336
1337 --Added for bug4185582, 4238563, find out if parent is KIT
1338 OPEN l_parent;
1339 FETCH l_parent INTO
1340 l_parent_item_type_code
1341 , l_shipped_qty;
1342 CLOSE l_parent;
1343
1344 --Find out if we need to treat this develivery as kit
1345 IF (l_item_type_code = 'INCLUDED'
1346 AND l_parent_item_type_code = 'KIT'
1347 AND l_shipped_qty IS NOT NULL
1348 AND l_stat_typ_transaction.kit_method = 'KIT')
1349 THEN
1350 l_need_create_kit := 'Y';
1351 ELSE
1352 l_need_create_kit := 'N';
1353 END IF;
1354
1355 --Find out if there is already a movement record created for this kit
1356 --if no, set l_kit_record to 'N' else set to 'Y'
1357 l_kit_record_status := INV_MGD_MVT_SO_MDTR.Get_KIT_Status
1358 (p_delivery_detail_id => l_movement_transaction.picking_line_detail_id);
1359 --End of bug 4185582, 4238563.
1360
1361 --Only create record for organization located in the same country as legal entity
1362 --If kit record has been created, do not need to create again
1363 -- Bug:5920143. Validation that LE Territory Code and
1364 -- Shipping Org Territory Code should be same, is needed only when
1365 -- user has selected Include Establishments as No.
1366 IF (((l_shipping_le_terri_code <> l_shipping_org_terri_code) AND
1367 (l_stat_typ_transaction.include_establishments = 'N'))
1368 OR (l_need_create_kit = 'Y' AND l_kit_record_status = 'Y'))
1369 THEN
1370 l_insert_flag := 'N';
1371 ELSE
1372 l_insert_flag := INV_MGD_MVT_SETUP_MDTR.Process_Setup_Context
1373 ( p_movement_transaction => l_movement_transaction);
1374 END IF;
1375
1376 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1377 THEN
1378 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1379 , G_MODULE_NAME
1380 ||'.The need crt kit,kit rec status,insert flg are '
1381 ||l_need_create_kit||','||l_kit_record_status
1382 ||','||l_insert_flag
1383 ,'debug msg');
1384 END IF;
1385
1386 -- Process the SO transaction
1387 IF l_insert_flag = 'Y'
1388 THEN
1389 INV_MGD_MVT_SO_MDTR.Get_SO_Details
1390 (x_movement_transaction => l_movement_transaction
1391 ,x_return_status => l_return_status
1392 );
1393
1394 IF l_need_create_kit = 'Y'
1395 THEN
1396 INV_MGD_MVT_SO_MDTR.Get_KIT_SO_Details
1397 ( p_link_to_line_id => l_link_to_line_id
1398 , x_movement_transaction => l_movement_transaction
1399 );
1400 END IF;
1401
1402
1403 IF l_return_status <> 'Y'
1404 THEN
1405 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1406 THEN
1407 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1408 , G_MODULE_NAME || l_api_name
1409 || '.Failed when call get_so_details'
1410 ,'Failed'
1411 );
1412 END IF;
1413 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1414 ELSE
1415 INV_MGD_MVT_UTILS_PKG.Mvt_Stats_Util_Info
1416 (p_stat_typ_transaction => l_stat_typ_transaction
1417 ,x_movement_transaction => l_movement_transaction
1418 ,x_return_status => l_return_status
1419 );
1420
1421 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1422 THEN
1423 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1424 THEN
1425 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1426 , G_MODULE_NAME || l_api_name
1427 || '.Failed when call mvt_stats_util_info'
1428 ,'Failed'
1429 );
1430 END IF;
1431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1432 ELSE
1433 l_movement_transaction.customer_vat_number :=
1434 INV_MGD_MVT_UTILS_PKG.Get_Cust_VAT_Number
1435 (l_movement_transaction.bill_to_site_use_id);
1436
1437 IF l_movement_transaction.document_source_type <> 'IO'
1438 THEN
1439 IF l_movement_transaction.invoice_id IS NULL
1440 THEN
1441 l_movement_transaction.invoice_quantity := NULL;
1442 l_movement_transaction.financial_document_flag := 'MISSING';
1443 ELSE
1444 l_movement_transaction.financial_document_flag := 'PROCESSED_INCLUDED';
1445 END IF;
1446 END IF;
1447
1448 -- Clear existing movement id if the mvt_stat_status is
1449 -- 'FORDISP in wsh_delivery_details table, otherwise
1450 -- the existing movement id will be populated as parent
1451 -- movement id for new record and we don't want that
1452 IF l_movement_transaction.picking_line_detail_id IS NOT NULL
1453 THEN
1454 SELECT mvt_stat_status
1455 INTO l_mvt_stat_status
1456 FROM wsh_delivery_details_ob_grp_v
1457 WHERE delivery_detail_id = l_movement_transaction.picking_line_detail_id;
1458 END IF;
1459
1460 IF (l_mvt_stat_status IS NOT NULL
1461 AND l_mvt_stat_status = 'FORDISP')
1462 THEN
1463 l_movement_transaction.movement_id := null;
1464 END IF;
1465
1466 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
1467 (p_api_version_number => 1.0
1468 ,p_init_msg_list => FND_API.G_FALSE
1469 ,x_movement_transaction => l_movement_transaction
1470 ,x_msg_count => x_msg_count
1471 ,x_msg_data => x_msg_data
1472 ,x_return_status => l_insert_status
1473 );
1474
1475 --yawang fix bug 2268875
1476 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
1477 THEN
1478 l_movement_id := l_movement_transaction.movement_id;
1479 g_records_inserted := g_records_inserted +1;
1480
1481 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1482 THEN
1483 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1484 , G_MODULE_NAME || l_api_name
1485 ||'.Created mvt id is '||l_movement_id
1486 ,'debug msg');
1487 END IF;
1488 END IF;
1489 END IF; --<end of IF from Mvt_Stats_Util_Info >
1490 END IF; --< end of IF from Get_SO_Details >
1491 END IF; --< end of IF from l_insert_flag = 'Y' >
1492 CLOSE ref_crsr;
1493 END IF; -- ref_crsr
1494 END LOOP l_inner;
1495 CLOSE setup_crsr;
1496
1497 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
1498 THEN
1499 l_movement_transaction.movement_id := l_movement_id;
1500
1501 --If the dispatch is cross legal entity, then set the status to "DISPPROCESSED"
1502 --used in update so transaction(wsh table)
1503 IF (l_so_le_id IS NOT NULL
1504 AND l_so_le_id <> l_movement_transaction.entity_org_id)
1505 THEN
1506 l_cross_le_status := 'DISPPROCESSED';
1507 ELSE
1508 l_cross_le_status := 'REGULAR';
1509 END IF;
1510
1511 IF l_need_create_kit = 'Y'
1512 THEN
1513 IF l_kit_record_status = 'N'
1514 THEN
1515 INV_MGD_MVT_SO_MDTR.Update_KIT_SO_Transactions
1516 ( p_movement_id => l_movement_transaction.movement_id
1517 , p_delivery_detail_id => l_movement_transaction.picking_line_detail_id
1518 , p_link_to_line_id => l_link_to_line_id
1519 , p_status => l_cross_le_status
1520 , x_return_status => l_return_status
1521 );
1522 END IF;
1523 ELSE
1524 INV_MGD_MVT_SO_MDTR.Update_SO_Transactions
1525 ( p_movement_transaction => l_movement_transaction
1526 , p_status => l_cross_le_status
1527 , x_return_status => l_return_status
1528 );
1529 END IF;
1530
1531 COMMIT;
1532 ELSE
1533 ROLLBACK TO SAVEPOINT SO_Transaction;
1534 END IF;
1535
1536 g_records_processed := g_records_processed +1;
1537 l_movement_transaction := p_movement_transaction;
1538 END LOOP l_outer;
1539 CLOSE so_crsr;
1540 END IF;
1541
1542 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1543 THEN
1544 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1545 , G_MODULE_NAME || l_api_name || '.end'
1546 ,'exit procedure'
1547 );
1548 END IF;
1549
1550 EXCEPTION
1551 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1553 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
1554
1555 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1556 THEN
1557 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1558 , G_MODULE_NAME || l_api_name||'. Unexpected exception'
1559 , l_error
1560 );
1561 END IF;
1562
1563 WHEN NO_DATA_FOUND THEN
1564 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1565 THEN
1566 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1567 , G_MODULE_NAME || l_api_name||'. No data found exception'
1568 , l_error
1569 );
1570 END IF;
1571 RAISE;
1572
1573 WHEN OTHERS THEN
1574 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1575 THEN
1576 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1577 , 'Others exception in '||l_api_name
1578 );
1579 END IF;
1580
1581 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1582 THEN
1583 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1584 , G_MODULE_NAME || l_api_name||'. Others exception'
1585 , l_error
1586 );
1587 END IF;
1588 RAISE;
1589
1590 END Process_SO_Transaction;
1591
1592 --========================================================================
1593 -- PROCEDURE : Process_Triangulation_Txn PRIVATE
1594 -- PARAMETERS: x_return_status status flag
1595 -- p_legal_entity_id Legal Entity ID
1596 -- p_start_date Transaction start date
1597 -- p_end_date Transaction end date
1598 -- p_transaction type SO
1599 -- COMMENT :
1600 -- This processes all the SO triangulation transactions (create
1601 -- transaction in one legal entity and pick release in another
1602 -- legal entity of different country) for the specified legal
1603 -- entity that have a transaction date within the specified
1604 -- date range.
1605 -- This procedure will create arrival record only for the
1606 -- creation side of cross legal entity transactions for invoice
1607 -- based triangulation mode. The pick release side will be taken
1608 -- care of by the regular process_so_transaction.
1609 --========================================================================
1610
1611 PROCEDURE Process_Triangulation_Txn
1612 ( p_movement_transaction IN
1613 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1614 , p_start_date IN DATE
1615 , p_end_date IN DATE
1616 , p_transaction_type IN VARCHAR2
1617 , x_return_status OUT NOCOPY VARCHAR2
1618 )
1619 IS
1620 l_api_name CONSTANT VARCHAR2(30) := 'Process_Triangulation_Txn';
1621 l_error VARCHAR2(600);
1622 sot_crsr INV_MGD_MVT_DATA_STR.soCurTyp;
1623 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1624 l_movement_transaction2 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1625 l_movement_transaction_outer INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1626 l_shipment_transaction INV_MGD_MVT_DATA_STR.Shipment_Transaction_Rec_Type;
1627 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
1628 l_stat_typ_transaction2 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
1629 setup_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
1630 ref_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
1631 l_insert_flag VARCHAR2(1);
1632 x_msg_count NUMBER;
1633 x_msg_data VARCHAR2(2000);
1634 l_insert_status VARCHAR2(10);
1635 l_movement_id NUMBER;
1636 l_movement_id2 NUMBER;
1637
1638 -- Bug: 5741580. Added variable l_trans_date to store trabsaction date
1639 l_trans_date DATE;
1640
1641 l_shipping_le_id NUMBER;
1642 l_le_territory_code VARCHAR2(10);
1643 l_customer_terri_code VARCHAR2(10);
1644 l_return_status VARCHAR2(1);
1645
1646 --Added for bug4185582, 4238563
1647 l_need_create_kit VARCHAR2(1);
1648 l_kit_record_status VARCHAR2(1);
1649 l_parent_item_type_code VARCHAR2(30);
1650 l_shipped_qty NUMBER;
1651 l_item_type_code VARCHAR2(30);
1652 l_link_to_line_id NUMBER;
1653 l_line_id NUMBER;
1654
1655 CURSOR l_parent
1656 IS
1657 SELECT
1658 item_type_code
1659 , NVL(shipped_quantity, fulfilled_quantity)
1660 FROM
1661 oe_order_lines_all
1662 WHERE line_id = l_link_to_line_id;
1663
1664 CURSOR l_order_currency
1665 IS
1666 SELECT
1667 transactional_curr_code
1668 , conversion_rate
1669 , conversion_type_code
1670 , conversion_rate_date
1671 FROM oe_order_headers_all
1672 WHERE header_id = l_movement_transaction2.order_header_id;
1673
1674 CURSOR bill_to_site IS
1675 SELECT
1676 bill_to_site_use_id
1677 FROM
1678 hz_cust_site_uses_all
1679 WHERE site_use_id = l_movement_transaction.ship_to_site_use_id;
1680 BEGIN
1681 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1682 THEN
1683 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1684 , G_MODULE_NAME || l_api_name || '.begin'
1685 ,'enter procedure'
1686 );
1687 END IF;
1688
1689 x_return_status := FND_API.G_RET_STS_SUCCESS;
1690 l_movement_transaction := p_movement_transaction;
1691
1692 -- Call the transaction proxy which processes all the transactions.
1693 INV_MGD_MVT_SO_MDTR.Get_Triangulation_Txns
1694 ( sot_crsr => sot_crsr
1695 , p_movement_transaction => l_movement_transaction
1696 , p_start_date => p_start_date
1697 , p_end_date => p_end_date
1698 , x_return_status => l_return_status);
1699
1700 IF l_return_status = 'Y' THEN
1701 <<l_outer>>
1702 LOOP
1703 --Reset movement record for each picking line
1704 l_movement_transaction := p_movement_transaction;
1705 l_movement_id := null;
1706
1707 FETCH sot_crsr
1708 INTO l_movement_transaction.picking_line_detail_id
1709 , l_movement_transaction.organization_id
1710 , l_movement_transaction.ship_to_site_use_id
1711 , l_movement_transaction.transaction_date
1712 , l_movement_transaction.order_line_id
1713 , l_movement_transaction.order_number
1714 , l_item_type_code
1715 , l_link_to_line_id;
1716
1717 EXIT WHEN sot_crsr%NOTFOUND;
1718
1719 SAVEPOINT SOT_Transaction;
1720
1721 --Timezone support, convert server transaction date to legal entity timezone
1722 l_movement_transaction.transaction_date :=
1723 INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Server
1724 ( p_trxn_date => l_movement_transaction.transaction_date
1725 , p_le_id => l_movement_transaction.entity_org_id
1726 );
1727
1728 --Bug:5741580, Actual transaction date stored in temperory variable
1729 l_trans_date := l_movement_transaction.transaction_date;
1730
1731 --Find out the legal entity of the shipping warehouse
1732 l_shipping_le_id := INV_MGD_MVT_UTILS_PKG.Get_Shipping_Legal_Entity
1733 (p_warehouse_id => l_movement_transaction.organization_id);
1734
1735 --Only process those SO which cross legal entities
1736 IF (l_shipping_le_id IS NOT NULL
1737 AND l_shipping_le_id <> l_movement_transaction.entity_org_id)
1738 THEN
1739 --Initialize movement record
1740 INV_MGD_MVT_STATS_PVT.Init_Movement_Record
1741 (x_movement_transaction => l_movement_transaction);
1742
1743 --Find out territory code for legal entity
1744 l_le_territory_code :=
1745 INV_MGD_MVT_UTILS_PKG.Get_LE_Location
1746 (p_le_id => l_movement_transaction.entity_org_id);
1747
1748 --Find out territory code for customer
1749 l_customer_terri_code :=
1750 INV_MGD_MVT_UTILS_PKG.Get_Site_Location
1751 (p_site_use_id => l_movement_transaction.ship_to_site_use_id);
1752
1753 --For creating Arrival SO record
1754 --Find out dispatch territory code and destination territory code
1755 l_movement_transaction.dispatch_territory_code :=
1756 INV_MGD_MVT_UTILS_PKG.Get_LE_Location
1757 (p_le_id => l_shipping_le_id);
1758
1759 l_movement_transaction.destination_territory_code := l_le_territory_code;
1760
1761 INV_MGD_MVT_SETUP_MDTR.Get_Setup_Context
1762 ( p_legal_entity_id => l_movement_transaction.entity_org_id
1763 , p_movement_transaction => l_movement_transaction
1764 , x_return_status => l_return_status
1765 , setup_crsr => setup_crsr
1766 );
1767
1768 --Back up the movement statistics record
1769 l_movement_transaction_outer := l_movement_transaction;
1770
1771 <<l_inner>>
1772 LOOP
1773 --Reset movement transaction record
1774 l_movement_transaction := l_movement_transaction_outer;
1775
1776 FETCH setup_crsr INTO
1777 l_movement_transaction.zone_code
1778 , l_movement_transaction.usage_type
1779 , l_movement_transaction.stat_type
1780 , l_stat_typ_transaction.reference_period_rule
1781 , l_stat_typ_transaction.pending_invoice_days
1782 , l_stat_typ_transaction.prior_invoice_days
1783 , l_stat_typ_transaction.triangulation_mode;
1784
1785 EXIT l_inner WHEN setup_crsr%NOTFOUND;
1786
1787 --Only attempt to create an Arrival record for cross legal entity SO
1788 --when the triangulation mode is invoice based
1789 --Also create record for Belgium when the SO is created in Belgium for
1790 --Beilgium customer (exception case, see Belgium INTRASTAT guide 14.4)
1791 IF (NVL(l_stat_typ_transaction.triangulation_mode,'INVOICE_BASED') = 'INVOICE_BASED'
1792 OR (l_le_territory_code = l_movement_transaction.destination_territory_code
1793 AND l_le_territory_code = 'BE'))
1794 THEN
1795 IF NVL(l_stat_typ_transaction.reference_period_rule,'SHIPMENT_BASED')
1796 = 'INVOICE_BASED'
1797 THEN
1798 IF l_movement_transaction.document_source_type IS NULL
1799 THEN
1800 l_movement_transaction.document_source_type := 'SO';
1801 END IF;
1802
1803 --Bug: 5440432. Change the movement type to 'A' before invoice creation.
1804 --since this procedure is creating an Arrival record for cross legal entity SO
1805 l_movement_transaction.movement_type := 'A';
1806
1807 l_line_id := l_movement_transaction.order_line_id;
1808
1809 --For included item, use parent invoice info
1810 IF l_item_type_code = 'INCLUDED'
1811 THEN
1812 l_movement_transaction.order_line_id := l_link_to_line_id;
1813 END IF;
1814
1815 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
1816 ( p_stat_typ_transaction => l_stat_typ_transaction
1817 , x_movement_transaction => l_movement_transaction
1818 );
1819
1820 INV_MGD_MVT_FIN_MDTR. Get_Reference_Date
1821 ( p_stat_typ_transaction => l_stat_typ_transaction
1822 , x_movement_transaction => l_movement_transaction
1823 );
1824
1825 l_movement_transaction.transaction_date :=
1826 l_movement_transaction.reference_date;
1827
1828 --Set back the included item line id
1829 l_movement_transaction.order_line_id := l_line_id;
1830 END IF;
1831
1832 INV_MGD_MVT_SETUP_MDTR.Get_Reference_Context
1833 ( p_legal_entity_id => l_movement_transaction.entity_org_id
1834 , p_start_date => p_start_date
1835 , p_end_date => p_end_date
1836 , p_transaction_type => p_transaction_type
1837 , p_movement_transaction => l_movement_transaction
1838 , x_return_status => l_return_status
1839 , ref_crsr => ref_crsr
1840 );
1841
1842 --Reset the movement_id before fetching the transaction
1843 l_movement_transaction.movement_id := NULL;
1844
1845 -- Bug:5920143. Added new parameter include_establishments in result.
1846 FETCH ref_crsr INTO
1847 l_movement_transaction.zone_code
1848 , l_movement_transaction.usage_type
1849 , l_movement_transaction.stat_type
1850 , l_stat_typ_transaction.start_period_name
1851 , l_stat_typ_transaction.end_period_name
1852 , l_stat_typ_transaction.period_set_name
1853 , l_stat_typ_transaction.period_type
1854 , l_stat_typ_transaction.weight_uom_code
1855 , l_stat_typ_transaction.conversion_type
1856 , l_stat_typ_transaction.attribute_rule_set_code
1857 , l_stat_typ_transaction.alt_uom_rule_set_code
1858 , l_stat_typ_transaction.start_date
1859 , l_stat_typ_transaction.end_date
1860 , l_stat_typ_transaction.category_set_id
1861 , l_movement_transaction.set_of_books_period
1862 , l_stat_typ_transaction.gl_currency_code
1863 , l_movement_transaction.gl_currency_code
1864 , l_stat_typ_transaction.conversion_option
1865 , l_stat_typ_transaction.triangulation_mode
1866 , l_stat_typ_transaction.reference_period_rule
1867 , l_stat_typ_transaction.pending_invoice_days
1868 , l_stat_typ_transaction.prior_invoice_days
1869 , l_stat_typ_transaction.returns_processing
1870 , l_stat_typ_transaction.kit_method
1871 , l_stat_typ_transaction.include_establishments;
1872
1873 IF ref_crsr%NOTFOUND
1874 THEN
1875 --the transaction is not inside of start period and end period
1876 --so not create transaction
1877 CLOSE ref_crsr;
1878 ELSE
1879 --Fix bug 4185582,4238563, find out if parent is KIT
1880 OPEN l_parent;
1881 FETCH l_parent INTO
1882 l_parent_item_type_code
1883 , l_shipped_qty;
1884 CLOSE l_parent;
1885
1886 --Find out if we need to treat this develivery as kit
1887 IF (l_item_type_code = 'INCLUDED'
1888 AND l_parent_item_type_code = 'KIT'
1889 AND l_shipped_qty IS NOT NULL
1890 AND l_stat_typ_transaction.kit_method = 'KIT')
1891 THEN
1892 l_need_create_kit := 'Y';
1893
1894 --Find out if there is already a movement record created for this kit
1895 --if no, set l_kit_record to 'N' else set to 'Y'
1896 l_kit_record_status := INV_MGD_MVT_SO_MDTR.Get_KIT_Triangulation_Status
1897 (p_delivery_detail_id => l_movement_transaction.picking_line_detail_id);
1898 ELSE
1899 l_need_create_kit := 'N';
1900 END IF;
1901 --End of fix bug 4185582
1902
1903 --If kit record has been created, do not need to create again
1904 IF (l_need_create_kit = 'Y' AND l_kit_record_status = 'Y')
1905 THEN
1906 l_insert_flag := 'N';
1907 ELSE
1908 l_insert_flag := INV_MGD_MVT_SETUP_MDTR.Process_Setup_Context
1909 ( p_movement_transaction => l_movement_transaction);
1910 END IF;
1911
1912 -- Process the SO transaction
1913 IF l_insert_flag = 'Y'
1914 THEN
1915 INV_MGD_MVT_SO_MDTR.Get_SO_Details
1916 (x_movement_transaction => l_movement_transaction
1917 ,x_return_status => l_return_status
1918 );
1919
1920 IF l_need_create_kit = 'Y'
1921 THEN
1922 INV_MGD_MVT_SO_MDTR.Get_KIT_SO_Details
1923 ( p_link_to_line_id => l_link_to_line_id
1924 , x_movement_transaction => l_movement_transaction
1925 );
1926 END IF;
1927
1928 --Clear any existing movement id since this procedure is creating new record
1929 --without parent
1930 l_movement_transaction.movement_id := null;
1931
1932 --Change the movement type to 'A' since this procedure is creating
1933 --an Arrival record for cross legal entity SO
1934 l_movement_transaction.movement_type := 'A';
1935
1936 IF l_return_status = 'Y'
1937 THEN
1938 INV_MGD_MVT_UTILS_PKG.Mvt_Stats_Util_Info
1939 (p_stat_typ_transaction => l_stat_typ_transaction
1940 ,x_movement_transaction => l_movement_transaction
1941 ,x_return_status => l_return_status
1942 );
1943
1944 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1945 THEN
1946 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1947 THEN
1948 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1949 , G_MODULE_NAME || l_api_name
1950 || '.Failed when call mvt_stats_util_info'
1951 ,'Failed'
1952 );
1953 END IF;
1954 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1955 ELSE
1956
1957 --Get bill to site id,performance reason to get bill to site id here
1958 --instead of in initial get_triangulation_transaction
1959 OPEN bill_to_site;
1960 FETCH bill_to_site INTO
1961 l_movement_transaction.bill_to_site_use_id;
1962 CLOSE bill_to_site;
1963
1964 l_movement_transaction.customer_vat_number :=
1965 INV_MGD_MVT_UTILS_PKG.Get_Cust_VAT_Number
1966 (l_movement_transaction.bill_to_site_use_id);
1967
1968 IF l_movement_transaction.invoice_id IS NULL
1969 THEN
1970 l_movement_transaction.invoice_quantity := NULL;
1971 l_movement_transaction.financial_document_flag := 'MISSING';
1972 ELSE
1973 l_movement_transaction.financial_document_flag := 'PROCESSED_INCLUDED';
1974 END IF;
1975
1976 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
1977 (p_api_version_number => 1.0
1978 ,p_init_msg_list => FND_API.G_FALSE
1979 ,x_movement_transaction => l_movement_transaction
1980 ,x_msg_count => x_msg_count
1981 ,x_msg_data => x_msg_data
1982 ,x_return_status => l_insert_status
1983 );
1984
1985 --yawang fix bug 2268875
1986 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
1987 THEN
1988 l_movement_id := l_movement_transaction.movement_id;
1989 g_records_inserted := g_records_inserted +1;
1990 END IF;
1991 END IF;
1992 END IF;
1993 END IF;
1994
1995 --Start here: Create second SO with movement type of D,this one follows
1996 --the regular invoice from the legal entity which creates this SO to
1997 --the customer
1998 l_movement_transaction2 := l_movement_transaction;
1999 l_stat_typ_transaction2 := l_stat_typ_transaction;
2000
2001 --Fix bug 5659898, initialize to 'Y' to be checked below. Without this
2002 --initialization, this variable may inherit value 'S' from upper part
2003 --code, so it will never pass below checking and hence no dispatch
2004 --SO will be created
2005 l_return_status := 'Y';
2006
2007 --Find out dispatch territory code and destination territory code
2008 l_movement_transaction2.dispatch_territory_code := l_le_territory_code;
2009 l_movement_transaction2.destination_territory_code := l_customer_terri_code;
2010
2011 --If kit record has been created, do not need to create again
2012 IF (l_need_create_kit = 'Y' AND l_kit_record_status = 'Y')
2013 THEN
2014 l_insert_flag := 'N';
2015 ELSE
2016 l_insert_flag := INV_MGD_MVT_SETUP_MDTR.Process_Setup_Context
2017 ( p_movement_transaction => l_movement_transaction2);
2018 END IF;
2019
2020 -- Process the SO transaction
2021 IF l_insert_flag = 'Y'
2022 THEN
2023 --Clear invoice information
2024 l_movement_transaction2.invoice_id := null;
2025 l_movement_transaction2.invoice_line_ext_value := null;
2026 l_movement_transaction2.invoice_quantity := null;
2027 l_movement_transaction2.invoice_unit_price := null;
2028 l_movement_transaction2.distribution_line_number := null;
2029 l_movement_transaction2.currency_code := null;
2030 l_movement_transaction2.currency_conversion_rate := null;
2031 l_movement_transaction2.currency_conversion_type := null;
2032 l_movement_transaction2.currency_conversion_date := null;
2033 l_movement_transaction2.invoice_batch_id := null;
2034 l_movement_transaction2.invoice_date_reference := null;
2035
2036 --Bug 5741580. Reset following parameters.
2037 l_movement_transaction2.movement_status := 'O';
2038 l_movement_transaction2.period_name := null;
2039 l_movement_transaction2.transaction_date := l_trans_date;
2040
2041 --Before call invoice pkg to get invoice currency conversion info, first
2042 --default from order header. If movement is is not null, don't not need
2043 --to call get_so_details only for currency info, since movement_transaction2
2044 --is a copied record, other info is already populated
2045 IF l_movement_transaction2.movement_id IS NOT NULL
2046 THEN
2047 OPEN l_order_currency;
2048 FETCH l_order_currency INTO
2049 l_movement_transaction2.currency_code
2050 , l_movement_transaction2.currency_conversion_rate
2051 , l_movement_transaction2.currency_conversion_type
2052 , l_movement_transaction2.currency_conversion_date;
2053 CLOSE l_order_currency;
2054 ELSE
2055 -- when create the first so, the insert flag is 'N', no so info populated
2056 INV_MGD_MVT_SO_MDTR.Get_SO_Details
2057 (x_movement_transaction => l_movement_transaction2
2058 ,x_return_status => l_return_status
2059 );
2060
2061 IF l_need_create_kit = 'Y'
2062 THEN
2063 INV_MGD_MVT_SO_MDTR.Get_KIT_SO_Details
2064 ( p_link_to_line_id => l_link_to_line_id
2065 , x_movement_transaction => l_movement_transaction2
2066 );
2067 END IF;
2068 END IF;
2069
2070 --Clear any existing movement id since this procedure is creating new record
2071 --without parent
2072 l_movement_transaction2.movement_id := null;
2073
2074 --Change the movement type to 'D' since this procedure is creating
2075 --an Dispatch record from legal entity where initiate this SO to the customer
2076 l_movement_transaction2.movement_type := 'D';
2077
2078 IF l_return_status = 'Y'
2079 THEN
2080 --Bug 5741580. Again calculate the Invoice and reference date for
2081 -- SO Dispatch triangulation transaction and assigne it to
2082 -- to Transaction Date.
2083 IF NVL(l_stat_typ_transaction2.reference_period_rule,'SHIPMENT_BASED')
2084 = 'INVOICE_BASED'
2085 THEN
2086 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
2087 ( p_stat_typ_transaction => l_stat_typ_transaction2
2088 , x_movement_transaction => l_movement_transaction2
2089 );
2090 INV_MGD_MVT_FIN_MDTR.Get_Reference_Date
2091 ( p_stat_typ_transaction => l_stat_typ_transaction2
2092 , x_movement_transaction => l_movement_transaction2
2093 );
2094 l_movement_transaction2.transaction_date :=
2095 l_movement_transaction2.reference_date;
2096 END IF;
2097
2098 INV_MGD_MVT_UTILS_PKG.Mvt_Stats_Util_Info
2099 (p_stat_typ_transaction => l_stat_typ_transaction2
2100 ,x_movement_transaction => l_movement_transaction2
2101 ,x_return_status => l_return_status
2102 );
2103
2104 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2105 THEN
2106 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2107 THEN
2108 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2109 , G_MODULE_NAME || l_api_name
2110 || '.Failed when call mvt_stats_util_info -second SO'
2111 ,'Failed'
2112 );
2113 END IF;
2114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2115 ELSE
2116
2117 /*l_movement_transaction2.customer_vat_number :=
2118 INV_MGD_MVT_UTILS_PKG.Get_Cust_VAT_Number
2119 (l_movement_transaction2.bill_to_site_use_id);*/
2120
2121 IF l_movement_transaction2.invoice_id IS NULL
2122 THEN
2123 l_movement_transaction2.invoice_quantity := NULL;
2124 l_movement_transaction2.financial_document_flag := 'MISSING';
2125 ELSE
2126 l_movement_transaction2.financial_document_flag := 'PROCESSED_INCLUDED';
2127 END IF;
2128
2129 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
2130 (p_api_version_number => 1.0
2131 ,p_init_msg_list => FND_API.G_FALSE
2132 ,x_movement_transaction => l_movement_transaction2
2133 ,x_msg_count => x_msg_count
2134 ,x_msg_data => x_msg_data
2135 ,x_return_status => l_insert_status
2136 );
2137
2138 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
2139 THEN
2140 l_movement_id2 := l_movement_transaction2.movement_id;
2141 g_records_inserted := g_records_inserted +1;
2142 END IF;
2143 END IF;
2144 END IF;
2145 END IF;
2146 CLOSE ref_crsr;
2147 END IF;
2148 END IF;
2149 END LOOP l_inner;
2150 CLOSE setup_crsr;
2151
2152 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
2153 THEN
2154 l_movement_transaction.movement_id := l_movement_id;
2155
2156 IF l_need_create_kit = 'Y'
2157 THEN
2158 IF l_kit_record_status = 'N'
2159 THEN
2160 INV_MGD_MVT_SO_MDTR.Update_KIT_SO_Transactions
2161 ( p_movement_id => l_movement_transaction.movement_id
2162 , p_delivery_detail_id => l_movement_transaction.picking_line_detail_id
2163 , p_link_to_line_id => l_link_to_line_id
2164 , p_status => 'ARRIVALPROCESSED'
2165 , x_return_status => l_return_status
2166 );
2167 END IF;
2168 ELSE
2169 INV_MGD_MVT_SO_MDTR.Update_SO_Transactions
2170 ( p_movement_transaction => l_movement_transaction
2171 , p_status => 'ARRIVALPROCESSED'
2172 , x_return_status => l_return_status
2173 );
2174 END IF;
2175
2176 COMMIT;
2177 ELSE
2178 ROLLBACK TO SAVEPOINT SOT_Transaction;
2179 END IF;
2180
2181 g_records_processed := g_records_processed +1;
2182 l_movement_transaction := p_movement_transaction;
2183 END IF;
2184 END LOOP l_outer;
2185 CLOSE sot_crsr;
2186 END IF;
2187
2188 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2189 THEN
2190 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2191 , G_MODULE_NAME || l_api_name || '.end'
2192 ,'exit procedure'
2193 );
2194 END IF;
2195
2196 EXCEPTION
2197 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2199 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
2200
2201 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2202 THEN
2203 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2204 , G_MODULE_NAME || l_api_name||'. Unexpected exception'
2205 , l_error
2206 );
2207 END IF;
2208
2209 WHEN NO_DATA_FOUND THEN
2210 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2211 THEN
2212 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2213 , G_MODULE_NAME || l_api_name||'. No data found exception'
2214 , l_error
2215 );
2216 END IF;
2217 RAISE;
2218
2219 WHEN OTHERS THEN
2220 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2221 THEN
2222 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2223 , 'Others exception in '||l_api_name
2224 );
2225 END IF;
2226
2227 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2228 THEN
2229 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2230 , G_MODULE_NAME || l_api_name||'. Others exception'
2231 , l_error
2232 );
2233 END IF;
2234 RAISE;
2235
2236 END Process_Triangulation_Txn;
2237
2238 --========================================================================
2239 -- PROCEDURE : Process_IO_Arrival_Txn PRIVATE
2240 -- PARAMETERS: x_return_status status flag
2241 -- p_legal_entity_id Legal Entity ID
2242 -- p_start_date Transaction start date
2243 -- p_end_date Transaction end date
2244 -- p_transaction type SO
2245 -- COMMENT :
2246 -- This processes all the internal order arrival transactions
2247 -- for the specified legal entity that have a transaction date
2248 -- within the specified date range.
2249 -- This procedure will create arrival record only for the
2250 -- receiving side of internal order transactions. The dispatch
2251 -- side will be taken care by the regular process_so_transaction.
2252 --========================================================================
2253
2254 PROCEDURE Process_IO_Arrival_Txn
2255 ( p_movement_transaction IN
2256 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
2257 , p_start_date IN DATE
2258 , p_end_date IN DATE
2259 , p_transaction_type IN VARCHAR2
2260 , x_return_status OUT NOCOPY VARCHAR2
2261 )
2262 IS
2263 -- Declare the REF Cursor
2264 io_arrival_crsr INV_MGD_MVT_DATA_STR.soCurTyp;
2265 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
2266 l_movement_transaction_outer INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
2267 l_shipment_transaction INV_MGD_MVT_DATA_STR.Shipment_Transaction_Rec_Type;
2268 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
2269 setup_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
2270 ref_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
2271 l_insert_flag VARCHAR2(1);
2272 x_msg_count NUMBER;
2273 x_msg_data VARCHAR2(2000);
2274 l_insert_status VARCHAR2(10);
2275 l_shipping_org_id NUMBER;
2276 l_movement_id NUMBER;
2277 l_cross_le_status VARCHAR2(20);
2278 l_mvt_stat_status VARCHAR2(20);
2279 l_subinv_code VARCHAR2(10);
2280 l_subinv_terr_code VARCHAR2(2);
2281 l_recv_org_terr_code VARCHAR2(2);
2282 l_le_terr_code VARCHAR2(2);
2283 l_shipping_org_terri_code VARCHAR2(10);
2284 l_req_number po_requisition_headers_all.segment1%TYPE;
2285 l_return_status VARCHAR2(1);
2286 l_api_name CONSTANT VARCHAR2(30) := 'Process_IO_Arrival_Txn';
2287 l_error VARCHAR2(600);
2288
2289 --Fix bug 3364811, move order lines and delivery table out of
2290 --io_arrival_crsr so that no duplicate rcv transactions picked
2291 --in outer loop.
2292 --This new cursor is created to fetch shipping org etc info
2293 CURSOR shipping_org IS
2294 SELECT
2295 MAX(oola.line_id)
2296 , MAX(wdd.delivery_detail_id) picking_line_detail_id
2297 , MAX(wdd.organization_id) shipping_organization_id
2298 FROM
2299 oe_order_lines_all oola
2300 , wsh_delivery_details_ob_grp_v wdd
2301 WHERE oola.order_source_id = 10 --combine 1st and 2nd condition to use index 11
2302 AND oola.orig_sys_document_ref = l_req_number
2303 AND oola.source_document_line_id = l_movement_transaction.requisition_line_id
2304 AND oola.line_id = wdd.source_line_id;
2305 BEGIN
2306 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2307 THEN
2308 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2309 , G_MODULE_NAME || l_api_name || '.begin'
2310 ,'enter procedure'
2311 );
2312 END IF;
2313
2314 x_return_status := FND_API.G_RET_STS_SUCCESS;
2315 l_movement_transaction := p_movement_transaction;
2316
2317 -- Call the transaction proxy which processes all the transactions.
2318 INV_MGD_MVT_PO_MDTR.Get_IO_Arrival_Txn
2319 ( io_arrival_crsr => io_arrival_crsr
2320 , p_movement_transaction => l_movement_transaction
2321 , p_start_date => p_start_date
2322 , p_end_date => p_end_date
2323 , x_return_status => l_return_status);
2324
2325 IF l_return_status = 'Y' THEN
2326 <<l_outer>>
2327 LOOP
2328 --Reset the movement record for each picking line
2329 l_movement_transaction := p_movement_transaction;
2330 l_movement_id := NULL;
2331
2332 FETCH io_arrival_crsr
2333 INTO l_movement_transaction.rcv_transaction_id
2334 , l_movement_transaction.transaction_date
2335 , l_movement_transaction.organization_id
2336 , l_subinv_code
2337 , l_movement_transaction.requisition_line_id
2338 , l_req_number;
2339
2340 EXIT WHEN io_arrival_crsr%NOTFOUND;
2341
2342 SAVEPOINT IO_Transaction;
2343
2344 --Timezone support, convert server transaction date to legal entity timezone
2345 l_movement_transaction.transaction_date :=
2346 INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Server
2347 ( p_trxn_date => l_movement_transaction.transaction_date
2348 , p_le_id => l_movement_transaction.entity_org_id
2349 );
2350
2351 --Fix bug 3364811,populate shipping org
2352 OPEN shipping_org;
2353 FETCH shipping_org INTO
2354 l_movement_transaction.order_line_id
2355 , l_movement_transaction.picking_line_detail_id
2356 , l_shipping_org_id;
2357 CLOSE shipping_org;
2358
2359 INV_MGD_MVT_STATS_PVT.Init_Movement_Record
2360 (x_movement_transaction => l_movement_transaction);
2361
2362 --Find out territory code
2363 l_subinv_terr_code :=
2364 INV_MGD_MVT_UTILS_PKG.Get_Subinv_Location
2365 ( p_warehouse_id => l_movement_transaction.organization_id
2366 , p_subinv_code => l_subinv_code);
2367
2368 l_recv_org_terr_code :=
2369 INV_MGD_MVT_UTILS_PKG.Get_Org_Location
2370 (p_warehouse_id => l_movement_transaction.organization_id);
2371
2372 l_le_terr_code :=
2373 INV_MGD_MVT_UTILS_PKG.Get_LE_Location
2374 (p_le_id => l_movement_transaction.entity_org_id);
2375
2376 l_shipping_org_terri_code :=
2377 INV_MGD_MVT_UTILS_PKG.Get_Org_Location
2378 (p_warehouse_id => l_shipping_org_id);
2379
2380 l_movement_transaction.dispatch_territory_code := l_shipping_org_terri_code;
2381 l_movement_transaction.destination_territory_code :=
2382 NVL(l_subinv_terr_code, l_recv_org_terr_code);
2383
2384 -- Bug: 5920143 Validation that LE Territory Code and
2385 -- Destination Org Territory Code should be same, is commented here.
2386 -- Its added in later section.
2387 --IF l_le_terr_code = l_movement_transaction.destination_territory_code
2388 --THEN
2389 INV_MGD_MVT_SETUP_MDTR.Get_Setup_Context
2390 ( p_legal_entity_id => l_movement_transaction.entity_org_id
2391 , p_movement_transaction => l_movement_transaction
2392 , x_return_status => l_return_status
2393 , setup_crsr => setup_crsr
2394 );
2395
2396 --Back up the movement statistics record
2397 l_movement_transaction_outer := l_movement_transaction;
2398
2399 <<l_inner>>
2400 LOOP
2401 --Reset movement transaction record
2402 l_movement_transaction := l_movement_transaction_outer;
2403
2404 FETCH setup_crsr INTO
2405 l_movement_transaction.zone_code
2406 , l_movement_transaction.usage_type
2407 , l_movement_transaction.stat_type
2408 , l_stat_typ_transaction.reference_period_rule
2409 , l_stat_typ_transaction.pending_invoice_days
2410 , l_stat_typ_transaction.prior_invoice_days
2411 , l_stat_typ_transaction.triangulation_mode;
2412
2413 EXIT l_inner WHEN setup_crsr%NOTFOUND;
2414
2415 INV_MGD_MVT_SETUP_MDTR.Get_Reference_Context
2416 ( p_legal_entity_id => l_movement_transaction.entity_org_id
2417 , p_start_date => p_start_date
2418 , p_end_date => p_end_date
2419 , p_transaction_type => p_transaction_type
2420 , p_movement_transaction => l_movement_transaction
2421 , x_return_status => l_return_status
2422 , ref_crsr => ref_crsr
2423 );
2424
2425 --Reset the movement_id before fetching the transaction
2426 l_movement_transaction.movement_id := NULL;
2427
2428 -- Bug:5920143. Added new parameter include_establishments in result.
2429 FETCH ref_crsr INTO
2430 l_movement_transaction.zone_code
2431 , l_movement_transaction.usage_type
2432 , l_movement_transaction.stat_type
2433 , l_stat_typ_transaction.start_period_name
2434 , l_stat_typ_transaction.end_period_name
2435 , l_stat_typ_transaction.period_set_name
2436 , l_stat_typ_transaction.period_type
2437 , l_stat_typ_transaction.weight_uom_code
2438 , l_stat_typ_transaction.conversion_type
2439 , l_stat_typ_transaction.attribute_rule_set_code
2440 , l_stat_typ_transaction.alt_uom_rule_set_code
2441 , l_stat_typ_transaction.start_date
2442 , l_stat_typ_transaction.end_date
2443 , l_stat_typ_transaction.category_set_id
2444 , l_movement_transaction.set_of_books_period
2445 , l_stat_typ_transaction.gl_currency_code
2446 , l_movement_transaction.gl_currency_code
2447 , l_stat_typ_transaction.conversion_option
2448 , l_stat_typ_transaction.triangulation_mode
2449 , l_stat_typ_transaction.reference_period_rule
2450 , l_stat_typ_transaction.pending_invoice_days
2451 , l_stat_typ_transaction.prior_invoice_days
2452 , l_stat_typ_transaction.returns_processing
2453 , l_stat_typ_transaction.kit_method
2454 , l_stat_typ_transaction.include_establishments;
2455
2456 IF ref_crsr%NOTFOUND
2457 THEN
2458 --the transaction is not inside of start period and end period
2459 --so not create transaction
2460 CLOSE ref_crsr;
2461 ELSE
2462 -- Bug: 5920143 Validation that LE Territory Code and
2463 -- Destination Org Territory Code should be same, is needed only when
2464 -- user has selected Include Establishments as No.
2465
2466 --Only create record for organization located in the same country as legal entity
2467 IF ((l_movement_transaction.stat_type = 'ESL'
2468 AND l_movement_transaction.usage_type = 'INTERNAL')
2469 OR ((l_le_terr_code <> l_movement_transaction.destination_territory_code)
2470 AND (l_stat_typ_transaction.include_establishments = 'N')))
2471 THEN
2472 l_insert_flag := 'N';
2473 ELSE
2474 l_insert_flag := INV_MGD_MVT_SETUP_MDTR.Process_Setup_Context
2475 ( p_movement_transaction => l_movement_transaction);
2476 END IF;
2477
2478 -- Process the SO transaction
2479 IF l_insert_flag = 'Y'
2480 THEN
2481 INV_MGD_MVT_PO_MDTR.Get_IO_Arrival_Details
2482 ( x_movement_transaction => l_movement_transaction
2483 , x_return_status => l_return_status
2484 );
2485
2486 IF l_return_status = 'Y'
2487 THEN
2488 INV_MGD_MVT_UTILS_PKG.Mvt_Stats_Util_Info
2489 (p_stat_typ_transaction => l_stat_typ_transaction
2490 ,x_movement_transaction => l_movement_transaction
2491 ,x_return_status => l_return_status
2492 );
2493
2494 IF l_return_status = FND_API.G_RET_STS_SUCCESS
2495 THEN
2496
2497 l_movement_transaction.customer_vat_number :=
2498 INV_MGD_MVT_UTILS_PKG.Get_Cust_VAT_Number
2499 (l_movement_transaction.bill_to_site_use_id);
2500
2501 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
2502 (p_api_version_number => 1.0
2503 ,p_init_msg_list => FND_API.G_FALSE
2504 ,x_movement_transaction => l_movement_transaction
2505 ,x_msg_count => x_msg_count
2506 ,x_msg_data => x_msg_data
2507 ,x_return_status => l_insert_status
2508 );
2509
2510 --yawang fix bug 2268875
2511 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
2512 THEN
2513 l_movement_id := l_movement_transaction.movement_id;
2514 g_records_inserted := g_records_inserted +1;
2515 END IF;
2516 ELSE
2517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2518 END IF;
2519 END IF; --< end of if from Get_IO_Arrival_Details>
2520 END IF; --< end of if from l_insert_flag>
2521 CLOSE ref_crsr;
2522 END IF;
2523 END LOOP l_inner;
2524 CLOSE setup_crsr;
2525 --END IF;
2526
2527 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
2528 THEN
2529 l_movement_transaction.movement_id := l_movement_id;
2530 /* 7165989 - Pass mvt_stat_status as NULL for non-RMA triangulation txns */
2531 INV_MGD_MVT_PO_MDTR.Update_PO_Transactions
2532 ( p_movement_transaction => l_movement_transaction
2533 , p_mvt_stat_status => NULL
2534 , x_return_status => l_return_status
2535 );
2536
2537 COMMIT;
2538 ELSE
2539 ROLLBACK TO SAVEPOINT IO_Transaction;
2540 END IF;
2541
2542 g_records_processed := g_records_processed +1;
2543 l_movement_transaction := p_movement_transaction;
2544 END LOOP l_outer;
2545 CLOSE io_arrival_crsr;
2546 END IF;
2547
2548 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2549 THEN
2550 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2551 , G_MODULE_NAME || l_api_name || '.end'
2552 ,'exit procedure'
2553 );
2554 END IF;
2555 EXCEPTION
2556 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2558 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
2559
2560 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2561 THEN
2562 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2563 , G_MODULE_NAME || l_api_name||'. Unexpected exception'
2564 , l_error
2565 );
2566 END IF;
2567
2568 WHEN NO_DATA_FOUND THEN
2569 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2570 THEN
2571 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2572 , G_MODULE_NAME || l_api_name||'. No data found exception'
2573 , l_error
2574 );
2575 END IF;
2576 RAISE;
2577
2578 WHEN OTHERS THEN
2579 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2580 THEN
2581 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2582 , 'Others exception in '||l_api_name
2583 );
2584 END IF;
2585
2586 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2587 THEN
2588 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2589 , G_MODULE_NAME || l_api_name||'. Others exception'
2590 , l_error
2591 );
2592 END IF;
2593 RAISE;
2594
2595 END Process_IO_Arrival_Txn;
2596
2597 --========================================================================
2598 -- PROCEDURE : Update_PO_With_RTV PRIVATE
2599 -- PARAMETERS: x_return_status return status
2600 -- x_mvt_rtv_transaction IN OUT Movement Statistics Record
2601 -- COMMENT : pocedure that process RTV transaction depend on if
2602 -- the parent PO is closed
2603 --=========================================================================
2604 PROCEDURE Update_PO_With_RTV
2605 ( x_mvt_rtv_transaction IN OUT NOCOPY
2606 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
2607 , x_return_status OUT NOCOPY VARCHAR2
2608 )
2609 IS
2610 l_parent_mvt_id NUMBER;
2611 l_parent_mvt_status VARCHAR2(30);
2612 l_parent_mvt_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
2613 x_msg_count NUMBER;
2614 x_msg_data VARCHAR2(2000);
2615 l_return_status VARCHAR2(1);
2616 l_api_name CONSTANT VARCHAR2(30) := 'Update_PO_With_RTV';
2617 l_error VARCHAR2(600);
2618
2619 CURSOR parent_status IS
2620 SELECT
2621 mms.movement_id
2622 , mms.movement_status
2623 FROM
2624 mtl_movement_statistics mms
2625 , rcv_transactions rt
2626 WHERE mms.rcv_transaction_id = rt.parent_transaction_id
2627 AND rt.transaction_id = x_mvt_rtv_transaction.rcv_transaction_id
2628 AND mms.entity_org_id = x_mvt_rtv_transaction.entity_org_id
2629 AND mms.zone_code = x_mvt_rtv_transaction.zone_code
2630 AND mms.usage_type = x_mvt_rtv_transaction.usage_type
2631 AND mms.stat_type = x_mvt_rtv_transaction.stat_type
2632 AND mms.movement_type <> 'AA';
2633
2634
2635 CURSOR parent_mvt_record IS
2636 SELECT
2637 movement_id
2638 , organization_id
2639 , entity_org_id
2640 , movement_type
2641 , movement_status
2642 , transaction_date
2643 , last_update_date
2644 , last_updated_by
2645 , creation_date
2646 , created_by
2647 , last_update_login
2648 , document_source_type
2649 , creation_method
2650 , document_reference
2651 , document_line_reference
2652 , document_unit_price
2653 , document_line_ext_value
2654 , receipt_reference
2655 , shipment_reference
2656 , shipment_line_reference
2657 , pick_slip_reference
2658 , customer_name
2659 , customer_number
2660 , customer_location
2661 , transacting_from_org
2662 , transacting_to_org
2663 , vendor_name
2664 , vendor_number
2665 , vendor_site
2666 , bill_to_name
2667 , bill_to_number
2668 , bill_to_site
2669 , po_header_id
2670 , po_line_id
2671 , po_line_location_id
2672 , order_header_id
2673 , order_line_id
2674 , picking_line_id
2675 , shipment_header_id
2676 , shipment_line_id
2677 , ship_to_customer_id
2678 , ship_to_site_use_id
2679 , bill_to_customer_id
2680 , bill_to_site_use_id
2681 , vendor_id
2682 , vendor_site_id
2683 , from_organization_id
2684 , to_organization_id
2685 , parent_movement_id
2686 , inventory_item_id
2687 , item_description
2688 , item_cost
2689 , transaction_quantity
2690 , transaction_uom_code
2691 , primary_quantity
2692 , invoice_batch_id
2693 , invoice_id
2694 , customer_trx_line_id
2695 , invoice_batch_reference
2696 , invoice_reference
2697 , invoice_line_reference
2698 , invoice_date_reference
2699 , invoice_quantity
2700 , invoice_unit_price
2701 , invoice_line_ext_value
2702 , outside_code
2703 , outside_ext_value
2704 , outside_unit_price
2705 , currency_code
2706 , currency_conversion_rate
2707 , currency_conversion_type
2708 , currency_conversion_date
2709 , period_name
2710 , report_reference
2711 , report_date
2712 , category_id
2713 , weight_method
2714 , unit_weight
2715 , total_weight
2716 , transaction_nature
2717 , delivery_terms
2718 , transport_mode
2719 , alternate_quantity
2720 , alternate_uom_code
2721 , dispatch_territory_code
2722 , destination_territory_code
2723 , origin_territory_code
2724 , stat_method
2725 , stat_adj_percent
2726 , stat_adj_amount
2727 , stat_ext_value
2728 , area
2729 , port
2730 , stat_type
2731 , comments
2732 , attribute_category
2733 , commodity_code
2734 , commodity_description
2735 , requisition_header_id
2736 , requisition_line_id
2737 , picking_line_detail_id
2738 , usage_type
2739 , zone_code
2740 , edi_sent_flag
2741 , statistical_procedure_code
2742 , movement_amount
2743 , triangulation_country_code
2744 , csa_code
2745 , oil_reference_code
2746 , container_type_code
2747 , flow_indicator_code
2748 , affiliation_reference_code
2749 , origin_territory_eu_code
2750 , destination_territory_eu_code
2751 , dispatch_territory_eu_code
2752 , set_of_books_period
2753 , taric_code
2754 , preference_code
2755 , rcv_transaction_id
2756 , mtl_transaction_id
2757 , total_weight_uom_code
2758 , financial_document_flag
2759 --, opm_trans_id
2760 , customer_vat_number
2761 , attribute1
2762 , attribute2
2763 , attribute3
2764 , attribute4
2765 , attribute5
2766 , attribute6
2767 , attribute7
2768 , attribute8
2769 , attribute9
2770 , attribute10
2771 , attribute11
2772 , attribute12
2773 , attribute13
2774 , attribute14
2775 , attribute15
2776 , triangulation_country_eu_code
2777 , distribution_line_number
2778 , ship_to_name
2779 , ship_to_number
2780 , ship_to_site
2781 , edi_transaction_date
2782 , edi_transaction_reference
2783 , esl_drop_shipment_code
2784 FROM
2785 mtl_movement_statistics
2786 WHERE movement_id = l_parent_mvt_id;
2787
2788 BEGIN
2789 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2790 THEN
2791 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2792 , G_MODULE_NAME || l_api_name || '.begin'
2793 ,'enter procedure'
2794 );
2795 END IF;
2796
2797 x_return_status := FND_API.G_RET_STS_SUCCESS;
2798
2799 --Get parent PO record status
2800 OPEN parent_status;
2801 FETCH parent_status INTO
2802 l_parent_mvt_id
2803 , l_parent_mvt_status;
2804
2805 IF parent_status%NOTFOUND
2806 THEN
2807 l_parent_mvt_id := null;
2808 l_parent_mvt_status := null;
2809 END IF;
2810 CLOSE parent_status;
2811
2812 --If parent PO is Frozen, then create new arrival adjustment RTV
2813 --with negative qty and amt,else update parent PO
2814 IF (l_parent_mvt_status IS NULL
2815 OR l_parent_mvt_status IN ('F', 'X'))
2816 THEN
2817 x_mvt_rtv_transaction.movement_type := 'AA';
2818
2819 --Set qty and amt to negative
2820 x_mvt_rtv_transaction.transaction_quantity :=
2821 0 - x_mvt_rtv_transaction.transaction_quantity;
2822 x_mvt_rtv_transaction.primary_quantity :=
2823 0 - x_mvt_rtv_transaction.primary_quantity;
2824 x_mvt_rtv_transaction.document_line_ext_value :=
2825 0 - x_mvt_rtv_transaction.document_line_ext_value;
2826 x_mvt_rtv_transaction.total_weight := 0 - x_mvt_rtv_transaction.total_weight;
2827
2828 IF x_mvt_rtv_transaction.movement_amount > 0
2829 THEN
2830 x_mvt_rtv_transaction.movement_amount :=
2831 0 - x_mvt_rtv_transaction.movement_amount;
2832 x_mvt_rtv_transaction.stat_ext_value :=
2833 0 - x_mvt_rtv_transaction.stat_ext_value;
2834 END IF;
2835
2836 IF x_mvt_rtv_transaction.alternate_quantity IS NOT NULL
2837 THEN
2838 x_mvt_rtv_transaction.alternate_quantity :=
2839 0- x_mvt_rtv_transaction.alternate_quantity;
2840 END IF;
2841
2842 --Set movement_id,used to insert into parent_movement_id for new record
2843 x_mvt_rtv_transaction.movement_id := l_parent_mvt_id;
2844
2845 --Insert rtv arrival adjustment record
2846 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
2847 ( p_api_version_number => 1.0
2848 , p_init_msg_list => FND_API.G_FALSE
2849 , x_movement_transaction => x_mvt_rtv_transaction
2850 , x_msg_count => x_msg_count
2851 , x_msg_data => x_msg_data
2852 , x_return_status => l_return_status
2853 );
2854 ELSE
2855 OPEN parent_mvt_record;
2856 FETCH parent_mvt_record
2857 INTO
2858 l_parent_mvt_transaction.movement_id
2859 , l_parent_mvt_transaction.organization_id
2860 , l_parent_mvt_transaction.entity_org_id
2861 , l_parent_mvt_transaction.movement_type
2862 , l_parent_mvt_transaction.movement_status
2863 , l_parent_mvt_transaction.transaction_date
2864 , l_parent_mvt_transaction.last_update_date
2865 , l_parent_mvt_transaction.last_updated_by
2866 , l_parent_mvt_transaction.creation_date
2867 , l_parent_mvt_transaction.created_by
2868 , l_parent_mvt_transaction.last_update_login
2869 , l_parent_mvt_transaction.document_source_type
2870 , l_parent_mvt_transaction.creation_method
2871 , l_parent_mvt_transaction.document_reference
2872 , l_parent_mvt_transaction.document_line_reference
2873 , l_parent_mvt_transaction.document_unit_price
2874 , l_parent_mvt_transaction.document_line_ext_value
2875 , l_parent_mvt_transaction.receipt_reference
2876 , l_parent_mvt_transaction.shipment_reference
2877 , l_parent_mvt_transaction.shipment_line_reference
2878 , l_parent_mvt_transaction.pick_slip_reference
2879 , l_parent_mvt_transaction.customer_name
2880 , l_parent_mvt_transaction.customer_number
2881 , l_parent_mvt_transaction.customer_location
2882 , l_parent_mvt_transaction.transacting_from_org
2883 , l_parent_mvt_transaction.transacting_to_org
2884 , l_parent_mvt_transaction.vendor_name
2885 , l_parent_mvt_transaction.vendor_number
2886 , l_parent_mvt_transaction.vendor_site
2887 , l_parent_mvt_transaction.bill_to_name
2888 , l_parent_mvt_transaction.bill_to_number
2889 , l_parent_mvt_transaction.bill_to_site
2890 , l_parent_mvt_transaction.po_header_id
2891 , l_parent_mvt_transaction.po_line_id
2892 , l_parent_mvt_transaction.po_line_location_id
2893 , l_parent_mvt_transaction.order_header_id
2894 , l_parent_mvt_transaction.order_line_id
2895 , l_parent_mvt_transaction.picking_line_id
2896 , l_parent_mvt_transaction.shipment_header_id
2897 , l_parent_mvt_transaction.shipment_line_id
2898 , l_parent_mvt_transaction.ship_to_customer_id
2899 , l_parent_mvt_transaction.ship_to_site_use_id
2900 , l_parent_mvt_transaction.bill_to_customer_id
2901 , l_parent_mvt_transaction.bill_to_site_use_id
2902 , l_parent_mvt_transaction.vendor_id
2903 , l_parent_mvt_transaction.vendor_site_id
2904 , l_parent_mvt_transaction.from_organization_id
2905 , l_parent_mvt_transaction.to_organization_id
2906 , l_parent_mvt_transaction.parent_movement_id
2907 , l_parent_mvt_transaction.inventory_item_id
2908 , l_parent_mvt_transaction.item_description
2909 , l_parent_mvt_transaction.item_cost
2910 , l_parent_mvt_transaction.transaction_quantity
2911 , l_parent_mvt_transaction.transaction_uom_code
2912 , l_parent_mvt_transaction.primary_quantity
2913 , l_parent_mvt_transaction.invoice_batch_id
2914 , l_parent_mvt_transaction.invoice_id
2915 , l_parent_mvt_transaction.customer_trx_line_id
2916 , l_parent_mvt_transaction.invoice_batch_reference
2917 , l_parent_mvt_transaction.invoice_reference
2918 , l_parent_mvt_transaction.invoice_line_reference
2919 , l_parent_mvt_transaction.invoice_date_reference
2920 , l_parent_mvt_transaction.invoice_quantity
2921 , l_parent_mvt_transaction.invoice_unit_price
2922 , l_parent_mvt_transaction.invoice_line_ext_value
2923 , l_parent_mvt_transaction.outside_code
2924 , l_parent_mvt_transaction.outside_ext_value
2925 , l_parent_mvt_transaction.outside_unit_price
2926 , l_parent_mvt_transaction.currency_code
2927 , l_parent_mvt_transaction.currency_conversion_rate
2928 , l_parent_mvt_transaction.currency_conversion_type
2929 , l_parent_mvt_transaction.currency_conversion_date
2930 , l_parent_mvt_transaction.period_name
2931 , l_parent_mvt_transaction.report_reference
2932 , l_parent_mvt_transaction.report_date
2933 , l_parent_mvt_transaction.category_id
2934 , l_parent_mvt_transaction.weight_method
2935 , l_parent_mvt_transaction.unit_weight
2936 , l_parent_mvt_transaction.total_weight
2937 , l_parent_mvt_transaction.transaction_nature
2938 , l_parent_mvt_transaction.delivery_terms
2939 , l_parent_mvt_transaction.transport_mode
2940 , l_parent_mvt_transaction.alternate_quantity
2941 , l_parent_mvt_transaction.alternate_uom_code
2942 , l_parent_mvt_transaction.dispatch_territory_code
2943 , l_parent_mvt_transaction.destination_territory_code
2944 , l_parent_mvt_transaction.origin_territory_code
2945 , l_parent_mvt_transaction.stat_method
2946 , l_parent_mvt_transaction.stat_adj_percent
2947 , l_parent_mvt_transaction.stat_adj_amount
2948 , l_parent_mvt_transaction.stat_ext_value
2949 , l_parent_mvt_transaction.area
2950 , l_parent_mvt_transaction.port
2951 , l_parent_mvt_transaction.stat_type
2952 , l_parent_mvt_transaction.comments
2953 , l_parent_mvt_transaction.attribute_category
2954 , l_parent_mvt_transaction.commodity_code
2955 , l_parent_mvt_transaction.commodity_description
2956 , l_parent_mvt_transaction.requisition_header_id
2957 , l_parent_mvt_transaction.requisition_line_id
2958 , l_parent_mvt_transaction.picking_line_detail_id
2959 , l_parent_mvt_transaction.usage_type
2960 , l_parent_mvt_transaction.zone_code
2961 , l_parent_mvt_transaction.edi_sent_flag
2962 , l_parent_mvt_transaction.statistical_procedure_code
2963 , l_parent_mvt_transaction.movement_amount
2964 , l_parent_mvt_transaction.triangulation_country_code
2965 , l_parent_mvt_transaction.csa_code
2966 , l_parent_mvt_transaction.oil_reference_code
2967 , l_parent_mvt_transaction.container_type_code
2968 , l_parent_mvt_transaction.flow_indicator_code
2969 , l_parent_mvt_transaction.affiliation_reference_code
2970 , l_parent_mvt_transaction.origin_territory_eu_code
2971 , l_parent_mvt_transaction.destination_territory_eu_code
2972 , l_parent_mvt_transaction.dispatch_territory_eu_code
2973 , l_parent_mvt_transaction.set_of_books_period
2974 , l_parent_mvt_transaction.taric_code
2975 , l_parent_mvt_transaction.preference_code
2976 , l_parent_mvt_transaction.rcv_transaction_id
2977 , l_parent_mvt_transaction.mtl_transaction_id
2978 , l_parent_mvt_transaction.total_weight_uom_code
2979 , l_parent_mvt_transaction.financial_document_flag
2980 --, l_parent_mvt_transaction.opm_trans_id
2981 , l_parent_mvt_transaction.customer_vat_number
2982 , l_parent_mvt_transaction.attribute1
2983 , l_parent_mvt_transaction.attribute2
2984 , l_parent_mvt_transaction.attribute3
2985 , l_parent_mvt_transaction.attribute4
2986 , l_parent_mvt_transaction.attribute5
2987 , l_parent_mvt_transaction.attribute6
2988 , l_parent_mvt_transaction.attribute7
2989 , l_parent_mvt_transaction.attribute8
2990 , l_parent_mvt_transaction.attribute9
2991 , l_parent_mvt_transaction.attribute10
2992 , l_parent_mvt_transaction.attribute11
2993 , l_parent_mvt_transaction.attribute12
2994 , l_parent_mvt_transaction.attribute13
2995 , l_parent_mvt_transaction.attribute14
2996 , l_parent_mvt_transaction.attribute15
2997 , l_parent_mvt_transaction.triangulation_country_eu_code
2998 , l_parent_mvt_transaction.distribution_line_number
2999 , l_parent_mvt_transaction.ship_to_name
3000 , l_parent_mvt_transaction.ship_to_number
3001 , l_parent_mvt_transaction.ship_to_site
3002 , l_parent_mvt_transaction.edi_transaction_date
3003 , l_parent_mvt_transaction.edi_transaction_reference
3004 , l_parent_mvt_transaction.esl_drop_shipment_code;
3005
3006 --Net rtv value into parent po
3007 l_parent_mvt_transaction.transaction_quantity :=
3008 l_parent_mvt_transaction.transaction_quantity -
3009 x_mvt_rtv_transaction.transaction_quantity;
3010 l_parent_mvt_transaction.primary_quantity :=
3011 l_parent_mvt_transaction.primary_quantity -
3012 x_mvt_rtv_transaction.primary_quantity;
3013 l_parent_mvt_transaction.document_line_ext_value :=
3014 l_parent_mvt_transaction.document_line_ext_value -
3015 x_mvt_rtv_transaction.document_line_ext_value;
3016 l_parent_mvt_transaction.movement_amount :=
3017 l_parent_mvt_transaction.movement_amount -
3018 x_mvt_rtv_transaction.movement_amount;
3019 l_parent_mvt_transaction.stat_ext_value :=
3020 l_parent_mvt_transaction.stat_ext_value -
3021 NVL(x_mvt_rtv_transaction.stat_ext_value,
3022 x_mvt_rtv_transaction.movement_amount);
3023 l_parent_mvt_transaction.total_weight :=
3024 l_parent_mvt_transaction.total_weight -
3025 x_mvt_rtv_transaction.total_weight;
3026
3027 IF l_parent_mvt_transaction.transaction_quantity IS NOT NULL
3028 AND l_parent_mvt_transaction.transaction_quantity <> 0
3029 THEN
3030 l_parent_mvt_transaction.document_unit_price :=
3031 l_parent_mvt_transaction.document_line_ext_value/
3032 l_parent_mvt_transaction.transaction_quantity;
3033 l_parent_mvt_transaction.unit_weight :=
3034 l_parent_mvt_transaction.total_weight/
3035 l_parent_mvt_transaction.transaction_quantity;
3036 END IF;
3037
3038 IF l_parent_mvt_transaction.alternate_quantity IS NOT NULL
3039 AND x_mvt_rtv_transaction.alternate_quantity IS NOT NULL
3040 THEN
3041 l_parent_mvt_transaction.alternate_quantity :=
3042 l_parent_mvt_transaction.alternate_quantity -
3043 x_mvt_rtv_transaction.alternate_quantity;
3044 END IF;
3045
3046 IF l_parent_mvt_transaction.invoice_quantity IS NOT NULL
3047 AND x_mvt_rtv_transaction.invoice_quantity IS NOT NULL
3048 THEN
3049 l_parent_mvt_transaction.invoice_quantity :=
3050 l_parent_mvt_transaction.invoice_quantity +
3051 x_mvt_rtv_transaction.invoice_quantity;
3052 END IF;
3053
3054 IF l_parent_mvt_transaction.invoice_line_ext_value IS NOT NULL
3055 AND x_mvt_rtv_transaction.invoice_line_ext_value IS NOT NULL
3056 THEN
3057 l_parent_mvt_transaction.invoice_line_ext_value :=
3058 l_parent_mvt_transaction.invoice_line_ext_value +
3059 x_mvt_rtv_transaction.invoice_line_ext_value;
3060 END IF;
3061
3062 IF l_parent_mvt_transaction.invoice_quantity IS NOT NULL
3063 AND l_parent_mvt_transaction.invoice_quantity <> 0
3064 THEN
3065 l_parent_mvt_transaction.invoice_unit_price :=
3066 l_parent_mvt_transaction.invoice_line_ext_value/
3067 l_parent_mvt_transaction.invoice_quantity;
3068 END IF;
3069
3070 --Update parent PO
3071 INV_MGD_MVT_STATS_PVT.Update_Movement_Statistics
3072 ( p_movement_statistics => l_parent_mvt_transaction
3073 , x_return_status => l_return_status
3074 , x_msg_count => x_msg_count
3075 , x_msg_data => x_msg_data
3076 );
3077
3078 --set movement id in rtv record to null, because we didn't
3079 --insert new record and this will be used in calling procedure
3080 x_mvt_rtv_transaction.movement_id := null;
3081 END IF;
3082
3083 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3084 THEN
3085 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3086 , G_MODULE_NAME || l_api_name || '.end'
3087 ,'exit procedure'
3088 );
3089 END IF;
3090
3091 EXCEPTION
3092 WHEN OTHERS THEN
3093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3094 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
3095
3096 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3097 THEN
3098 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3099 , G_MODULE_NAME || l_api_name||'. Others exception'
3100 , l_error
3101 );
3102 END IF;
3103
3104 END Update_PO_With_RTV;
3105
3106 --========================================================================
3107 -- PROCEDURE : Process_PO_Transaction PRIVATE
3108 -- PARAMETERS: x_return_status status flag
3109 -- p_legal_entity_id Legal Entity ID
3110 -- p_start_date Transaction start date
3111 -- p_end_date Transaction end date
3112 -- p_transaction type PO
3113 -- COMMENT :
3114 -- This processes all the PO transaction for the specified legal
3115 -- entity that have a transaction date within the specified
3116 -- date range.
3117 --========================================================================
3118
3119 PROCEDURE Process_PO_Transaction
3120 ( p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
3121 , p_start_date IN DATE
3122 , p_end_date IN DATE
3123 , p_transaction_type IN VARCHAR2
3124 , x_return_status OUT NOCOPY VARCHAR2
3125 )
3126 IS
3127 -- Declare the REF Cursor
3128 po_crsr INV_MGD_MVT_DATA_STR.poCurTyp;
3129 rtv_crsr INV_MGD_MVT_DATA_STR.rtvCurTyp;
3130 setup_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
3131 ref_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
3132 l_receipt_transaction INV_MGD_MVT_DATA_STR.Receipt_Transaction_Rec_Type;
3133 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
3134 x_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
3135 l_movement_transaction_outer INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
3136 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
3137 x_msg_count NUMBER;
3138 x_msg_data VARCHAR2(2000);
3139 l_insert_flag VARCHAR2(1);
3140 l_vendor_site_id NUMBER;
3141 l_site_id NUMBER;
3142 l_parent_id NUMBER;
3143 l_parent_trans_type VARCHAR2(25);
3144 l_return_status VARCHAR2(1);
3145 l_quantity NUMBER;
3146 l_insert_status VARCHAR2(10);
3147 l_movement_id NUMBER;
3148 -- l_trans_date DATE;
3149 l_par_mvt_id NUMBER;
3150 l_par_movement_status mtl_movement_statistics.movement_status%TYPE;
3151 l_par_source_type mtl_movement_statistics.document_source_type%TYPE;
3152 l_update_status VARCHAR2(1);
3153 l_dropship_source_id NUMBER;
3154 l_le_terr_code VARCHAR2(2);
3155 l_subinv_code RCV_SHIPMENT_LINES.To_Subinventory%TYPE;
3156 l_subinv_terr_code VARCHAR2(2);
3157 l_org_terr_code VARCHAR2(2);
3158 l_api_name CONSTANT VARCHAR2(30) := 'Process_PO_Transaction';
3159 l_error VARCHAR2(600);
3160
3161 CURSOR l_drpshp IS
3162 SELECT
3163 drop_ship_source_id
3164 FROM
3165 OE_DROP_SHIP_SOURCES
3166 WHERE po_header_id = l_movement_transaction.po_header_id
3167 AND po_line_id = l_movement_transaction.po_line_id
3168 AND line_location_id = l_movement_transaction.po_line_location_id;
3169
3170 CURSOR l_vendor_site IS
3171 SELECT
3172 vendor_site_id
3173 FROM
3174 po_headers_all
3175 WHERE po_header_id = l_movement_transaction.po_header_id;
3176
3177 BEGIN
3178 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3179 THEN
3180 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3181 , G_MODULE_NAME || l_api_name || '.begin'
3182 ,'enter procedure'
3183 );
3184 END IF;
3185
3186 x_return_status := FND_API.G_RET_STS_SUCCESS;
3187 l_movement_transaction := p_movement_transaction;
3188
3189 -- Call the transaction proxy which processes all the transactions.
3190 INV_MGD_MVT_PO_MDTR.Get_PO_Transactions
3191 ( po_crsr => po_crsr
3192 , p_movement_transaction => l_movement_transaction
3193 , p_start_date => p_start_date
3194 , p_end_date => p_end_date
3195 , x_return_status => l_return_status);
3196
3197 IF l_return_status = 'Y'
3198 THEN
3199 <<l_outer>>
3200 LOOP
3201 --yawang initialize l_insert_status
3202 --l_insert_status := 'E';
3203
3204 --Reset the movement record for each transaction
3205 l_movement_transaction := p_movement_transaction;
3206 l_movement_id := NULL;
3207
3208 --Fix bug5010132, reset dropship status
3209 l_dropship_source_id := null;
3210
3211 FETCH po_crsr INTO
3212 l_movement_transaction.rcv_transaction_id
3213 , l_receipt_transaction.parent_transaction_id
3214 , l_receipt_transaction.transaction_type
3215 , l_movement_transaction.po_header_id
3216 , l_movement_transaction.po_line_id
3217 , l_movement_transaction.po_line_location_id
3218 , l_receipt_transaction.source_document_code
3219 , l_movement_transaction.vendor_site_id
3220 , l_movement_transaction.transaction_date
3221 , l_movement_transaction.organization_id
3222 , l_subinv_code;
3223
3224 EXIT WHEN po_crsr%NOTFOUND;
3225
3226 SAVEPOINT PO_Transaction;
3227
3228 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3229 THEN
3230 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3231 , G_MODULE_NAME ||'.The PO hd,ln,loc,rcv id,txn date are '
3232 ||l_movement_transaction.po_header_id
3233 ||','||l_movement_transaction.po_line_id
3234 ||','||l_movement_transaction.po_line_location_id
3235 ||','||l_movement_transaction.rcv_transaction_id
3236 ||','||l_movement_transaction.transaction_date
3237 ,'debug msg');
3238 END IF;
3239
3240 --Timezone support, convert server transaction date to legal entity timezone
3241 l_movement_transaction.transaction_date :=
3242 INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Server
3243 ( p_trxn_date => l_movement_transaction.transaction_date
3244 , p_le_id => l_movement_transaction.entity_org_id
3245 );
3246
3247 INV_MGD_MVT_SETUP_MDTR.Get_Setup_Context
3248 ( p_legal_entity_id => l_movement_transaction.entity_org_id
3249 , p_movement_transaction => l_movement_transaction
3250 , x_return_status => l_return_status
3251 , setup_crsr => setup_crsr
3252 );
3253
3254 --Back up the movement statistics record
3255 l_movement_transaction_outer := l_movement_transaction;
3256 <<l_inner>>
3257 LOOP
3258 --Reset movement transaction record
3259 l_movement_transaction := l_movement_transaction_outer;
3260
3261 FETCH setup_crsr INTO
3262 l_movement_transaction.zone_code
3263 , l_movement_transaction.usage_type
3264 , l_movement_transaction.stat_type
3265 , l_stat_typ_transaction.reference_period_rule
3266 , l_stat_typ_transaction.pending_invoice_days
3267 , l_stat_typ_transaction.prior_invoice_days
3268 , l_stat_typ_transaction.triangulation_mode;
3269
3270 EXIT l_inner WHEN setup_crsr%NOTFOUND;
3271
3272 --Populate transaction date (reference date)
3273 --Correction transaction does not have invoice
3274 IF (NVL(l_stat_typ_transaction.reference_period_rule,'SHIPMENT_BASED')
3275 = 'INVOICE_BASED'
3276 AND l_receipt_transaction.transaction_type <> 'CORRECT')
3277 THEN
3278 --Document source type
3279 IF l_receipt_transaction.transaction_type = 'RETURN TO VENDOR'
3280 THEN
3281 l_movement_transaction.document_source_type := 'RTV';
3282 ELSE
3283 l_movement_transaction.document_source_type := 'PO';
3284 END IF;
3285
3286 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
3287 ( p_stat_typ_transaction => l_stat_typ_transaction
3288 , x_movement_transaction => l_movement_transaction
3289 );
3290
3291 INV_MGD_MVT_FIN_MDTR. Get_Reference_Date
3292 ( p_stat_typ_transaction => l_stat_typ_transaction
3293 , x_movement_transaction => l_movement_transaction
3294 );
3295
3296 l_movement_transaction.transaction_date :=
3297 l_movement_transaction.reference_date;
3298
3299 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3300 THEN
3301 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3302 , G_MODULE_NAME ||'.The reference txn date is '
3303 ||l_movement_transaction.transaction_date
3304 ,'debug msg');
3305 END IF;
3306 END IF;
3307
3308 INV_MGD_MVT_SETUP_MDTR.Get_Reference_Context
3309 ( p_legal_entity_id => l_movement_transaction.entity_org_id
3310 , p_start_date => p_start_date
3311 , p_end_date => p_end_date
3312 , p_transaction_type => p_transaction_type
3313 , p_movement_transaction => l_movement_transaction
3314 , x_return_status => l_return_status
3315 , ref_crsr => ref_crsr
3316 );
3317
3318 -- Reset the movement_id before fetching the transaction
3319 l_movement_transaction.movement_id := NULL;
3320
3321 -- Bug:5920143. Added new parameter include_establishments in result.
3322 FETCH ref_crsr INTO
3323 l_movement_transaction.zone_code
3324 , l_movement_transaction.usage_type
3325 , l_movement_transaction.stat_type
3326 , l_stat_typ_transaction.start_period_name
3327 , l_stat_typ_transaction.end_period_name
3328 , l_stat_typ_transaction.period_set_name
3329 , l_stat_typ_transaction.period_type
3330 , l_stat_typ_transaction.weight_uom_code
3331 , l_stat_typ_transaction.conversion_type
3332 , l_stat_typ_transaction.attribute_rule_set_code
3333 , l_stat_typ_transaction.alt_uom_rule_set_code
3334 , l_stat_typ_transaction.start_date
3335 , l_stat_typ_transaction.end_date
3336 , l_stat_typ_transaction.category_set_id
3337 , l_movement_transaction.set_of_books_period
3338 , l_stat_typ_transaction.gl_currency_code
3339 , l_movement_transaction.gl_currency_code
3340 , l_stat_typ_transaction.conversion_option
3341 , l_stat_typ_transaction.triangulation_mode
3342 , l_stat_typ_transaction.reference_period_rule
3343 , l_stat_typ_transaction.pending_invoice_days
3344 , l_stat_typ_transaction.prior_invoice_days
3345 , l_stat_typ_transaction.returns_processing
3346 , l_stat_typ_transaction.kit_method
3347 , l_stat_typ_transaction.include_establishments;
3348
3349 IF ref_crsr%NOTFOUND
3350 THEN
3351 --the transaction is not inside of start period and end period
3352 --so not create transaction
3353 CLOSE ref_crsr;
3354 ELSE
3355 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3356 THEN
3357 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3358 , G_MODULE_NAME ||'.The usg,stat type,currency,tri mode,ref rule are '
3359 ||l_movement_transaction.usage_type
3360 ||','||l_movement_transaction.stat_type
3361 ||','||l_stat_typ_transaction.gl_currency_code
3362 ||','||l_stat_typ_transaction.triangulation_mode
3363 ||','||l_stat_typ_transaction.reference_period_rule
3364 ,'debug msg');
3365 END IF;
3366
3367 INV_MGD_MVT_STATS_PVT.Init_Movement_Record
3368 (x_movement_transaction => l_movement_transaction);
3369
3370 IF (p_transaction_type = 'RTV')
3371 AND (l_receipt_transaction.transaction_type = 'RECEIVE')
3372 THEN
3373 --Do not process PO RECEIVE, but if CORRECT we should process
3374 EXIT;
3375 ELSE
3376 l_movement_transaction.movement_id := NULL;
3377
3378 -- For every record fetched get the dispatch and destination territory
3379 -- codes.
3380 IF (l_receipt_transaction.parent_transaction_id IS NOT NULL
3381 AND (l_receipt_transaction.transaction_type = 'CORRECT')
3382 AND (l_movement_transaction.vendor_site_id IS NULL))
3383 OR
3384 (l_receipt_transaction.parent_transaction_id IS NOT NULL
3385 AND (l_receipt_transaction.transaction_type = 'RETURN TO VENDOR')
3386 AND (l_movement_transaction.vendor_site_id IS NULL))
3387 THEN
3388 l_vendor_site_id := l_movement_transaction.vendor_site_id;
3389 l_parent_id := l_receipt_transaction.parent_transaction_id;
3390
3391 WHILE NVL(l_vendor_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
3392 LOOP
3393 IF (l_parent_id IS NULL)
3394 THEN
3395 EXIT;
3396 ELSE
3397 INV_MGD_MVT_PO_MDTR.Get_RTV_Transactions
3398 ( rtv_crsr => rtv_crsr
3399 , p_parent_id => l_parent_id
3400 , x_return_status => l_return_status);
3401
3402 FETCH rtv_crsr
3403 INTO
3404 l_movement_transaction.vendor_site_id
3405 , l_receipt_transaction.parent_transaction_id
3406 , l_parent_trans_type;
3407
3408 IF rtv_crsr%NOTFOUND
3409 THEN
3410 CLOSE rtv_crsr;
3411 EXIT;
3412 END IF;
3413
3414 CLOSE rtv_crsr;
3415
3416 l_parent_id := l_receipt_transaction.parent_transaction_id;
3417 l_vendor_site_id := l_movement_transaction.vendor_site_id;
3418 END IF;
3419 END LOOP;
3420 END IF;
3421
3422 --Get vendor site id for unordered match
3423 IF l_receipt_transaction.transaction_type = 'MATCH'
3424 THEN
3425 OPEN l_vendor_site;
3426 FETCH l_vendor_site INTO
3427 l_movement_transaction.vendor_site_id;
3428 CLOSE l_vendor_site;
3429 END IF;
3430
3431 --Check if it's a drop shipment
3432 OPEN l_drpshp;
3433 FETCH l_drpshp INTO
3434 l_dropship_source_id;
3435 CLOSE l_drpshp;
3436
3437 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3438 THEN
3439 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3440 , G_MODULE_NAME || l_api_name
3441 ||'.The drpshp source id is '||l_dropship_source_id
3442 ,'debug msg');
3443 END IF;
3444
3445 --Get subinventory location fix bug 2683302
3446 l_subinv_terr_code :=
3447 INV_MGD_MVT_UTILS_PKG.Get_Subinv_Location
3448 ( p_warehouse_id => l_movement_transaction.organization_id
3449 , p_subinv_code => l_subinv_code);
3450
3451 l_org_terr_code :=
3452 INV_MGD_MVT_UTILS_PKG.Get_Org_Location
3453 (p_warehouse_id => l_movement_transaction.organization_id);
3454
3455 l_le_terr_code := INV_MGD_MVT_UTILS_PKG.Get_LE_Location
3456 (p_le_id => l_movement_transaction.entity_org_id);
3457
3458 l_movement_transaction.dispatch_territory_code :=
3459 INV_MGD_MVT_UTILS_PKG.Get_Vendor_Location
3460 (p_vendor_site_id =>l_movement_transaction.vendor_site_id);
3461
3462 --If dropship,do not consider subinventory location,because dropship
3463 --receipt is logical receipt,not real receipt.
3464 IF l_dropship_source_id IS NOT NULL
3465 THEN
3466 --Bug:5920143. l_org_terr_code is replaced with l_le_terr_code because
3467 -- this is logical record so logical PO destination should be LE.
3468 l_movement_transaction.destination_territory_code := l_le_terr_code;
3469 ELSE
3470 l_movement_transaction.destination_territory_code :=
3471 NVL(l_subinv_terr_code, l_org_terr_code);
3472 END IF;
3473
3474 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3475 THEN
3476 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3477 , G_MODULE_NAME ||'.The subinv,org,dest,disp terr code are '
3478 ||l_subinv_terr_code
3479 ||','||l_org_terr_code
3480 ||','||l_movement_transaction.destination_territory_code
3481 ||','||l_movement_transaction.dispatch_territory_code
3482 ,'debug msg');
3483 END IF;
3484
3485 --Only create record for dest territory located in the same country as
3486 --legal entity and ignore if the stat type is ESL
3487 -- Bug: 5920143 Validation that LE Territory Code and
3488 -- Destination Org Territory Code should be same, is needed only when
3489 -- user has selected Include Establishments as No.
3490 IF (((l_le_terr_code <> l_movement_transaction.destination_territory_code)
3491 AND (l_stat_typ_transaction.include_establishments = 'N'))
3492 OR (l_movement_transaction.stat_type = 'ESL'
3493 AND l_movement_transaction.usage_type = 'INTERNAL'))
3494 THEN
3495 l_insert_flag := 'N';
3496 ELSE
3497 l_insert_flag := INV_MGD_MVT_SETUP_MDTR.Process_Setup_Context
3498 ( p_movement_transaction => l_movement_transaction);
3499 END IF;
3500
3501 --Find out the parent movement id and movement status of a correct
3502 --transaction. If there is a movement id and if the status is closed,
3503 --then create a new movement record for this correction,otherwise do
3504 --not create new record
3505 IF l_receipt_transaction.transaction_type='CORRECT'
3506 THEN
3507 INV_MGD_MVT_PO_MDTR.Get_Parent_Mvt
3508 ( p_movement_transaction => l_movement_transaction
3509 , p_rcv_transaction_id => l_receipt_transaction.parent_transaction_id
3510 , x_movement_id => l_par_mvt_id
3511 , x_movement_status => l_par_movement_status
3512 , x_source_type => l_par_source_type
3513 );
3514
3515 --Initialize update status for this transaction
3516 l_update_status := 'N';
3517
3518 IF (NVL(l_insert_flag,'N') = 'Y'
3519 AND l_par_mvt_id IS NOT NULL
3520 AND l_par_movement_status IN ('F','X'))
3521 THEN
3522 l_insert_flag := 'Y';
3523
3524 --Invoice information is not required for correction transaction
3525 l_movement_transaction.financial_document_flag := 'NOT_REQUIRED_CORRECT';
3526
3527 --Parent movement id is the movement id of original PO
3528 l_movement_transaction.parent_movement_id := l_par_mvt_id;
3529 ELSE
3530 l_insert_flag := 'N';
3531
3532 --set following, used in calling update_po_transaction at the end
3533 --set mvt id of correction to the same as parent mvt id
3534 --IF transaction type is RTV, do not update this rcv transaction
3535 --if its parent is not RTV(only process rtv's correction)
3536 IF (p_transaction_type = 'RTV'
3537 AND l_par_source_type <> 'RTV')
3538 THEN
3539 l_update_status := 'N';
3540 ELSE
3541 l_update_status := 'Y';
3542 l_movement_id := l_par_mvt_id;
3543 END IF;
3544 END IF;
3545 END IF;
3546
3547 IF l_insert_flag = 'Y'
3548 THEN
3549 INV_MGD_MVT_PO_MDTR.Get_PO_Details
3550 (p_stat_typ_transaction => l_stat_typ_transaction
3551 ,x_movement_transaction => l_movement_transaction
3552 ,x_return_status => l_return_status
3553 );
3554
3555 IF l_return_status = 'Y'
3556 THEN
3557 INV_MGD_MVT_UTILS_PKG.Mvt_Stats_Util_Info
3558 (p_stat_typ_transaction => l_stat_typ_transaction
3559 ,x_movement_transaction => l_movement_transaction
3560 ,x_return_status => l_return_status
3561 );
3562
3563 IF l_return_status = FND_API.G_RET_STS_SUCCESS
3564 THEN
3565 IF l_movement_transaction.invoice_id IS NULL
3566 THEN
3567 l_movement_transaction.invoice_quantity := NULL;
3568
3569 -- Set financial flag. Change back to NOT_REQUIRED for correction
3570 -- transaction
3571 IF l_movement_transaction.financial_document_flag = 'NOT_REQUIRED_CORRECT'
3572 THEN
3573 l_movement_transaction.financial_document_flag := 'NOT_REQUIRED';
3574 ELSE
3575 l_movement_transaction.financial_document_flag := 'MISSING';
3576 END IF;
3577 ELSE
3578 l_movement_transaction.financial_document_flag
3579 := 'PROCESSED_INCLUDED';
3580 END IF;
3581
3582 IF l_movement_transaction.transaction_nature='17'
3583 THEN
3584 IF NVL(l_stat_typ_transaction.triangulation_mode,'INVOICE_BASED')=
3585 'INVOICE_BASED'
3586 THEN
3587 l_quantity := l_movement_transaction.transaction_quantity;
3588 l_movement_transaction.total_weight := 0;
3589 l_movement_transaction.transaction_quantity := 0;
3590 ELSE -- don't report the movement if triangulation mode is shipment
3591 l_return_status := FND_API.G_RET_STS_ERROR;
3592 END IF;
3593 END IF;
3594
3595 IF l_return_status = FND_API.G_RET_STS_SUCCESS
3596 THEN
3597 -- A RTV transaction may need to be netted into its parent
3598 -- PO if the returns processing parameter is set to "Aggregate Return"
3599 -- and the parent PO is not Frozen or Exported
3600 IF (l_movement_transaction.document_source_type = 'RTV'
3601 AND l_stat_typ_transaction.returns_processing = 'AGGRTN')
3602 THEN
3603 Update_PO_With_RTV
3604 ( x_mvt_rtv_transaction => l_movement_transaction
3605 , x_return_status => l_insert_status
3606 );
3607 ELSE
3608 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
3609 (p_api_version_number => 1.0
3610 ,p_init_msg_list => FND_API.G_FALSE
3611 ,x_movement_transaction => l_movement_transaction
3612 ,x_msg_count => x_msg_count
3613 ,x_msg_data => x_msg_data
3614 ,x_return_status => l_insert_status
3615 );
3616 END IF;
3617
3618 --yawang fix bug 2268875
3619 IF l_insert_status = FND_API.G_RET_STS_SUCCESS
3620 THEN
3621 l_movement_id := l_movement_transaction.movement_id;
3622
3623 --If rtv is netted into po, movement id is null and no new
3624 --record inserted
3625 IF l_movement_id IS NOT NULL
3626 THEN
3627 g_records_inserted := g_records_inserted +1;
3628
3629 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3630 THEN
3631 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3632 , G_MODULE_NAME || l_api_name
3633 ||'.Created mvt id is '||l_movement_id
3634 ,'debug msg');
3635 END IF;
3636 END IF;
3637 END IF;
3638
3639 -- If it is a drop shipment make quantity=0, this assignment is done here so
3640 -- that all the Movement amount calcns are done with the right quantity.
3641 -- Create a SO for drop shipment
3642 IF l_movement_transaction.transaction_nature='17'
3643 THEN
3644 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3645 THEN
3646 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3647 , G_MODULE_NAME ||'Process_PO_Dropship_Transaction1.begin'
3648 ,'enter dropship');
3649 END IF;
3650
3651 l_movement_transaction.transaction_quantity := l_quantity;
3652 l_movement_transaction.movement_id := NULL;
3653
3654 INV_MGD_MVT_PO_MDTR.Get_DropShipment_Details
3655 (p_stat_typ_transaction => l_stat_typ_transaction
3656 ,x_movement_transaction => l_movement_transaction
3657 ,x_return_status => l_return_status);
3658
3659 --Set dispatch/destination territory code
3660 l_movement_transaction.dispatch_territory_code :=
3661 l_movement_transaction.destination_territory_code;
3662
3663 --Bug:5920143. Following code is commented becasue
3664 --value is already assigned to Triangulation Country.
3665 --l_movement_transaction.triangulation_country_code :=
3666 -- l_movement_transaction.destination_territory_code;
3667
3668 l_movement_transaction.destination_territory_code :=
3669 INV_MGD_MVT_UTILS_PKG.Get_Site_Location
3670 (l_movement_transaction.ship_to_site_use_id);
3671
3672 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3673 THEN
3674 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3675 , G_MODULE_NAME ||'Process_PO_Dropship_Transaction1'
3676 ||'dest and disp terr code are '
3677 ||l_movement_transaction.destination_territory_code
3678 ||','||l_movement_transaction.dispatch_territory_code
3679 ,'enter dropship');
3680 END IF;
3681
3682 IF l_return_status = 'Y'
3683 THEN
3684 -- Bug: 5920143. Following condition added. If Dispatch and
3685 -- and LE countries are not same for SO dispatch dropship
3686 -- record and user has selected Include Establishments as No
3687 -- then no record will be created for original LE.
3688 IF(((l_le_terr_code <> l_movement_transaction.dispatch_territory_code) ) AND
3689 (l_stat_typ_transaction.include_establishments = 'N'))
3690 THEN
3691 l_insert_flag := 'N';
3692 ELSE
3693 --Find out the insert flag
3694 l_insert_flag := INV_MGD_MVT_SETUP_MDTR.Process_Setup_Context
3695 ( p_movement_transaction => l_movement_transaction);
3696 END IF;
3697 ELSE
3698 l_insert_flag := 'N';
3699 END IF;
3700
3701 --Continue if the insert flag is Yes
3702 IF l_insert_flag = 'Y'
3703 THEN
3704 INV_MGD_MVT_UTILS_PKG.Mvt_Stats_Util_Info
3705 (p_stat_typ_transaction => l_stat_typ_transaction
3706 ,x_movement_transaction => l_movement_transaction
3707 ,x_return_status => l_return_status
3708 );
3709
3710 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3711 THEN
3712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3713 ELSE
3714
3715 l_movement_transaction.total_weight := 0;
3716 l_movement_transaction.transaction_quantity := 0;
3717
3718 IF l_movement_transaction.invoice_id IS NULL
3719 THEN
3720 l_movement_transaction.invoice_quantity := NULL;
3721 l_movement_transaction.financial_document_flag := 'MISSING';
3722 ELSE
3723 l_movement_transaction.financial_document_flag
3724 := 'PROCESSED_INCLUDED';
3725 END IF;
3726
3727 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
3728 (p_api_version_number => 1.0
3729 ,p_init_msg_list => FND_API.G_FALSE
3730 ,x_movement_transaction => l_movement_transaction
3731 ,x_msg_count => x_msg_count
3732 ,x_msg_data => x_msg_data
3733 ,x_return_status => l_return_status
3734 );
3735
3736 --yawang fix bug 2268875
3737 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS) =
3738 FND_API.G_RET_STS_SUCCESS
3739 THEN
3740 g_records_inserted := g_records_inserted +1;
3741 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3742 THEN
3743 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3744 , G_MODULE_NAME ||'Process_PO_Dropship_Transaction1'
3745 ||'.Created mvt id is '
3746 ||l_movement_transaction.movement_id
3747 ,'debug msg');
3748 END IF;
3749 END IF;
3750 END IF; -- end success from Mvt_Stats_Util_Info
3751 END IF; -- end l_insert_flag
3752
3753 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3754 THEN
3755 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3756 , G_MODULE_NAME ||'Process_PO_Dropship_Transaction1.end'
3757 ,'exit dropship');
3758 END IF;
3759 END IF; -- end dropship SO creation
3760 END IF;
3761 ELSE
3762 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3763 END IF; -- end success from Mvt_Stats_Util_Info
3764 END IF; -- < end insert>
3765 --Fix bug 2586495 case 8,PO insert flag is N, but dropship SO insert
3766 --flag maybe Y, then a dropship SO needs to be created
3767 ELSE
3768 --Fix bug5010132, replace x_return_status with l_return_status
3769 --in this block. The x_return_status is used to check the status
3770 --of whole procedure and set in exception section. It is not
3771 --used to check local status
3772 IF l_dropship_source_id IS NOT NULL
3773 THEN
3774 --Only create dropshop SO for invoice based
3775 --Bug 5060410, filter out transaction type of 'CORRECT'
3776 --only process transaction type of 'RECEIVE'
3777 IF (NVL(l_stat_typ_transaction.triangulation_mode,'INVOICE_BASED')=
3778 'INVOICE_BASED'
3779 AND l_receipt_transaction.transaction_type = 'RECEIVE')
3780 THEN
3781 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3782 THEN
3783 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3784 , G_MODULE_NAME ||'Process_PO_Dropship_Transaction2.begin'
3785 ,'enter dropship');
3786 END IF;
3787
3788 INV_MGD_MVT_PO_MDTR.Get_PO_Details
3789 (p_stat_typ_transaction => l_stat_typ_transaction
3790 ,x_movement_transaction => l_movement_transaction
3791 ,x_return_status => l_return_status
3792 );
3793
3794 IF l_return_status = 'Y'
3795 THEN
3796 l_movement_transaction.movement_id := NULL;
3797
3798 INV_MGD_MVT_PO_MDTR.Get_DropShipment_Details
3799 (p_stat_typ_transaction => l_stat_typ_transaction
3800 ,x_movement_transaction => l_movement_transaction
3801 ,x_return_status => l_return_status);
3802
3803 --Set dispatch/destination territory code
3804 l_movement_transaction.dispatch_territory_code :=
3805 l_movement_transaction.destination_territory_code;
3806
3807 l_movement_transaction.triangulation_country_code :=
3808 l_movement_transaction.destination_territory_code;
3809
3810 l_movement_transaction.destination_territory_code :=
3811 INV_MGD_MVT_UTILS_PKG.Get_Site_Location
3812 (l_movement_transaction.ship_to_site_use_id);
3813
3814 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3815 THEN
3816 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3817 , G_MODULE_NAME ||'Process_PO_Dropship_Transaction2'
3818 ||'dest and disp terr code are '
3819 ||l_movement_transaction.destination_territory_code
3820 ||','||l_movement_transaction.dispatch_territory_code
3821 ,'enter dropship');
3822 END IF;
3823
3824 IF l_return_status = 'Y'
3825 THEN
3826 -- Bug: 5920143. Following condition added. If Dispatch and
3827 -- and LE countries are not same for SO dispatch dropship
3828 -- record and user has selected Include Establishments as No
3829 -- then no record will be created for original LE.
3830 IF(((l_le_terr_code <> l_movement_transaction.dispatch_territory_code) ) AND
3831 (l_stat_typ_transaction.include_establishments = 'N'))
3832 THEN
3833 l_insert_flag := 'N';
3834 ELSE
3835 --Find out the insert flag
3836 l_insert_flag := INV_MGD_MVT_SETUP_MDTR.Process_Setup_Context
3837 ( p_movement_transaction => l_movement_transaction);
3838 END IF;
3839 ELSE
3840 l_insert_flag := 'N';
3841 END IF;
3842 ELSE
3843 l_insert_flag := 'N';
3844 END IF;
3845
3846 --Continue if the insert flag is Yes
3847 IF l_insert_flag = 'Y'
3848 THEN
3849 INV_MGD_MVT_UTILS_PKG.Mvt_Stats_Util_Info
3850 (p_stat_typ_transaction => l_stat_typ_transaction
3851 ,x_movement_transaction => l_movement_transaction
3852 ,x_return_status => l_return_status
3853 );
3854
3855 IF l_return_status = FND_API.G_RET_STS_SUCCESS
3856 THEN
3857
3858 l_movement_transaction.total_weight := 0;
3859 l_movement_transaction.transaction_quantity := 0;
3860
3861 IF l_movement_transaction.invoice_id IS NULL
3862 THEN
3863 l_movement_transaction.invoice_quantity := NULL;
3864 l_movement_transaction.financial_document_flag := 'MISSING';
3865 ELSE
3866 l_movement_transaction.financial_document_flag
3867 := 'PROCESSED_INCLUDED';
3868 END IF;
3869
3870 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
3871 (p_api_version_number => 1.0
3872 ,p_init_msg_list => FND_API.G_FALSE
3873 ,x_movement_transaction => l_movement_transaction
3874 ,x_msg_count => x_msg_count
3875 ,x_msg_data => x_msg_data
3876 ,x_return_status => l_insert_status
3877 );
3878
3879 --yawang fix bug 2268875
3880 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) =
3881 FND_API.G_RET_STS_SUCCESS
3882 THEN
3883 l_movement_id := l_movement_transaction.movement_id;
3884 g_records_inserted := g_records_inserted +1;
3885
3886 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3887 THEN
3888 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3889 , G_MODULE_NAME ||'Process_PO_Dropship_Transaction2'
3890 ||'.Created mvt id is ' ||l_movement_transaction.movement_id
3891 ,'debug msg');
3892 END IF;
3893 END IF;
3894 ELSE
3895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3896 END IF; --end sucess from Mvt_Stats_Util_Info
3897 END IF; --end insert flag Y
3898
3899 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3900 THEN
3901 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3902 , G_MODULE_NAME ||'Process_PO_Dropship_Transaction2.end'
3903 ,'exit dropship');
3904 END IF;
3905 END IF; --end invoice based
3906 END IF; --end l_dropship_source_id
3907 END IF; --end if for l_insert_flag
3908 END IF; --end if 'RTV' and 'RECEIVE' condition
3909 CLOSE ref_crsr;
3910 END IF; --end if ref_crsr found
3911 END LOOP l_inner;
3912 CLOSE setup_crsr;
3913
3914 IF (p_transaction_type = 'RTV')
3915 AND (l_receipt_transaction.transaction_type = 'RECEIVE')
3916 THEN
3917 NULL;
3918 ELSE
3919 IF (NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
3920 OR l_update_status = 'Y')
3921 THEN
3922 l_movement_transaction.movement_id := l_movement_id;
3923 /* 7165989 - Pass null for mvt_stat_status for non-RMA triangulation txns*/
3924 INV_MGD_MVT_PO_MDTR.Update_PO_Transactions
3925 ( p_movement_transaction => l_movement_transaction
3926 , p_mvt_stat_status => NULL
3927 , x_return_status => l_return_status
3928 );
3929
3930 COMMIT;
3931 ELSE
3932 ROLLBACK TO SAVEPOINT PO_Transaction;
3933 END IF;
3934 END IF;
3935
3936 g_records_processed := g_records_processed +1;
3937
3938 l_movement_transaction := p_movement_transaction;
3939 END LOOP l_outer;
3940 CLOSE po_crsr;
3941 END IF;
3942
3943 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3944 THEN
3945 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3946 , G_MODULE_NAME || l_api_name || '.end'
3947 ,'exit procedure'
3948 );
3949 END IF;
3950
3951 EXCEPTION
3952 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3954 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
3955
3956 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3957 THEN
3958 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3959 , G_MODULE_NAME || l_api_name||'. Unexpected exception'
3960 , l_error
3961 );
3962 END IF;
3963
3964 WHEN NO_DATA_FOUND THEN
3965 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3966 THEN
3967 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3968 , G_MODULE_NAME || l_api_name||'. No data found exception'
3969 , l_error
3970 );
3971 END IF;
3972 RAISE;
3973
3974 WHEN OTHERS THEN
3975 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3976 THEN
3977 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
3978 , 'Others exception in '||l_api_name
3979 );
3980 END IF;
3981
3982 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3983 THEN
3984 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3985 , G_MODULE_NAME || l_api_name||'. Others exception'
3986 , l_error
3987 );
3988 END IF;
3989 RAISE;
3990
3991 END Process_PO_Transaction;
3992
3993 --========================================================================
3994 -- PROCEDURE : Update_SO_With_RMA PRIVATE
3995 -- PARAMETERS: x_return_status return status
3996 -- x_mvt_rma_transaction IN OUT Movement Statistics Record
3997 -- COMMENT : pocedure that process RMA transaction depend on if
3998 -- the parent SO is closed
3999 --=========================================================================
4000 PROCEDURE Update_SO_With_RMA
4001 ( x_mvt_rma_transaction IN OUT NOCOPY
4002 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
4003 , x_return_status OUT NOCOPY VARCHAR2
4004 )
4005 IS
4006 l_parent_mvt_id NUMBER;
4007 l_parent_mvt_status VARCHAR2(30);
4008 l_parent_mvt_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
4009 x_msg_count NUMBER;
4010 x_msg_data VARCHAR2(2000);
4011 l_return_status VARCHAR2(1);
4012 l_api_name CONSTANT VARCHAR2(30) := 'Update_SO_With_RMA';
4013 l_error VARCHAR2(600);
4014
4015 CURSOR parent_status IS
4016 SELECT
4017 mms.movement_id
4018 , mms.movement_status
4019 FROM
4020 mtl_movement_statistics mms
4021 , oe_order_lines_all oola
4022 WHERE mms.order_header_id = oola.reference_header_id
4023 AND mms.order_line_id = oola.reference_line_id
4024 AND oola.line_id = x_mvt_rma_transaction.order_line_id
4025 AND mms.entity_org_id = x_mvt_rma_transaction.entity_org_id
4026 AND mms.zone_code = x_mvt_rma_transaction.zone_code
4027 AND mms.usage_type = x_mvt_rma_transaction.usage_type
4028 AND mms.stat_type = x_mvt_rma_transaction.stat_type
4029 AND mms.movement_type <> 'DA';
4030
4031 CURSOR parent_mvt_record IS
4032 SELECT
4033 movement_id
4034 , organization_id
4035 , entity_org_id
4036 , movement_type
4037 , movement_status
4038 , transaction_date
4039 , last_update_date
4040 , last_updated_by
4041 , creation_date
4042 , created_by
4043 , last_update_login
4044 , document_source_type
4045 , creation_method
4046 , document_reference
4047 , document_line_reference
4048 , document_unit_price
4049 , document_line_ext_value
4050 , receipt_reference
4051 , shipment_reference
4052 , shipment_line_reference
4053 , pick_slip_reference
4054 , customer_name
4055 , customer_number
4056 , customer_location
4057 , transacting_from_org
4058 , transacting_to_org
4059 , vendor_name
4060 , vendor_number
4061 , vendor_site
4062 , bill_to_name
4063 , bill_to_number
4064 , bill_to_site
4065 , po_header_id
4066 , po_line_id
4067 , po_line_location_id
4068 , order_header_id
4069 , order_line_id
4070 , picking_line_id
4071 , shipment_header_id
4072 , shipment_line_id
4073 , ship_to_customer_id
4074 , ship_to_site_use_id
4075 , bill_to_customer_id
4076 , bill_to_site_use_id
4077 , vendor_id
4078 , vendor_site_id
4079 , from_organization_id
4080 , to_organization_id
4081 , parent_movement_id
4082 , inventory_item_id
4083 , item_description
4084 , item_cost
4085 , transaction_quantity
4086 , transaction_uom_code
4087 , primary_quantity
4088 , invoice_batch_id
4089 , invoice_id
4090 , customer_trx_line_id
4091 , invoice_batch_reference
4092 , invoice_reference
4093 , invoice_line_reference
4094 , invoice_date_reference
4095 , invoice_quantity
4096 , invoice_unit_price
4097 , invoice_line_ext_value
4098 , outside_code
4099 , outside_ext_value
4100 , outside_unit_price
4101 , currency_code
4102 , currency_conversion_rate
4103 , currency_conversion_type
4104 , currency_conversion_date
4105 , period_name
4106 , report_reference
4107 , report_date
4108 , category_id
4109 , weight_method
4110 , unit_weight
4111 , total_weight
4112 , transaction_nature
4113 , delivery_terms
4114 , transport_mode
4115 , alternate_quantity
4116 , alternate_uom_code
4117 , dispatch_territory_code
4118 , destination_territory_code
4119 , origin_territory_code
4120 , stat_method
4121 , stat_adj_percent
4122 , stat_adj_amount
4123 , stat_ext_value
4124 , area
4125 , port
4126 , stat_type
4127 , comments
4128 , attribute_category
4129 , commodity_code
4130 , commodity_description
4131 , requisition_header_id
4132 , requisition_line_id
4133 , picking_line_detail_id
4134 , usage_type
4135 , zone_code
4136 , edi_sent_flag
4137 , statistical_procedure_code
4138 , movement_amount
4139 , triangulation_country_code
4140 , csa_code
4141 , oil_reference_code
4142 , container_type_code
4143 , flow_indicator_code
4144 , affiliation_reference_code
4145 , origin_territory_eu_code
4146 , destination_territory_eu_code
4147 , dispatch_territory_eu_code
4148 , set_of_books_period
4149 , taric_code
4150 , preference_code
4151 , rcv_transaction_id
4152 , mtl_transaction_id
4153 , total_weight_uom_code
4154 , financial_document_flag
4155 --, opm_trans_id
4156 , customer_vat_number
4157 , attribute1
4158 , attribute2
4159 , attribute3
4160 , attribute4
4161 , attribute5
4162 , attribute6
4163 , attribute7
4164 , attribute8
4165 , attribute9
4166 , attribute10
4167 , attribute11
4168 , attribute12
4169 , attribute13
4170 , attribute14
4171 , attribute15
4172 , triangulation_country_eu_code
4173 , distribution_line_number
4174 , ship_to_name
4175 , ship_to_number
4176 , ship_to_site
4177 , edi_transaction_date
4178 , edi_transaction_reference
4179 , esl_drop_shipment_code
4180 FROM
4181 mtl_movement_statistics
4182 WHERE movement_id = l_parent_mvt_id;
4183
4184 BEGIN
4185 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4186 THEN
4187 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4188 , G_MODULE_NAME || l_api_name || '.begin'
4189 ,'enter procedure'
4190 );
4191 END IF;
4192
4193 x_return_status := FND_API.G_RET_STS_SUCCESS;
4194
4195 --Get parent PO record status
4196 OPEN parent_status;
4197 FETCH parent_status INTO
4198 l_parent_mvt_id
4199 , l_parent_mvt_status;
4200
4201 IF parent_status%NOTFOUND
4202 THEN
4203 l_parent_mvt_id := null;
4204 l_parent_mvt_status := null;
4205 END IF;
4206 CLOSE parent_status;
4207
4208 --If parent SO is Frozen, then create new dispaatch adjustment RMA
4209 --with negative qty and amt,else update parent SO
4210 IF (l_parent_mvt_status IS NULL
4211 OR l_parent_mvt_status IN ('F', 'X'))
4212 THEN
4213 x_mvt_rma_transaction.movement_type := 'DA';
4214
4215 --Set qty and amt to negative
4216 x_mvt_rma_transaction.transaction_quantity :=
4217 0 - x_mvt_rma_transaction.transaction_quantity;
4218 x_mvt_rma_transaction.primary_quantity :=
4219 0 - x_mvt_rma_transaction.primary_quantity;
4220 x_mvt_rma_transaction.document_line_ext_value :=
4221 0 - x_mvt_rma_transaction.document_line_ext_value;
4222 x_mvt_rma_transaction.total_weight := 0 - x_mvt_rma_transaction.total_weight;
4223
4224 IF x_mvt_rma_transaction.movement_amount >0
4225 THEN
4226 x_mvt_rma_transaction.movement_amount :=
4227 0 - x_mvt_rma_transaction.movement_amount;
4228 x_mvt_rma_transaction.stat_ext_value :=
4229 0 - x_mvt_rma_transaction.stat_ext_value;
4230 END IF;
4231
4232 IF x_mvt_rma_transaction.alternate_quantity IS NOT NULL
4233 THEN
4234 x_mvt_rma_transaction.alternate_quantity :=
4235 0- x_mvt_rma_transaction.alternate_quantity;
4236 END IF;
4237
4238 --Set movement_id,used to insert into parent_movement_id for new record
4239 x_mvt_rma_transaction.movement_id := l_parent_mvt_id;
4240
4241 --Insert rma arrival adjustment record
4242 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
4243 ( p_api_version_number => 1.0
4244 , p_init_msg_list => FND_API.G_FALSE
4245 , x_movement_transaction => x_mvt_rma_transaction
4246 , x_msg_count => x_msg_count
4247 , x_msg_data => x_msg_data
4248 , x_return_status => l_return_status
4249 );
4250 ELSE
4251 OPEN parent_mvt_record;
4252 FETCH parent_mvt_record
4253 INTO
4254 l_parent_mvt_transaction.movement_id
4255 , l_parent_mvt_transaction.organization_id
4256 , l_parent_mvt_transaction.entity_org_id
4257 , l_parent_mvt_transaction.movement_type
4258 , l_parent_mvt_transaction.movement_status
4259 , l_parent_mvt_transaction.transaction_date
4260 , l_parent_mvt_transaction.last_update_date
4261 , l_parent_mvt_transaction.last_updated_by
4262 , l_parent_mvt_transaction.creation_date
4263 , l_parent_mvt_transaction.created_by
4264 , l_parent_mvt_transaction.last_update_login
4265 , l_parent_mvt_transaction.document_source_type
4266 , l_parent_mvt_transaction.creation_method
4267 , l_parent_mvt_transaction.document_reference
4268 , l_parent_mvt_transaction.document_line_reference
4269 , l_parent_mvt_transaction.document_unit_price
4270 , l_parent_mvt_transaction.document_line_ext_value
4271 , l_parent_mvt_transaction.receipt_reference
4272 , l_parent_mvt_transaction.shipment_reference
4273 , l_parent_mvt_transaction.shipment_line_reference
4274 , l_parent_mvt_transaction.pick_slip_reference
4275 , l_parent_mvt_transaction.customer_name
4276 , l_parent_mvt_transaction.customer_number
4277 , l_parent_mvt_transaction.customer_location
4278 , l_parent_mvt_transaction.transacting_from_org
4279 , l_parent_mvt_transaction.transacting_to_org
4280 , l_parent_mvt_transaction.vendor_name
4281 , l_parent_mvt_transaction.vendor_number
4282 , l_parent_mvt_transaction.vendor_site
4283 , l_parent_mvt_transaction.bill_to_name
4284 , l_parent_mvt_transaction.bill_to_number
4285 , l_parent_mvt_transaction.bill_to_site
4286 , l_parent_mvt_transaction.po_header_id
4287 , l_parent_mvt_transaction.po_line_id
4288 , l_parent_mvt_transaction.po_line_location_id
4289 , l_parent_mvt_transaction.order_header_id
4290 , l_parent_mvt_transaction.order_line_id
4291 , l_parent_mvt_transaction.picking_line_id
4292 , l_parent_mvt_transaction.shipment_header_id
4293 , l_parent_mvt_transaction.shipment_line_id
4294 , l_parent_mvt_transaction.ship_to_customer_id
4295 , l_parent_mvt_transaction.ship_to_site_use_id
4296 , l_parent_mvt_transaction.bill_to_customer_id
4297 , l_parent_mvt_transaction.bill_to_site_use_id
4298 , l_parent_mvt_transaction.vendor_id
4299 , l_parent_mvt_transaction.vendor_site_id
4300 , l_parent_mvt_transaction.from_organization_id
4301 , l_parent_mvt_transaction.to_organization_id
4302 , l_parent_mvt_transaction.parent_movement_id
4303 , l_parent_mvt_transaction.inventory_item_id
4304 , l_parent_mvt_transaction.item_description
4305 , l_parent_mvt_transaction.item_cost
4306 , l_parent_mvt_transaction.transaction_quantity
4307 , l_parent_mvt_transaction.transaction_uom_code
4308 , l_parent_mvt_transaction.primary_quantity
4309 , l_parent_mvt_transaction.invoice_batch_id
4310 , l_parent_mvt_transaction.invoice_id
4311 , l_parent_mvt_transaction.customer_trx_line_id
4312 , l_parent_mvt_transaction.invoice_batch_reference
4313 , l_parent_mvt_transaction.invoice_reference
4314 , l_parent_mvt_transaction.invoice_line_reference
4315 , l_parent_mvt_transaction.invoice_date_reference
4316 , l_parent_mvt_transaction.invoice_quantity
4317 , l_parent_mvt_transaction.invoice_unit_price
4318 , l_parent_mvt_transaction.invoice_line_ext_value
4319 , l_parent_mvt_transaction.outside_code
4320 , l_parent_mvt_transaction.outside_ext_value
4321 , l_parent_mvt_transaction.outside_unit_price
4322 , l_parent_mvt_transaction.currency_code
4323 , l_parent_mvt_transaction.currency_conversion_rate
4324 , l_parent_mvt_transaction.currency_conversion_type
4325 , l_parent_mvt_transaction.currency_conversion_date
4326 , l_parent_mvt_transaction.period_name
4327 , l_parent_mvt_transaction.report_reference
4328 , l_parent_mvt_transaction.report_date
4329 , l_parent_mvt_transaction.category_id
4330 , l_parent_mvt_transaction.weight_method
4331 , l_parent_mvt_transaction.unit_weight
4332 , l_parent_mvt_transaction.total_weight
4333 , l_parent_mvt_transaction.transaction_nature
4334 , l_parent_mvt_transaction.delivery_terms
4335 , l_parent_mvt_transaction.transport_mode
4336 , l_parent_mvt_transaction.alternate_quantity
4337 , l_parent_mvt_transaction.alternate_uom_code
4338 , l_parent_mvt_transaction.dispatch_territory_code
4339 , l_parent_mvt_transaction.destination_territory_code
4340 , l_parent_mvt_transaction.origin_territory_code
4341 , l_parent_mvt_transaction.stat_method
4342 , l_parent_mvt_transaction.stat_adj_percent
4343 , l_parent_mvt_transaction.stat_adj_amount
4344 , l_parent_mvt_transaction.stat_ext_value
4345 , l_parent_mvt_transaction.area
4346 , l_parent_mvt_transaction.port
4347 , l_parent_mvt_transaction.stat_type
4348 , l_parent_mvt_transaction.comments
4349 , l_parent_mvt_transaction.attribute_category
4350 , l_parent_mvt_transaction.commodity_code
4351 , l_parent_mvt_transaction.commodity_description
4352 , l_parent_mvt_transaction.requisition_header_id
4353 , l_parent_mvt_transaction.requisition_line_id
4354 , l_parent_mvt_transaction.picking_line_detail_id
4355 , l_parent_mvt_transaction.usage_type
4356 , l_parent_mvt_transaction.zone_code
4357 , l_parent_mvt_transaction.edi_sent_flag
4358 , l_parent_mvt_transaction.statistical_procedure_code
4359 , l_parent_mvt_transaction.movement_amount
4360 , l_parent_mvt_transaction.triangulation_country_code
4361 , l_parent_mvt_transaction.csa_code
4362 , l_parent_mvt_transaction.oil_reference_code
4363 , l_parent_mvt_transaction.container_type_code
4364 , l_parent_mvt_transaction.flow_indicator_code
4365 , l_parent_mvt_transaction.affiliation_reference_code
4366 , l_parent_mvt_transaction.origin_territory_eu_code
4367 , l_parent_mvt_transaction.destination_territory_eu_code
4368 , l_parent_mvt_transaction.dispatch_territory_eu_code
4369 , l_parent_mvt_transaction.set_of_books_period
4370 , l_parent_mvt_transaction.taric_code
4371 , l_parent_mvt_transaction.preference_code
4372 , l_parent_mvt_transaction.rcv_transaction_id
4373 , l_parent_mvt_transaction.mtl_transaction_id
4374 , l_parent_mvt_transaction.total_weight_uom_code
4375 , l_parent_mvt_transaction.financial_document_flag
4376 --, l_parent_mvt_transaction.opm_trans_id
4377 , l_parent_mvt_transaction.customer_vat_number
4378 , l_parent_mvt_transaction.attribute1
4379 , l_parent_mvt_transaction.attribute2
4380 , l_parent_mvt_transaction.attribute3
4381 , l_parent_mvt_transaction.attribute4
4382 , l_parent_mvt_transaction.attribute5
4383 , l_parent_mvt_transaction.attribute6
4384 , l_parent_mvt_transaction.attribute7
4385 , l_parent_mvt_transaction.attribute8
4386 , l_parent_mvt_transaction.attribute9
4387 , l_parent_mvt_transaction.attribute10
4388 , l_parent_mvt_transaction.attribute11
4389 , l_parent_mvt_transaction.attribute12
4390 , l_parent_mvt_transaction.attribute13
4391 , l_parent_mvt_transaction.attribute14
4392 , l_parent_mvt_transaction.attribute15
4393 , l_parent_mvt_transaction.triangulation_country_eu_code
4394 , l_parent_mvt_transaction.distribution_line_number
4395 , l_parent_mvt_transaction.ship_to_name
4396 , l_parent_mvt_transaction.ship_to_number
4397 , l_parent_mvt_transaction.ship_to_site
4398 , l_parent_mvt_transaction.edi_transaction_date
4399 , l_parent_mvt_transaction.edi_transaction_reference
4400 , l_parent_mvt_transaction.esl_drop_shipment_code;
4401
4402 --Net rma value into parent po
4403 l_parent_mvt_transaction.transaction_quantity :=
4404 l_parent_mvt_transaction.transaction_quantity -
4405 x_mvt_rma_transaction.transaction_quantity;
4406 l_parent_mvt_transaction.primary_quantity :=
4407 l_parent_mvt_transaction.primary_quantity -
4408 x_mvt_rma_transaction.primary_quantity;
4409 l_parent_mvt_transaction.document_line_ext_value :=
4410 l_parent_mvt_transaction.document_line_ext_value -
4411 x_mvt_rma_transaction.document_line_ext_value;
4412 l_parent_mvt_transaction.movement_amount :=
4413 l_parent_mvt_transaction.movement_amount -
4414 x_mvt_rma_transaction.movement_amount;
4415 l_parent_mvt_transaction.stat_ext_value :=
4416 l_parent_mvt_transaction.stat_ext_value -
4417 NVL(x_mvt_rma_transaction.stat_ext_value,
4418 x_mvt_rma_transaction.movement_amount);
4419 l_parent_mvt_transaction.total_weight :=
4420 l_parent_mvt_transaction.total_weight -
4421 x_mvt_rma_transaction.total_weight;
4422
4423 IF l_parent_mvt_transaction.transaction_quantity IS NOT NULL
4424 AND l_parent_mvt_transaction.transaction_quantity <> 0
4425 THEN
4426 l_parent_mvt_transaction.document_unit_price :=
4427 l_parent_mvt_transaction.document_line_ext_value/
4428 l_parent_mvt_transaction.transaction_quantity;
4429 l_parent_mvt_transaction.unit_weight :=
4430 l_parent_mvt_transaction.total_weight/
4431 l_parent_mvt_transaction.transaction_quantity;
4432 END IF;
4433
4434 IF l_parent_mvt_transaction.alternate_quantity IS NOT NULL
4435 AND x_mvt_rma_transaction.alternate_quantity IS NOT NULL
4436 THEN
4437 l_parent_mvt_transaction.alternate_quantity :=
4438 l_parent_mvt_transaction.alternate_quantity -
4439 x_mvt_rma_transaction.alternate_quantity;
4440 END IF;
4441
4442 IF l_parent_mvt_transaction.invoice_quantity IS NOT NULL
4443 AND x_mvt_rma_transaction.invoice_quantity IS NOT NULL
4444 THEN
4445 l_parent_mvt_transaction.invoice_quantity :=
4446 l_parent_mvt_transaction.invoice_quantity +
4447 x_mvt_rma_transaction.invoice_quantity;
4448 END IF;
4449
4450 IF l_parent_mvt_transaction.invoice_line_ext_value IS NOT NULL
4451 AND x_mvt_rma_transaction.invoice_line_ext_value IS NOT NULL
4452 THEN
4453 l_parent_mvt_transaction.invoice_line_ext_value :=
4454 l_parent_mvt_transaction.invoice_line_ext_value +
4455 x_mvt_rma_transaction.invoice_line_ext_value;
4456 END IF;
4457
4458 IF l_parent_mvt_transaction.invoice_quantity IS NOT NULL
4459 AND l_parent_mvt_transaction.invoice_quantity <> 0
4460 THEN
4461 l_parent_mvt_transaction.invoice_unit_price :=
4462 l_parent_mvt_transaction.invoice_line_ext_value/
4463 l_parent_mvt_transaction.invoice_quantity;
4464 END IF;
4465
4466 --Update parent SO
4467 INV_MGD_MVT_STATS_PVT.Update_Movement_Statistics
4468 ( p_movement_statistics => l_parent_mvt_transaction
4469 , x_return_status => l_return_status
4470 , x_msg_count => x_msg_count
4471 , x_msg_data => x_msg_data
4472 );
4473
4474 --set movement id in rma record to null, because we didn't
4475 --insert new record and this will be used in calling procedure
4476 x_mvt_rma_transaction.movement_id := null;
4477 END IF;
4478
4479 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4480 THEN
4481 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4482 , G_MODULE_NAME || l_api_name || '.end'
4483 ,'exit procedure'
4484 );
4485 END IF;
4486
4487 EXCEPTION
4488 WHEN OTHERS THEN
4489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4490 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
4491
4492 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4493 THEN
4494 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
4495 , G_MODULE_NAME || l_api_name||'.Others exception'
4496 , l_error
4497 );
4498 END IF;
4499
4500 END Update_SO_With_RMA;
4501
4502 --========================================================================
4503 -- PROCEDURE : Process_RMA_Transaction PRIVATE
4504 -- PARAMETERS: x_return_status status flag
4505 -- p_legal_entity_id Legal Entity ID
4506 -- p_start_date Transaction start date
4507 -- p_end_date Transaction end date
4508 -- p_transaction type RMA
4509 -- COMMENT :
4510 -- This processes all the RMA transaction for the specified legal
4511 -- entity that have a transaction date within the specified
4512 -- date range.
4513 --========================================================================
4514
4515 PROCEDURE Process_RMA_Transaction
4516 ( p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
4517 , p_start_date IN DATE
4518 , p_end_date IN DATE
4519 , p_transaction_type IN VARCHAR2
4520 , x_return_status OUT NOCOPY VARCHAR2
4521 )
4522 IS
4523 -- Declare the REF Cursor
4524 rma_crsr INV_MGD_MVT_DATA_STR.rtvCurTyp;
4525 setup_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
4526 ref_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
4527 l_receipt_transaction INV_MGD_MVT_DATA_STR.Receipt_Transaction_Rec_Type;
4528 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
4529 x_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
4530 l_movement_transaction_outer INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
4531 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
4532 x_msg_count NUMBER;
4533 x_msg_data VARCHAR2(2000);
4534 l_insert_flag VARCHAR2(1);
4535 l_vendor_site_id NUMBER;
4536 l_site_id NUMBER;
4537 l_parent_id NUMBER;
4538 l_parent_trans_type VARCHAR2(25);
4539 l_return_status VARCHAR2(1);
4540 l_insert_status VARCHAR2(10);
4541 l_movement_id NUMBER;
4542 --l_trans_date DATE;
4543 l_subinv_code RCV_SHIPMENT_LINES.To_Subinventory%TYPE;
4544 l_subinv_terr_code VARCHAR2(2);
4545 l_org_terr_code VARCHAR2(2);
4546 l_rma_le_id NUMBER;
4547 l_receiving_le_id NUMBER;
4548 l_le_terr_code VARCHAR2(2);
4549 l_process_flag VARCHAR2(2);
4550
4551 --Fix bug 2695323
4552 l_item_type_code VARCHAR2(30);
4553
4554 l_api_name CONSTANT VARCHAR2(30) := 'Process_RMA_Transaction';
4555 l_error VARCHAR2(600);
4556 l_sold_from_org_code VARCHAR2(2); -- 7165989
4557 l_mvt_stat_status RCV_TRANSACTIONS.mvt_stat_status%TYPE; -- 7165989
4558 l_dispatch mtl_movement_statistics.dispatch_territory_code%TYPE;-- 7165989
4559 l_destination mtl_movement_statistics.destination_territory_code%TYPE;-- 7165989
4560 l_insert VARCHAR2(1);-- 7165989
4561 l_ship_from_loc VARCHAR2(10);-- 7165989
4562 l_ship_to_loc VARCHAR2(10);-- 7165989
4563
4564 CURSOR c_item_type IS
4565 SELECT
4566 oola2.item_type_code
4567 FROM
4568 oe_order_lines_all oola1
4569 , oe_order_lines_all oola2
4570 WHERE oola1.line_id = l_movement_transaction.order_line_id
4571 AND oola2.line_id = oola1.reference_line_id;
4572
4573 BEGIN
4574 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4575 THEN
4576 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4577 , G_MODULE_NAME || l_api_name || '.begin'
4578 ,'enter procedure'
4579 );
4580 END IF;
4581 l_mvt_stat_status := NULL ; -- 7165989 : initialize the mvt_stat_status in rcv_transactions
4582 x_return_status := FND_API.G_RET_STS_SUCCESS;
4583 l_movement_transaction := p_movement_transaction;
4584
4585 -- Call the transaction proxy which processes all the transactions.
4586 INV_MGD_MVT_PO_MDTR.Get_RMA_Transactions
4587 ( rma_crsr => rma_crsr
4588 , p_movement_transaction => l_movement_transaction
4589 , p_start_date => p_start_date
4590 , p_end_date => p_end_date
4591 , x_return_status => l_return_status);
4592
4593 IF l_return_status = 'Y' THEN
4594
4595 <<l_outer>>
4596 LOOP
4597 --Reset the movement record for each transaction
4598 l_movement_transaction := p_movement_transaction;
4599 l_movement_id := NULL;
4600
4601 FETCH rma_crsr INTO
4602 l_movement_transaction.rcv_transaction_id
4603 , l_receipt_transaction.parent_transaction_id
4604 , l_receipt_transaction.transaction_type
4605 , l_receipt_transaction.source_document_code
4606 , l_movement_transaction.ship_to_site_use_id
4607 , l_movement_transaction.order_header_id
4608 , l_movement_transaction.order_line_id
4609 , l_movement_transaction.transaction_date
4610 , l_movement_transaction.organization_id
4611 , l_subinv_code
4612 , l_mvt_stat_status;-- 7165989
4613 /*bug#7165989 : Added code to fetch Order Number. In the absence of Order Number
4614 Movement Statistic Processor was not able to pick Invoice detail
4615 in First run.*/
4616 IF (l_movement_transaction.order_header_id IS NOT NULL
4617 AND l_movement_transaction.order_number IS null) Then
4618 Begin
4619 SELECT ooha.order_number order_number
4620 INTO l_movement_transaction.order_number
4621 FROM OE_ORDER_HEADERS_ALL ooha
4622 WHERE ooha.header_id= l_movement_transaction.order_header_id;
4623 EXCEPTION
4624 WHEN OTHERS THEN
4625 NULL;
4626 END;
4627 END IF;
4628 /*bug#7165989 : End */
4629
4630 EXIT WHEN rma_crsr%NOTFOUND;
4631
4632 SAVEPOINT RMA_Transaction;
4633
4634 OPEN c_item_type;
4635 FETCH c_item_type INTO
4636 l_item_type_code;
4637
4638 IF c_item_type%NOTFOUND
4639 THEN
4640 l_item_type_code := 'STANDARD';
4641 END IF;
4642 CLOSE c_item_type;
4643
4644 --Timezone support, convert server transaction date to legal entity timezone
4645 l_movement_transaction.transaction_date :=
4646 INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Server
4647 ( p_trxn_date => l_movement_transaction.transaction_date
4648 , p_le_id => l_movement_transaction.entity_org_id
4649 );
4650
4651 INV_MGD_MVT_SETUP_MDTR.Get_Setup_Context
4652 ( p_legal_entity_id => l_movement_transaction.entity_org_id
4653 , p_movement_transaction => l_movement_transaction
4654 , x_return_status => l_return_status
4655 , setup_crsr => setup_crsr
4656 );
4657
4658 --Back up the movement statistics record
4659 l_movement_transaction_outer := l_movement_transaction;
4660
4661 <<l_inner>>
4662 LOOP
4663 --Reset movement transaction record
4664 l_movement_transaction := l_movement_transaction_outer;
4665
4666 FETCH setup_crsr INTO
4667 l_movement_transaction.zone_code
4668 , l_movement_transaction.usage_type
4669 , l_movement_transaction.stat_type
4670 , l_stat_typ_transaction.reference_period_rule
4671 , l_stat_typ_transaction.pending_invoice_days
4672 , l_stat_typ_transaction.prior_invoice_days
4673 , l_stat_typ_transaction.triangulation_mode;
4674
4675 EXIT l_inner WHEN setup_crsr%NOTFOUND;
4676
4677 IF NVL(l_stat_typ_transaction.reference_period_rule,'SHIPMENT_BASED')
4678 = 'INVOICE_BASED'
4679 THEN
4680 IF l_movement_transaction.document_source_type IS NULL
4681 THEN
4682 l_movement_transaction.document_source_type := 'RMA';
4683 END IF;
4684
4685 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
4686 ( p_stat_typ_transaction => l_stat_typ_transaction
4687 , x_movement_transaction => l_movement_transaction
4688 );
4689
4690 INV_MGD_MVT_FIN_MDTR. Get_Reference_Date
4691 ( p_stat_typ_transaction => l_stat_typ_transaction
4692 , x_movement_transaction => l_movement_transaction
4693 );
4694
4695 l_movement_transaction.transaction_date :=
4696 l_movement_transaction.reference_date;
4697 END IF;
4698
4699 INV_MGD_MVT_SETUP_MDTR.Get_Reference_Context
4700 ( p_legal_entity_id => l_movement_transaction.entity_org_id
4701 , p_start_date => p_start_date
4702 , p_end_date => p_end_date
4703 , p_transaction_type => p_transaction_type
4704 , p_movement_transaction => l_movement_transaction
4705 , x_return_status => l_return_status
4706 , ref_crsr => ref_crsr
4707 );
4708
4709 -- Reset the movement_id before fetching the transaction
4710
4711 l_movement_transaction.movement_id := NULL;
4712
4713 -- Bug:5920143. Added new parameter include_establishments in result.
4714 FETCH ref_crsr INTO
4715 l_movement_transaction.zone_code
4716 , l_movement_transaction.usage_type
4717 , l_movement_transaction.stat_type
4718 , l_stat_typ_transaction.start_period_name
4719 , l_stat_typ_transaction.end_period_name
4720 , l_stat_typ_transaction.period_set_name
4721 , l_stat_typ_transaction.period_type
4722 , l_stat_typ_transaction.weight_uom_code
4723 , l_stat_typ_transaction.conversion_type
4724 , l_stat_typ_transaction.attribute_rule_set_code
4725 , l_stat_typ_transaction.alt_uom_rule_set_code
4726 , l_stat_typ_transaction.start_date
4727 , l_stat_typ_transaction.end_date
4728 , l_stat_typ_transaction.category_set_id
4729 , l_movement_transaction.set_of_books_period
4730 , l_stat_typ_transaction.gl_currency_code
4731 , l_movement_transaction.gl_currency_code
4732 , l_stat_typ_transaction.conversion_option
4733 , l_stat_typ_transaction.triangulation_mode
4734 , l_stat_typ_transaction.reference_period_rule
4735 , l_stat_typ_transaction.pending_invoice_days
4736 , l_stat_typ_transaction.prior_invoice_days
4737 , l_stat_typ_transaction.returns_processing
4738 , l_stat_typ_transaction.kit_method
4739 , l_stat_typ_transaction.include_establishments;
4740
4741 IF ref_crsr%NOTFOUND
4742 THEN
4743 --the transaction is not inside of start period and end period
4744 --so not create transaction
4745 CLOSE ref_crsr;
4746 ELSE
4747 INV_MGD_MVT_STATS_PVT.Init_Movement_Record
4748 (x_movement_transaction => l_movement_transaction);
4749
4750 -- The RMA details is fetched here because sometimes the ship_to_site
4751 -- is not present in the RCV table; hence we get the ship to site from
4752 -- the sales order. This guarentees that the ship to site is not null
4753
4754 INV_MGD_MVT_PO_MDTR.Get_RMA_Details
4755 ( p_stat_typ_transaction => l_stat_typ_transaction
4756 , x_movement_transaction => l_movement_transaction
4757 , x_return_status => l_return_status
4758 );
4759
4760 --Fix bug3057775. Consider to create mvt RMA at the LE where this
4761 --RMA is created when the triangulation mode is invoice based.
4762 --Find out the legal entity where this RMA is created(use existing
4763 --procedure for SO)
4764 l_rma_le_id := INV_MGD_MVT_UTILS_PKG.Get_SO_Legal_Entity
4765 (p_order_line_id => l_movement_transaction.order_line_id);
4766
4767 --Find out the legal entity where this RMA is received
4768 l_receiving_le_id := INV_MGD_MVT_UTILS_PKG.Get_Shipping_Legal_Entity
4769 (p_warehouse_id => l_movement_transaction.organization_id);
4770
4771 --Get subinventory location fix bug 2683302
4772 l_subinv_terr_code :=
4773 INV_MGD_MVT_UTILS_PKG.Get_Subinv_Location
4774 ( p_warehouse_id => l_movement_transaction.organization_id
4775 , p_subinv_code => l_subinv_code);
4776
4777 l_org_terr_code :=
4778 INV_MGD_MVT_UTILS_PKG.Get_Org_Location
4779 (p_warehouse_id => l_movement_transaction.organization_id);
4780
4781 --Get the country where the processor is run
4782 l_le_terr_code :=
4783 INV_MGD_MVT_UTILS_PKG.Get_LE_Location
4784 (p_le_id => l_movement_transaction.entity_org_id);
4785
4786 l_movement_transaction.dispatch_territory_code :=
4787 INV_MGD_MVT_UTILS_PKG.Get_Site_Location
4788 (p_site_use_id => l_movement_transaction.ship_to_site_use_id);
4789 /*7165989*/
4790 l_sold_from_org_code :=
4791 INV_MGD_MVT_UTILS_PKG.Get_Org_Location
4792 (p_warehouse_id => l_movement_transaction.sold_from_org_id);
4793 FND_FILE.put_line (FND_FILE.log , '< l_sold_from_org_code - '|| l_sold_from_org_code );
4794 /*7165989*/
4795 --Initialize insert flag
4796 l_insert_flag := 'N';
4797
4798 --Fix bug3057775. Initialize process flag, only when this flag is 'Y'
4799 --the mvt_stat_status in rcv_transactions will be upgrade to 'PROCESSED'
4800 --otherwise the status remians to 'NEW', so that it will be picked up again
4801 --when run processor in the other legal entity
4802 --Fix bug 5453241 do not initialize here. This will cause status reset
4803 --to 'N' after successfully create a record in first loop. If the
4804 --status is set back to 'N', the final rcv mvt_status will not be updated
4805 --to 'PROCESSED'
4806 --l_process_flag := 'N';
4807
4808 --Fix bug3057775. Depend on the triangulation mode, the mvt RMA
4809 --record maybe created at the creating LE not the receiving LE
4810 IF l_rma_le_id IS NOT NULL AND (l_rma_le_id <> l_receiving_le_id)
4811 THEN
4812 --Processor is run at the legal entity where the RMA is created
4813 IF l_rma_le_id = l_movement_transaction.entity_org_id
4814 THEN
4815 /* 7165989 - check for mvt_stat_status in RCV_TRANSACTIONS before creating triangulation records */
4816 /* Create records only when the status is 'NEW' or 'FORDISP'(record already created in the receiving LE)*/
4817 IF ((NVL(l_stat_typ_transaction.triangulation_mode,'INVOICE_BASED')
4818 = 'INVOICE_BASED') AND (l_mvt_stat_status is NOT NULL )
4819 AND (l_mvt_stat_status = 'NEW' OR l_mvt_stat_status = 'FORDISP')
4820 )
4821 THEN
4822 l_movement_transaction.destination_territory_code := l_le_terr_code;
4823 /* 7165989 triangulation country would be the country where the RMA was created / booked */
4824 l_movement_transaction.triangulation_country_code := l_sold_from_org_code;
4825
4826 l_insert_flag := INV_MGD_MVT_SETUP_MDTR.Process_Setup_Context
4827 ( p_movement_transaction => l_movement_transaction);
4828 l_process_flag := 'Y';
4829 /* 7165989*/
4830 IF (l_insert_flag = 'Y' ) THEN
4831 Process_RMA_Triangulation
4832 ( p_movement_transaction => l_movement_transaction
4833 , p_stat_typ_transaction => l_stat_typ_transaction
4834 , x_return_status => x_return_status
4835 );
4836 /* 7165989 - Change mvt_stat_status in RCV_TRANSACTIONS based on the triangulation records created */
4837 /* If status is NEW , change status to 'FORARVL' so that the arrival record can be created in the*/
4838 /* receiving LE. If status is 'FORDISP', set status to 'PROCESSED' */
4839 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4840 IF (l_mvt_stat_status is NOT NULL AND l_mvt_stat_status = 'NEW') THEN
4841 l_mvt_stat_status := 'FORARVL';
4842 ELSIF (l_mvt_stat_status is NOT NULL AND l_mvt_stat_status = 'FORDISP') THEN
4843 l_mvt_stat_status := 'PROCESSED';
4844 END IF;
4845 END IF;
4846
4847 FND_FILE.put_line (FND_FILE.log ,'mvt stat status after update'||l_mvt_stat_status );
4848 FND_FILE.put_line (FND_FILE.log ,'Case 1 : RMA Invoice Based Triangulation run at booking LE' );
4849
4850 ELSIF (l_insert_flag = 'N') THEN
4851 l_dispatch :=
4852 INV_MGD_MVT_UTILS_PKG.Get_Org_Location(p_warehouse_id => l_movement_transaction.sold_from_org_id);
4853 l_destination :=
4854 INV_MGD_MVT_UTILS_PKG.Get_Org_Location(p_warehouse_id => l_movement_transaction.organization_id);
4855
4856 if (l_dispatch <> l_destination ) THEN
4857
4858 l_ship_from_loc :=
4859 INV_MGD_MVT_UTILS_PKG.Get_Zone_Code
4860 ( p_territory_code => l_dispatch
4861 , p_zone_code => l_movement_transaction.zone_code
4862 , p_trans_date => l_movement_Transaction.transaction_date
4863 );
4864
4865 l_ship_to_loc :=
4866 INV_MGD_MVT_UTILS_PKG.Get_Zone_Code
4867 ( p_territory_code => l_destination
4868 , p_zone_code => l_movement_transaction.zone_code
4869 , p_trans_date => l_movement_Transaction.transaction_date
4870 );
4871
4872 IF l_movement_transaction.usage_type = 'INTERNAL'
4873 THEN
4874
4875 IF (l_ship_from_loc IS NOT NULL)
4876 AND (l_ship_to_loc IS NOT NULL)
4877 AND (l_ship_from_loc = l_ship_to_loc)
4878 THEN
4879 l_insert := 'Y';
4880 ELSE
4881 l_insert := 'N';
4882 END If;
4883 ELSIF l_movement_transaction.usage_type = 'EXTERNAL'
4884 THEN
4885 IF (l_ship_from_loc IS NULL)
4886 AND (l_ship_to_loc IS NULL)
4887 THEN
4888 l_insert_flag := 'N';
4889 ELSIF (l_ship_from_loc IS NULL)
4890 OR (l_ship_to_loc IS NULL)
4891 AND (NVL(l_ship_from_loc,'NONE') <> NVL(l_ship_to_loc,'NONE'))
4892 THEN
4893 l_insert := 'Y';
4894 ELSE
4895 l_insert := 'N';
4896 END IF;
4897 ELSE
4898 l_insert := 'N';
4899 END IF;
4900
4901 if (l_insert = 'Y') THEN
4902
4903 Process_RMA_Triangulation
4904 ( p_movement_transaction => l_movement_transaction
4905 , p_stat_typ_transaction => l_stat_typ_transaction
4906 , x_return_status => x_return_status
4907 );
4908 /* 6732517 - Change mvt_stat_status in RCV_TRANSACTIONS based on the triangulation records created */
4909 /* If status is NEW , change status to 'FORARVL' so that the arrival record can be created in the*/
4910 /* receiving LE. If status is 'FORDISP', set status to 'PROCESSED' */
4911
4912
4913 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4914 IF (l_mvt_stat_status is NOT NULL AND l_mvt_stat_status = 'NEW') THEN
4915 l_mvt_stat_status := 'FORARVL';
4916 ELSIF (l_mvt_stat_status is NOT NULL AND l_mvt_stat_status = 'FORDISP') THEN
4917 l_mvt_stat_status := 'PROCESSED';
4918 END IF;
4919 END IF;
4920
4921 FND_FILE.put_line (FND_FILE.log ,'mvt stat status after update'||l_mvt_stat_status );
4922 FND_FILE.put_line (FND_FILE.log ,'Case 1.1 : RMA Invoice Based Triangulation run at booking LE where booking LE country is same as the customer country' );
4923 END IF;
4924 END IF ;
4925
4926 END IF;
4927 /* 7165989*/
4928 END IF;
4929 --Processor is run at the legal entity where the RMA is received
4930 ELSIF l_receiving_le_id = l_movement_transaction.entity_org_id
4931 THEN
4932 IF (NVL(l_stat_typ_transaction.triangulation_mode,'INVOICE_BASED')
4933 = 'SHIPMENT_BASED')
4934 THEN
4935 l_movement_transaction.destination_territory_code :=
4936 NVL(l_subinv_terr_code, l_org_terr_code);
4937 l_process_flag := 'Y';
4938
4939 --Only create record if organization id is located in the same country
4940 --as legal entity
4941 -- Bug: 5920143 Validation that LE Territory Code and
4942 -- Destination Org Territory Code should be same, is needed only when
4943 -- user has selected Include Establishments as No.
4944 IF (l_stat_typ_transaction.include_establishments = 'Y' OR
4945 l_le_terr_code = l_movement_transaction.destination_territory_code)
4946 THEN
4947 l_insert_flag := INV_MGD_MVT_SETUP_MDTR.Process_Setup_Context
4948 ( p_movement_transaction => l_movement_transaction);
4949 END IF;
4950 FND_FILE.put_line (FND_FILE.log ,'Case 2 : RMA Shipment Based Triangulation run at receiving LE' );
4951 END IF;
4952 /********************** Process RMA Triangulation at receiving LE- 7165989 - Start ****************************/
4953
4954 /* 7165989 - check for mvt_stat_status in RCV_TRANSACTIONS before creating triangulation records */
4955 /* Create records only when the status is 'NEW' or 'ARVL(record already created in the selling LE)*/
4956 IF ((NVL(l_stat_typ_transaction.triangulation_mode,'INVOICE_BASED') = 'INVOICE_BASED')
4957 AND (l_mvt_stat_status is NOT NULL )
4958 AND (l_mvt_stat_status = 'NEW' OR l_mvt_stat_status = 'FORARVL')
4959 )
4960 THEN
4961
4962 l_movement_transaction.destination_territory_code :=
4963 NVL(l_subinv_terr_code, l_org_terr_code);
4964 l_process_flag := 'Y';
4965
4966 /* The dispatch territory code will be the country where the RMA
4967 was booked - sold_from_org_id from OE_ORDER_HEADERS_ALL */
4968 l_movement_transaction.dispatch_territory_code := l_sold_from_org_code;
4969 /* triangulation country would be the country where the RMA was created / booked */
4970 l_movement_transaction.triangulation_country_code := l_sold_from_org_code;
4971
4972
4973 --Only create record if organization id is located in the same country
4974 --as legal entity
4975 -- Bug: 5765897 Validation that LE Territory Code and
4976 -- Destination Org Territory Code should be same, is needed only when
4977 -- user has selected Include Establishments as No.
4978 IF (l_stat_typ_transaction.include_establishments = 'Y' OR
4979 l_le_terr_code = l_movement_transaction.destination_territory_code)
4980 THEN
4981 l_insert_flag := INV_MGD_MVT_SETUP_MDTR.Process_Setup_Context
4982 ( p_movement_transaction => l_movement_transaction);
4983 END IF;
4984 /* 6732517 - Change mvt_stat_status in RCV_TRANSACTIONS based on the triangulation records created */
4985 /* If status is NEW , change status to 'FORARVL' so that the arrival record can be created in the*/
4986 /* receiving LE. If status is 'FORDISP', set status to 'PROCESSED' */
4987
4988 IF (l_insert_flag = 'Y') THEN
4989 IF (l_mvt_stat_status is NOT NULL AND l_mvt_stat_status = 'NEW') THEN
4990 l_mvt_stat_status := 'FORDISP';
4991 ELSIF (l_mvt_stat_status is NOT NULL AND l_mvt_stat_status = 'FORARVL') THEN
4992 l_mvt_stat_status := 'PROCESSED';
4993 END IF;
4994 END IF;
4995 FND_FILE.put_line (FND_FILE.log ,'Case 3 : RMA Invoice Based Triangulation run at receiving LE' );
4996
4997 END IF;
4998
4999 /********************** Process RMA Triangulation at receiving LE - 7165989 - End ****************************/
5000
5001 END IF;
5002 ELSE --regular RMA case,the creating LE is same as received LE
5003 l_movement_transaction.destination_territory_code :=
5004 NVL(l_subinv_terr_code, l_org_terr_code);
5005 l_process_flag := 'Y';
5006
5007 --Only create record if organization id is located in the same country
5008 --as legal entity
5009 -- Bug: 5920143 Validation that LE Territory Code and
5010 -- Destination Org Territory Code should be same, is needed only when
5011 -- user has selected Include Establishments as No.
5012 IF (l_stat_typ_transaction.include_establishments = 'Y' OR
5013 l_le_terr_code = l_movement_transaction.destination_territory_code )
5014 THEN
5015 l_insert_flag := INV_MGD_MVT_SETUP_MDTR.Process_Setup_Context
5016 ( p_movement_transaction => l_movement_transaction);
5017 END IF;
5018
5019 END IF;
5020
5021 IF (l_insert_flag = 'Y'
5022 AND l_return_status = 'Y')
5023 THEN
5024 l_process_flag := 'Y';
5025
5026 INV_MGD_MVT_UTILS_PKG.Mvt_Stats_Util_Info
5027 ( p_stat_typ_transaction => l_stat_typ_transaction
5028 , x_movement_transaction => l_movement_transaction
5029 , x_return_status => l_return_status
5030 );
5031
5032 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
5033 THEN
5034 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5035 THEN
5036 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
5037 , G_MODULE_NAME || l_api_name
5038 || '.Failed when call mvt_stats_util_info'
5039 ,'Failed'
5040 );
5041 END IF;
5042 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5043 ELSE
5044 l_movement_transaction.customer_vat_number :=
5045 INV_MGD_MVT_UTILS_PKG.Get_Cust_VAT_Number
5046 (l_movement_transaction.bill_to_site_use_id);
5047
5048 IF l_movement_transaction.invoice_id IS NULL
5049 THEN
5050 l_movement_transaction.invoice_quantity := NULL;
5051 l_movement_transaction.financial_document_flag := 'MISSING';
5052 ELSE
5053 l_movement_transaction.financial_document_flag
5054 := 'PROCESSED_INCLUDED';
5055 END IF;
5056
5057 --A RMA transaction may need to be netted into its parent
5058 --SO if the returns processing parameter is set to "Aggregate Return"
5059 --and the parent SO is not Frozen or Exported
5060 IF l_stat_typ_transaction.returns_processing = 'AGGRTN'
5061 THEN
5062 Update_SO_With_RMA
5063 ( x_mvt_rma_transaction => l_movement_transaction
5064 , x_return_status => l_insert_status
5065 );
5066 ELSE
5067 INV_MGD_MVT_STATS_PVT.Create_Movement_Statistics
5068 (p_api_version_number => 1.0
5069 ,p_init_msg_list => FND_API.G_FALSE
5070 ,x_movement_transaction => l_movement_transaction
5071 ,x_msg_count => x_msg_count
5072 ,x_msg_data => x_msg_data
5073 ,x_return_status => l_insert_status
5074 );
5075 END IF;
5076
5077 --yawang fix bug 2268875
5078 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
5079 AND l_movement_transaction.movement_id IS NOT NULL
5080 THEN
5081 l_movement_id := l_movement_transaction.movement_id;
5082 g_records_inserted := g_records_inserted +1;
5083 END IF;
5084 END IF;
5085 END IF;
5086
5087 CLOSE ref_crsr;
5088 END IF;
5089 END LOOP l_inner;
5090 CLOSE setup_crsr;
5091
5092 IF NVL(l_insert_status,FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS
5093 THEN
5094 l_movement_transaction.movement_id := l_movement_id;
5095
5096 IF NVL(l_process_flag,'N') = 'Y'
5097 THEN
5098 INV_MGD_MVT_PO_MDTR.Update_PO_Transactions
5099 ( p_movement_transaction => l_movement_transaction
5100 , p_mvt_stat_status => l_mvt_stat_status /* 7165989 - Pass the appropriate mvt_stat_status for RMA triangulation txns */
5101 , x_return_status => l_return_status
5102 );
5103 END IF;
5104
5105 COMMIT;
5106 ELSE
5107 ROLLBACK TO SAVEPOINT RMA_Transaction;
5108 END IF;
5109
5110 g_records_processed := g_records_processed +1;
5111 l_movement_transaction := p_movement_transaction;
5112 END LOOP l_outer;
5113 CLOSE rma_crsr;
5114 END IF;
5115
5116 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5117 THEN
5118 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5119 , G_MODULE_NAME || l_api_name || '.end'
5120 ,'exit procedure'
5121 );
5122 END IF;
5123
5124 EXCEPTION
5125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5127 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
5128
5129 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5130 THEN
5131 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
5132 , G_MODULE_NAME || l_api_name||'. Unexpected exception'
5133 , l_error
5134 );
5135 END IF;
5136
5137 WHEN NO_DATA_FOUND THEN
5138 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5139 THEN
5140 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
5141 , G_MODULE_NAME || l_api_name||'. No data found exception'
5142 , l_error
5143 );
5144 END IF;
5145 RAISE;
5146
5147 WHEN OTHERS THEN
5148 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5149 THEN
5150 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
5151 , 'Others exception in '||l_api_name
5152 );
5153 END IF;
5154
5155 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5156 THEN
5157 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
5158 , G_MODULE_NAME || l_api_name||'. Others exception'
5159 , l_error
5160 );
5161 END IF;
5162 RAISE;
5163
5164 END Process_RMA_Transaction;
5165
5166
5167 --========================================================================
5168 -- PROCEDURE : Update_Invoice_Info PRIVATE
5169 -- PARAMETERS: x_return_status status flag
5170 -- p_legal_entity_id Legal Entity ID
5171 -- p_start_date Transaction start date
5172 -- p_end_date Transaction end date
5173 -- p_transaction type Transaction Type
5174 -- COMMENT :
5175 -- This updates the invoice information for the particular
5176 -- transaction_type
5177 --========================================================================
5178
5179 PROCEDURE Update_Invoice_Info
5180 ( p_movement_transaction IN
5181 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
5182 , p_start_date IN DATE
5183 , p_end_date IN DATE
5184 , p_transaction_type IN VARCHAR2
5185 , x_return_status OUT NOCOPY VARCHAR2
5186 )
5187 IS
5188 -- Declare the REF Cursor
5189 inv_crsr INV_MGD_MVT_DATA_STR.invCurTyp;
5190 setup_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
5191 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
5192 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
5193 l_insert_flag VARCHAR2(1);
5194 x_msg_count NUMBER;
5195 x_msg_data VARCHAR2(2000);
5196 l_insert_status VARCHAR2(10);
5197 l_movement_id NUMBER;
5198 l_return_status VARCHAR2(1);
5199 l_api_name CONSTANT VARCHAR2(30) := 'Update_Invoice_Info';
5200 l_error VARCHAR2(600);
5201
5202 BEGIN
5203 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5204 THEN
5205 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5206 , G_MODULE_NAME || l_api_name || '.begin'
5207 ,'enter procedure'
5208 );
5209 END IF;
5210
5211 x_return_status := FND_API.G_RET_STS_SUCCESS;
5212 l_movement_transaction := p_movement_transaction;
5213
5214 -- Call the transaction proxy which processes all the transactions.
5215
5216 INV_MGD_MVT_STATS_PVT.Get_Invoice_Transactions
5217 ( inv_crsr => inv_crsr
5218 , p_movement_transaction => l_movement_transaction
5219 , p_start_date => p_start_date
5220 , p_end_date => p_end_date
5221 , p_transaction_type => p_transaction_type
5222 , x_return_status => l_return_status);
5223
5224 IF l_return_status = 'Y' THEN
5225 LOOP
5226 --Reset the movement record for each transaction
5227 l_movement_transaction := p_movement_transaction;
5228 l_movement_id := NULL;
5229
5230 FETCH inv_crsr INTO
5231 l_movement_transaction.movement_id
5232 , l_movement_transaction.organization_id
5233 , l_movement_transaction.entity_org_id
5234 , l_movement_transaction.movement_type
5235 , l_movement_transaction.movement_status
5236 , l_movement_transaction.transaction_date
5237 , l_movement_transaction.last_update_date
5238 , l_movement_transaction.last_updated_by
5239 , l_movement_transaction.creation_date
5240 , l_movement_transaction.created_by
5241 , l_movement_transaction.last_update_login
5242 , l_movement_transaction.document_source_type
5243 , l_movement_transaction.creation_method
5244 , l_movement_transaction.document_reference
5245 , l_movement_transaction.document_line_reference
5246 , l_movement_transaction.document_unit_price
5247 , l_movement_transaction.document_line_ext_value
5248 , l_movement_transaction.receipt_reference
5249 , l_movement_transaction.shipment_reference
5250 , l_movement_transaction.shipment_line_reference
5251 , l_movement_transaction.pick_slip_reference
5252 , l_movement_transaction.customer_name
5253 , l_movement_transaction.customer_number
5254 , l_movement_transaction.customer_location
5255 , l_movement_transaction.transacting_from_org
5256 , l_movement_transaction.transacting_to_org
5257 , l_movement_transaction.vendor_name
5258 , l_movement_transaction.vendor_number
5259 , l_movement_transaction.vendor_site
5260 , l_movement_transaction.bill_to_name
5261 , l_movement_transaction.bill_to_number
5262 , l_movement_transaction.bill_to_site
5263 , l_movement_transaction.po_header_id
5264 , l_movement_transaction.po_line_id
5265 , l_movement_transaction.po_line_location_id
5266 , l_movement_transaction.order_header_id
5267 , l_movement_transaction.order_line_id
5268 , l_movement_transaction.picking_line_id
5269 , l_movement_transaction.shipment_header_id
5270 , l_movement_transaction.shipment_line_id
5271 , l_movement_transaction.ship_to_customer_id
5272 , l_movement_transaction.ship_to_site_use_id
5273 , l_movement_transaction.bill_to_customer_id
5274 , l_movement_transaction.bill_to_site_use_id
5275 , l_movement_transaction.vendor_id
5276 , l_movement_transaction.vendor_site_id
5277 , l_movement_transaction.from_organization_id
5278 , l_movement_transaction.to_organization_id
5279 , l_movement_transaction.parent_movement_id
5280 , l_movement_transaction.inventory_item_id
5281 , l_movement_transaction.item_description
5282 , l_movement_transaction.item_cost
5283 , l_movement_transaction.transaction_quantity
5284 , l_movement_transaction.transaction_uom_code
5285 , l_movement_transaction.primary_quantity
5286 , l_movement_transaction.invoice_batch_id
5287 , l_movement_transaction.invoice_id
5288 , l_movement_transaction.customer_trx_line_id
5289 , l_movement_transaction.invoice_batch_reference
5290 , l_movement_transaction.invoice_reference
5291 , l_movement_transaction.invoice_line_reference
5292 , l_movement_transaction.invoice_date_reference
5293 , l_movement_transaction.invoice_quantity
5294 , l_movement_transaction.invoice_unit_price
5295 , l_movement_transaction.invoice_line_ext_value
5296 , l_movement_transaction.outside_code
5297 , l_movement_transaction.outside_ext_value
5298 , l_movement_transaction.outside_unit_price
5299 , l_movement_transaction.currency_code
5300 , l_movement_transaction.currency_conversion_rate
5301 , l_movement_transaction.currency_conversion_type
5302 , l_movement_transaction.currency_conversion_date
5303 , l_movement_transaction.period_name
5304 , l_movement_transaction.report_reference
5305 , l_movement_transaction.report_date
5306 , l_movement_transaction.category_id
5307 , l_movement_transaction.weight_method
5308 , l_movement_transaction.unit_weight
5309 , l_movement_transaction.total_weight
5310 , l_movement_transaction.transaction_nature
5311 , l_movement_transaction.delivery_terms
5312 , l_movement_transaction.transport_mode
5313 , l_movement_transaction.alternate_quantity
5314 , l_movement_transaction.alternate_uom_code
5315 , l_movement_transaction.dispatch_territory_code
5316 , l_movement_transaction.destination_territory_code
5317 , l_movement_transaction.origin_territory_code
5318 , l_movement_transaction.stat_method
5319 , l_movement_transaction.stat_adj_percent
5320 , l_movement_transaction.stat_adj_amount
5321 , l_movement_transaction.stat_ext_value
5322 , l_movement_transaction.area
5323 , l_movement_transaction.port
5324 , l_movement_transaction.stat_type
5325 , l_movement_transaction.comments
5326 , l_movement_transaction.attribute_category
5327 , l_movement_transaction.commodity_code
5328 , l_movement_transaction.commodity_description
5329 , l_movement_transaction.requisition_header_id
5330 , l_movement_transaction.requisition_line_id
5331 , l_movement_transaction.picking_line_detail_id
5332 , l_movement_transaction.usage_type
5333 , l_movement_transaction.zone_code
5334 , l_movement_transaction.edi_sent_flag
5335 , l_movement_transaction.statistical_procedure_code
5336 , l_movement_transaction.movement_amount
5337 , l_movement_transaction.triangulation_country_code
5338 , l_movement_transaction.csa_code
5339 , l_movement_transaction.oil_reference_code
5340 , l_movement_transaction.container_type_code
5341 , l_movement_transaction.flow_indicator_code
5342 , l_movement_transaction.affiliation_reference_code
5343 , l_movement_transaction.origin_territory_eu_code
5344 , l_movement_transaction.destination_territory_eu_code
5345 , l_movement_transaction.dispatch_territory_eu_code
5346 , l_movement_transaction.set_of_books_period
5347 , l_movement_transaction.taric_code
5348 , l_movement_transaction.preference_code
5349 , l_movement_transaction.rcv_transaction_id
5350 , l_movement_transaction.mtl_transaction_id
5351 , l_movement_transaction.total_weight_uom_code
5352 , l_movement_transaction.financial_document_flag
5353 --, l_movement_transaction.opm_trans_id
5354 , l_movement_transaction.customer_vat_number
5355 , l_movement_transaction.attribute1
5356 , l_movement_transaction.attribute2
5357 , l_movement_transaction.attribute3
5358 , l_movement_transaction.attribute4
5359 , l_movement_transaction.attribute5
5360 , l_movement_transaction.attribute6
5361 , l_movement_transaction.attribute7
5362 , l_movement_transaction.attribute8
5363 , l_movement_transaction.attribute9
5364 , l_movement_transaction.attribute10
5365 , l_movement_transaction.attribute11
5366 , l_movement_transaction.attribute12
5367 , l_movement_transaction.attribute13
5368 , l_movement_transaction.attribute14
5369 , l_movement_transaction.attribute15
5370 , l_movement_transaction.triangulation_country_eu_code
5371 , l_movement_transaction.distribution_line_number
5372 , l_movement_transaction.ship_to_name
5373 , l_movement_transaction.ship_to_number
5374 , l_movement_transaction.ship_to_site
5375 , l_movement_transaction.edi_transaction_date
5376 , l_movement_transaction.edi_transaction_reference
5377 , l_movement_transaction.esl_drop_shipment_code;
5378
5379 EXIT WHEN inv_crsr%NOTFOUND;
5380
5381 INV_MGD_MVT_SETUP_MDTR.Get_Invoice_Context
5382 ( p_legal_entity_id => l_movement_transaction.entity_org_id
5383 , p_start_date => p_start_date
5384 , p_end_date => p_end_date
5385 , p_transaction_type => p_transaction_type
5386 , p_movement_transaction => l_movement_transaction
5387 , x_return_status => l_return_status
5388 , setup_crsr => setup_crsr
5389 );
5390
5391
5392 IF l_return_status = 'Y' THEN
5393
5394 LOOP
5395
5396 FETCH setup_crsr INTO
5397 l_stat_typ_transaction.start_period_name
5398 , l_stat_typ_transaction.end_period_name
5399 , l_stat_typ_transaction.period_set_name
5400 , l_stat_typ_transaction.period_type
5401 , l_stat_typ_transaction.weight_uom_code
5402 , l_stat_typ_transaction.conversion_type
5403 , l_stat_typ_transaction.attribute_rule_set_code
5404 , l_stat_typ_transaction.alt_uom_rule_set_code
5405 , l_stat_typ_transaction.start_date
5406 , l_stat_typ_transaction.end_date
5407 , l_stat_typ_transaction.category_set_id
5408 , l_stat_typ_transaction.gl_currency_code
5409 , l_movement_transaction.gl_currency_code
5410 , l_stat_typ_transaction.conversion_option
5411 , l_stat_typ_transaction.triangulation_mode
5412 , l_stat_typ_transaction.reference_period_rule
5413 , l_stat_typ_transaction.pending_invoice_days
5414 , l_stat_typ_transaction.prior_invoice_days
5415 , l_stat_typ_transaction.returns_processing;
5416
5417 EXIT WHEN setup_crsr%NOTFOUND;
5418
5419 INV_MGD_MVT_UTILS_PKG.Get_Order_Number
5420 ( x_movement_transaction => l_movement_transaction
5421 );
5422
5423 INV_MGD_MVT_PO_MDTR.Get_Blanket_Info
5424 ( x_movement_transaction => l_movement_transaction
5425 );
5426
5427 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
5428 ( p_stat_typ_transaction => l_stat_typ_transaction
5429 , x_movement_transaction => l_movement_transaction
5430 );
5431
5432 l_movement_transaction.movement_amount :=
5433 INV_MGD_MVT_FIN_MDTR.Calc_Movement_Amount
5434 (p_movement_transaction => l_movement_transaction
5435 );
5436
5437 --Calculate freight charge and include in statistics value
5438 l_movement_transaction.stat_ext_value :=
5439 INV_MGD_MVT_FIN_MDTR.Calc_Statistics_Value
5440 (p_movement_transaction => l_movement_transaction);
5441
5442 IF l_movement_transaction.invoice_id IS NOT NULL
5443 THEN
5444 IF l_movement_transaction.financial_document_flag = 'MISSING'
5445 THEN
5446 l_movement_transaction.financial_document_flag := 'PROCESSED_INCLUDED';
5447 END IF;
5448 ELSE
5449 l_movement_transaction.invoice_line_ext_value := null;
5450 l_movement_transaction.invoice_unit_price := null;
5451 l_movement_transaction.invoice_quantity := null;
5452 END IF;
5453
5454
5455 INV_MGD_MVT_STATS_PVT.Update_Movement_Statistics
5456 (p_movement_statistics => l_movement_transaction
5457 , x_return_status => l_return_status
5458 , x_msg_count => x_msg_count
5459 , x_msg_data => x_msg_data
5460 );
5461
5462
5463 END LOOP ;
5464 CLOSE setup_crsr;
5465 END IF;
5466 END LOOP ;
5467 CLOSE inv_crsr;
5468 END IF;
5469
5470 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5471 THEN
5472 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5473 , G_MODULE_NAME || l_api_name || '.end'
5474 ,'exit procedure'
5475 );
5476 END IF;
5477
5478 EXCEPTION
5479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5481 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
5482
5483 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5484 THEN
5485 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
5486 , G_MODULE_NAME || l_api_name||'. Unexpected exception'
5487 , l_error
5488 );
5489 END IF;
5490
5491 WHEN NO_DATA_FOUND THEN
5492 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5493 THEN
5494 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
5495 , G_MODULE_NAME || l_api_name||'. No data found exception'
5496 , l_error
5497 );
5498 END IF;
5499 RAISE;
5500
5501 WHEN OTHERS THEN
5502 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5503 THEN
5504 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
5505 , 'Others exception in '||l_api_name
5506 );
5507 END IF;
5508
5509 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5510 THEN
5511 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
5512 , G_MODULE_NAME || l_api_name||'. Others exception'
5513 , l_error
5514 );
5515 END IF;
5516 RAISE;
5517
5518 END Update_Invoice_Info;
5519
5520
5521 --========================================================================
5522 -- PROCEDURE : Update_PO_With_Correction PRIVATE
5523 -- PARAMETERS: x_return_status status flag
5524 -- p_legal_entity_id Legal Entity ID
5525 -- p_start_date Transaction start date
5526 -- p_end_date Transaction end date
5527 -- p_transaction type Transaction Type
5528 -- COMMENT :
5529 -- This updates the PO transaction with correction if the original
5530 -- PO is not closed yet
5531 --========================================================================
5532
5533
5534 PROCEDURE Update_PO_With_Correction
5535 ( p_legal_entity_id IN NUMBER
5536 , p_start_date IN DATE
5537 , p_end_date IN DATE
5538 , p_transaction_type IN VARCHAR2
5539 , x_return_status OUT NOCOPY VARCHAR2
5540 )
5541 IS
5542 -- Declare the REF Cursor
5543 inv_crsr INV_MGD_MVT_DATA_STR.invCurTyp;
5544 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
5545 x_msg_count NUMBER;
5546 x_msg_data VARCHAR2(2000);
5547 l_correct_qty NUMBER;
5548 l_correct_parimary_qty NUMBER;
5549 l_weight_uom_code VARCHAR2(3);
5550 l_weight_precision NUMBER;
5551 l_total_weight NUMBER;
5552 l_rounding_method VARCHAR2(30);
5553 l_return_status VARCHAR2(1);
5554 l_api_name CONSTANT VARCHAR2(30) := 'Update_PO_With_Correction';
5555 l_error VARCHAR2(600);
5556
5557 --cursor to get correction quantity
5558 CURSOR l_correct_quantity IS
5559 SELECT
5560 SUM(quantity)
5561 , SUM(primary_quantity)
5562 FROM
5563 rcv_transactions
5564 WHERE parent_transaction_id = l_movement_transaction.rcv_transaction_id
5565 AND mvt_stat_status = 'NEW'
5566 AND transaction_type = 'CORRECT';
5567 BEGIN
5568 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5569 THEN
5570 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5571 , G_MODULE_NAME || l_api_name || '.begin'
5572 ,'enter procedure'
5573 );
5574 END IF;
5575
5576 x_return_status := FND_API.G_RET_STS_SUCCESS;
5577
5578 -- Call the transaction proxy which processes all the PO and RTV transactions
5579 -- with corrections in the specified date range.
5580 INV_MGD_MVT_STATS_PVT.Get_PO_Trans_With_Correction
5581 ( inv_crsr => inv_crsr
5582 , p_legal_entity_id => p_legal_entity_id
5583 , p_start_date => p_start_date
5584 , p_end_date => p_end_date
5585 , p_transaction_type => p_transaction_type
5586 , x_return_status => l_return_status);
5587
5588 IF l_return_status = 'Y'
5589 THEN
5590 LOOP
5591 FETCH inv_crsr INTO
5592 l_movement_transaction.movement_id
5593 , l_movement_transaction.organization_id
5594 , l_movement_transaction.entity_org_id
5595 , l_movement_transaction.movement_type
5596 , l_movement_transaction.movement_status
5597 , l_movement_transaction.transaction_date
5598 , l_movement_transaction.last_update_date
5599 , l_movement_transaction.last_updated_by
5600 , l_movement_transaction.creation_date
5601 , l_movement_transaction.created_by
5602 , l_movement_transaction.last_update_login
5603 , l_movement_transaction.document_source_type
5604 , l_movement_transaction.creation_method
5605 , l_movement_transaction.document_reference
5606 , l_movement_transaction.document_line_reference
5607 , l_movement_transaction.document_unit_price
5608 , l_movement_transaction.document_line_ext_value
5609 , l_movement_transaction.receipt_reference
5610 , l_movement_transaction.shipment_reference
5611 , l_movement_transaction.shipment_line_reference
5612 , l_movement_transaction.pick_slip_reference
5613 , l_movement_transaction.customer_name
5614 , l_movement_transaction.customer_number
5615 , l_movement_transaction.customer_location
5616 , l_movement_transaction.transacting_from_org
5617 , l_movement_transaction.transacting_to_org
5618 , l_movement_transaction.vendor_name
5619 , l_movement_transaction.vendor_number
5620 , l_movement_transaction.vendor_site
5621 , l_movement_transaction.bill_to_name
5622 , l_movement_transaction.bill_to_number
5623 , l_movement_transaction.bill_to_site
5624 , l_movement_transaction.po_header_id
5625 , l_movement_transaction.po_line_id
5626 , l_movement_transaction.po_line_location_id
5627 , l_movement_transaction.order_header_id
5628 , l_movement_transaction.order_line_id
5629 , l_movement_transaction.picking_line_id
5630 , l_movement_transaction.shipment_header_id
5631 , l_movement_transaction.shipment_line_id
5632 , l_movement_transaction.ship_to_customer_id
5633 , l_movement_transaction.ship_to_site_use_id
5634 , l_movement_transaction.bill_to_customer_id
5635 , l_movement_transaction.bill_to_site_use_id
5636 , l_movement_transaction.vendor_id
5637 , l_movement_transaction.vendor_site_id
5638 , l_movement_transaction.from_organization_id
5639 , l_movement_transaction.to_organization_id
5640 , l_movement_transaction.parent_movement_id
5641 , l_movement_transaction.inventory_item_id
5642 , l_movement_transaction.item_description
5643 , l_movement_transaction.item_cost
5644 , l_movement_transaction.transaction_quantity
5645 , l_movement_transaction.transaction_uom_code
5646 , l_movement_transaction.primary_quantity
5647 , l_movement_transaction.invoice_batch_id
5648 , l_movement_transaction.invoice_id
5649 , l_movement_transaction.customer_trx_line_id
5650 , l_movement_transaction.invoice_batch_reference
5651 , l_movement_transaction.invoice_reference
5652 , l_movement_transaction.invoice_line_reference
5653 , l_movement_transaction.invoice_date_reference
5654 , l_movement_transaction.invoice_quantity
5655 , l_movement_transaction.invoice_unit_price
5656 , l_movement_transaction.invoice_line_ext_value
5657 , l_movement_transaction.outside_code
5658 , l_movement_transaction.outside_ext_value
5659 , l_movement_transaction.outside_unit_price
5660 , l_movement_transaction.currency_code
5661 , l_movement_transaction.currency_conversion_rate
5662 , l_movement_transaction.currency_conversion_type
5663 , l_movement_transaction.currency_conversion_date
5664 , l_movement_transaction.period_name
5665 , l_movement_transaction.report_reference
5666 , l_movement_transaction.report_date
5667 , l_movement_transaction.category_id
5668 , l_movement_transaction.weight_method
5669 , l_movement_transaction.unit_weight
5670 , l_movement_transaction.total_weight
5671 , l_movement_transaction.transaction_nature
5672 , l_movement_transaction.delivery_terms
5673 , l_movement_transaction.transport_mode
5674 , l_movement_transaction.alternate_quantity
5675 , l_movement_transaction.alternate_uom_code
5676 , l_movement_transaction.dispatch_territory_code
5677 , l_movement_transaction.destination_territory_code
5678 , l_movement_transaction.origin_territory_code
5679 , l_movement_transaction.stat_method
5680 , l_movement_transaction.stat_adj_percent
5681 , l_movement_transaction.stat_adj_amount
5682 , l_movement_transaction.stat_ext_value
5683 , l_movement_transaction.area
5684 , l_movement_transaction.port
5685 , l_movement_transaction.stat_type
5686 , l_movement_transaction.comments
5687 , l_movement_transaction.attribute_category
5688 , l_movement_transaction.commodity_code
5689 , l_movement_transaction.commodity_description
5690 , l_movement_transaction.requisition_header_id
5691 , l_movement_transaction.requisition_line_id
5692 , l_movement_transaction.picking_line_detail_id
5693 , l_movement_transaction.usage_type
5694 , l_movement_transaction.zone_code
5695 , l_movement_transaction.edi_sent_flag
5696 , l_movement_transaction.statistical_procedure_code
5697 , l_movement_transaction.movement_amount
5698 , l_movement_transaction.triangulation_country_code
5699 , l_movement_transaction.csa_code
5700 , l_movement_transaction.oil_reference_code
5701 , l_movement_transaction.container_type_code
5702 , l_movement_transaction.flow_indicator_code
5703 , l_movement_transaction.affiliation_reference_code
5704 , l_movement_transaction.origin_territory_eu_code
5705 , l_movement_transaction.destination_territory_eu_code
5706 , l_movement_transaction.dispatch_territory_eu_code
5707 , l_movement_transaction.set_of_books_period
5708 , l_movement_transaction.taric_code
5709 , l_movement_transaction.preference_code
5710 , l_movement_transaction.rcv_transaction_id
5711 , l_movement_transaction.mtl_transaction_id
5712 , l_movement_transaction.total_weight_uom_code
5713 , l_movement_transaction.financial_document_flag
5714 --, l_movement_transaction.opm_trans_id
5715 , l_movement_transaction.customer_vat_number
5716 , l_movement_transaction.attribute1
5717 , l_movement_transaction.attribute2
5718 , l_movement_transaction.attribute3
5719 , l_movement_transaction.attribute4
5720 , l_movement_transaction.attribute5
5721 , l_movement_transaction.attribute6
5722 , l_movement_transaction.attribute7
5723 , l_movement_transaction.attribute8
5724 , l_movement_transaction.attribute9
5725 , l_movement_transaction.attribute10
5726 , l_movement_transaction.attribute11
5727 , l_movement_transaction.attribute12
5728 , l_movement_transaction.attribute13
5729 , l_movement_transaction.attribute14
5730 , l_movement_transaction.attribute15
5731 , l_movement_transaction.triangulation_country_eu_code
5732 , l_movement_transaction.distribution_line_number
5733 , l_movement_transaction.ship_to_name
5734 , l_movement_transaction.ship_to_number
5735 , l_movement_transaction.ship_to_site
5736 , l_movement_transaction.edi_transaction_date
5737 , l_movement_transaction.edi_transaction_reference
5738 , l_movement_transaction.esl_drop_shipment_code;
5739
5740 EXIT WHEN inv_crsr%NOTFOUND;
5741
5742 --yawang open correct quantity cursor
5743 OPEN l_correct_quantity;
5744 FETCH l_correct_quantity
5745 INTO
5746 l_correct_qty
5747 , l_correct_parimary_qty;
5748
5749 IF l_correct_quantity%NOTFOUND
5750 THEN
5751 l_correct_qty := 0;
5752 l_correct_parimary_qty := 0;
5753 CLOSE l_correct_quantity;
5754 END IF;
5755
5756 CLOSE l_correct_quantity;
5757
5758 --Net correction quantity into original transaction quantity
5759 l_movement_transaction.transaction_quantity :=
5760 l_movement_transaction.transaction_quantity + NVL(l_correct_qty,0);
5761 l_movement_transaction.primary_quantity :=
5762 l_movement_transaction.primary_quantity + NVL(l_correct_parimary_qty,0);
5763
5764 --Recalculate document lin ext value
5765 l_movement_transaction.document_line_ext_value :=
5766 abs(l_movement_transaction.document_unit_price *
5767 l_movement_transaction.transaction_quantity);
5768
5769 --Recalculate movement amount
5770 l_movement_transaction.movement_amount :=
5771 INV_MGD_MVT_FIN_MDTR.Calc_Movement_Amount
5772 (p_movement_transaction => l_movement_transaction);
5773
5774 --Calculate freight charge and include in statistics value
5775 l_movement_transaction.stat_ext_value :=
5776 INV_MGD_MVT_FIN_MDTR.Calc_Statistics_Value
5777 (p_movement_transaction => l_movement_transaction);
5778
5779 --Fix bug 4866967 and 5203245
5780 INV_MGD_MVT_UTILS_PKG.Get_Weight_Precision
5781 (p_legal_entity_id => l_movement_transaction.entity_org_id
5782 , p_zone_code => l_movement_transaction.zone_code
5783 , p_usage_type => l_movement_transaction.usage_type
5784 , p_stat_type => l_movement_transaction.stat_type
5785 , x_weight_precision => l_weight_precision
5786 , x_rep_rounding => l_rounding_method
5787 );
5788
5789 --Recalculate transaction total weight and alternate quantity
5790 IF l_movement_transaction.transaction_quantity IS NOT NULL
5791 THEN
5792 l_total_weight := l_movement_transaction.unit_weight *
5793 l_movement_transaction.transaction_quantity;
5794
5795 l_movement_transaction.total_weight := INV_MGD_MVT_UTILS_PKG.Round_Number
5796 ( p_number => l_total_weight
5797 , p_precision => l_weight_precision
5798 , p_rounding_method => l_rounding_method
5799 );
5800
5801
5802 IF (l_movement_transaction.alternate_uom_code IS NOT NULL)
5803 THEN
5804 l_movement_transaction.alternate_quantity :=
5805 INV_MGD_MVT_UTILS_PKG.Convert_alternate_Quantity
5806 ( p_transaction_quantity => l_movement_transaction.transaction_quantity
5807 , p_alternate_uom_code => l_movement_transaction.alternate_uom_code
5808 , p_transaction_uom_code => l_movement_transaction.transaction_uom_code
5809 , p_inventory_item_id => l_movement_transaction.inventory_item_id
5810 );
5811 END IF;
5812 ELSE
5813 l_movement_transaction.total_weight := null;
5814 l_movement_transaction.unit_weight := null;
5815 END IF;
5816
5817 --Update original movement records
5818 INV_MGD_MVT_STATS_PVT.Update_Movement_Statistics
5819 ( p_movement_statistics => l_movement_transaction
5820 , x_return_status => l_return_status
5821 , x_msg_count => x_msg_count
5822 , x_msg_data => x_msg_data
5823 );
5824
5825 IF l_return_status = FND_API.G_RET_STS_SUCCESS
5826 THEN
5827 UPDATE rcv_transactions
5828 SET
5829 mvt_stat_status = 'PROCESSED'
5830 , movement_id = l_movement_transaction.movement_id
5831 WHERE mvt_stat_status = 'NEW'
5832 AND transaction_type = 'CORRECT'
5833 AND parent_transaction_id = l_movement_transaction.rcv_transaction_id;
5834
5835 COMMIT;
5836 END IF;
5837
5838 END LOOP ;
5839 CLOSE inv_crsr;
5840 END IF;
5841
5842 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5843 THEN
5844 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5845 , G_MODULE_NAME || l_api_name || '.end'
5846 ,'exit procedure'
5847 );
5848 END IF;
5849
5850 EXCEPTION
5851 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5853 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
5854
5855 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5856 THEN
5857 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
5858 , G_MODULE_NAME || l_api_name||'. Unexpected exception'
5859 , l_error
5860 );
5861 END IF;
5862
5863 WHEN NO_DATA_FOUND THEN
5864 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5865 THEN
5866 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
5867 , G_MODULE_NAME || l_api_name||'. No data found exception'
5868 , l_error
5869 );
5870 END IF;
5871 RAISE;
5872
5873 WHEN OTHERS THEN
5874 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5875 THEN
5876 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
5877 , 'Others exception in '||l_api_name
5878 );
5879 END IF;
5880
5881 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5882 THEN
5883 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
5884 , G_MODULE_NAME || l_api_name||'. Others exception'
5885 , l_error
5886 );
5887 END IF;
5888 RAISE;
5889
5890 END Update_PO_With_Correction;
5891
5892 --========================================================================
5893 -- PROCEDURE : Process_Pending_Transaction
5894 -- PARAMETERS: x_return_status status flag
5895 -- p_legal_entity_id Legal Entity ID
5896 -- p_start_date Transaction start date
5897 -- p_end_date Transaction end date
5898 -- p_transaction type Transaction Type
5899 -- COMMENT :
5900 -- This updates the invoice information for the particular
5901 -- transaction_type
5902 --========================================================================
5903
5904 PROCEDURE Process_Pending_Transaction
5905 ( p_movement_transaction IN
5906 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
5907 , p_start_date IN DATE
5908 , p_end_date IN DATE
5909 , p_transaction_type IN VARCHAR2
5910 , x_return_status OUT NOCOPY VARCHAR2
5911 )
5912 IS
5913 -- Declare the REF Cursor
5914 inv_crsr INV_MGD_MVT_DATA_STR.invCurTyp;
5915 setup_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
5916 ref_crsr INV_MGD_MVT_DATA_STR.setupCurTyp;
5917 mvt_crsr INV_MGD_MVT_DATA_STR.valCurTyp;
5918 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
5919 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
5920 l_insert_flag VARCHAR2(1);
5921 x_msg_count NUMBER;
5922 x_msg_data VARCHAR2(2000);
5923 l_insert_status VARCHAR2(10);
5924 l_movement_id NUMBER;
5925 l_trans_date DATE;
5926 l_return_status VARCHAR2(1);
5927 l_api_name CONSTANT VARCHAR2(30) := 'Process_Pending_Transaction';
5928 l_error VARCHAR2(600);
5929
5930 BEGIN
5931 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
5932 THEN
5933 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5934 , G_MODULE_NAME || l_api_name || '.begin'
5935 ,'enter procedure'
5936 );
5937 END IF;
5938
5939 x_return_status := FND_API.G_RET_STS_SUCCESS;
5940 l_movement_transaction := p_movement_transaction;
5941
5942 -- Call the transaction proxy which processes all the transactions.
5943
5944 INV_MGD_MVT_STATS_PVT.Get_Pending_Txns
5945 ( p_movement_transaction => l_movement_transaction
5946 , val_crsr => mvt_crsr
5947 , p_document_source_type => p_transaction_type
5948 , x_return_status => l_return_status);
5949
5950 IF l_return_status = 'Y' THEN
5951 LOOP
5952 --Reset the movement record for each transaction
5953 l_movement_transaction := p_movement_transaction;
5954 l_movement_id := NULL;
5955
5956 FETCH mvt_crsr INTO
5957 l_movement_transaction.movement_id
5958 , l_movement_transaction.organization_id
5959 , l_movement_transaction.entity_org_id
5960 , l_movement_transaction.movement_type
5961 , l_movement_transaction.movement_status
5962 , l_movement_transaction.transaction_date
5963 , l_movement_transaction.last_update_date
5964 , l_movement_transaction.last_updated_by
5965 , l_movement_transaction.creation_date
5966 , l_movement_transaction.created_by
5967 , l_movement_transaction.last_update_login
5968 , l_movement_transaction.document_source_type
5969 , l_movement_transaction.creation_method
5970 , l_movement_transaction.document_reference
5971 , l_movement_transaction.document_line_reference
5972 , l_movement_transaction.document_unit_price
5973 , l_movement_transaction.document_line_ext_value
5974 , l_movement_transaction.receipt_reference
5975 , l_movement_transaction.shipment_reference
5976 , l_movement_transaction.shipment_line_reference
5977 , l_movement_transaction.pick_slip_reference
5978 , l_movement_transaction.customer_name
5979 , l_movement_transaction.customer_number
5980 , l_movement_transaction.customer_location
5981 , l_movement_transaction.transacting_from_org
5982 , l_movement_transaction.transacting_to_org
5983 , l_movement_transaction.vendor_name
5984 , l_movement_transaction.vendor_number
5985 , l_movement_transaction.vendor_site
5986 , l_movement_transaction.bill_to_name
5987 , l_movement_transaction.bill_to_number
5988 , l_movement_transaction.bill_to_site
5989 , l_movement_transaction.po_header_id
5990 , l_movement_transaction.po_line_id
5991 , l_movement_transaction.po_line_location_id
5992 , l_movement_transaction.order_header_id
5993 , l_movement_transaction.order_line_id
5994 , l_movement_transaction.picking_line_id
5995 , l_movement_transaction.shipment_header_id
5996 , l_movement_transaction.shipment_line_id
5997 , l_movement_transaction.ship_to_customer_id
5998 , l_movement_transaction.ship_to_site_use_id
5999 , l_movement_transaction.bill_to_customer_id
6000 , l_movement_transaction.bill_to_site_use_id
6001 , l_movement_transaction.vendor_id
6002 , l_movement_transaction.vendor_site_id
6003 , l_movement_transaction.from_organization_id
6004 , l_movement_transaction.to_organization_id
6005 , l_movement_transaction.parent_movement_id
6006 , l_movement_transaction.inventory_item_id
6007 , l_movement_transaction.item_description
6008 , l_movement_transaction.item_cost
6009 , l_movement_transaction.transaction_quantity
6010 , l_movement_transaction.transaction_uom_code
6011 , l_movement_transaction.primary_quantity
6012 , l_movement_transaction.invoice_batch_id
6013 , l_movement_transaction.invoice_id
6014 , l_movement_transaction.customer_trx_line_id
6015 , l_movement_transaction.invoice_batch_reference
6016 , l_movement_transaction.invoice_reference
6017 , l_movement_transaction.invoice_line_reference
6018 , l_movement_transaction.invoice_date_reference
6019 , l_movement_transaction.invoice_quantity
6020 , l_movement_transaction.invoice_unit_price
6021 , l_movement_transaction.invoice_line_ext_value
6022 , l_movement_transaction.outside_code
6023 , l_movement_transaction.outside_ext_value
6024 , l_movement_transaction.outside_unit_price
6025 , l_movement_transaction.currency_code
6026 , l_movement_transaction.currency_conversion_rate
6027 , l_movement_transaction.currency_conversion_type
6028 , l_movement_transaction.currency_conversion_date
6029 , l_movement_transaction.period_name
6030 , l_movement_transaction.report_reference
6031 , l_movement_transaction.report_date
6032 , l_movement_transaction.category_id
6033 , l_movement_transaction.weight_method
6034 , l_movement_transaction.unit_weight
6035 , l_movement_transaction.total_weight
6036 , l_movement_transaction.transaction_nature
6037 , l_movement_transaction.delivery_terms
6038 , l_movement_transaction.transport_mode
6039 , l_movement_transaction.alternate_quantity
6040 , l_movement_transaction.alternate_uom_code
6041 , l_movement_transaction.dispatch_territory_code
6042 , l_movement_transaction.destination_territory_code
6043 , l_movement_transaction.origin_territory_code
6044 , l_movement_transaction.stat_method
6045 , l_movement_transaction.stat_adj_percent
6046 , l_movement_transaction.stat_adj_amount
6047 , l_movement_transaction.stat_ext_value
6048 , l_movement_transaction.area
6049 , l_movement_transaction.port
6050 , l_movement_transaction.stat_type
6051 , l_movement_transaction.comments
6052 , l_movement_transaction.attribute_category
6053 , l_movement_transaction.commodity_code
6054 , l_movement_transaction.commodity_description
6055 , l_movement_transaction.requisition_header_id
6056 , l_movement_transaction.requisition_line_id
6057 , l_movement_transaction.picking_line_detail_id
6058 , l_movement_transaction.usage_type
6059 , l_movement_transaction.zone_code
6060 , l_movement_transaction.edi_sent_flag
6061 , l_movement_transaction.statistical_procedure_code
6062 , l_movement_transaction.movement_amount
6063 , l_movement_transaction.triangulation_country_code
6064 , l_movement_transaction.csa_code
6065 , l_movement_transaction.oil_reference_code
6066 , l_movement_transaction.container_type_code
6067 , l_movement_transaction.flow_indicator_code
6068 , l_movement_transaction.affiliation_reference_code
6069 , l_movement_transaction.origin_territory_eu_code
6070 , l_movement_transaction.destination_territory_eu_code
6071 , l_movement_transaction.dispatch_territory_eu_code
6072 , l_movement_transaction.set_of_books_period
6073 , l_movement_transaction.taric_code
6074 , l_movement_transaction.preference_code
6075 , l_movement_transaction.rcv_transaction_id
6076 , l_movement_transaction.mtl_transaction_id
6077 , l_movement_transaction.total_weight_uom_code
6078 , l_movement_transaction.financial_document_flag
6079 --, l_movement_transaction.opm_trans_id
6080 , l_movement_transaction.customer_vat_number
6081 , l_movement_transaction.attribute1
6082 , l_movement_transaction.attribute2
6083 , l_movement_transaction.attribute3
6084 , l_movement_transaction.attribute4
6085 , l_movement_transaction.attribute5
6086 , l_movement_transaction.attribute6
6087 , l_movement_transaction.attribute7
6088 , l_movement_transaction.attribute8
6089 , l_movement_transaction.attribute9
6090 , l_movement_transaction.attribute10
6091 , l_movement_transaction.attribute11
6092 , l_movement_transaction.attribute12
6093 , l_movement_transaction.attribute13
6094 , l_movement_transaction.attribute14
6095 , l_movement_transaction.attribute15
6096 , l_movement_transaction.triangulation_country_eu_code
6097 , l_movement_transaction.distribution_line_number
6098 , l_movement_transaction.ship_to_name
6099 , l_movement_transaction.ship_to_number
6100 , l_movement_transaction.ship_to_site
6101 , l_movement_transaction.edi_transaction_date
6102 , l_movement_transaction.edi_transaction_reference
6103 , l_movement_transaction.esl_drop_shipment_code;
6104
6105 EXIT WHEN mvt_crsr%NOTFOUND;
6106
6107 l_trans_date := l_movement_transaction.transaction_date;
6108
6109 INV_MGD_MVT_SETUP_MDTR.Get_Invoice_Context
6110 ( p_legal_entity_id => l_movement_transaction.entity_org_id
6111 , p_start_date => p_start_date
6112 , p_end_date => p_end_date
6113 , p_transaction_type => p_transaction_type
6114 , p_movement_transaction => l_movement_transaction
6115 , x_return_status => l_return_status
6116 , setup_crsr => setup_crsr
6117 );
6118
6119
6120 IF l_return_status = 'Y' THEN
6121 LOOP
6122
6123 l_movement_transaction.transaction_date := l_trans_date;
6124
6125 FETCH setup_crsr INTO
6126 l_stat_typ_transaction.start_period_name
6127 , l_stat_typ_transaction.end_period_name
6128 , l_stat_typ_transaction.period_set_name
6129 , l_stat_typ_transaction.period_type
6130 , l_stat_typ_transaction.weight_uom_code
6131 , l_stat_typ_transaction.conversion_type
6132 , l_stat_typ_transaction.attribute_rule_set_code
6133 , l_stat_typ_transaction.alt_uom_rule_set_code
6134 , l_stat_typ_transaction.start_date
6135 , l_stat_typ_transaction.end_date
6136 , l_stat_typ_transaction.category_set_id
6137 , l_stat_typ_transaction.gl_currency_code
6138 , l_movement_transaction.gl_currency_code
6139 , l_stat_typ_transaction.conversion_option
6140 , l_stat_typ_transaction.triangulation_mode
6141 , l_stat_typ_transaction.reference_period_rule
6142 , l_stat_typ_transaction.pending_invoice_days
6143 , l_stat_typ_transaction.prior_invoice_days
6144 , l_stat_typ_transaction.returns_processing;
6145
6146 EXIT WHEN setup_crsr%NOTFOUND;
6147
6148 INV_MGD_MVT_UTILS_PKG.Get_Order_Number
6149 ( x_movement_transaction => l_movement_transaction
6150 );
6151
6152 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
6153 ( p_stat_typ_transaction => l_stat_typ_transaction
6154 , x_movement_transaction => l_movement_transaction
6155 );
6156
6157 --Fix bug 4927726
6158 --Only continue if the invoice is found, otherwise, everything
6159 --is same as before, no need to go through following code
6160 IF l_movement_transaction.invoice_id IS NOT NULL
6161 THEN
6162 INV_MGD_MVT_FIN_MDTR. Get_Reference_Date
6163 ( p_stat_typ_transaction => l_stat_typ_transaction
6164 , x_movement_transaction => l_movement_transaction
6165 );
6166
6167 l_movement_transaction.transaction_date :=
6168 NVL(l_movement_transaction.reference_date,
6169 l_movement_transaction.transaction_date);
6170
6171 l_movement_transaction.period_name :=
6172 INV_MGD_MVT_FIN_MDTR.Get_Period_Name
6173 (p_movement_transaction => l_movement_transaction
6174 ,p_stat_typ_transaction => l_stat_typ_transaction);
6175
6176 /* Bug: 5291257. Call to function INV_MGD_MVT_FIN_MDTR.Get_Set_Of_Books_Period
6177 is modified becasue p_period_type is no more required. */
6178 l_movement_transaction.set_of_books_period :=
6179 INV_MGD_MVT_FIN_MDTR.Get_Set_Of_Books_Period
6180 (p_legal_entity_id => l_movement_transaction.entity_org_id
6181 ,p_period_date => NVL(l_movement_transaction.invoice_date_reference,
6182 l_movement_transaction.transaction_date)
6183 --,p_period_type => NVL(l_stat_typ_transaction.period_type,'Month')
6184 );
6185
6186 /*l_movement_transaction.currency_conversion_date :=
6187 INV_MGD_MVT_FIN_MDTR.
6188 Calc_Conversion_Date(p_movement_transaction => l_movement_transaction
6189 , p_stat_typ_transaction => l_stat_typ_transaction
6190 );
6191
6192 l_movement_transaction.currency_conversion_rate :=
6193 INV_MGD_MVT_FIN_MDTR.
6194 Calc_Exchange_Rate(p_movement_transaction => l_movement_transaction
6195 , p_stat_typ_transaction => l_stat_typ_transaction
6196 );
6197 */
6198
6199
6200 l_movement_transaction.movement_amount :=
6201 INV_MGD_MVT_FIN_MDTR.Calc_Movement_Amount
6202 (p_movement_transaction => l_movement_transaction
6203 );
6204
6205 --Calculate freight charge and include in statistics value
6206 l_movement_transaction.stat_ext_value :=
6207 INV_MGD_MVT_FIN_MDTR.Calc_Statistics_Value
6208 (p_movement_transaction => l_movement_transaction);
6209
6210 l_movement_transaction.financial_document_flag := 'PROCESSED_INCLUDED';
6211 l_movement_transaction.movement_status := 'O';
6212
6213 INV_MGD_MVT_STATS_PVT.Update_Movement_Statistics
6214 (p_movement_statistics => l_movement_transaction
6215 , x_return_status => l_return_status
6216 , x_msg_count => x_msg_count
6217 , x_msg_data => x_msg_data
6218 );
6219 --Bug: 5911911, Following else clause is added to change the MS record
6220 --status from Pending to Open if it has crossed timeframe specified by
6221 --the Pending Invoice Days parameter.
6222 ELSE
6223 IF ( sysdate > l_movement_transaction.transaction_date )
6224 THEN
6225 l_movement_transaction.movement_status :='O';
6226 INV_MGD_MVT_STATS_PVT.Update_Movement_Statistics
6227 (p_movement_statistics => l_movement_transaction
6228 , x_return_status => l_return_status
6229 , x_msg_count => x_msg_count
6230 , x_msg_data => x_msg_data
6231 );
6232 END IF;
6233 END IF;
6234 END LOOP ;
6235 CLOSE setup_crsr;
6236 END IF;
6237 END LOOP ;
6238 CLOSE mvt_crsr;
6239 END IF;
6240
6241 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
6242 THEN
6243 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
6244 , G_MODULE_NAME || l_api_name || '.end'
6245 ,'exit procedure'
6246 );
6247 END IF;
6248
6249 EXCEPTION
6250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6252 l_error := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,250);
6253
6254 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
6255 THEN
6256 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
6257 , G_MODULE_NAME || l_api_name||'. Unexpected exception'
6258 , l_error
6259 );
6260 END IF;
6261
6262 WHEN NO_DATA_FOUND THEN
6263 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
6264 THEN
6265 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
6266 , G_MODULE_NAME || l_api_name||'. No data found exception'
6267 , l_error
6268 );
6269 END IF;
6270 RAISE;
6271
6272 WHEN OTHERS THEN
6273 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6274 THEN
6275 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
6276 , 'Others exception in '||l_api_name
6277 );
6278 END IF;
6279
6280 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
6281 THEN
6282 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
6283 , G_MODULE_NAME || l_api_name||'. Others exception'
6284 , l_error
6285 );
6286 END IF;
6287 RAISE;
6288
6289 END Process_Pending_Transaction;
6290
6291 END INV_MGD_MVT_STATS_PROC;