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