1 PACKAGE BODY INV_MGD_MVT_FIN_MDTR AS
2 /* $Header: INVFMDRB.pls 120.12.12010000.3 2008/10/01 11:55:31 ajmittal ship $ */
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVFMDRB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| |
14 --| PROCEDURE LIST |
15 --| Calc_Exchange_Rate |
16 --| Calc_Conversion_Date |
17 --| Calc_Invoice_Info |
18 --| Calc_Movement_Amount |
19 --| Calc_Statistics_Value |
20 --| Get_Set_Of_Books_Period |
21 --| Get_Reference_Date |
22 --| Log_Initialize |
23 --| Log |
24 --| Calc_Cto_Amount_So |
25 --| Calc_Cto_Amount_drp |
26 --| Calc_Cto_Amount_rma |
27 --| Calc_Cto_Amount_ar |
28 --| Calc_Cto_Amount_ap |
29 --| Calc_Line_Charge |
30 --| Calc_Total_Line_Charge |
31 --| Calc_Processed_Ret_Data |
32 --| HISTORY |
33 --| 12-03-2002 vma Add code to print to log only if debug profile |
34 --| option is enabled. This is to comply with new |
35 --| PL/SQL standard for better performance. |
36 --| 07-Nov-06 nesoni File is modified for bug 5440432 to calculate |
37 --| invoice correctly for intercompany SO Arrival |
38 --| 22-Jul-07 kdevadas Fix for bug 6035548 - Invoice Details calculated |
39 --| for RMA when selling and shipping orgs are diff. |
40 --| 22-Jul-07 kdevadas Fix for bug 6158521 - Calc_Movement_Amount |
41 --| ignores UOM conversion for Arrival transactions |
42 --| 26-Jun-2008 kdevadas Movement ISO uptake - 6889669( ER :4930271) |
43 --| 01-oct-08 Ajmittal Fix for bug 7446311 - Calc_Processed_Ret_Data |
44 --| Added one join in cursor l_rtv_cm_po_based. |
45 --+========================================================================
46
47 --===================
48 -- GLOBALS
49 --===================
50
51 g_too_many_transactions_exc EXCEPTION;
52 g_no_data_transaction_exc EXCEPTION;
53 g_log_level NUMBER ;
54 g_log_mode VARCHAR2(3); -- possible values: OFF, SQL, SRS
55 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_MGD_MVT_FIN_MDTR.';
56
57 --====================
58 --Private procedure
59 --====================
60 -- ========================================================================
61 -- PROCEDURE : Get_Conversion_rate PUBLIC
62 -- PARAMETERS: p_invoice_currency_code - I/C AP invoice currency
63 -- p_movement_transaction - movement transaction data record
64 -- COMMENT : This function returns the conversion rate based on
65 -- the conversion date that is set up in the
66 -- statistical type info form.
67 --=======================================================================
68
69 Function Get_Conversion_Rate
70 ( p_invoice_currency_code VARCHAR2
71 , p_movement_transaction IN
72 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
73 )
74 RETURN NUMBER IS
75 l_gl_set_of_books_id VARCHAR2(15);
76 l_last_dayofperiod DATE;
77 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Conversion_Rate';
78 l_period_set_name mtl_stat_type_usages.period_set_name%type;
79 l_period_type mtl_stat_type_usages.period_type%type;
80 l_conversion_option mtl_stat_type_usages.conversion_option%type;
81 l_conversion_type mtl_stat_type_usages.conversion_type%type;
82 l_currency_conversion_rate number;
83 l_currency_conversion_date date;
84
85 BEGIN
86 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
87 THEN
88 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
89 , G_MODULE_NAME || l_procedure_name || '.begin'
90 ,'enter procedure'
91 );
92
93 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
94 , G_MODULE_NAME || l_procedure_name || '.legal entiry id - '
95 ,p_movement_transaction.entity_org_id
96 );
97
98 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
99 , G_MODULE_NAME || l_procedure_name || '.stat type - '
100 ,p_movement_transaction.stat_type
101 );
102
103 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
104 , G_MODULE_NAME || l_procedure_name || '.period name - '
105 ,p_movement_transaction.period_name
106 );
107
108
109 END IF;
110
111 SELECT
112 period_set_name
113 , period_type
114 , conversion_option
115 , conversion_type
116 INTO
117 l_period_set_name
118 , l_period_type
119 , l_conversion_option
120 , l_conversion_type
121 FROM
122 mtl_stat_type_usages
123 WHERE
124 legal_entity_id = p_movement_transaction.entity_org_id
125 AND stat_type = p_movement_transaction.stat_type;
126
127 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
128 THEN
129 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
130 , G_MODULE_NAME || l_procedure_name || '.period set name'
131 ,l_period_set_name
132 );
133
134 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
135 , G_MODULE_NAME || l_procedure_name || '.period type - '
136 ,l_period_type
137 );
138
139 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
140 , G_MODULE_NAME || l_procedure_name || '.conversion option - '
141 ,l_conversion_option
142 );
143
144 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
145 , G_MODULE_NAME || l_procedure_name || '.conversion type - '
146 ,l_conversion_type
147 );
148
149 END IF;
150
151
152 --Get the end date of the period
153 SELECT end_date
154 INTO
155 l_last_dayofperiod
156 FROM
157 GL_PERIODS
158 WHERE period_name = p_movement_transaction.period_name
159 AND period_set_name = l_period_set_name
160 AND period_type = l_period_type;
161
162
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_procedure_name || '.last day of period'
167 , l_last_dayofperiod
168 );
169 END IF ;
170
171 IF UPPER(l_conversion_option) = 'CO_LAST_DAY'
172 THEN
173
174 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
175 THEN
176 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
177 , G_MODULE_NAME || l_procedure_name || '.co last day'
178 , l_conversion_option
179 );
180 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
181 , G_MODULE_NAME || l_procedure_name || '.invoice currency code'
182 , p_invoice_currency_code
183 );
184 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
185 , G_MODULE_NAME || l_procedure_name || '.gl_currency_code'
186 , p_movement_transaction.gl_currency_code
187 );
188 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
189 , G_MODULE_NAME || l_procedure_name || '.l_last_dayofperiod'
190 , l_last_dayofperiod
191 );
192 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
193 , G_MODULE_NAME || l_procedure_name || '.l_conversion_type'
194 , l_conversion_type
195 );
196 END IF ;
197
198 l_currency_conversion_rate := GL_CURRENCY_API.Get_Rate
199 ( x_from_currency => p_invoice_currency_code
200 , x_to_currency => p_movement_transaction.gl_currency_code
201 , x_conversion_date => l_last_dayofperiod
202 , x_conversion_type => l_conversion_type
203 );
204
205 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
206 THEN
207 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
208 , G_MODULE_NAME || l_procedure_name || '.conversion rate'
209 , l_currency_conversion_rate
210 );
211 END IF ;
212
213
214 ELSE
215
216 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
217 THEN
218 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
219 , G_MODULE_NAME || l_procedure_name || '.CO DAILY'
220 , 'in daily conversion routine'
221 );
222 END IF ;
223
224 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
225 THEN
226 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
227 , G_MODULE_NAME || l_procedure_name || '.co last day'
228 , l_conversion_option
229 );
230 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
231 , G_MODULE_NAME || l_procedure_name || '.invoice currency code'
232 , p_invoice_currency_code
233 );
234 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
235 , G_MODULE_NAME || l_procedure_name || '.gl_currency_code'
236 , p_movement_transaction.gl_currency_code
237 );
238 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
239 , G_MODULE_NAME || l_procedure_name || '.l_last_dayofperiod'
240 , l_last_dayofperiod
241 );
242 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
243 , G_MODULE_NAME || l_procedure_name || '.l_conversion_type'
244 , l_conversion_type
245 );
246 END IF ;
247
248 l_currency_conversion_date :=
249 NVL(p_movement_transaction.invoice_date_reference, p_movement_transaction.transaction_date);
250
251 l_currency_conversion_rate := GL_CURRENCY_API.Get_Rate
252 ( x_from_currency => p_invoice_currency_code
253 , x_to_currency => p_movement_transaction.gl_currency_code
254 , x_conversion_date => l_currency_conversion_date
255 , x_conversion_type => l_conversion_type
256 );
257 END IF;
258
259 IF l_currency_conversion_rate IS NULL
260 THEN
261 l_currency_conversion_rate := 1;
262 END IF;
263
264 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
265 THEN
266 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
267 , G_MODULE_NAME || l_procedure_name || '.end'
268 ,'exit procedure'
269 );
270 END IF;
271
272 return l_currency_conversion_rate;
273 EXCEPTION
274 WHEN GL_CURRENCY_API.no_rate
275 THEN
276 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
277 THEN
278 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
279 , G_MODULE_NAME || l_procedure_name||'.No rate exception'
280 , 'Exception'
281 );
282 END IF;
283 l_currency_conversion_rate := 1;
284 return l_currency_conversion_rate;
285 WHEN NO_DATA_FOUND
286 THEN
287 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
288 THEN
289 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
290 , G_MODULE_NAME || l_procedure_name||'.No data found exception'
291 , 'Exception'
292 );
293 END IF;
294 l_currency_conversion_rate := 1;
295 return l_currency_conversion_rate;
296 WHEN OTHERS
297 THEN
298 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
299 THEN
300 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
301 , G_MODULE_NAME || l_procedure_name||'.Others exception'
302 , 'Exception'
303 );
304 END IF;
305 l_currency_conversion_rate := 1;
306 return l_currency_conversion_rate;
307 END Get_Conversion_Rate;
308
309
310 -- ========================================================================
311 -- PROCEDURE : Calc_Cto_Amount_So Private
312 -- PARAMETERS: p_order_line_id IN order line id
313 -- p_order_number IN sales order number
314 -- x_extended_amount OUT invoice amount
315 -- x_cm_extended_amount OUT invoice amount for credit memo
316 -- COMMENT : Procedure to calcualte the invoice amount for CTO items
317 -- ========================================================================
318 PROCEDURE Calc_Cto_Amount_So
319 ( p_order_line_id IN NUMBER
320 , p_order_number IN VARCHAR2
321 , x_extended_amount OUT NOCOPY NUMBER
322 , x_cm_extended_amount OUT NOCOPY NUMBER
323 )
324 IS
325 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Cto_Amount_So';
326
327 --Cursor to calculate invoice amount for CTO item
328 CURSOR l_cto_amt IS
329 SELECT
330 SUM(ratl.extended_amount)
331 FROM
332 RA_CUSTOMER_TRX_ALL rat
333 , RA_CUSTOMER_TRX_LINES_ALL ratl
334 , RA_CUST_TRX_TYPES_ALL ratt
335 , oe_order_lines_all oola
336 WHERE rat.customer_trx_id = ratl.customer_trx_id
337 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
338 AND rat.org_id = ratt.org_id
339 AND ratt.type NOT IN ('CM','DM')
340 AND NOT EXISTS
341 (SELECT null
342 FROM oe_price_adjustments
343 WHERE (line_id = oola.line_id
344 OR (line_id IS NULL AND modifier_level_code = 'ORDER'))
345 AND TO_CHAR(price_adjustment_id)= NVL(ratl.interface_line_attribute11, '-9999')
346 AND header_id = oola.header_id)
347 AND ratl.line_type = 'LINE'
348 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
349 AND ratl.sales_order = p_order_number
350 AND ratl.interface_line_attribute6 = to_char(oola.line_id)
351 AND oola.top_model_line_id = p_order_line_id
352 AND ratl.interface_line_context <> 'INTERCOMPANY';
353
354 --Cursor to calculate invoice amount for CTO item credit memo
355 CURSOR l_cto_cm_amt IS
356 SELECT
357 SUM(ratl.extended_amount)
358 FROM
359 RA_CUSTOMER_TRX_ALL rat
360 , RA_CUSTOMER_TRX_LINES_ALL ratl
361 , RA_CUST_TRX_TYPES_ALL ratt
362 , oe_order_lines_all oola
363 WHERE rat.customer_trx_id = ratl.customer_trx_id
364 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
365 AND rat.org_id = ratt.org_id
366 AND ((ratt.type IN ('CM','DM'))
367 OR (NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
368 IN (SELECT TO_CHAR(price_adjustment_id)
369 FROM oe_price_adjustments
370 WHERE header_id = oola.header_id
371 AND (line_id = oola.line_id
372 OR (line_id IS NULL AND modifier_level_code = 'ORDER')) )))
373 AND ratl.line_type = 'LINE'
374 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
375 AND ratl.quantity_credited IS NULL
376 AND ratl.sales_order = p_order_number
377 AND ratl.interface_line_attribute6 = to_char(oola.line_id)
378 AND oola.top_model_line_id = p_order_line_id
379 AND ratl.interface_line_context <> 'INTERCOMPANY';
380 BEGIN
381 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
382 THEN
383 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
384 , G_MODULE_NAME || l_procedure_name || '.begin'
385 ,'enter procedure'
386 );
387 END IF;
388
389 --No need to check cursor not found, because cursor with SUM
390 --function always return true
391 OPEN l_cto_amt;
392 FETCH l_cto_amt INTO
393 x_extended_amount;
394 CLOSE l_cto_amt;
395
396 OPEN l_cto_cm_amt;
397 FETCH l_cto_cm_amt INTO
398 x_cm_extended_amount;
399 CLOSE l_cto_cm_amt;
400
401 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
402 THEN
403 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
404 , G_MODULE_NAME || l_procedure_name || '.end'
405 ,'exit procedure'
406 );
407 END IF;
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
412 THEN
413 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
414 , G_MODULE_NAME || l_procedure_name||'.Others exception'
415 , 'Exception'
416 );
417 END IF;
418 END Calc_Cto_Amount_So;
419
420 -- ========================================================================
421 -- PROCEDURE : Calc_Cto_Amount_Drp PUBLIC
422 -- PARAMETERS: p_order_line_id IN order line id
423 -- x_extended_amount OUT invoice amount
424 -- x_cm_extended_amount OUT invoice amount for credit memo
425 -- COMMENT : Procedure to calcualte the invoice amount for CTO items
426 -- ========================================================================
427 PROCEDURE Calc_Cto_Amount_Drp
428 ( p_order_line_id IN NUMBER
429 , p_order_number IN VARCHAR2
430 , x_extended_amount OUT NOCOPY NUMBER
431 , x_cm_extended_amount OUT NOCOPY NUMBER
432 )
433 IS
434 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Cto_Amount_Drp';
435
436 --Cursor to calculate invoice amount for CTO item for dropship
437 CURSOR l_cto_amt_drp IS
438 SELECT
439 SUM(ratl.extended_amount)
440 FROM
441 RA_CUSTOMER_TRX_ALL rat
442 , RA_CUSTOMER_TRX_LINES_ALL ratl
443 , RA_CUST_TRX_TYPES_ALL ratt --add in for fixing bug 2447381
444 , oe_order_lines_all oola
445 WHERE rat.customer_trx_id = ratl.customer_trx_id
446 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
447 AND rat.org_id = ratt.org_id
448 AND ratt.type NOT IN ('CM','DM')
449 AND NOT EXISTS
450 (SELECT null
451 FROM oe_price_adjustments
452 WHERE (line_id = oola.line_id
453 OR (line_id IS NULL AND modifier_level_code = 'ORDER'))
454 AND TO_CHAR(price_adjustment_id)= NVL(ratl.interface_line_attribute11, '-9999')
455 AND header_id = oola.header_id)
456 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
457 AND ratl.line_type = 'LINE' --yawang
458 AND ratl.sales_order = p_order_number
459 AND ratl.interface_line_attribute6 = to_char(oola.line_id)
460 AND oola.top_model_line_id = p_order_line_id
461 AND rat.complete_flag = 'Y'
462 AND ratl.interface_line_context <> 'INTERCOMPANY';
463
464 --Cursor to calculate invoice amount for dropship CTO item credit memo
465 CURSOR l_cto_cm_amt_drp IS
466 SELECT
467 SUM(ratl.extended_amount)
468 FROM
469 RA_CUSTOMER_TRX_ALL rat
470 , RA_CUSTOMER_TRX_LINES_ALL ratl
471 , RA_CUST_TRX_TYPES_ALL ratt
472 , oe_order_lines_all oola
473 WHERE rat.customer_trx_id = ratl.customer_trx_id
474 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
475 AND rat.org_id = ratt.org_id
476 AND ((ratt.type IN ('CM','DM'))
477 OR (NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
478 IN (SELECT TO_CHAR(price_adjustment_id)
479 FROM oe_price_adjustments
480 WHERE header_id = oola.header_id
481 AND (line_id = oola.line_id
482 OR (line_id IS NULL AND modifier_level_code = 'ORDER')) )))
483 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
484 AND ratl.line_type = 'LINE' --yawang
485 AND ratl.sales_order = p_order_number
486 AND ratl.interface_line_attribute6 =
487 to_char(oola.line_id)
488 AND oola.top_model_line_id = p_order_line_id
489 AND rat.complete_flag = 'Y'
490 AND ratl.interface_line_context <> 'INTERCOMPANY';
491
492 BEGIN
493 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
494 THEN
495 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
496 , G_MODULE_NAME || l_procedure_name || '.begin'
497 ,'enter procedure'
498 );
499 END IF;
500
501 --No need to check cursor not found, because cursor with SUM
502 --function always return true
503 OPEN l_cto_amt_drp;
504 FETCH l_cto_amt_drp INTO
505 x_extended_amount;
506 CLOSE l_cto_amt_drp;
507
508 OPEN l_cto_cm_amt_drp;
509 FETCH l_cto_cm_amt_drp INTO
510 x_cm_extended_amount;
511 CLOSE l_cto_cm_amt_drp;
512
513 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
514 THEN
515 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
516 , G_MODULE_NAME || l_procedure_name || '.end'
517 ,'exit procedure'
518 );
519 END IF;
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
524 THEN
525 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
526 , G_MODULE_NAME || l_procedure_name||'. Others exception'
527 , 'Exception'
528 );
529 END IF;
530 END Calc_Cto_Amount_Drp;
531
532 -- ========================================================================
533 -- PROCEDURE : Calc_Cto_Amount_Rma Private
534 -- PARAMETERS: p_order_line_id IN order line id
535 -- p_org_id IN operating unit id
536 -- x_extended_amount OUT invoice amount
537 -- COMMENT : Procedure to calcualte the invoice amount for CTO items
538 -- ========================================================================
539 PROCEDURE Calc_Cto_Amount_Rma
540 ( p_order_line_id IN NUMBER
541 , p_order_number IN VARCHAR2
542 , p_org_id IN NUMBER
543 , x_extended_amount OUT NOCOPY NUMBER
544 )
545 IS
546 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Cto_Amount_Rma';
547
548 --Cursor to calculate invoice amount for RMA CTO item
549 CURSOR l_cto_amt_rma IS
550 SELECT
551 SUM(ratl.extended_amount)
552 FROM
553 RA_CUSTOMER_TRX_ALL rat
554 , RA_CUSTOMER_TRX_LINES_ALL ratl
555 , RA_CUST_TRX_TYPES_ALL ratt
556 , oe_order_lines_all oola
557 WHERE rat.customer_trx_id = ratl.customer_trx_id
558 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
559 AND rat.org_id = ratt.org_id
560 AND ratt.type IN ('CM','DM')
561 AND ratl.line_type = 'LINE' --yawang
562 AND ratl.quantity_credited IS NOT NULL
563 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
564 AND NVL(ratl.org_id,0) = NVL(p_org_id,0)
565 AND ratl.interface_line_attribute6 = to_char(oola.line_id)
566 AND ratl.sales_order = p_order_number
567 AND oola.top_model_line_id = p_order_line_id
568 AND ratl.interface_line_context <> 'INTERCOMPANY';
569 BEGIN
570 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
571 THEN
572 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
573 , G_MODULE_NAME || l_procedure_name || '.begin'
574 ,'enter procedure'
575 );
576 END IF;
577
578 --No need to check cursor not found, because cursor with SUM
579 --function always return true
580 OPEN l_cto_amt_rma;
581 FETCH l_cto_amt_rma INTO
582 x_extended_amount;
583 CLOSE l_cto_amt_rma;
584
585 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
586 THEN
587 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
588 , G_MODULE_NAME || l_procedure_name || '.end'
589 ,'exit procedure'
590 );
591 END IF;
592
593 EXCEPTION
594 WHEN OTHERS THEN
595 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
596 THEN
597 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
598 , G_MODULE_NAME || l_procedure_name||'. Others exception'
599 , 'Exception'
600 );
601 END IF;
602 END Calc_Cto_Amount_Rma;
603
604 -- ========================================================================
605 -- PROCEDURE : Calc_Cto_Amount_Ar Private
606 -- PARAMETERS: p_order_line_id IN order line id
607 -- x_extended_amount OUT invoice amount
608 -- COMMENT : Procedure to calcualte the invoice amount for CTO items
609 -- ========================================================================
610 PROCEDURE Calc_Cto_Amount_Ar
611 ( p_order_line_id IN NUMBER
612 , p_order_number IN VARCHAR2
613 , x_extended_amount OUT NOCOPY NUMBER
614 )
615 IS
616 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Cto_Amount_Ar';
617
618 --Cursor to calculate invoice amount for ar intercompany invoice for CTO item
619 CURSOR l_cto_amt_ar IS
620 SELECT
621 SUM(ratl.extended_amount)
622 FROM
623 RA_CUSTOMER_TRX_LINES_ALL ratl
624 , oe_order_lines_all oola
625 WHERE ratl.line_type = 'LINE'
626 AND ratl.interface_line_attribute6 = to_char(oola.line_id)
627 AND ratl.sales_order = p_order_number
628 AND oola.top_model_line_id = p_order_line_id
629 AND ratl.interface_line_context = 'INTERCOMPANY';
630 BEGIN
631 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
632 THEN
633 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
634 , G_MODULE_NAME || l_procedure_name || '.begin'
635 ,'enter procedure'
636 );
637 END IF;
638
639 --No need to check cursor not found, because cursor with SUM
640 --function always return true
641 OPEN l_cto_amt_ar;
642 FETCH l_cto_amt_ar INTO
643 x_extended_amount;
644 CLOSE l_cto_amt_ar;
645
646 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
647 THEN
648 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
649 , G_MODULE_NAME || l_procedure_name || '.end'
650 ,'exit procedure'
651 );
652 END IF;
653
654 EXCEPTION
655 WHEN OTHERS THEN
656 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
657 THEN
658 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
659 , G_MODULE_NAME || l_procedure_name||'. Others exception'
660 , 'Exception'
661 );
662 END IF;
663 END Calc_Cto_Amount_Ar;
664
665 -- ========================================================================
666 -- PROCEDURE : Calc_Cto_Amount_Ap Private
667 -- PARAMETERS: p_order_line_id IN order line id
668 -- x_extended_amount OUT invoice amount
669 -- COMMENT : Procedure to calcualte the invoice amount for CTO items
670 -- ========================================================================
671 PROCEDURE Calc_Cto_Amount_Ap
672 ( p_order_line_id IN NUMBER
673 , p_order_number IN VARCHAR2
674 , x_extended_amount OUT NOCOPY NUMBER
675 )
676 IS
677 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Cto_Amount_Ap';
678
679 -- Bug 5440432: Following query is modified to replace where clause
680 -- 'AND rcta.trx_number||'-'||rcta.org_id = aia.invoice_num' with
681 -- 'AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
682 -- OR rcta.trx_number = aia.invoice_num)'
683 -- This change was introduced because of bug 4180686 in 11.5.10
684
685 --Cursor to calculate invoice amount for ap intercompany invoice for CTO item
686 CURSOR l_cto_amt_ap IS
687 SELECT
688 SUM(NVL(aila.amount,rctla.extended_amount))
689 FROM
690 AP_INVOICES_ALL aia
691 , AP_INVOICE_LINES_ALL aila
692 , RA_CUSTOMER_TRX_LINES_ALL rctla
693 , ra_customer_trx_all rcta
694 , oe_order_lines_all oola
695 WHERE aia.invoice_id = aila.invoice_id
696 AND aia.cancelled_date IS NULL
697 AND aila.line_type_lookup_code = 'ITEM'
698 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
699 AND aila.reference_1 = TO_CHAR(rctla.customer_trx_line_id)
700 AND rctla.customer_trx_id = rcta.customer_trx_id
701 AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
702 OR rcta.trx_number = aia.invoice_num)
703 AND rctla.sales_order = p_order_number
704 AND rctla.interface_line_attribute6 = to_char(oola.line_id)
705 AND oola.top_model_line_id = p_order_line_id
706 AND nvl(aila.discarded_flag, 'N') <> 'Y'
707 AND NOT EXISTS (SELECT 'Unreleased holds exist'
708 FROM ap_holds_all aha
709 WHERE aha.invoice_id = aia.invoice_id
710 AND aha.release_lookup_code is null)
711 AND EXISTS (SELECT 'Invoice is approved'
712 FROM ap_invoice_distributions_all aida
713 WHERE aida.invoice_id = aia.invoice_id
714 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
715
716 BEGIN
717 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
718 THEN
719 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
720 , G_MODULE_NAME || l_procedure_name || '.begin'
721 ,'enter procedure'
722 );
723 END IF;
724
725 --No need to check cursor not found, because cursor with SUM
726 --function always return true
727 OPEN l_cto_amt_ap;
728 FETCH l_cto_amt_ap INTO
729 x_extended_amount;
730 CLOSE l_cto_amt_ap;
731
732 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
733 THEN
734 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
735 , G_MODULE_NAME || l_procedure_name || '.end'
736 ,'exit procedure'
737 );
738 END IF;
739
740 EXCEPTION
741 WHEN OTHERS THEN
742 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
743 THEN
744 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
745 , G_MODULE_NAME || l_procedure_name||'. Others exception'
746 , 'Exception'
747 );
748 END IF;
749 END Calc_Cto_Amount_Ap;
750
751
752 -- ========================================================================
753 -- PROCEDURE : Calc_Line_Charge Private
754 -- PARAMETERS: p_line_id IN order line id
755 -- p_invoiced_line_qty IN invoiced quantity for this line
756 -- x_line_freight_charge OUT line level freight charge
757 -- COMMENT : Procedure to calcualte the line level freight charge
758 -- ========================================================================
759 PROCEDURE Calc_Line_Charge
760 ( p_line_id IN NUMBER
761 , p_invoiced_line_qty IN NUMBER
762 , x_line_freight_charge OUT NOCOPY NUMBER
763 )
764 IS
765 l_line_freight_unit_amt NUMBER;
766 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Line_Charge';
767
768 --Find out line level freight charge per unit
769 CURSOR c_adj_line_freight
770 IS
771 SELECT
772 SUM(NVL(ADJUSTED_AMOUNT_PER_PQTY,0))
773 FROM
774 oe_price_adjustments
775 WHERE line_id = p_line_id
776 AND modifier_level_code = 'LINE'
777 AND list_line_type_code = 'FREIGHT_CHARGE';
778
779 BEGIN
780 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
781 THEN
782 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
783 , G_MODULE_NAME || l_procedure_name || '.begin'
784 ,'enter procedure'
785 );
786 END IF;
787
788 OPEN c_adj_line_freight;
789 FETCH c_adj_line_freight INTO l_line_freight_unit_amt;
790 CLOSE c_adj_line_freight;
791
792 --Check null value. Can not use cursor%notfound,because sum
793 --function in cursor select always returns a row (found)
794 IF l_line_freight_unit_amt IS NULL
795 THEN
796 l_line_freight_unit_amt := 0;
797 END IF;
798
799 --Use invoiced quantity to calculate the line level charge, because the
800 --invoiced qty may be different from the ordered quantity
801 x_line_freight_charge := l_line_freight_unit_amt * nvl(p_invoiced_line_qty,0);
802
803 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
804 THEN
805 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
806 , G_MODULE_NAME || l_procedure_name || '.end'
807 ,'exit procedure'
808 );
809 END IF;
810
811 EXCEPTION
812 WHEN OTHERS THEN
813 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
814 THEN
815 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
816 , G_MODULE_NAME || l_procedure_name||'. Others exception'
817 , 'Exception'
818 );
819 END IF;
820 END Calc_Line_Charge;
821
822 -- ========================================================================
823 -- PROCEDURE : Calc_Total_Line_Charge Private
824 -- PARAMETERS: p_movement_transaction IN movement transaction record
825 -- x_total_line_charge OUT line level charge for all lines
826 -- x_total_invoiced_qty OUT total invoiced qty for this order
827 -- COMMENT : Procedure to calcualte total line level charge
828 -- ========================================================================
829 PROCEDURE Calc_Total_Line_Charge
830 ( p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
831 , x_this_line_charge OUT NOCOPY NUMBER
832 , x_total_line_charge OUT NOCOPY NUMBER
833 , x_total_invoiced_qty OUT NOCOPY NUMBER
834 )
835 IS
836 l_line_id NUMBER;
837 l_model_line_id NUMBER;
838 l_item_type_code oe_order_lines_all.item_type_code%TYPE;
839 l_invoiced_line_qty NUMBER;
840 l_total_invoiced_qty NUMBER :=0;
841 l_line_freight_charge NUMBER;
842 l_total_line_charge NUMBER := 0;
843 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Total_Line_Charge';
844
845 --Find out all the order lines which share an invoice with the processing
846 --order line
847 CURSOR c_order_lines IS
848 SELECT DISTINCT
849 TO_NUMBER(interface_line_attribute6)
850 FROM
851 ra_customer_trx_lines_all
852 WHERE sales_order = to_char(p_movement_transaction.order_number)
853 AND line_type = 'LINE'
854 AND customer_trx_id IN
855 (SELECT customer_trx_id
856 FROM ra_customer_trx_lines_all
857 WHERE interface_line_attribute6 = to_char(p_movement_transaction.order_line_id)
858 AND sales_order = to_char(p_movement_transaction.order_number))
859 ORDER BY TO_NUMBER(interface_line_attribute6);
860
861 --Find out if this item is a CTO item
862 CURSOR c_cto IS
863 SELECT
864 item_type_code
865 , top_model_line_id
866 FROM
867 oe_order_lines_all
868 WHERE line_id = p_movement_transaction.order_line_id;
869
870 --Calculate the invoice quantity for an order line
871 CURSOR c_invoiced_line_qty IS
872 SELECT
873 SUM(NVL(ratl.quantity_invoiced,0))
874 FROM
875 RA_CUSTOMER_TRX_ALL rat
876 , RA_CUSTOMER_TRX_LINES_ALL ratl
877 , RA_CUST_TRX_TYPES_ALL ratt
878 WHERE rat.customer_trx_id = ratl.customer_trx_id
879 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
880 AND rat.org_id = ratt.org_id
881 AND ratt.type NOT IN ('CM','DM')
882 AND NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
883 NOT IN (SELECT TO_CHAR(price_adjustment_id)
884 FROM oe_price_adjustments
885 WHERE header_id = p_movement_transaction.order_header_id
886 AND (line_id = l_line_id
887 OR (line_id IS NULL AND modifier_level_code = 'ORDER')))
888 AND ratl.line_type = 'LINE'
889 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
890 AND ratl.sales_order = to_char(p_movement_transaction.order_number)
891 AND ratl.interface_line_attribute6 = l_line_id
892 AND ratl.interface_line_context <> 'INTERCOMPANY';
893 BEGIN
894 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
895 THEN
896 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
897 , G_MODULE_NAME || l_procedure_name || '.begin'
898 ,'enter procedure'
899 );
900 END IF;
901
902 OPEN c_order_lines;
903 LOOP
904 FETCH c_order_lines INTO l_line_id;
905 EXIT WHEN c_order_lines%NOTFOUND;
906
907 --Check if this order line is of CTO item
908 OPEN c_cto;
909 FETCH c_cto INTO
910 l_item_type_code
911 , l_model_line_id;
912 CLOSE c_cto;
913
914 IF l_item_type_code = 'CONFIG'
915 THEN
916 l_line_id := l_model_line_id;
917 END IF;
918
919 --Calculate invoiced qty for this line in the line loop
920 OPEN c_invoiced_line_qty;
921 FETCH c_invoiced_line_qty INTO l_invoiced_line_qty;
922 CLOSE c_invoiced_line_qty;
923
924 IF l_invoiced_line_qty IS NULL
925 THEN
926 l_invoiced_line_qty := 0;
927 END IF;
928
929 --Calculate line charge (using invoiced qty) for this line in
930 --the line loop
931 Calc_Line_Charge
932 ( p_line_id => l_line_id
933 , p_invoiced_line_qty => l_invoiced_line_qty
934 , x_line_freight_charge => l_line_freight_charge
935 );
936
937 --The line charge for the processing line in calling program. This value will
938 --be passed back to calling program so that the calling program does not need
939 --to call calc_line_charge again
940 IF l_line_id = p_movement_transaction.order_line_id
941 THEN
942 x_this_line_charge := l_line_freight_charge;
943 END IF;
944
945 --Total invoiced qty and total line charge
946 l_total_invoiced_qty := l_total_invoiced_qty + l_invoiced_line_qty;
947 l_total_line_charge := l_total_line_charge + l_line_freight_charge;
948 END LOOP;
949 CLOSE c_order_lines;
950
951 x_total_invoiced_qty := l_total_invoiced_qty;
952 x_total_line_charge := l_total_line_charge;
953
954 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
955 THEN
956 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
957 , G_MODULE_NAME || l_procedure_name || '.end'
958 ,'exit procedure'
959 );
960 END IF;
961
962 EXCEPTION
963 WHEN OTHERS THEN
964 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
965 THEN
966 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
967 , G_MODULE_NAME || l_procedure_name||'. Others exception'
968 , 'Exception'
969 );
970 END IF;
971 END Calc_Total_Line_Charge;
972
973 -- ========================================================================
974 -- PROCEDURE : Calc_Processed_Ret_Data Private
975 -- PARAMETERS: p_movement_transaction IN movement transaction record
976 -- x_processed_ret_amt OUT line level charge for all lines
977 -- x_processed_ret_qty OUT total invoiced qty for this order
978 -- COMMENT : Procedure to calcualte processed invoice amount and quantity
979 -- for RTV and RMA
980 -- ========================================================================
981 PROCEDURE Calc_Processed_Ret_Data
982 ( p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
983 , x_processed_ret_amt OUT NOCOPY NUMBER
984 , x_processed_ret_qty OUT NOCOPY NUMBER
985 )
986 IS
987 l_rtv_count NUMBER;
988 l_processed_rtv_trans_qty NUMBER;
989 l_total_rtv_trans_qty NUMBER;
990 l_rma_count NUMBER;
991 l_parent_transaction_id NUMBER;
992 l_rtv_extended_amount NUMBER;
993 l_rtv_invoice_quantity NUMBER;
994 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Processed_Ret_Data';
995
996 --Cursor to get processed rma count for a given SO
997 --Fix bug 2861110
998 CURSOR l_rma IS
999 SELECT
1000 count(transaction_id)
1001 FROM
1002 rcv_transactions rt
1003 , oe_order_lines_all oola
1004 WHERE rt.oe_order_line_id = oola.line_id
1005 AND oola.reference_line_id = to_char(p_movement_transaction.order_line_id)
1006 AND rt.mvt_stat_status = 'PROCESSED'
1007 AND rt.source_document_code = 'RMA';
1008
1009 --Cursor to get processed rma invoice for a given SO
1010 --Fix bug 2861110
1011 CURSOR l_rma_processed IS
1012 SELECT
1013 SUM(NVL(ratl.extended_amount,0))
1014 , SUM(NVL(ratl.quantity_credited,0))
1015 FROM
1016 RA_CUSTOMER_TRX_ALL rat
1017 , RA_CUSTOMER_TRX_LINES_ALL ratl
1018 , RA_CUST_TRX_TYPES_ALL ratt
1019 , OE_ORDER_LINES_ALL oola
1020 , RCV_TRANSACTIONS rt
1021 WHERE rat.customer_trx_id = ratl.customer_trx_id
1022 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
1023 AND rat.org_id = ratt.org_id
1024 AND ratt.type IN ('CM','DM')
1025 AND ratl.line_type = 'LINE' --yawang
1026 AND ratl.quantity_credited IS NOT NULL
1027 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
1028 -- AND NVL(ratl.org_id,0) = NVL(p_movement_transaction.org_id,0)
1029 AND ratl.interface_line_attribute6 = to_char(oola.line_id)
1030 AND oola.reference_line_id = to_char(p_movement_transaction.order_line_id)
1031 AND rt.oe_order_line_id = oola.line_id
1032 AND rt.mvt_stat_status = 'PROCESSED'
1033 AND rt.transaction_type = 'DELIVER'
1034 AND ratl.interface_line_context <> 'INTERCOMPANY'
1035 AND oola.line_id NOT IN (SELECT order_line_id
1036 FROM mtl_movement_statistics
1037 WHERE entity_org_id = p_movement_transaction.entity_org_id
1038 AND zone_code = p_movement_transaction.zone_code
1039 AND usage_type = p_movement_transaction.usage_type
1040 AND stat_type = p_movement_transaction.stat_type
1041 AND document_source_type = 'RMA'
1042 AND rcv_transaction_id = rt.transaction_id);
1043
1044 --Cursor to check if this PO receipt has any processed RTV
1045 --Fix bug 2861110
1046 CURSOR l_rtv IS
1047 SELECT
1048 COUNT(transaction_id)
1049 FROM
1050 rcv_transactions
1051 WHERE transaction_type = 'RETURN TO VENDOR'
1052 AND parent_transaction_id = p_movement_transaction.rcv_transaction_id
1053 AND mvt_stat_status = 'PROCESSED';
1054
1055 --Cursor to get total rtv transaction quantity for Italian LE
1056 --fix bug 2861110
1057 CURSOR l_total_rtv_quantity IS
1058 SELECT
1059 SUM(quantity)
1060 FROM
1061 rcv_transactions
1062 WHERE po_header_id = p_movement_transaction.po_header_id
1063 AND transaction_type = 'RETURN TO VENDOR';
1064
1065 --Cursor to get processed rtv transaction quantity exclude
1066 --those RTV that created in next period, which will create
1067 --seperate MS record with its own credit memo as invoice info
1068 CURSOR l_netted_rtv_quantity IS
1069 SELECT
1070 SUM(quantity)
1071 FROM
1072 rcv_transactions rt
1073 WHERE po_header_id = p_movement_transaction.po_header_id
1074 AND transaction_type = 'RETURN TO VENDOR'
1075 AND mvt_stat_status = 'PROCESSED'
1076 AND transaction_id NOT IN (SELECT rcv_transaction_id
1077 FROM mtl_movement_statistics
1078 WHERE document_source_type = 'RTV'
1079 AND po_header_id = rt.po_header_id
1080 AND entity_org_id = p_movement_transaction.entity_org_id
1081 AND zone_code = p_movement_transaction.zone_code
1082 AND usage_type = p_movement_transaction.usage_type
1083 AND stat_type = p_movement_transaction.stat_type);
1084
1085 --Cursor for Credit memos that is associated with RTV transaction
1086 --in case of receipt based matching
1087 -- Bug 5655040.Cursor has been modified to AP Line tables in place of Distributions
1088 --CURSOR l_rtv_cm_receipt_based IS
1089 ---SELECT
1090 -- SUM(apid.amount)
1091 --, SUM(apid.quantity_invoiced)
1092 --FROM
1093 -- AP_INVOICES_ALL api,
1094 -- AP_INVOICE_DISTRIBUTIONS_ALL apid
1095 --WHERE api.invoice_id = apid.invoice_id
1096 --AND api.invoice_type_lookup_code in ('CREDIT','DEBIT')
1097 --AND apid.rcv_transaction_id = l_parent_transaction_id
1098 --AND apid.line_type_lookup_code = 'ITEM' --yawang, limit for good cost only
1099 --AND NVL(apid.quantity_invoiced,0) < 0
1100 --AND api.cancelled_date IS NULL
1101 --AND (NVL(apid.match_status_flag,'N') = 'A'
1102 -- OR (NVL(apid.match_status_flag,'N') = 'T'
1103 -- AND api.wfapproval_status = 'NOT REQUIRED'));
1104
1105 CURSOR l_rtv_cm_receipt_based IS
1106 SELECT
1107 SUM(aila.amount)
1108 , SUM(aila.quantity_invoiced)
1109 FROM
1110 AP_INVOICES_ALL aia,
1111 AP_INVOICE_LINES_ALL aila
1112 WHERE aia.invoice_id = aila.invoice_id
1113 AND aia.invoice_type_lookup_code in ('CREDIT','DEBIT')
1114 AND aila.rcv_transaction_id = l_parent_transaction_id
1115 AND aila.line_type_lookup_code = 'ITEM'
1116 AND NVL(aila.quantity_invoiced,0) < 0
1117 AND aia.cancelled_date IS NULL
1118 AND nvl(aila.discarded_flag, 'N') <> 'Y'
1119 AND NOT EXISTS (SELECT 'Unreleased holds exist'
1120 FROM ap_holds_all aha
1121 WHERE aha.invoice_id = aia.invoice_id
1122 AND aha.release_lookup_code is null)
1123 AND EXISTS (SELECT 'Invoice is approved'
1124 FROM ap_invoice_distributions_all aida
1125 WHERE aida.invoice_id = aia.invoice_id
1126 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
1127
1128 -- Cursor for RTV's where credit memo is to be associated with
1129 -- RTV in case of PO based matching.
1130 -- Bug 5655040.Cursor has been modified to AP Line tables in place of Distributions
1131 --CURSOR l_rtv_cm_po_based IS
1132 --SELECT
1133 -- SUM(a.amount) --yawang
1134 --, SUM(quantity_invoiced)
1135 --FROM
1136 -- PO_HEADERS_ALL b
1137 --, PO_DISTRIBUTIONS_ALL c
1138 --, AP_INVOICES_ALL d
1139 --, AP_INVOICE_DISTRIBUTIONS_ALL a
1140 --WHERE b.po_header_id = c.po_header_id
1141 --AND d.invoice_id = a.invoice_id
1142 --AND c.po_distribution_id = a.po_distribution_id
1143 --AND (NVL(a.match_status_flag,'N') = 'A'
1144 -- OR (NVL(a.match_status_flag,'N') = 'T'
1145 -- AND d.wfapproval_status = 'NOT REQUIRED'))
1146 --AND d.invoice_type_lookup_code in ('CREDIT','DEBIT')
1147 --AND d.cancelled_date IS NULL
1148 --AND a.line_type_lookup_code = 'ITEM' --yawang, limit for good cost only
1149 --AND NVL(a.quantity_invoiced,0) < 0
1150 --AND b.po_header_id = p_movement_transaction.po_header_id
1151 --AND c.line_location_id = p_movement_transaction.po_line_location_id;
1152 CURSOR l_rtv_cm_po_based IS
1153 SELECT
1154 SUM(aila.amount) --yawang
1155 , SUM(aila.quantity_invoiced)
1156 FROM
1157 PO_HEADERS_ALL pha
1158 , PO_DISTRIBUTIONS_ALL pda
1159 , AP_INVOICES_ALL aia
1160 , AP_INVOICE_LINES_ALL aila
1161 WHERE pha.po_header_id = pda.po_header_id
1162 AND aia.invoice_id = aila.invoice_id
1163 AND pda.po_header_id = aila.po_header_id /*Bug 7446311 Joined to imporve performance*/
1164 AND pda.po_distribution_id = aila.po_distribution_id
1165 AND aia.invoice_type_lookup_code in ('CREDIT','DEBIT')
1166 AND aia.cancelled_date IS NULL
1167 AND aila.line_type_lookup_code = 'ITEM' --yawang, limit for good cost only
1168 AND NVL(aila.quantity_invoiced,0) < 0
1169 AND pha.po_header_id = p_movement_transaction.po_header_id
1170 AND pda.line_location_id = p_movement_transaction.po_line_location_id
1171 AND nvl(aila.discarded_flag, 'N') <> 'Y'
1172 AND NOT EXISTS (SELECT 'Unreleased holds exist'
1173 FROM ap_holds_all aha
1174 WHERE aha.invoice_id = aia.invoice_id
1175 AND aha.release_lookup_code is null)
1176 AND EXISTS (SELECT 'Invoice is approved'
1177 FROM ap_invoice_distributions_all aida
1178 WHERE aida.invoice_id = aia.invoice_id
1179 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
1180
1181 BEGIN
1182 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1183 THEN
1184 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1185 , G_MODULE_NAME || l_procedure_name || '.begin'
1186 ,'enter procedure'
1187 );
1188 END IF;
1189
1190 IF p_movement_transaction.document_source_type = 'SO'
1191 THEN
1192 OPEN l_rma;
1193 FETCH l_rma INTO
1194 l_rma_count;
1195 CLOSE l_rma;
1196
1197 IF l_rma_count > 0
1198 THEN
1199 OPEN l_rma_processed;
1200 FETCH l_rma_processed INTO
1201 x_processed_ret_amt
1202 , x_processed_ret_qty;
1203 CLOSE l_rma_processed;
1204 END IF;
1205 ELSIF p_movement_transaction.document_source_type = 'PO'
1206 THEN
1207 OPEN l_rtv;
1208 FETCH l_rtv INTO
1209 l_rtv_count;
1210 CLOSE l_rtv;
1211
1212 IF l_rtv_count > 0
1213 THEN
1214 --RTV is child of PO. To find RTV credit memo, needs to know the
1215 --parent id of the rtv. Parent id of rtv is the rcv transaction id of
1216 --current PO, which is used in receipt based matching
1217 l_parent_transaction_id := p_movement_transaction.rcv_transaction_id;
1218
1219 --Open receipt based matching rtv credit memo cursor
1220 OPEN l_rtv_cm_receipt_based;
1221 FETCH l_rtv_cm_receipt_based INTO
1222 l_rtv_extended_amount
1223 , l_rtv_invoice_quantity;
1224
1225 --If not receipt based credit memo, open po based credit memo for rtv
1226 IF l_rtv_cm_receipt_based%NOTFOUND OR l_rtv_extended_amount IS NULL
1227 THEN
1228 OPEN l_rtv_cm_po_based;
1229 FETCH l_rtv_cm_po_based INTO
1230 l_rtv_extended_amount
1231 , l_rtv_invoice_quantity;
1232
1233 IF l_rtv_cm_po_based%NOTFOUND OR l_rtv_extended_amount IS NULL
1234 THEN
1235 l_rtv_extended_amount := 0;
1236 l_rtv_invoice_quantity := 0;
1237 END IF;
1238
1239 CLOSE l_rtv_cm_po_based;
1240 END IF;
1241 CLOSE l_rtv_cm_receipt_based;
1242
1243 --Find total rtv transaction quantity
1244 OPEN l_total_rtv_quantity;
1245 FETCH l_total_rtv_quantity INTO
1246 l_total_rtv_trans_qty;
1247 CLOSE l_total_rtv_quantity;
1248
1249 --Find netted rtv transaction quantity
1250 OPEN l_netted_rtv_quantity;
1251 FETCH l_netted_rtv_quantity INTO
1252 l_processed_rtv_trans_qty;
1253 CLOSE l_netted_rtv_quantity;
1254
1255 --Find processed rtv invoice amount and invoice quantity
1256 IF l_total_rtv_trans_qty IS NOT NULL
1257 THEN
1258 x_processed_ret_amt := (l_processed_rtv_trans_qty/l_total_rtv_trans_qty)
1259 * l_rtv_extended_amount;
1260 x_processed_ret_qty := (l_processed_rtv_trans_qty/l_total_rtv_trans_qty)
1261 * l_rtv_invoice_quantity;
1262 END IF;
1263 END IF;
1264 END IF;
1265
1266 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1267 THEN
1268 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1269 , G_MODULE_NAME || l_procedure_name || '.end'
1270 ,'exit procedure'
1271 );
1272 END IF;
1273
1274 EXCEPTION
1275 WHEN OTHERS THEN
1276 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1277 THEN
1278 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1279 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1280 , 'Exception'
1281 );
1282 END IF;
1283 END Calc_Processed_Ret_Data;
1284
1285
1286 --====================
1287 --Public procedure
1288 --====================
1289
1290 --========================================================================
1291 -- PROCEDURE : Exchange_Rate_Calc PUBLIC
1292 -- PARAMETERS:
1293 -- p_stat_typ_transaction mtl_stat_type_usages data record
1294 -- p_movement_transaction movement transaction data record
1295 -- COMMENT : This function returns the exchange rate based on
1296 -- the conversion date that is set up in the
1297 -- statistical type info form.
1298 --=======================================================================
1299
1300 PROCEDURE Calc_Exchange_Rate
1301 ( x_movement_transaction IN OUT NOCOPY
1302 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1303 , p_stat_typ_transaction IN
1304 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
1305 )
1306 IS
1307 l_gl_set_of_books_id VARCHAR2(15);
1308 l_last_dayofperiod DATE;
1309 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Exchange_Rate';
1310
1311 BEGIN
1312 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1313 THEN
1314 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1315 , G_MODULE_NAME || l_procedure_name || '.begin'
1316 ,'enter procedure'
1317 );
1318 END IF;
1319
1320 --Get the end date of the period
1321 SELECT end_date
1322 INTO
1323 l_last_dayofperiod
1324 FROM
1325 GL_PERIODS
1326 WHERE period_name = x_movement_transaction.period_name
1327 AND period_set_name = p_stat_typ_transaction.period_set_name
1328 AND period_type = p_stat_typ_transaction.period_type;
1329
1330 IF UPPER(p_stat_typ_transaction.conversion_option) = 'CO_LAST_DAY'
1331 THEN
1332 x_movement_transaction.currency_conversion_rate := GL_CURRENCY_API.Get_Rate
1333 ( x_from_currency => x_movement_transaction.currency_code
1334 , x_to_currency => x_movement_transaction.gl_currency_code
1335 , x_conversion_date => l_last_dayofperiod
1336 , x_conversion_type => x_movement_transaction.currency_conversion_type
1337 );
1338
1339 x_movement_transaction.currency_conversion_date := l_last_dayofperiod;
1340 ELSE
1341 x_movement_transaction.currency_conversion_date :=
1342 NVL(x_movement_transaction.invoice_date_reference, x_movement_transaction.transaction_date);
1343
1344 x_movement_transaction.currency_conversion_rate := GL_CURRENCY_API.Get_Rate
1345 ( x_from_currency => x_movement_transaction.currency_code
1346 , x_to_currency => x_movement_transaction.gl_currency_code
1347 , x_conversion_date => x_movement_transaction.currency_conversion_date
1348 , x_conversion_type => x_movement_transaction.currency_conversion_type
1349 );
1350 END IF;
1351
1352 IF x_movement_transaction.currency_conversion_rate IS NULL
1353 THEN
1354 x_movement_transaction.currency_conversion_rate := 1;
1355 x_movement_transaction.currency_conversion_type := null;
1356 x_movement_transaction.currency_conversion_date := null;
1357 END IF;
1358
1359 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1360 THEN
1361 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1362 , G_MODULE_NAME || l_procedure_name || '.end'
1363 ,'exit procedure'
1364 );
1365 END IF;
1366 EXCEPTION
1367 WHEN GL_CURRENCY_API.no_rate
1368 THEN
1369 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1370 THEN
1371 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1372 , G_MODULE_NAME || l_procedure_name||'.No rate exception'
1373 , 'Exception'
1374 );
1375 END IF;
1376 x_movement_transaction.currency_conversion_rate := 1;
1377 x_movement_transaction.currency_conversion_type := null;
1378 x_movement_transaction.currency_conversion_date := null;
1379 WHEN NO_DATA_FOUND
1380 THEN
1381 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1382 THEN
1383 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1384 , G_MODULE_NAME || l_procedure_name||'.No data found exception'
1385 , 'Exception'
1386 );
1387 END IF;
1388 x_movement_transaction.currency_conversion_rate := 1;
1389 x_movement_transaction.currency_conversion_type := null;
1390 x_movement_transaction.currency_conversion_date := null;
1391 WHEN OTHERS
1392 THEN
1393 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1394 THEN
1395 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1396 , G_MODULE_NAME || l_procedure_name||'.Others exception'
1397 , 'Exception'
1398 );
1399 END IF;
1400 x_movement_transaction.currency_conversion_rate := 1;
1401 x_movement_transaction.currency_conversion_type := null;
1402 x_movement_transaction.currency_conversion_date := null;
1403 END Calc_Exchange_Rate;
1404
1405 --========================================================================
1406 -- FUNCTION : Calc_Movement_AMount PUBLIC
1407 -- PARAMETERS:
1408 -- p_movement_transaction movement transaction data record
1409 -- COMMENT : Calculates and returns the Movement Amount value
1410 --=======================================================================
1411
1412 FUNCTION Calc_Movement_Amount
1413 ( p_movement_transaction IN
1414 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1415 )
1416 RETURN NUMBER
1417 IS
1418 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1419 l_report_price NUMBER;
1420 l_tr_value NUMBER;
1421 l_inv_uom VARCHAR2(10);
1422 l_trans_conv_inv_rate NUMBER;
1423 l_inv_qty NUMBER;
1424 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Movement_Amount';
1425 l_invoice_currency VARCHAR2(10);
1426 l_currency_conversion_rate NUMBER;
1427
1428 CURSOR inv_uom IS
1429 SELECT
1430 uom_code
1431 FROM
1432 ra_customer_trx_lines_all
1433 WHERE customer_trx_line_id = l_movement_transaction.customer_trx_line_id;
1434
1435 /* Bug 6158521 - Start */
1436 /* Define cursor to the invoice UOM for AP invoices */
1437 CURSOR poinv_uom IS
1438 SELECT
1439 UOM.UOM_CODE
1440 FROM
1441 AP_INVOICE_DISTRIBUTIONS_ALL AID,MTL_UNITS_OF_MEASURE UOM
1442 WHERE AID.invoice_id = l_movement_transaction.invoice_id
1443 AND AID.distribution_line_number = l_movement_transaction.distribution_line_number
1444 AND AID.MATCHED_UOM_LOOKUP_CODE=UOM.UNIT_OF_MEASURE;
1445
1446 /* Bug 6158521 - End */
1447
1448 BEGIN
1449
1450 l_currency_conversion_rate := 1; -- 6889669
1451 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1452 THEN
1453 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1454 , G_MODULE_NAME || l_procedure_name || '.begin'
1455 ,'enter procedure'
1456 );
1457 END IF;
1458
1459 l_movement_transaction := p_movement_transaction;
1460
1461 -----------------------------------------------
1462 -- INVOICE_ID is not null --
1463 -----------------------------------------------
1464 IF l_movement_transaction.invoice_id is not null
1465 AND (l_movement_transaction.document_source_type IN ('SO','RMA', 'IO') -- 6889669
1466 OR l_movement_transaction.document_source_type IN ('PO','RTV'))
1467 THEN
1468 --Invoice quantity may have different uom than transaction qty
1469 --convert invoice qty to same uom as transaction qty
1470 --This is for SO only
1471 IF (l_movement_transaction.document_source_type IN ('SO','RMA')
1472 AND l_movement_transaction.customer_trx_line_id IS NOT NULL)
1473 THEN
1474 OPEN inv_uom;
1475 FETCH inv_uom INTO l_inv_uom;
1476 IF inv_uom%NOTFOUND
1477 THEN
1478 l_inv_uom := l_movement_transaction.transaction_uom_code;
1479 END IF;
1480 CLOSE inv_uom;
1481
1482 /* Bug 6158521 - Start */
1483 /* Call INV UOM conversion if invoice UOM is different from txn UOM */
1484 ELSE
1485 IF (l_movement_transaction.distribution_line_number IS NOT NULL)
1486 THEN
1487 OPEN poinv_uom;
1488 FETCH poinv_uom INTO l_inv_uom;
1489 IF poinv_uom%NOTFOUND
1490 Then
1491 l_inv_uom := l_movement_transaction.transaction_uom_code;
1492 END IF;
1493 CLOSE poinv_uom;
1494 ELSE
1495 l_inv_uom := l_movement_transaction.transaction_uom_code;
1496 END IF;
1497 END IF;
1498
1499 IF l_movement_transaction.transaction_uom_code <> l_inv_uom
1500 THEN
1501 INV_CONVERT.Inv_Um_Conversion
1502 ( from_unit => l_movement_transaction.transaction_uom_code
1503 , to_unit => l_inv_uom
1504 , item_id => l_movement_transaction.inventory_item_id
1505 , uom_rate => l_trans_conv_inv_rate
1506 );
1507 ELSE
1508 l_trans_conv_inv_rate := 1;
1509 END IF;
1510
1511 /* Special case for Internal Orders - 6889669 - BEGIN */
1512 IF (l_movement_transaction.document_source_type = 'IO')
1513 THEN
1514 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1515 THEN
1516 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1517 , G_MODULE_NAME || l_procedure_name || '.begin'
1518 ,'in Calc Movement Amount for IO'
1519 );
1520 END IF;
1521
1522 /* Invoice conversion for I/C AP - 6889669 - Start*/
1523 /* For an IO Arrival, ensure that the invoice currency is the
1524 same as the SOB currency, if not do the necessary conversion
1525 Note: This does NOT have to be done for IO dispatch as the currency
1526 is pulled from the shipping transaction whereas for an IO arrival,
1527 the currency is pulled from the Receiving transactions */
1528
1529 IF (l_movement_transaction.movement_type = 'A') THEN
1530 BEGIN
1531 SELECT
1532 NVL(invoice_currency_code, -1)
1533 INTO
1534 l_invoice_currency
1535 FROM
1536 AP_INVOICES_ALL
1537 WHERE
1538 invoice_id = l_movement_transaction.invoice_id ;
1539
1540 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1541 THEN
1542 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1543 , G_MODULE_NAME || l_procedure_name || '.begin'
1544 ,'The I/C AP invoice currency is '||l_invoice_currency
1545 );
1546 END IF;
1547
1548 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1549 THEN
1550 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1551 , G_MODULE_NAME || l_procedure_name || '.begin'
1552 ,'The GL currency code is '||l_movement_transaction.gl_currency_code
1553 );
1554 END IF;
1555
1556 IF (l_invoice_currency <> l_movement_transaction.gl_currency_code) THEN
1557 l_currency_conversion_rate := Get_Conversion_Rate(l_invoice_currency,l_movement_transaction );
1558 END IF;
1559 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1560 THEN
1561 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1562 , G_MODULE_NAME || l_procedure_name || '.begin'
1563 ,'The currency conversion is '||l_currency_conversion_rate
1564 );
1565 END IF;
1566
1567 EXCEPTION
1568 WHEN NO_DATA_FOUND THEN
1569 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1570 THEN
1571 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1572 , G_MODULE_NAME || l_procedure_name || '.begin'
1573 ,'No data found for Internal Order I/C AP'
1574 );
1575 END IF;
1576 WHEN OTHERS THEN
1577 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1578 THEN
1579 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1580 , G_MODULE_NAME || l_procedure_name || '.begin'
1581 ,'Others exception for Internal Order I/C AP'
1582 );
1583 END IF;
1584 END;
1585 END IF ;
1586
1587 /* Invoice conversion for I/C AP - 6889669 - End*/
1588
1589 OPEN inv_uom;
1590 FETCH inv_uom INTO l_inv_uom;
1591 IF inv_uom%NOTFOUND
1592 THEN
1593 l_inv_uom := l_movement_transaction.transaction_uom_code;
1594 END IF;
1595 CLOSE inv_uom;
1596 ELSE
1597 l_inv_uom := l_movement_transaction.transaction_uom_code;
1598 END IF;
1599
1600 IF l_movement_transaction.transaction_uom_code <> l_inv_uom
1601 THEN
1602 INV_CONVERT.Inv_Um_Conversion
1603 ( from_unit => l_movement_transaction.transaction_uom_code
1604 , to_unit => l_inv_uom
1605 , item_id => l_movement_transaction.inventory_item_id
1606 , uom_rate => l_trans_conv_inv_rate
1607 );
1608 ELSE
1609 l_trans_conv_inv_rate := 1;
1610 END IF;
1611 /* Special case for Internal Orders - 6889669 - BEGIN */
1612
1613 l_inv_qty := ROUND(l_movement_transaction.invoice_quantity / l_trans_conv_inv_rate,2);
1614
1615 --transaction quantity maybe negative for Italian RTV and RMA
1616 --when this procedurre is called in update_invoice. bug 2861110
1617 IF abs(l_inv_qty) = abs(l_movement_transaction.transaction_quantity)
1618 THEN
1619 l_tr_value := l_movement_transaction.invoice_line_ext_value;
1620 ELSE
1621 IF abs(l_inv_qty) < abs(l_movement_transaction.transaction_quantity)
1622 THEN
1623 l_report_price := NVL(l_movement_transaction.document_unit_price,0)
1624 + NVL(l_movement_transaction.outside_unit_price,0);
1625
1626 l_tr_value := (abs(l_movement_transaction.transaction_quantity)
1627 - abs(NVL(l_inv_qty,0))) * l_report_price
1628 +abs(l_movement_transaction.invoice_line_ext_value);
1629 ELSE
1630 --Bug 3665762, in the case of update invoice, the movement amount should not
1631 --be updated to 0 for dropshipment, set the movement amount to invoice value
1632 IF (l_movement_transaction.transaction_nature = '17'
1633 AND l_movement_transaction.transaction_quantity = 0)
1634 THEN
1635 l_tr_value := l_movement_transaction.invoice_line_ext_value;
1636 ELSE
1637 l_tr_value := abs(l_movement_transaction.transaction_quantity)
1638 * l_movement_transaction.invoice_unit_price * l_trans_conv_inv_rate;
1639 END IF;
1640 END IF;
1641
1642 /*
1643 IF (l_movement_transaction.document_source_type='RTV') THEN
1644 IF (abs(l_movement_transaction.invoice_quantity)) =
1645 (abs(l_movement_transaction.transaction_quantity)) THEN
1646 l_tr_value := l_movement_transaction.invoice_line_ext_value;
1647 END IF;
1648 END IF;
1649 */
1650
1651 END IF;
1652 --------------------------------------------------
1653 -- INVOICE_ID is null --
1654 --------------------------------------------------
1655 ELSE
1656 IF abs(NVL(l_movement_transaction.invoice_quantity,0)) =
1657 abs(l_movement_transaction.transaction_quantity)
1658 THEN
1659 l_tr_value := l_movement_transaction.document_line_ext_value;
1660 ELSE
1661 -- if DOCUMENT_SOURCE_TYPE is a Sales Order
1662 -- or DOCUMENT_SOURCE_TYPE is a Purchase Order
1663 -- or DOCUMENT_SOURCE_TYPE is a Return of Merchandize
1664 -- Authorization
1665 -- or DOCUMENT_SOURCE_TYPE is miscellaneous
1666 -- with a price not equal to 0
1667
1668 IF l_movement_transaction.document_source_type IN ('SO', 'IO') -- 6889669
1669 OR l_movement_transaction.document_source_type = 'PO'
1670 OR l_movement_transaction.document_source_type = 'RTV'
1671 OR l_movement_transaction.document_source_type = 'RMA'
1672 OR (l_movement_transaction.document_source_type = 'MISC'
1673 AND NVL(l_movement_transaction.document_unit_price,0) <> 0)
1674 THEN
1675 l_report_price := NVL(l_movement_transaction.document_unit_price,0)
1676 + NVL(l_movement_transaction.outside_unit_price,0);
1677 ELSE
1678 l_report_price := NVL(l_movement_transaction.item_cost,0)
1679 + NVL(l_movement_transaction.outside_unit_price,0);
1680 END IF;
1681
1682 l_tr_value := (abs(l_movement_transaction.transaction_quantity)
1683 - abs(NVL(l_movement_transaction.invoice_quantity,0)))
1684 * l_report_price;
1685 END IF;
1686 END IF;
1687
1688 /* Special case for 6889669 */
1689 IF (l_movement_transaction.document_source_type = 'IO') AND (l_movement_transaction.movement_type = 'A')
1690 AND (nvl(l_currency_conversion_rate, 1)<>1) THEN
1691 l_tr_value := round (l_tr_value * l_currency_conversion_rate, 2);
1692 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1693 THEN
1694 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1695 , G_MODULE_NAME || l_procedure_name || '.begin'
1696 ,'l_tr_value for IO arrival is '||l_tr_value
1697 );
1698 END IF;
1699 ELSE
1700 l_tr_value := round (l_tr_value * NVL(l_movement_transaction.currency_conversion_rate,1),2);
1701 END IF ;
1702
1703
1704 --RTV and RMA are special (AA and DA) in Italian,the movement amount in
1705 --Italian should be negative.If this procedure is called in update_invoice,the
1706 --movement type is already set to AA and DA, so we can use this criteria
1707 --to make movement amount negative,therwise always make it positive,for Italian
1708 --RTV and RMA,it will be set to negative later.bug2861110
1709 IF ((l_movement_transaction.document_source_type = 'RTV'
1710 AND NVL(l_movement_transaction.movement_type,'D') = 'AA')
1711 OR (l_movement_transaction.document_source_type = 'RMA'
1712 AND NVL(l_movement_transaction.movement_type,'A') = 'DA'))
1713 THEN
1714 l_tr_value := 0 - abs(l_tr_value);
1715 ELSE
1716 l_tr_value := abs(l_tr_value);
1717 END IF;
1718
1719 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1720 THEN
1721 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1722 , G_MODULE_NAME || l_procedure_name || '.end'
1723 ,'exit procedure'
1724 );
1725 END IF;
1726
1727 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1728 THEN
1729 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1730 , G_MODULE_NAME || l_procedure_name || '.begin'
1731 ,'in Calc Movement Amountis '||l_tr_value
1732 );
1733 END IF;
1734
1735
1736 RETURN(l_tr_value);
1737
1738 EXCEPTION
1739 WHEN NO_DATA_FOUND THEN
1740 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1741 THEN
1742 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1743 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
1744 , 'Exception'
1745 );
1746 END IF;
1747 RETURN(0);
1748 WHEN OTHERS THEN
1749 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1750 THEN
1751 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1752 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1753 , 'Exception'
1754 );
1755 END IF;
1756 RETURN(0);
1757
1758 END Calc_Movement_Amount;
1759
1760 --========================================================================
1761 -- FUNCTION : Calc_Statistics_Value PUBLIC
1762 -- PARAMETERS:
1763 -- p_movement_transaction movement transaction data record
1764 -- VERSION : current version 1.0
1765 -- initial version 1.0
1766 -- COMMENT : Calculates and returns the Statistics value
1767 --=======================================================================
1768
1769 FUNCTION Calc_Statistics_Value
1770 ( p_movement_transaction IN
1771 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1772 )
1773 RETURN NUMBER
1774 IS
1775 l_stat_value NUMBER;
1776 l_inv_total_freight_amt NUMBER;
1777 l_order_freight_charge NUMBER;
1778 l_order_freight_charge_to_line NUMBER;
1779 l_line_freight_charge NUMBER;
1780 l_freight_charge NUMBER;
1781 --l_invoiced_line_qty NUMBER := p_movement_transaction.invoice_quantity;
1782 l_movement_amount NUMBER;
1783
1784 --Stat adjusted amount needs to be kept if this procedure is called
1785 --after updating invoice and the stat adj amt is not null (entered
1786 --through Movement Statistics form)
1787 l_stat_adj_amount NUMBER;
1788
1789 l_total_line_charge NUMBER;
1790 l_total_invoiced_qty NUMBER;
1791 l_stat_ext_value NUMBER;
1792 l_conversion_rate NUMBER;
1793 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Statistics_Value';
1794
1795 --Total freight amount on the invoices for this order line
1796 CURSOR c_inv_freight_amt
1797 IS
1798 SELECT
1799 SUM(extended_amount)
1800 FROM
1801 ra_customer_trx_lines_all
1802 WHERE (sales_order = to_char(p_movement_transaction.order_number)
1803 OR sales_order IS NULL) --for manual invoice
1804 AND line_type = 'FREIGHT'
1805 AND customer_trx_id IN
1806 (SELECT customer_trx_id
1807 FROM ra_customer_trx_lines_all
1808 WHERE interface_line_attribute6 = to_char(p_movement_transaction.order_line_id)
1809 AND sales_order = to_char(p_movement_transaction.order_number));
1810
1811 BEGIN
1812 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1813 THEN
1814 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1815 , G_MODULE_NAME || l_procedure_name || '.begin'
1816 ,'enter procedure'
1817 );
1818 END IF;
1819
1820 --Initialize variables
1821 l_movement_amount := p_movement_transaction.movement_amount;
1822 l_stat_adj_amount := NVL(p_movement_transaction.stat_adj_amount,0);
1823 l_conversion_rate := p_movement_transaction.currency_conversion_rate;
1824
1825 --Statistics value should be same as movement amount in most cases, however
1826 --there are cases where the two values are different. For example when
1827 --freight charge is included in the same SO invoice, we need to include that
1828 --freight charge into statistics value to do Intrastat report
1829
1830 --The following calcualtion trys to cover manual invoice case too. But it's
1831 --very compilicated in manual case. There could be freight charge on manual
1832 --invoice different from that in price adjustment table. There could be
1833 --invoiced quantity different from ordered quantity. There could be multiple
1834 --shipments with one invoice or one shipment transaction with multiple invoices
1835 --and each of the invoice has freight charge. There could be credit memo with
1836 --freight charge on it......
1837
1838 --Only calculate freight charge for SO where invoice and freight charge is
1839 --existed
1840
1841 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1842 THEN
1843 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1844 , G_MODULE_NAME || l_procedure_name || '.movement amt in stat value is '
1845 ,p_movement_transaction.movement_amount
1846 );
1847 END IF;
1848
1849
1850 IF (p_movement_transaction.invoice_id IS NOT NULL
1851 AND p_movement_transaction.document_source_type = 'SO')
1852 THEN
1853 --Find out total freight charge on all related invoices for this order line
1854 OPEN c_inv_freight_amt;
1855 FETCH c_inv_freight_amt INTO
1856 l_inv_total_freight_amt;
1857 CLOSE c_inv_freight_amt;
1858
1859 --Check if freight amount on invoice is null, cursor with sum function
1860 --is always return row, so can not use cursor%notfound to check
1861 IF l_inv_total_freight_amt IS NULL
1862 THEN
1863 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1864 THEN
1865 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1866 , G_MODULE_NAME || l_procedure_name
1867 || '.end no freight amt'
1868 ,'exit procedure'
1869 );
1870 END IF;
1871 RETURN (l_movement_amount + l_stat_adj_amount);
1872 END IF;
1873
1874 --Calculate total line freight charge from invoiced quantity for
1875 --all lines
1876 Calc_Total_Line_Charge
1877 ( p_movement_transaction => p_movement_transaction
1878 , x_this_line_charge => l_line_freight_charge
1879 , x_total_line_charge => l_total_line_charge
1880 , x_total_invoiced_qty => l_total_invoiced_qty
1881 );
1882
1883 --Freight charge 1: Calculate order level freight charge
1884 --To cover manual invoice scenario, the total order freight charge will
1885 --be the result of total freight charge on invoices substract the total
1886 --line charge for all the lines. The charge is in functional currency
1887 l_order_freight_charge := l_inv_total_freight_amt * l_conversion_rate - l_total_line_charge;
1888
1889 --Distribute this order level charge to each line
1890 IF (l_order_freight_charge <> 0
1891 AND l_total_invoiced_qty <> 0)
1892 THEN
1893 l_order_freight_charge_to_line :=
1894 ROUND(((l_order_freight_charge/l_total_invoiced_qty)
1895 * p_movement_transaction.invoice_quantity),5);
1896 ELSE
1897 l_order_freight_charge_to_line := 0;
1898 END IF;
1899
1900 --Freight charge2: line level freight charge
1901 --Can use oe_price_adjustments.adjusted_amount_per_pqty to calculate total
1902 --line charge for this line.
1903 --Comment out following code, because the line charge is calculated and
1904 --returned from the call of Calc_Total_Line_Charge
1905 /*Calc_Total_Line_Charge
1906 ( p_movement_transaction => p_movement_transaction
1907 , x_this_line_charge => l_line_freight_charge
1908 , x_total_line_charge => l_total_line_charge
1909 , x_total_invoiced_qty => l_total_invoiced_qty
1910 );*/
1911
1912 --Total freight charge for each line including order level and line level
1913 l_freight_charge := ROUND((l_order_freight_charge_to_line + l_line_freight_charge),2);
1914
1915 --Include freight charge into statistical value
1916 l_stat_ext_value := l_movement_amount + l_freight_charge + l_stat_adj_amount;
1917 ELSE
1918 l_stat_ext_value := l_movement_amount + l_stat_adj_amount;
1919 END IF;
1920
1921 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1922 THEN
1923 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1924 , G_MODULE_NAME || l_procedure_name || '.end'
1925 ,'exit procedure'
1926 );
1927 END IF;
1928
1929 RETURN(l_stat_ext_value);
1930
1931 EXCEPTION
1932 WHEN NO_DATA_FOUND THEN
1933 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1934 THEN
1935 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1936 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
1937 , 'Exception'
1938 );
1939 END IF;
1940 RETURN(l_movement_amount + l_stat_adj_amount);
1941 WHEN OTHERS THEN
1942 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1943 THEN
1944 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1945 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1946 , 'Exception'
1947 );
1948 END IF;
1949 RETURN(l_movement_amount + l_stat_adj_amount);
1950 END Calc_Statistics_Value;
1951
1952 -- ========================================================================
1953 -- PROCEDURE : Calc_Invoice_Info PUBLIC
1954 -- PARAMETERS: x_movement_transaction IN OUT Movement Statistics Record
1955 -- p_stat_typ_transaction IN Stat type Usages record
1956 -- COMMENT : Procedure to calcualte the invoice information
1957 -- Calculation of Invoice Information
1958 -- The verification program calls this Procedure to populate the
1959 -- invoice information to the Movement Statistics table.
1960 -- ========================================================================
1961 PROCEDURE Calc_Invoice_Info
1962 ( p_stat_typ_transaction IN
1963 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
1964 , x_movement_transaction IN OUT NOCOPY
1965 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1966 )
1967 IS
1968 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
1969 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1970 l_unit_price NUMBER;
1971 l_invoice_quantity NUMBER;
1972 l_rtv_invoice_quantity NUMBER :=0;
1973 l_tran_curr_code VARCHAR2(15);
1974 l_tran_curr_type VARCHAR2(30);
1975 l_tran_curr_rate NUMBER;
1976 l_tran_curr_date DATE;
1977 l_extended_amount NUMBER;
1978 l_cm_extended_amount NUMBER :=0;
1979 l_rtv_extended_amount NUMBER :=0;
1980 l_uom_code VARCHAR2(3);
1981 l_parent_transaction_id NUMBER;
1982 l_so_le_id NUMBER;
1983 l_shipping_le_id NUMBER;
1984 l_item_type_code OE_ORDER_LINES_ALL.Item_Type_Code%TYPE;
1985 l_model_line_id OE_ORDER_LINES_ALL.Line_Id%TYPE;
1986 l_cto_line_id OE_ORDER_LINES_ALL.Line_Id%TYPE;
1987
1988 --fix bug2861110
1989 l_processed_ret_amt NUMBER;
1990 l_processed_ret_qty NUMBER;
1991 l_total_rtv_trans_qty NUMBER;
1992
1993 --fix bug 2695323
1994 l_total_rma_qty NUMBER;
1995
1996 -- bug 5440432, new variables intorduced.
1997 l_ar_invoiced_amount NUMBER;
1998 l_ar_invoiced_qty NUMBER;
1999
2000 --R12 PO price and qty correction
2001 l_prc_amount NUMBER; --price correction amt
2002 l_qtc_amount NUMBER; --qty correction amt
2003 l_qtc_qty NUMBER; --qty corrected in qty correction
2004
2005 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Invoice_Info';
2006
2007 -- Cursor to get all the invoice information after we get the invoice_id
2008
2009 CURSOR l_arc IS
2010 SELECT
2011 rat.trx_date
2012 , rat.batch_id
2013 , NVL(rat.exchange_rate,1)
2014 , rat.exchange_rate_type
2015 , rat.exchange_date
2016 , NVL(rat.invoice_currency_code,l_movement_transaction.currency_code)
2017 FROM
2018 RA_CUSTOMER_TRX_ALL rat
2019 WHERE rat.customer_trx_id = x_movement_transaction.invoice_id;
2020
2021 -- Cursor for SO invoices which are non credit/debit memos.
2022
2023 CURSOR l_sum_arc IS
2024 SELECT
2025 MAX(ratl.customer_trx_line_id)
2026 , MAX(rat.customer_trx_id)
2027 , SUM(ratl.extended_amount)
2028 , SUM(NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity))
2029 FROM
2030 RA_CUSTOMER_TRX_ALL rat
2031 , RA_CUSTOMER_TRX_LINES_ALL ratl
2032 , RA_CUST_TRX_TYPES_ALL ratt
2033 WHERE rat.customer_trx_id = ratl.customer_trx_id
2034 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2035 AND rat.org_id = ratt.org_id
2036 AND ratt.type NOT IN ('CM','DM')
2037 AND NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
2038 NOT IN (SELECT TO_CHAR(price_adjustment_id)
2039 FROM oe_price_adjustments
2040 WHERE header_id = l_movement_transaction.order_header_id
2041 AND (line_id = l_movement_transaction.order_line_id
2042 OR (line_id IS NULL AND modifier_level_code = 'ORDER')))
2043 AND ratl.line_type = 'LINE'
2044 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
2045 AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2046 AND ratl.interface_line_attribute6 =
2047 to_char(l_movement_transaction.order_line_id)
2048 AND ratl.interface_line_context <> 'INTERCOMPANY';
2049
2050 -- Cursor for RMA transactions
2051
2052 CURSOR l_sum_rma_arc IS
2053 SELECT
2054 MAX(ratl.customer_trx_line_id)
2055 , MAX(rat.customer_trx_id)
2056 , SUM(ratl.extended_amount)
2057 , SUM(NVL(ratl.quantity_credited,l_movement_transaction.transaction_quantity))
2058 FROM
2059 RA_CUSTOMER_TRX_ALL rat
2060 , RA_CUSTOMER_TRX_LINES_ALL ratl
2061 , RA_CUST_TRX_TYPES_ALL ratt
2062 WHERE rat.customer_trx_id = ratl.customer_trx_id
2063 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2064 AND rat.org_id = ratt.org_id
2065 AND ratt.type IN ('CM','DM')
2066 AND ratl.line_type = 'LINE' --yawang
2067 AND ratl.quantity_credited IS NOT NULL
2068 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
2069 --AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2070 AND ratl.interface_line_attribute1 = to_char(l_movement_transaction.order_number)
2071 AND NVL(ratl.org_id,0) = NVL(l_movement_transaction.org_id,0)
2072 AND ratl.interface_line_attribute6 =
2073 to_char(l_movement_transaction.order_line_id)
2074 AND ratl.interface_line_context <> 'INTERCOMPANY';
2075
2076 -- Cursor for SO Credit/Debit memos.
2077
2078 CURSOR l_sum_cm_arc IS
2079 SELECT
2080 SUM(ratl.extended_amount)
2081 FROM
2082 RA_CUSTOMER_TRX_ALL rat
2083 , RA_CUSTOMER_TRX_LINES_ALL ratl
2084 , RA_CUST_TRX_TYPES_ALL ratt
2085 WHERE rat.customer_trx_id = ratl.customer_trx_id
2086 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2087 AND rat.org_id = ratt.org_id
2088 AND ((ratt.type IN ('CM','DM'))
2089 OR (NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
2090 IN (SELECT TO_CHAR(price_adjustment_id)
2091 FROM oe_price_adjustments
2092 WHERE header_id = l_movement_transaction.order_header_id
2093 AND (line_id = l_movement_transaction.order_line_id
2094 OR (line_id IS NULL AND modifier_level_code = 'ORDER')) )))
2095 AND ratl.line_type = 'LINE' --yawang
2096 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
2097 AND ratl.quantity_credited IS NULL
2098 AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2099 AND ratl.interface_line_attribute6 =
2100 to_char(l_movement_transaction.order_line_id)
2101 AND ratl.interface_line_context <> 'INTERCOMPANY';
2102
2103 -- Cursor for drop shipment transactions
2104
2105 CURSOR l_sum_drparc IS
2106 SELECT
2107 MAX(ratl.customer_trx_line_id)
2108 , MAX(rat.customer_trx_id)
2109 , SUM(ratl.extended_amount)
2110 , SUM(NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity))
2111 FROM
2112 RA_CUSTOMER_TRX_ALL rat
2113 , RA_CUSTOMER_TRX_LINES_ALL ratl
2114 , RA_CUST_TRX_TYPES_ALL ratt --add in for fixing bug 2447381
2115 WHERE rat.customer_trx_id = ratl.customer_trx_id
2116 --AND ratl.quantity_invoiced = ratl.quantity_ordered
2117 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2118 AND rat.org_id = ratt.org_id
2119 AND ratt.type NOT IN ('CM','DM')
2120 AND NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
2121 NOT IN (SELECT TO_CHAR(price_adjustment_id)
2122 FROM oe_price_adjustments
2123 WHERE header_id = l_movement_transaction.order_header_id
2124 AND (line_id = l_movement_transaction.order_line_id
2125 OR (line_id IS NULL AND modifier_level_code = 'ORDER')))
2126 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
2127 AND ratl.line_type = 'LINE' --yawang
2128 AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2129 AND ratl.interface_line_attribute6 =
2130 to_char(l_movement_transaction.order_line_id)
2131 AND rat.complete_flag = 'Y'
2132 AND ratl.interface_line_context <> 'INTERCOMPANY';
2133
2134 -- Cursor for drop shipment transactions with credit memo/debit memos
2135
2136 CURSOR l_sum_cm_drparc IS
2137 SELECT
2138 SUM(ratl.extended_amount)
2139 FROM
2140 RA_CUSTOMER_TRX_ALL rat
2141 , RA_CUSTOMER_TRX_LINES_ALL ratl
2142 , RA_CUST_TRX_TYPES_ALL ratt
2143 WHERE rat.customer_trx_id = ratl.customer_trx_id
2144 --AND ratl.quantity_invoiced = ratl.quantity_ordered
2145 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2146 AND rat.org_id = ratt.org_id
2147 AND ((ratt.type IN ('CM','DM'))
2148 OR (NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
2149 IN (SELECT TO_CHAR(price_adjustment_id)
2150 FROM oe_price_adjustments
2151 WHERE header_id = l_movement_transaction.order_header_id
2152 AND (line_id = l_movement_transaction.order_line_id
2153 OR (line_id IS NULL AND modifier_level_code = 'ORDER')) )))
2154 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
2155 AND ratl.line_type = 'LINE' --yawang
2156 AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2157 AND ratl.interface_line_attribute6 =
2158 to_char(l_movement_transaction.order_line_id)
2159 AND rat.complete_flag = 'Y'
2160 AND ratl.interface_line_context <> 'INTERCOMPANY';
2161
2162 --Cursor for ar intercompany invoice
2163 CURSOR l_ar_intercompany IS
2164 SELECT
2165 MAX(ratl.customer_trx_line_id)
2166 , MAX(rat.customer_trx_id)
2167 , SUM(ratl.extended_amount)
2168 , SUM(NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity))
2169 FROM
2170 RA_CUSTOMER_TRX_ALL rat
2171 , RA_CUSTOMER_TRX_LINES_ALL ratl
2172 WHERE rat.customer_trx_id = ratl.customer_trx_id
2173 AND ratl.line_type = 'LINE'
2174 AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2175 AND ratl.interface_line_attribute6 =
2176 to_char(l_movement_transaction.order_line_id)
2177 AND ratl.interface_line_context = 'INTERCOMPANY';
2178
2179 -- Bug 5440432: Following query is modified to replace where clause
2180 -- 'AND rcta.trx_number||'-'||rcta.org_id = aia.invoice_num' with
2181 -- 'AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
2182 -- OR rcta.trx_number = aia.invoice_num)'
2183 -- This change was introduced because of bug 4180686 in 11.5.10
2184 -- Apart from that a new cursor l_ap_intercompany_invoiced_qty is
2185 -- introduced to get quantity/Amount from AR Invoice tables if
2186 -- AP details are missing.
2187 --Cursor for ap intercompany invoice
2188 --CURSOR l_ap_intercompany IS
2189 --SELECT
2190 -- MAX(aida.invoice_id)
2191 --, MAX(distribution_line_number)
2192 --, SUM(NVL(aida.amount,rctla.extended_amount))
2193 --, SUM(NVL(aida.quantity_invoiced,rctla.quantity_invoiced))
2194 --FROM
2195 -- AP_INVOICES_ALL aia
2196 --, AP_INVOICE_DISTRIBUTIONS_ALL aida
2197 --, RA_CUSTOMER_TRX_LINES_ALL rctla
2198 --, ra_customer_trx_all rcta
2199 --WHERE aia.invoice_id = aida.invoice_id
2200 -- AND (NVL(aida.match_status_flag,'N') = 'A'
2201 -- OR (NVL(aida.match_status_flag,'N') = 'T'
2202 -- AND aia.wfapproval_status = 'NOT REQUIRED'))
2203 -- AND aia.cancelled_date IS NULL
2204 -- AND aida.line_type_lookup_code = 'ITEM' --yawang, limit for good cost only
2205 -- AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2206 -- AND aida.reference_1 = TO_CHAR(rctla.customer_trx_line_id)
2207 -- AND rctla.customer_trx_id = rcta.customer_trx_id
2208 -- AND rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
2209 -- AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2210 -- AND rctla.interface_line_attribute6 =
2211 -- to_char(l_movement_transaction.order_line_id);
2212 CURSOR l_ap_intercompany IS
2213 SELECT
2214 MAX(aia.invoice_id)
2215 , MAX(aila.line_number)
2216 , SUM(NVL(aila.amount, 0))
2217 , SUM(NVL(aila.quantity_invoiced,0))
2218 FROM
2219 AP_INVOICES_ALL aia
2220 , AP_INVOICE_LINES_ALL aila
2221 , RA_CUSTOMER_TRX_LINES_ALL rctla
2222 , ra_customer_trx_all rcta
2223 WHERE aia.invoice_id = aila.invoice_id
2224 AND aia.cancelled_date IS NULL
2225 AND aila.line_type_lookup_code = 'ITEM'
2226 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2227 AND aila.reference_1 = TO_CHAR(rctla.customer_trx_line_id)
2228 AND rctla.customer_trx_id = rcta.customer_trx_id
2229 AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
2230 OR rcta.trx_number = aia.invoice_num)
2231 AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2232 AND rctla.interface_line_attribute6 =
2233 to_char(l_movement_transaction.order_line_id)
2234 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2235 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2236 FROM ap_holds_all aha
2237 WHERE aha.invoice_id = aia.invoice_id
2238 AND aha.release_lookup_code is null)
2239 AND EXISTS (SELECT 'Invoice is approved'
2240 FROM ap_invoice_distributions_all aida
2241 WHERE aida.invoice_id = aia.invoice_id
2242 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2243
2244 CURSOR l_ap_intercompany_invoiced_qty IS
2245 SELECT
2246 SUM(NVL(rctla.extended_amount,0)) InvoiceAmount
2247 ,SUM(NVL(rctla.quantity_invoiced,0)) InvoicedQuantity
2248 FROM
2249 AP_INVOICES_ALL aia
2250 , RA_CUSTOMER_TRX_LINES_ALL rctla
2251 WHERE aia.invoice_id = x_movement_transaction.invoice_id
2252 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2253 AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2254 AND rctla.interface_line_attribute6 =
2255 to_char(l_movement_transaction.order_line_id);
2256 /* 7165989 - Intercompany AP invoice for RMA */
2257 CURSOR l_ap_intercompany_rma_inv_qty IS
2258 SELECT
2259 SUM(NVL(rctla.extended_amount,0)) InvoiceAmount
2260 , SUM(NVL(rctla.quantity_credited,l_movement_transaction.transaction_quantity)) InvoicedQuantity
2261 FROM
2262 AP_INVOICES_ALL aia
2263 , RA_CUSTOMER_TRX_LINES_ALL rctla
2264 WHERE aia.invoice_id = x_movement_transaction.invoice_id
2265 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2266 AND rctla.interface_line_attribute1 = to_char(l_movement_transaction.order_number)
2267 AND rctla.interface_line_attribute6 =
2268 to_char(l_movement_transaction.order_line_id);
2269
2270 /* 7165989 - End */
2271
2272 -- Cursor to get the invoice info based on the invoice_id
2273 CURSOR l_ap_intercompany_invo IS
2274 SELECT
2275 ap.invoice_currency_code
2276 , NVL(ap.exchange_rate,NVL(rctl.exchange_rate, 1))
2277 , NVL(ap.exchange_rate_type,rctl.exchange_rate_type)
2278 , NVL(ap.exchange_date,rctl.exchange_date)
2279 , ap.batch_id
2280 , ap.invoice_date
2281 FROM
2282 AP_INVOICES_ALL ap
2283 , RA_CUSTOMER_TRX_ALL rctl
2284 WHERE ap.invoice_id = x_movement_transaction.invoice_id
2285 AND ap.reference_1 = rctl.customer_trx_id;
2286
2287 -- Cursor to get all the invoice information for drop shipments after fetching
2288 -- the invoice_id.
2289
2290 CURSOR l_drparc IS
2291 SELECT
2292 rat.trx_date
2293 , rat.batch_id
2294 , NVL(rat.exchange_rate,1)
2295 , rat.exchange_rate_type
2296 , rat.exchange_date
2297 , NVL(rat.invoice_currency_code,l_movement_transaction.currency_code)
2298 FROM
2299 RA_CUSTOMER_TRX_ALL rat
2300 , RA_CUSTOMER_TRX_LINES_ALL ratl
2301 WHERE rat.customer_trx_id = ratl.customer_trx_id
2302 --AND ratl.quantity_invoiced = ratl.quantity_ordered
2303 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
2304 AND ratl.interface_line_attribute6 = to_char(l_movement_transaction.order_line_id)
2305 AND ratl.customer_trx_line_id = x_movement_transaction.customer_trx_line_id;
2306
2307 -- Cursor to get the invoice info based on the invoice_id for PO
2308
2309 CURSOR l_apc IS
2310 SELECT
2311 ap.invoice_currency_code
2312 , NVL(ap.exchange_rate,1)
2313 , ap.exchange_rate_type
2314 , ap.exchange_date
2315 , ap.batch_id
2316 , ap.invoice_date
2317 FROM
2318 AP_INVOICES_ALL ap
2319 WHERE ap.invoice_id = x_movement_transaction.invoice_id;
2320
2321 -- Cursor for PO based matching regular invoice
2322 -- modified for AP invoice lines uptake in R12
2323 CURSOR l_po_inv IS
2324 SELECT
2325 SUM(aila.amount)
2326 , MAX(aila.line_number)
2327 , MAX(aia.invoice_id)
2328 , SUM(aila.quantity_invoiced)
2329 FROM
2330 ap_invoices_all aia
2331 , ap_invoice_lines_all aila
2332 WHERE aia.invoice_id = aila.invoice_id
2333 AND aia.invoice_type_lookup_code in ('STANDARD','MIXED')
2334 AND aia.cancelled_date IS NULL
2335 AND aila.line_type_lookup_code = 'ITEM'
2336 AND aila.match_type = 'ITEM_TO_PO'
2337 AND aila.po_line_location_id = l_movement_transaction.po_line_location_id
2338 -- Bug 5655040. Commented as condition is modified and few more conditioned
2339 -- added to whereclause to check hold and disregard status.
2340 --AND NOT EXISTS (SELECT 1
2341 -- FROM ap_invoice_distributions_all aida
2342 -- WHERE aida.invoice_id = aia.invoice_id
2343 -- AND aida.invoice_line_number = aila.line_number
2344 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2345 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2346 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2347 FROM ap_holds_all aha
2348 WHERE aha.invoice_id = aia.invoice_id
2349 AND aha.release_lookup_code is null)
2350 AND EXISTS (SELECT 'Invoice is approved'
2351 FROM ap_invoice_distributions_all aida
2352 WHERE aida.invoice_id = aia.invoice_id
2353 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2354
2355 -- Cursor for price correction in case of PO based matching.
2356 -- Modified for AP invoice lines uptake in R12
2357 -- Although price correction in AP directly reflects price correction for certain
2358 -- quantity, but for us we display only one invoice for each transaction, we still
2359 -- need to average the price for each qty after price adjustment. So here we only
2360 -- take the amount.
2361 --CURSOR l_po_cm_inv IS
2362 CURSOR l_po_prc_inv IS
2363 SELECT
2364 SUM(aila.amount)
2365 FROM
2366 ap_invoices_all aia
2367 , ap_invoice_lines_all aila
2368 WHERE aia.invoice_id = aila.invoice_id
2369 AND aia.cancelled_date IS NULL
2370 AND aila.line_type_lookup_code = 'ITEM'
2371 AND aila.match_type = 'PRICE_CORRECTION'
2372 AND aila.po_line_location_id = l_movement_transaction.po_line_location_id
2373 -- Bug 5655040. Commented as condition is modified and few more conditioned
2374 -- added to whereclause to check hold and disregard status.
2375 --AND NOT EXISTS (SELECT 1
2376 -- FROM ap_invoice_distributions_all aida
2377 -- WHERE aida.invoice_id = aia.invoice_id
2378 -- AND aida.invoice_line_number = aila.line_number
2379 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2380 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2381 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2382 FROM ap_holds_all aha
2383 WHERE aha.invoice_id = aia.invoice_id
2384 AND aha.release_lookup_code is null)
2385 AND EXISTS (SELECT 'Invoice is approved'
2386 FROM ap_invoice_distributions_all aida
2387 WHERE aida.invoice_id = aia.invoice_id
2388 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2389 -- Cursor for quantity correction in case of PO based matching.
2390 -- Modified for AP invoice lines uptake in R12
2391 CURSOR l_po_qtc_inv IS
2392 SELECT
2393 SUM(aila.quantity_invoiced)
2394 , SUM(aila.amount)
2395 FROM
2396 ap_invoices_all aia
2397 , ap_invoice_lines_all aila
2398 WHERE aia.invoice_id = aila.invoice_id
2399 AND aia.cancelled_date IS NULL
2400 AND aila.line_type_lookup_code = 'ITEM'
2401 AND aila.match_type = 'QTY_CORRECTION'
2402 AND aila.po_line_location_id = l_movement_transaction.po_line_location_id
2403 -- Bug 5655040. Commented as condition is modified and few more conditioned
2404 -- added to whereclause to check hold and disregard status.
2405 --AND NOT EXISTS (SELECT 1
2406 -- FROM ap_invoice_distributions_all aida
2407 -- WHERE aida.invoice_id = aia.invoice_id
2408 -- AND aida.invoice_line_number = aila.line_number
2409 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2410 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2411 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2412 FROM ap_holds_all aha
2413 WHERE aha.invoice_id = aia.invoice_id
2414 AND aha.release_lookup_code is null)
2415 AND EXISTS (SELECT 'Invoice is approved'
2416 FROM ap_invoice_distributions_all aida
2417 WHERE aida.invoice_id = aia.invoice_id
2418 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2419
2420 -- Cursor for RTV's where credit memo is to be associated with
2421 -- RTV in case of PO based matching.
2422 -- modified for AP invoice lines uptake in R12
2423 CURSOR l_po_rtv_cm_inv IS
2424 SELECT
2425 SUM(aila.amount)
2426 , MAX(aila.line_number)
2427 , MAX(aia.invoice_id)
2428 , SUM(aila.quantity_invoiced)
2429 FROM
2430 ap_invoices_all aia
2431 , ap_invoice_lines_all aila
2432 WHERE aia.invoice_id = aila.invoice_id
2433 AND aia.invoice_type_lookup_code in ('CREDIT','DEBIT')
2434 AND aia.cancelled_date IS NULL
2435 AND aila.line_type_lookup_code = 'ITEM'
2436 AND aila.match_type = 'ITEM_TO_PO'
2437 AND NVL(aila.quantity_invoiced,0) < 0
2438 AND aila.po_line_location_id = l_movement_transaction.po_line_location_id
2439 -- Bug 5655040. Commented as condition is modified and few more conditioned
2440 -- added to whereclause to check hold and disregard status.
2441 --AND NOT EXISTS (SELECT 1
2442 -- FROM ap_invoice_distributions_all aida
2443 -- WHERE aida.invoice_id = aia.invoice_id
2444 -- AND aida.invoice_line_number = aila.line_number
2445 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2446 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2447 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2448 FROM ap_holds_all aha
2449 WHERE aha.invoice_id = aia.invoice_id
2450 AND aha.release_lookup_code is null)
2451 AND EXISTS (SELECT 'Invoice is approved'
2452 FROM ap_invoice_distributions_all aida
2453 WHERE aida.invoice_id = aia.invoice_id
2454 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2455
2456 -- Cursor for Receipt based matching regular invoice
2457 -- Modified for AP invoice lines uptake in R12
2458 CURSOR l_ap_inv IS
2459 SELECT
2460 sum(aila.amount)
2461 , MAX(aila.line_number)
2462 , MAX(aia.invoice_id)
2463 , sum(aila.quantity_invoiced)
2464 FROM
2465 ap_invoices_all aia
2466 , ap_invoice_lines_all aila
2467 WHERE aia.invoice_id = aila.invoice_id
2468 AND aia.invoice_type_lookup_code in ('STANDARD','MIXED')
2469 AND aia.cancelled_date IS NULL
2470 AND aila.line_type_lookup_code = 'ITEM'
2471 AND aila.match_type = 'ITEM_TO_RECEIPT'
2472 AND aila.rcv_transaction_id = l_movement_transaction.rcv_transaction_id
2473 -- Bug 5655040. Commented as condition is modified and few more conditioned
2474 -- added to whereclause to check hold and disregard status.
2475 --AND NOT EXISTS (SELECT 1
2476 -- FROM ap_invoice_distributions_all aida
2477 -- WHERE aida.invoice_id = aia.invoice_id
2478 -- AND aida.invoice_line_number = aila.line_number
2479 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2480 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2481 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2482 FROM ap_holds_all aha
2483 WHERE aha.invoice_id = aia.invoice_id
2484 AND aha.release_lookup_code is null)
2485 AND EXISTS (SELECT 'Invoice is approved'
2486 FROM ap_invoice_distributions_all aida
2487 WHERE aida.invoice_id = aia.invoice_id
2488 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2489
2490 -- Cursor for price correction in case of Receipt based matching.
2491 -- Modified for AP invoice lines uptake in R12
2492 -- Although price correction in AP directly reflects price correction for certain
2493 -- quantity, but for us we display only one invoice for each transaction, we still
2494 -- need to average the price for each qty after price adjustment. So here we only
2495 -- take the amount.
2496 --CURSOR l_ap_cm_inv IS
2497 CURSOR l_ap_prc_inv IS
2498 SELECT
2499 SUM(aila.amount)
2500 FROM
2501 ap_invoices_all aia
2502 , ap_invoice_lines_all aila
2503 WHERE aia.invoice_id = aila.invoice_id
2504 AND aia.cancelled_date IS NULL
2505 AND aila.line_type_lookup_code = 'ITEM'
2506 AND aila.match_type = 'PRICE_CORRECTION'
2507 AND aila.rcv_transaction_id = l_movement_transaction.rcv_transaction_id
2508 -- Bug 5655040. Commented as condition is modified and few more conditioned
2509 -- added to whereclause to check hold and disregard status.
2510 --AND NOT EXISTS (SELECT 1
2511 -- FROM ap_invoice_distributions_all aida
2512 -- WHERE aida.invoice_id = aia.invoice_id
2513 -- AND aida.invoice_line_number = aila.line_number
2514 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2515 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2516 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2517 FROM ap_holds_all aha
2518 WHERE aha.invoice_id = aia.invoice_id
2519 AND aha.release_lookup_code is null)
2520 AND EXISTS (SELECT 'Invoice is approved'
2521 FROM ap_invoice_distributions_all aida
2522 WHERE aida.invoice_id = aia.invoice_id
2523 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2524
2525 -- Cursor for quantity correction in case of Receipt based matching.
2526 -- Modified for AP invoice lines uptake in R12
2527 CURSOR l_ap_qtc_inv IS
2528 SELECT
2529 SUM(aila.quantity_invoiced)
2530 , SUM(aila.amount)
2531 FROM
2532 ap_invoices_all aia
2533 , ap_invoice_lines_all aila
2534 WHERE aia.invoice_id = aila.invoice_id
2535 AND aia.cancelled_date IS NULL
2536 AND aila.line_type_lookup_code = 'ITEM'
2537 AND aila.match_type = 'QTY_CORRECTION'
2538 AND aila.rcv_transaction_id = l_movement_transaction.rcv_transaction_id
2539 -- Bug 5655040. Commented as condition is modified and few more conditioned
2540 -- added to whereclause to check hold and disregard status.
2541 --AND NOT EXISTS (SELECT 1
2542 -- FROM ap_invoice_distributions_all aida
2543 -- WHERE aida.invoice_id = aia.invoice_id
2544 -- AND aida.invoice_line_number = aila.line_number
2545 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2546 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2547 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2548 FROM ap_holds_all aha
2549 WHERE aha.invoice_id = aia.invoice_id
2550 AND aha.release_lookup_code is null)
2551 AND EXISTS (SELECT 'Invoice is approved'
2552 FROM ap_invoice_distributions_all aida
2553 WHERE aida.invoice_id = aia.invoice_id
2554 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2555
2556 -- Cursor for Credit memos that is associated with RTV transaction
2557 -- in case of receipt based matching.
2558 -- modified in R12 to use line table
2559 CURSOR l_ap_rtv_cm_inv IS
2560 SELECT
2561 sum(aila.amount)
2562 , MAX(aila.line_number)
2563 , MAX(aia.invoice_id)
2564 , sum(aila.quantity_invoiced)
2565 FROM
2566 ap_invoices_all aia
2567 , ap_invoice_lines_all aila
2568 WHERE aia.invoice_id = aila.invoice_id
2569 AND aia.invoice_type_lookup_code in ('CREDIT','DEBIT')
2570 AND aia.cancelled_date IS NULL
2571 AND aila.line_type_lookup_code = 'ITEM'
2572 AND aila.match_type = 'ITEM_TO_RECEIPT'
2573 AND NVL(aila.quantity_invoiced,0) < 0
2574 AND aila.rcv_transaction_id = l_parent_transaction_id
2575 -- Bug 5655040. Commented as condition is modified and few more conditioned
2576 -- added to whereclause to check hold and disregard status.
2577 --AND NOT EXISTS (SELECT 1
2578 -- FROM ap_invoice_distributions_all aida
2579 -- WHERE aida.invoice_id = aia.invoice_id
2580 -- AND aida.invoice_line_number = aila.line_number
2581 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2582 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2583 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2584 FROM ap_holds_all aha
2585 WHERE aha.invoice_id = aia.invoice_id
2586 AND aha.release_lookup_code is null)
2587 AND EXISTS (SELECT 'Invoice is approved'
2588 FROM ap_invoice_distributions_all aida
2589 WHERE aida.invoice_id = aia.invoice_id
2590 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2591
2592 --Cursor to check if this SO is for CTO items
2593 CURSOR l_cto IS
2594 SELECT
2595 item_type_code
2596 , top_model_line_id
2597 FROM
2598 oe_order_lines_all
2599 WHERE line_id = l_movement_transaction.order_line_id;
2600
2601 --Cursor to get total rtv transaction quantity
2602 CURSOR l_total_rtv_quantity IS
2603 SELECT
2604 SUM(quantity)
2605 FROM
2606 rcv_transactions
2607 WHERE po_header_id = l_movement_transaction.po_header_id
2608 AND transaction_type = 'RETURN TO VENDOR';
2609
2610 BEGIN
2611
2612 --Consigned support move condition from INVUINTB.pls to here
2613 -- IF (x_movement_transaction.document_source_type IN ('IO','INV')
2614 IF (x_movement_transaction.document_source_type IN ('INV')
2615 OR x_movement_transaction.consigned_flag = 'Y'
2616 OR x_movement_transaction.financial_document_flag = 'NOT_REQUIRED_CORRECT')
2617 THEN
2618 RETURN;
2619
2620 ELSE
2621 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2622 THEN
2623 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2624 , G_MODULE_NAME || l_procedure_name || '.begin'
2625 ,'enter procedure'
2626 );
2627 END IF;
2628
2629 l_movement_transaction := x_movement_transaction;
2630 l_stat_typ_transaction := p_stat_typ_transaction;
2631
2632 -- Now do all the calculations for currency.
2633 --x_movement_transaction.currency_conversion_type :=
2634 -- l_stat_typ_transaction.conversion_type;
2635
2636 l_tran_curr_code := x_movement_transaction.currency_code;
2637 l_tran_curr_rate := x_movement_transaction.currency_conversion_rate;
2638 l_tran_curr_date := x_movement_transaction.currency_conversion_date;
2639 l_tran_curr_type := x_movement_transaction.currency_conversion_type;
2640
2641 -- IF l_movement_transaction.document_source_type IN ( 'SO','RMA')
2642 IF l_movement_transaction.document_source_type IN ( 'IO','SO','RMA')
2643 THEN
2644
2645
2646
2647
2648 --Find out the legal entity where this SO is created
2649 l_so_le_id := INV_MGD_MVT_UTILS_PKG.Get_SO_Legal_Entity
2650 (p_order_line_id => l_movement_transaction.order_line_id);
2651
2652 --Find out the legal entity where this SO is shipped
2653 l_shipping_le_id := INV_MGD_MVT_UTILS_PKG.Get_Shipping_Legal_Entity
2654 (p_warehouse_id => l_movement_transaction.organization_id);
2655
2656 --Check if this order line is of CTO item
2657 OPEN l_cto;
2658 FETCH l_cto INTO
2659 l_item_type_code
2660 , l_model_line_id;
2661 CLOSE l_cto;
2662
2663 IF l_item_type_code = 'CONFIG'
2664 THEN
2665 l_cto_line_id := l_movement_transaction.order_line_id;
2666
2667 --Set order line to model line id to calculate correct invoice
2668 l_movement_transaction.order_line_id := l_model_line_id;
2669 END IF;
2670
2671 -- Check if the transaction is a drop shipment; if it is not then
2672 -- fetch the l_sum_arc cursor and l_sum_cm_arc which gets the
2673 -- credit/memo transactions.
2674 IF l_movement_transaction.po_header_id IS NULL
2675 OR l_movement_transaction.po_header_id = FND_API.G_MISS_NUM
2676 THEN
2677 --Not drop shipment
2678 IF l_movement_transaction.document_source_type = 'SO'
2679 THEN
2680 --If this SO is created and shipped in different legal entity
2681 --and we are in invoice based triangulation mode,we need
2682 -- intercompany invoice
2683 IF (l_so_le_id IS NOT NULL
2684 AND l_so_le_id <> l_shipping_le_id
2685 AND NVL(l_stat_typ_transaction.triangulation_mode,'INVOICE_BASED')
2686 = 'INVOICE_BASED')
2687 THEN
2688 --If this processor is run at the legal entity where this SO
2689 --is created and the record created is Arrival intercompany SO
2690 --we need an intercompany ap invoice
2691 IF (l_so_le_id = l_movement_transaction.entity_org_id
2692 AND l_movement_transaction.movement_type = 'A')
2693 THEN
2694 --intercompany ap invoice
2695 OPEN l_ap_intercompany;
2696 FETCH l_ap_intercompany INTO
2697 x_movement_transaction.invoice_id
2698 , x_movement_transaction.distribution_line_number
2699 , l_extended_amount
2700 , x_movement_transaction.invoice_quantity;
2701 CLOSE l_ap_intercompany;
2702
2703 IF x_movement_transaction.invoice_id IS NOT NULL
2704 THEN
2705 --Recalculate invoice amount for ap intercompany CTO item
2706 IF l_item_type_code = 'CONFIG'
2707 THEN
2708 Calc_Cto_Amount_ap
2709 ( p_order_line_id => l_movement_transaction.order_line_id
2710 , p_order_number => to_char(l_movement_transaction.order_number)
2711 , x_extended_amount => l_extended_amount
2712 );
2713 END IF;
2714
2715 -- bug 5440432, Following IF block is added to get details from AR invoice
2716 -- if Amount or Quantity is missing from AP Invoice.
2717 IF l_extended_amount = 0 OR x_movement_transaction.invoice_quantity = 0 THEN
2718 OPEN l_ap_intercompany_invoiced_qty;
2719 FETCH l_ap_intercompany_invoiced_qty INTO
2720 l_ar_invoiced_amount, l_ar_invoiced_qty;
2721 CLOSE l_ap_intercompany_invoiced_qty;
2722 IF l_extended_amount = 0 THEN
2723 l_extended_amount := l_ar_invoiced_amount;
2724 END IF;
2725 IF x_movement_transaction.invoice_quantity = 0 THEN
2726 x_movement_transaction.invoice_quantity := l_ar_invoiced_qty;
2727 END IF;
2728 END IF;
2729 --Open cursor to get other invoice information
2730 OPEN l_ap_intercompany_invo;
2731 FETCH l_ap_intercompany_invo INTO
2732 x_movement_transaction.currency_code
2733 , x_movement_transaction.currency_conversion_rate
2734 , x_movement_transaction.currency_conversion_type
2735 , x_movement_transaction.currency_conversion_date
2736 , x_movement_transaction.invoice_batch_id
2737 , x_movement_transaction.invoice_date_reference;
2738
2739 IF l_ap_intercompany_invo%NOTFOUND
2740 THEN
2741 x_movement_transaction.currency_code := l_tran_curr_code;
2742 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
2743 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
2744 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
2745 x_movement_transaction.invoice_batch_id := null;
2746 x_movement_transaction.invoice_date_reference := null;
2747 END IF;
2748 CLOSE l_ap_intercompany_invo;
2749 END IF; --yawang
2750
2751 --If this processor is run at the legal entity where this SO
2752 --is created but the record created is a virtual Dispatch to customer
2753 --we need an regular ar invoice to customer
2754 ELSIF (l_so_le_id = l_movement_transaction.entity_org_id
2755 AND l_movement_transaction.movement_type = 'D')
2756 THEN
2757 --Regular SO invoice
2758 OPEN l_sum_arc;
2759 FETCH l_sum_arc INTO
2760 x_movement_transaction.customer_trx_line_id
2761 , x_movement_transaction.invoice_id
2762 , l_extended_amount
2763 , x_movement_transaction.invoice_quantity;
2764 CLOSE l_sum_arc;
2765
2766 IF x_movement_transaction.invoice_id IS NOT NULL
2767 THEN
2768 OPEN l_sum_cm_arc;
2769 FETCH l_sum_cm_arc INTO
2770 l_cm_extended_amount;
2771 CLOSE l_sum_cm_arc;
2772
2773 --Recalculate invoice amount for CTO item
2774 IF l_item_type_code = 'CONFIG'
2775 THEN
2776 Calc_Cto_Amount_So
2777 ( p_order_line_id => l_movement_transaction.order_line_id
2778 , p_order_number => to_char(l_movement_transaction.order_number)
2779 , x_extended_amount => l_extended_amount
2780 , x_cm_extended_amount => l_cm_extended_amount
2781 );
2782 END IF;
2783 END IF;
2784
2785 --If this processor is run at the legal entity where this SO
2786 --is shipped, we need an ar intercompany invoice
2787 ELSIF (l_shipping_le_id = l_movement_transaction.entity_org_id)
2788 THEN
2789 OPEN l_ar_intercompany;
2790 FETCH l_ar_intercompany INTO
2791 x_movement_transaction.customer_trx_line_id
2792 , x_movement_transaction.invoice_id
2793 , l_extended_amount
2794 , x_movement_transaction.invoice_quantity;
2795 CLOSE l_ar_intercompany;
2796
2797 --Recalculate invoice amount for ar intercompany CTO item
2798 IF l_item_type_code = 'CONFIG'
2799 AND x_movement_transaction.customer_trx_line_id IS NOT NULL
2800 THEN
2801 Calc_Cto_Amount_ar
2802 ( p_order_line_id => l_movement_transaction.order_line_id
2803 , p_order_number => to_char(l_movement_transaction.order_number)
2804 , x_extended_amount => l_extended_amount
2805 );
2806 END IF;
2807 END IF;
2808 ELSE
2809 --Regular SO invoice
2810 OPEN l_sum_arc;
2811 FETCH l_sum_arc INTO
2812 x_movement_transaction.customer_trx_line_id
2813 , x_movement_transaction.invoice_id
2814 , l_extended_amount
2815 , x_movement_transaction.invoice_quantity;
2816 CLOSE l_sum_arc;
2817
2818 IF x_movement_transaction.invoice_id IS NOT NULL
2819 THEN
2820 OPEN l_sum_cm_arc;
2821 FETCH l_sum_cm_arc INTO
2822 l_cm_extended_amount;
2823 CLOSE l_sum_cm_arc;
2824
2825 --Recalculate invoice amount for CTO item
2826 IF l_item_type_code = 'CONFIG'
2827 THEN
2828 Calc_Cto_Amount_So
2829 ( p_order_line_id => l_movement_transaction.order_line_id
2830 , p_order_number => to_char(l_movement_transaction.order_number)
2831 , x_extended_amount => l_extended_amount
2832 , x_cm_extended_amount => l_cm_extended_amount
2833 );
2834 END IF;
2835
2836 --Fix Italian bug 2861110.Update_invoice_info will revert any netted invoice
2837 --amt and qty back to original SO invoice amt and qty. The following code
2838 --will net the invoice amt and qty again for processed RMA
2839 IF l_stat_typ_transaction.returns_processing = 'AGGRTN'
2840 THEN
2841 Calc_Processed_Ret_Data
2842 (p_movement_transaction => l_movement_transaction
2843 , x_processed_ret_amt => l_processed_ret_amt
2844 , x_processed_ret_qty => l_processed_ret_qty
2845 );
2846
2847 --Net processed rma amt and qty to SO
2848 l_extended_amount := l_extended_amount + NVL(l_processed_ret_amt,0);
2849 x_movement_transaction.invoice_quantity :=
2850 x_movement_transaction.invoice_quantity + NVL(l_processed_ret_qty,0);
2851 END IF;
2852 END IF; --yawang end of invoice not null
2853 END IF;
2854 END IF;
2855
2856
2857 /*********************************** Special case for an IO - BEGIN **************************************/
2858
2859 IF l_movement_transaction.document_source_type = 'IO'
2860 THEN
2861 --If this processor is run at the legal entity where this SO
2862 --is created and the record created is Arrival intercompany SO
2863 --we need an intercompany ap invoice
2864
2865 -- IF (l_so_le_id = l_movement_transaction.entity_org_id
2866 -- AND l_movement_transaction.movement_type = 'A')
2867 -- kdevadas
2868 --FOR an Internal ORDER, the so le Id would always be the ship TO LE -
2869 --will NOT be the same AS the entity_org_id WHERE MSP IS RUN
2870 IF (l_movement_transaction.movement_type = 'A')
2871 THEN
2872 --intercompany ap invoice
2873 OPEN l_ap_intercompany;
2874 FETCH l_ap_intercompany INTO
2875 x_movement_transaction.invoice_id
2876 , x_movement_transaction.distribution_line_number
2877 , l_extended_amount
2878 , x_movement_transaction.invoice_quantity;
2879
2880 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2881 THEN
2882 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2883 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANY INVOICE ID ********************* '
2884 , x_movement_transaction.invoice_id
2885 );
2886 END IF ;
2887
2888 CLOSE l_ap_intercompany;
2889
2890
2891 IF x_movement_transaction.invoice_id IS NOT NULL
2892 THEN
2893
2894 --Recalculate invoice amount for ap intercompany CTO item
2895 IF l_item_type_code = 'CONFIG'
2896 THEN
2897 Calc_Cto_Amount_ap
2898 ( p_order_line_id => l_movement_transaction.order_line_id
2899 , p_order_number => to_char(l_movement_transaction.order_number)
2900 , x_extended_amount => l_extended_amount
2901 );
2902 END IF;
2903 -- bug 5411006, Following IF block is added to get details from AR invoice
2904 -- if Amount or Quantity is missing from AP Invoice.
2905 IF l_extended_amount = 0 OR x_movement_transaction.invoice_quantity = 0 THEN
2906 OPEN l_ap_intercompany_invoiced_qty;
2907 FETCH l_ap_intercompany_invoiced_qty INTO
2908 l_ar_invoiced_amount, l_ar_invoiced_qty;
2909 CLOSE l_ap_intercompany_invoiced_qty;
2910 IF l_extended_amount = 0 THEN
2911 l_extended_amount := l_ar_invoiced_amount;
2912 END IF;
2913 IF x_movement_transaction.invoice_quantity = 0 THEN
2914 x_movement_transaction.invoice_quantity := l_ar_invoiced_qty;
2915 END IF;
2916 END IF;
2917 --Open cursor to get other invoice information
2918 OPEN l_ap_intercompany_invo;
2919 FETCH l_ap_intercompany_invo INTO
2920 x_movement_transaction.currency_code
2921 , x_movement_transaction.currency_conversion_rate
2922 , x_movement_transaction.currency_conversion_type
2923 , x_movement_transaction.currency_conversion_date
2924 , x_movement_transaction.invoice_batch_id
2925 , x_movement_transaction.invoice_date_reference;
2926
2927 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2928 THEN
2929 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2930 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANY CURRENCY CODE ********************* '
2931 , x_movement_transaction.currency_code
2932 );
2933 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2934 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANY currency_conversion_type ********************* '
2935 , x_movement_transaction.currency_conversion_rate
2936 );
2937 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2938 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANYcurrency_conversion_typeE ********************* '
2939 , x_movement_transaction.currency_conversion_type
2940 );
2941 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2942 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANY currency_conversion_date********************* '
2943 , x_movement_transaction.currency_conversion_date
2944 );
2945 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2946 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANY invoice_batch_id ********************* '
2947 , x_movement_transaction.invoice_batch_id
2948 );
2949
2950 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2951 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANY invoice_date_reference ********************* '
2952 , x_movement_transaction.invoice_date_reference
2953 );
2954 END IF ;
2955
2956 IF l_ap_intercompany_invo%NOTFOUND
2957 THEN
2958 x_movement_transaction.currency_code := l_tran_curr_code;
2959 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
2960 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
2961 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
2962 x_movement_transaction.invoice_batch_id := null;
2963 x_movement_transaction.invoice_date_reference := null;
2964 END IF;
2965 CLOSE l_ap_intercompany_invo;
2966 END IF; --yawang
2967
2968 --If this processor is run at the legal entity where this SO
2969 --is shipped, we need an ar intercompany invoice
2970 ELSIF (l_shipping_le_id = l_movement_transaction.entity_org_id)
2971 THEN
2972 OPEN l_ar_intercompany;
2973 FETCH l_ar_intercompany INTO
2974 x_movement_transaction.customer_trx_line_id
2975 , x_movement_transaction.invoice_id
2976 , l_extended_amount
2977 , x_movement_transaction.invoice_quantity;
2978 CLOSE l_ar_intercompany;
2979
2980 --Recalculate invoice amount for ar intercompany CTO item
2981 IF l_item_type_code = 'CONFIG'
2982 AND x_movement_transaction.customer_trx_line_id IS NOT NULL
2983 THEN
2984 Calc_Cto_Amount_ar
2985 ( p_order_line_id => l_movement_transaction.order_line_id
2986 , p_order_number => to_char(l_movement_transaction.order_number)
2987 , x_extended_amount => l_extended_amount
2988 );
2989 END IF;
2990 END IF; -- end if elsif
2991 END IF ; -- end of IO loop
2992
2993
2994 /*********************************** Special case for an IO - END**************************************/
2995
2996 IF l_movement_transaction.document_source_type = 'RMA'
2997 THEN
2998 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2999 THEN
3000 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3001 , G_MODULE_NAME || l_procedure_name || '.begin'
3002 ,'************ 2 RMA ******************'
3003 );
3004 END IF;
3005 /* bug# 7165989 Intercompany AP/AR Invoice for RMA*/
3006 --If this RMA is created and shipped in different legal entity
3007 --and we are in invoice based triangulation mode,we need
3008 -- intercompany invoice
3009 IF (l_so_le_id IS NOT NULL
3010 AND l_so_le_id <> l_shipping_le_id
3011 AND NVL(l_stat_typ_transaction.triangulation_mode,'INVOICE_BASED')
3012 ='INVOICE_BASED')
3013 THEN
3014 --If this processor is run at the legal entity where this SO
3015 --is created and the record created is Arrival intercompany SO
3016 --we need an intercompany ap invoice
3017 IF (l_so_le_id = l_movement_transaction.entity_org_id
3018 AND l_movement_transaction.movement_type = 'D')--change from A to D
3019 THEN
3020 --intercompany ap invoice
3021 OPEN l_ap_intercompany;
3022 FETCH l_ap_intercompany INTO
3023 x_movement_transaction.invoice_id
3024 , x_movement_transaction.distribution_line_number
3025 , l_extended_amount
3026 , x_movement_transaction.invoice_quantity;
3027 CLOSE l_ap_intercompany;
3028
3029 FND_FILE.put_line(FND_FILE.log, 'The invoice id is : '||x_movement_transaction.invoice_id);
3030 FND_FILE.put_line(FND_FILE.log, 'The invoice qty(1) is : '||x_movement_transaction.invoice_quantity);
3031
3032 IF x_movement_transaction.invoice_id IS NOT NULL
3033 THEN
3034 --Recalculate invoice amount for ap intercompany CTO item
3035 IF l_item_type_code = 'CONFIG'
3036 THEN
3037 Calc_Cto_Amount_ap
3038 ( p_order_line_id => l_movement_transaction.order_line_id
3039 , p_order_number => to_char(l_movement_transaction.order_number)
3040 , x_extended_amount => l_extended_amount
3041 );
3042 END IF;
3043 -- bug 5411006, Following IF block is added to get details from AR invoice
3044 -- if Amount or Quantity is missing from AP Invoice.
3045
3046 /* 7165989 - new cursor defined for intercompany RMA Dispatch */
3047 /* The invoice quantity is fetched from quantity_credited in RA_CUSTOMER_TRX_LINES_ALL*/
3048 /* as AP_INVOICES_ALL does not have the invoice quantity */
3049 IF l_extended_amount = 0 OR x_movement_transaction.invoice_quantity = 0 THEN
3050 OPEN l_ap_intercompany_rma_inv_qty;
3051 FETCH l_ap_intercompany_rma_inv_qty INTO
3052 l_ar_invoiced_amount, l_ar_invoiced_qty;
3053 CLOSE l_ap_intercompany_rma_inv_qty;
3054 IF l_extended_amount = 0 THEN
3055 l_extended_amount := l_ar_invoiced_amount;
3056 END IF;
3057 IF x_movement_transaction.invoice_quantity = 0 THEN
3058 x_movement_transaction.invoice_quantity := l_ar_invoiced_qty;
3059 END IF;
3060
3061 FND_FILE.put_line(FND_FILE.log, 'The invoice qty(2) is : '||x_movement_transaction.invoice_quantity);
3062
3063 END IF;
3064 --Open cursor to get other invoice information
3065 OPEN l_ap_intercompany_invo;
3066 FETCH l_ap_intercompany_invo INTO
3067 x_movement_transaction.currency_code
3068 , x_movement_transaction.currency_conversion_rate
3069 , x_movement_transaction.currency_conversion_type
3070 , x_movement_transaction.currency_conversion_date
3071 , x_movement_transaction.invoice_batch_id
3072 , x_movement_transaction.invoice_date_reference;
3073
3074 FND_FILE.put_line(FND_FILE.log, 'The currency_code is : '||x_movement_transaction.currency_code);
3075 FND_FILE.put_line(FND_FILE.log, 'The currency_conversion_rate is : '||x_movement_transaction.currency_conversion_rate);
3076 FND_FILE.put_line(FND_FILE.log, 'The currency_conversion_type is : '||x_movement_transaction.currency_conversion_type);
3077 FND_FILE.put_line(FND_FILE.log, 'The currency_conversion_date is : '||x_movement_transaction.currency_conversion_date);
3078 FND_FILE.put_line(FND_FILE.log, 'The invoice_batch_id is : '||x_movement_transaction.invoice_batch_id);
3079 FND_FILE.put_line(FND_FILE.log, 'The invoice_date_reference is : '||x_movement_transaction.invoice_date_reference);
3080
3081
3082 IF l_ap_intercompany_invo%NOTFOUND
3083 THEN
3084 x_movement_transaction.currency_code := l_tran_curr_code;
3085 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
3086 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
3087 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
3088 x_movement_transaction.invoice_batch_id := null;
3089 x_movement_transaction.invoice_date_reference := null;
3090 END IF;
3091 CLOSE l_ap_intercompany_invo;
3092 END IF; -- Intercompany AR Invoice
3093 --If this processor is run at the legal entity where this SO
3094 --is created but the record created is a virtual Dispatch to customer
3095 --we need an regular ar invoice to customer
3096 ELSIF (l_so_le_id = l_movement_transaction.entity_org_id
3097 AND l_movement_transaction.movement_type = 'A')--change from D to A
3098 THEN
3099 --Check for Credit/debit memo transaction type against RMA.
3100 OPEN l_sum_rma_arc;
3101 FETCH l_sum_rma_arc INTO
3102 x_movement_transaction.customer_trx_line_id
3103 , x_movement_transaction.invoice_id
3104 , l_extended_amount
3105 , x_movement_transaction.invoice_quantity;
3106 CLOSE l_sum_rma_arc;
3107
3108 --Recalculate invoice amount for rma CTO item
3109 IF l_item_type_code = 'CONFIG'
3110 AND x_movement_transaction.customer_trx_line_id IS NOT NULL
3111 THEN
3112 Calc_Cto_Amount_Rma
3113 ( p_order_line_id => l_movement_transaction.order_line_id
3114 , p_order_number => to_char(l_movement_transaction.order_number)
3115 , p_org_id => l_movement_transaction.org_id
3116 , x_extended_amount => l_extended_amount
3117 );
3118 END IF;
3119
3120 --If this processor is run at the legal entity where this SO
3121 --is shipped, we need an ar intercompany invoice
3122 ELSIF (l_shipping_le_id = l_movement_transaction.entity_org_id
3123 AND l_movement_transaction.movement_type = 'A')
3124 THEN
3125 OPEN l_ar_intercompany;
3126 FETCH l_ar_intercompany INTO
3127 x_movement_transaction.customer_trx_line_id
3128 , x_movement_transaction.invoice_id
3129 , l_extended_amount
3130 , x_movement_transaction.invoice_quantity;
3131 CLOSE l_ar_intercompany;
3132
3133 --Recalculate invoice amount for ar intercompany CTO item
3134 IF l_item_type_code = 'CONFIG'
3135 AND x_movement_transaction.customer_trx_line_id IS NOT NULL
3136 THEN
3137 Calc_Cto_Amount_ar
3138 ( p_order_line_id => l_movement_transaction.order_line_id
3139 , p_order_number => to_char(l_movement_transaction.order_number)
3140 , x_extended_amount => l_extended_amount
3141 );
3142 END IF;
3143 END IF;
3144 ELSE
3145 /* bug# 7165989 End Intercompany AP/AR Invoice for RMA*/
3146 --Check for Credit/debit memo transaction type against SO.
3147 OPEN l_sum_rma_arc;
3148 FETCH l_sum_rma_arc INTO
3149 x_movement_transaction.customer_trx_line_id
3150 , x_movement_transaction.invoice_id
3151 , l_extended_amount
3152 , x_movement_transaction.invoice_quantity;
3153 CLOSE l_sum_rma_arc;
3154
3155 --Recalculate invoice amount for rma CTO item
3156 IF l_item_type_code = 'CONFIG'
3157 AND x_movement_transaction.customer_trx_line_id IS NOT NULL
3158 THEN
3159 Calc_Cto_Amount_Rma
3160 ( p_order_line_id => l_movement_transaction.order_line_id
3161 , p_order_number => to_char(l_movement_transaction.order_number)
3162 , p_org_id => l_movement_transaction.org_id
3163 , x_extended_amount => l_extended_amount
3164 );
3165 END IF;
3166
3167 /*--Fix bug 2695323
3168 --In the case of multiple receipts for a KIT RMA, the kit order line
3169 --is not split for the multiple receipts. One invoice for the order line
3170 --and multiple receipts. Do a proportional calculation for the invoice
3171 --qty for each receipt
3172 --Get total return qty
3173 SELECT ordered_quantity
3174 INTO l_total_rma_qty
3175 FROM oe_order_lines_all
3176 WHERE line_id = l_movement_transaction.order_line_id;
3177
3178 x_movement_transaction.invoice_quantity :=
3179 (l_movement_transaction.transaction_quantity/l_total_rma_qty)
3180 * x_movement_transaction.invoice_quantity;
3181 l_extended_amount :=
3182 (l_movement_transaction.transaction_quantity/l_total_rma_qty)
3183 * l_extended_amount;*/
3184 END IF;
3185 End if;/*7165989*/
3186 --Get other invoice information
3187 --Bug 6035548. Invoice details for RMA should always be calculated
3188 --even when selling org is diff than shipping org.
3189 IF (l_so_le_id <> l_shipping_le_id
3190 AND l_so_le_id = l_movement_transaction.entity_org_id
3191 AND l_movement_transaction.movement_type = 'A'
3192 AND l_movement_transaction.document_source_type <> 'RMA' )
3193 THEN
3194 NULL;
3195 ELSE
3196 /* Bug 7165989 - For IO arrival, look only at the AP invoice, not the AR invoice */
3197 IF (l_movement_transaction.movement_type = 'A'
3198 AND l_movement_transaction.document_source_type = 'IO')
3199 OR (l_movement_transaction.movement_type = 'D'
3200 AND l_movement_transaction.document_source_type = 'RMA') THEN
3201 NULL;
3202 else
3203
3204 OPEN l_arc;
3205 FETCH l_arc INTO
3206 x_movement_transaction.invoice_date_reference
3207 , x_movement_transaction.invoice_batch_id
3208 , x_movement_transaction.currency_conversion_rate
3209 , x_movement_transaction.currency_conversion_type
3210 , x_movement_transaction.currency_conversion_date
3211 , x_movement_transaction.currency_code;
3212
3213 IF l_arc%NOTFOUND
3214 THEN
3215 x_movement_transaction.currency_code := l_tran_curr_code;
3216 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
3217 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
3218 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
3219 x_movement_transaction.invoice_batch_id := null;
3220 x_movement_transaction.invoice_date_reference := null;
3221 END IF;
3222 CLOSE l_arc;
3223 END IF;
3224 End if;
3225 ELSE --drop shimement
3226 OPEN l_sum_drparc;
3227 FETCH l_sum_drparc INTO
3228 x_movement_transaction.customer_trx_line_id
3229 , x_movement_transaction.invoice_id
3230 , l_extended_amount
3231 , x_movement_transaction.invoice_quantity;
3232 CLOSE l_sum_drparc;
3233
3234 IF x_movement_transaction.invoice_id IS NOT NULL
3235 THEN
3236 OPEN l_sum_cm_drparc;
3237 FETCH l_sum_cm_drparc INTO
3238 l_cm_extended_amount;
3239 CLOSE l_sum_cm_drparc;
3240 END IF;
3241
3242 OPEN l_drparc;
3243 FETCH l_drparc INTO
3244 x_movement_transaction.invoice_date_reference
3245 , x_movement_transaction.invoice_batch_id
3246 , x_movement_transaction.currency_conversion_rate
3247 , x_movement_transaction.currency_conversion_type
3248 , x_movement_transaction.currency_conversion_date
3249 , x_movement_transaction.currency_code;
3250 CLOSE l_drparc;
3251
3252 --Recalculate invoice amount for dropship CTO item
3253 IF l_item_type_code = 'CONFIG'
3254 AND x_movement_transaction.customer_trx_line_id IS NOT NULL
3255 THEN
3256 Calc_Cto_Amount_Drp
3257 ( p_order_line_id => l_movement_transaction.order_line_id
3258 , p_order_number => to_char(l_movement_transaction.order_number)
3259 , x_extended_amount => l_extended_amount
3260 , x_cm_extended_amount => l_cm_extended_amount
3261 );
3262 END IF;
3263 END IF;
3264
3265 IF l_extended_amount IS NULL
3266 THEN
3267 x_movement_transaction.invoice_line_ext_value := null;
3268 x_movement_transaction.invoice_unit_price := null;
3269 x_movement_transaction.invoice_quantity := null;
3270 ELSE
3271
3272 l_extended_amount := l_extended_amount + NVL(l_cm_extended_amount,0);
3273 x_movement_transaction.invoice_line_ext_value := l_extended_amount;
3274 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3275 THEN
3276 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3277 , G_MODULE_NAME || l_procedure_name || 'EXTENDED AMOUNT IS ********************* '
3278 , l_extended_amount
3279 );
3280 END IF ;
3281
3282 x_movement_transaction.invoice_quantity :=
3283 NVL(x_movement_transaction.invoice_quantity,
3284 x_movement_transaction.transaction_quantity);
3285
3286 IF (x_movement_transaction.invoice_quantity IS NOT NULL
3287 AND x_movement_transaction.invoice_quantity <> 0)
3288 THEN
3289 x_movement_transaction.invoice_unit_price :=
3290 l_extended_amount / x_movement_transaction.invoice_quantity;
3291 -- IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3292 -- THEN
3293 -- FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3294 -- , G_MODULE_NAME || l_procedure_name || 'unit_price IS ********************* '
3295 -- , x_movement_transaction.invoice_unit_price
3296 -- );
3297 -- END IF ;
3298
3299 -- IF (x_movement_transaction.invoice_id IS NOT NULL)
3300 -- THEN
3301 -- /* if intercompany invoice exists, the document unit price will be the same as the invoice unit price - 6889669 */
3302 -- --x_movement_transaction.document_unit_price := x_movement_transaction.invoice_unit_price;
3303 -- x_movement_transaction.document_line_ext_value := x_movement_transaction.invoice_unit_price *
3304 -- x_movement_transaction.transaction_quantity;
3305 -- END IF;
3306 ELSE
3307 x_movement_transaction.invoice_unit_price := null;
3308 END IF;
3309 END IF;
3310
3311 ELSIF l_movement_transaction.document_source_type IN ('PO')
3312 THEN
3313 -- Fetch the cursor for non credit memo type transactions. This is
3314 -- against receipt based matching if one exists;
3315 OPEN l_ap_inv ;
3316 FETCH l_ap_inv INTO
3317 l_extended_amount
3318 , x_movement_transaction.distribution_line_number
3319 , x_movement_transaction.invoice_id
3320 , l_invoice_quantity;
3321 CLOSE l_ap_inv;
3322
3323 IF x_movement_transaction.invoice_id IS NOT NULL
3324 THEN
3325 -- Fetch the cursor for price correction.
3326 OPEN l_ap_prc_inv;
3327 FETCH l_ap_prc_inv INTO
3328 l_prc_amount;
3329 CLOSE l_ap_prc_inv;
3330
3331 -- Fetch the cursor for quantity correction.
3332 OPEN l_ap_qtc_inv;
3333 FETCH l_ap_qtc_inv INTO
3334 l_qtc_qty
3335 , l_qtc_amount;
3336 CLOSE l_ap_qtc_inv;
3337 ELSE
3338 -- If receipt based matching does not exists, check if it matched agains
3339 -- a PO; check non credit memo based transactions;
3340 OPEN l_po_inv ;
3341 FETCH l_po_inv INTO
3342 l_extended_amount
3343 , x_movement_transaction.distribution_line_number
3344 , x_movement_transaction.invoice_id
3345 , l_invoice_quantity;
3346 CLOSE l_po_inv;
3347
3348 IF x_movement_transaction.invoice_id IS NOT NULL
3349 THEN
3350 -- Fetch the cursor for price correction.
3351 OPEN l_po_prc_inv ;
3352 FETCH l_po_prc_inv INTO
3353 l_prc_amount;
3354 CLOSE l_po_prc_inv;
3355
3356 -- Fetch the cursor for quantity correction.
3357 OPEN l_po_qtc_inv;
3358 FETCH l_po_qtc_inv INTO
3359 l_qtc_qty
3360 , l_qtc_amount;
3361 CLOSE l_po_qtc_inv;
3362 ELSE
3363 x_movement_transaction.currency_code := l_tran_curr_code;
3364 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
3365 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
3366 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
3367 x_movement_transaction.invoice_batch_id := null;
3368 x_movement_transaction.invoice_date_reference := null;
3369 x_movement_transaction.invoice_id := null;
3370 x_movement_transaction.invoice_quantity := null;
3371 x_movement_transaction.invoice_unit_price := null;
3372 x_movement_transaction.invoice_line_ext_value := null;
3373 x_movement_transaction.distribution_line_number := null;
3374 END IF;
3375 END IF; -- finish check receipt/po based invoice
3376
3377 --Fix italian bug 2861110.Update_invoice_info will revert any netted invoice
3378 --amt and qty back to original PO invoice amt and qty. The following code
3379 --will net the invoice amt and qty again for processed RTV
3380 IF (l_stat_typ_transaction.returns_processing = 'AGGRTN'
3381 AND x_movement_transaction.invoice_id IS NOT NULL)
3382 THEN
3383 Calc_Processed_Ret_Data
3384 (p_movement_transaction => l_movement_transaction
3385 , x_processed_ret_amt => l_processed_ret_amt
3386 , x_processed_ret_qty => l_processed_ret_qty
3387 );
3388
3389 --The correct netted PO invoce amount and quantity
3390 l_extended_amount := l_extended_amount + NVL(l_processed_ret_amt,0);
3391 l_invoice_quantity := l_invoice_quantity + NVL(l_processed_ret_qty,0);
3392 END IF; --end bug 2861110
3393
3394 IF l_extended_amount IS NULL
3395 THEN
3396 x_movement_transaction.invoice_line_ext_value := null;
3397 x_movement_transaction.invoice_unit_price := null;
3398 x_movement_transaction.invoice_quantity := null;
3399 ELSE
3400 l_extended_amount := l_extended_amount + NVL(l_prc_amount,0) + NVL(l_qtc_amount, 0);
3401 x_movement_transaction.invoice_line_ext_value := l_extended_amount;
3402
3403 x_movement_transaction.invoice_quantity :=
3404 NVL(l_invoice_quantity,x_movement_transaction.transaction_quantity) + NVL(l_qtc_qty, 0);
3405
3406 --Fix bug 2340128, decide to use extended amount to calculate unit
3407 --price
3408 IF (x_movement_transaction.invoice_quantity IS NOT NULL
3409 AND x_movement_transaction.invoice_quantity <> 0)
3410 THEN
3411 x_movement_transaction.invoice_unit_price :=
3412 l_extended_amount / x_movement_transaction.invoice_quantity;
3413 ELSE
3414 x_movement_transaction.invoice_unit_price := null;
3415 END IF;
3416 END IF;
3417
3418 OPEN l_apc;
3419 FETCH l_apc INTO
3420 x_movement_transaction.currency_code
3421 , x_movement_transaction.currency_conversion_rate
3422 , x_movement_transaction.currency_conversion_type
3423 , x_movement_transaction.currency_conversion_date
3424 , x_movement_transaction.invoice_batch_id
3425 , x_movement_transaction.invoice_date_reference;
3426
3427 IF l_apc%NOTFOUND
3428 THEN
3429 x_movement_transaction.currency_code := l_tran_curr_code;
3430 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
3431 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
3432 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
3433 x_movement_transaction.invoice_batch_id := null;
3434 x_movement_transaction.invoice_date_reference := null;
3435 x_movement_transaction.invoice_id := null;
3436 END IF;
3437
3438 CLOSE l_apc;
3439 ELSIF l_movement_transaction.document_source_type IN ('RTV')
3440 THEN
3441 --Get parent transaction id for RTV transaction
3442 --Used in open l_ap_rtv_cm_inv
3443 BEGIN
3444 IF l_movement_transaction.rcv_transaction_id IS NOT NULL
3445 THEN
3446 SELECT parent_transaction_id
3447 INTO l_parent_transaction_id
3448 FROM rcv_transactions
3449 WHERE transaction_id = l_movement_transaction.rcv_transaction_id;
3450 END IF;
3451
3452 EXCEPTION
3453 WHEN OTHERS THEN
3454 l_parent_transaction_id := -1;
3455 END;
3456
3457 --First check if this rtv matched to any receipt based credit memo
3458 OPEN l_ap_rtv_cm_inv ;
3459 FETCH l_ap_rtv_cm_inv INTO
3460 l_rtv_extended_amount
3461 , x_movement_transaction.distribution_line_number
3462 , x_movement_transaction.invoice_id
3463 , l_rtv_invoice_quantity;
3464 CLOSE l_ap_rtv_cm_inv;
3465
3466 -- if the Credit/Debit memo is matched against Purchase Orders rather than
3467 -- receipts.
3468 IF x_movement_transaction.invoice_id IS NULL
3469 THEN
3470 OPEN l_po_rtv_cm_inv ;
3471 FETCH l_po_rtv_cm_inv INTO
3472 l_rtv_extended_amount
3473 , x_movement_transaction.distribution_line_number
3474 , x_movement_transaction.invoice_id
3475 , l_rtv_invoice_quantity;
3476 CLOSE l_po_rtv_cm_inv;
3477
3478 IF x_movement_transaction.invoice_id IS NULL
3479 THEN
3480 x_movement_transaction.currency_code := l_tran_curr_code;
3481 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
3482 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
3483 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
3484 x_movement_transaction.invoice_batch_id := null;
3485 x_movement_transaction.invoice_date_reference := null;
3486 x_movement_transaction.invoice_id := null;
3487 x_movement_transaction.invoice_quantity := null;
3488 x_movement_transaction.invoice_unit_price := null;
3489 x_movement_transaction.invoice_line_ext_value := null;
3490 x_movement_transaction.distribution_line_number := null;
3491 ELSE
3492 OPEN l_apc;
3493 FETCH l_apc INTO
3494 x_movement_transaction.currency_code
3495 , x_movement_transaction.currency_conversion_rate
3496 , x_movement_transaction.currency_conversion_type
3497 , x_movement_transaction.currency_conversion_date
3498 , x_movement_transaction.invoice_batch_id
3499 , x_movement_transaction.invoice_date_reference;
3500
3501 IF l_apc%NOTFOUND
3502 THEN
3503 x_movement_transaction.currency_code := l_tran_curr_code;
3504 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
3505 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
3506 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
3507 x_movement_transaction.invoice_batch_id := null;
3508 x_movement_transaction.invoice_date_reference := null;
3509 END IF;
3510 CLOSE l_apc;
3511 END IF; --second invoice id null
3512 END IF; --first invoice id null
3513
3514 IF l_rtv_extended_amount IS NULL
3515 THEN
3516 x_movement_transaction.invoice_line_ext_value := null;
3517 x_movement_transaction.invoice_unit_price := null;
3518 x_movement_transaction.invoice_quantity := null;
3519 ELSE
3520 --In case there are multiple rtv associated with creidt memo, calculate
3521 --inoice amt and qty for this rtv
3522 --First find total rtv transaction quantity
3523 OPEN l_total_rtv_quantity;
3524 FETCH l_total_rtv_quantity INTO
3525 l_total_rtv_trans_qty;
3526 CLOSE l_total_rtv_quantity;
3527
3528 --Amount and quantity for this rtv
3529 IF (l_total_rtv_trans_qty IS NOT NULL
3530 AND l_total_rtv_trans_qty <> 0)
3531 THEN
3532 l_rtv_invoice_quantity :=
3533 (x_movement_transaction.transaction_quantity/l_total_rtv_trans_qty)
3534 * l_rtv_invoice_quantity;
3535 l_rtv_extended_amount :=
3536 (x_movement_transaction.transaction_quantity/l_total_rtv_trans_qty)
3537 *l_rtv_extended_amount;
3538 END IF;
3539
3540 --Set rtv qty and amt
3541 x_movement_transaction.invoice_quantity :=
3542 NVL(l_rtv_invoice_quantity,x_movement_transaction.transaction_quantity);
3543 x_movement_transaction.invoice_line_ext_value := l_rtv_extended_amount;
3544
3545 IF (l_rtv_invoice_quantity <> 0
3546 AND l_rtv_extended_amount IS NOT NULL)
3547 THEN
3548 x_movement_transaction.invoice_unit_price
3549 := l_rtv_extended_amount/l_rtv_invoice_quantity;
3550 ELSE
3551 x_movement_transaction.invoice_unit_price := NULL;
3552 END IF;
3553 END IF; -- end rtv_extended_amount null
3554 END IF;
3555
3556 /*--If there is no invoice
3557 IF x_movement_transaction.invoice_id IS NULL
3558 THEN
3559 --If transaction currency is same as functional currency
3560 IF NVL(x_movement_transaction.currency_code,FND_API.G_MISS_CHAR) =
3561 NVL(l_stat_typ_transaction.gl_currency_code,FND_API.G_MISS_CHAR)
3562 THEN
3563 x_movement_transaction.currency_conversion_rate := 1;
3564 x_movement_transaction.currency_conversion_type := null;
3565 x_movement_transaction.currency_conversion_date := null;
3566
3567 --If transaction currency is different from functional currency and
3568 --transaction conversion rate/type is not populated in PO/SO document
3569 --calc rate from GL package
3570 ELSIF (x_movement_transaction.currency_conversion_rate IS NULL
3571 OR x_movement_transaction.currency_conversion_type IS NULL)
3572 THEN
3573 Calc_Exchange_Rate( x_movement_transaction => x_movement_transaction
3574 , p_stat_typ_transaction => l_stat_typ_transaction
3575 );
3576 END IF;
3577 END IF; */
3578
3579 IF NVL(x_movement_transaction.currency_code,FND_API.G_MISS_CHAR) =
3580 NVL(l_stat_typ_transaction.gl_currency_code,FND_API.G_MISS_CHAR)
3581 THEN
3582 x_movement_transaction.currency_conversion_rate := 1;
3583 x_movement_transaction.currency_conversion_date := null;
3584 x_movement_transaction.currency_conversion_type := null;
3585 ELSE
3586 --Fix bug 4285335, if user setup a conversion type on parameter form, always take the type
3587 --from parameter form, the two parameters are modified to optional
3588 IF l_stat_typ_transaction.conversion_type IS NOT NULL
3589 THEN
3590 x_movement_transaction.currency_conversion_type := l_stat_typ_transaction.conversion_type;
3591
3592 Calc_Exchange_Rate
3593 ( x_movement_transaction => x_movement_transaction
3594 , p_stat_typ_transaction => l_stat_typ_transaction
3595 );
3596 END IF;
3597 END IF;
3598 END IF;
3599
3600 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3601 THEN
3602 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3603 , G_MODULE_NAME || l_procedure_name || '.end'
3604 ,'exit procedure'
3605 );
3606 END IF;
3607
3608 EXCEPTION
3609 WHEN NO_DATA_FOUND
3610 THEN
3611 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3612 THEN
3613 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3614 , G_MODULE_NAME || l_procedure_name||'.No data found exception'
3615 , 'Exception'
3616 );
3617 END IF;
3618 x_movement_transaction := l_movement_transaction;
3619
3620 --Switch back the order line id for CTO item
3621 IF (l_item_type_code = 'CONFIG' AND l_cto_line_id IS NOT NULL)
3622 THEN
3623 x_movement_transaction.order_line_id := l_cto_line_id;
3624 END IF;
3625 WHEN TOO_MANY_ROWS
3626 THEN
3627 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3628 THEN
3629 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3630 , G_MODULE_NAME || l_procedure_name||'.too many rows exception'
3631 , 'Exception'
3632 );
3633 END IF;
3634 x_movement_transaction := l_movement_transaction;
3635
3636 --Switch back the order line id for CTO item
3637 IF (l_item_type_code = 'CONFIG' AND l_cto_line_id IS NOT NULL)
3638 THEN
3639 x_movement_transaction.order_line_id := l_cto_line_id;
3640 END IF;
3641 WHEN OTHERS
3642 THEN
3643 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3644 THEN
3645 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3646 , G_MODULE_NAME || l_procedure_name||'.Others exception'
3647 , 'Exception'
3648 );
3649 END IF;
3650 x_movement_transaction := l_movement_transaction;
3651
3652 --Switch back the order line id for CTO item
3653 IF (l_item_type_code = 'CONFIG' AND l_cto_line_id IS NOT NULL)
3654 THEN
3655 x_movement_transaction.order_line_id := l_cto_line_id;
3656 END IF;
3657 END Calc_Invoice_Info;
3658
3659
3660 --========================================================================
3661 -- FUNCTION : Get_Set_Of_Books_Period
3662 -- PARAMETERS: p_legal_entity_id Legal Entity
3663 -- p_period_date Invoice date or transaction date
3664 -- COMMENT : Function that returns the Period Name
3665 -- based on invoice date or movement date if invoice date is null
3666 --=========================================================================
3667 /* Bug: 5291257. Function defintion is modified to remove parameter
3668 p_period_type. */
3669 FUNCTION Get_Set_Of_Books_Period
3670 ( p_legal_entity_id IN VARCHAR2
3671 , p_period_date IN DATE
3672 --, p_period_type IN VARCHAR2
3673 )
3674 RETURN VARCHAR2
3675 IS
3676 l_set_of_books_period VARCHAR2(15);
3677 l_function_name CONSTANT VARCHAR2(30) := 'Get_Set_Of_Books_Period';
3678
3679 /* Bug: 5291257. Following cursor definition is modified and p_period_type is replaced with
3680 gllv.accounted_period_type */
3681 CURSOR c_pname IS
3682 SELECT
3683 glp.period_name
3684 FROM
3685 gl_periods glp
3686 , gl_ledger_le_v gllv
3687 WHERE gllv.period_set_name = glp.period_set_name
3688 AND gllv.legal_entity_id = p_legal_entity_id
3689 AND gllv.ledger_category_code = 'PRIMARY'
3690 AND glp.period_type = gllv.accounted_period_type
3691 AND NVL(glp.adjustment_period_flag,'N') = 'N'
3692 AND trunc(p_period_date) BETWEEN trunc(glp.start_date) AND trunc(glp.end_date);
3693
3694 BEGIN
3695 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3696 THEN
3697 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3698 , G_MODULE_NAME || l_function_name || '.begin'
3699 ,'enter procedure'
3700 );
3701 END IF;
3702
3703 OPEN c_pname;
3704 FETCH c_pname
3705 INTO l_set_of_books_period;
3706
3707 IF c_pname%NOTFOUND THEN
3708 l_set_of_books_period:= null;
3709 END IF;
3710
3711 CLOSE c_pname;
3712
3713 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3714 THEN
3715 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3716 , G_MODULE_NAME || l_function_name || '.end'
3717 ,'exit procedure'
3718 );
3719 END IF;
3720
3721 RETURN l_set_of_books_period;
3722
3723 EXCEPTION
3724 WHEN NO_DATA_FOUND THEN
3725 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3726 THEN
3727 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3728 , G_MODULE_NAME || l_function_name||'.No data found exception'
3729 , 'Exception'
3730 );
3731 END IF;
3732 RETURN null;
3733 WHEN TOO_MANY_ROWS
3734 THEN
3735 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3736 THEN
3737 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3738 , G_MODULE_NAME || l_function_name||'.too many rows exception'
3739 , 'Exception'
3740 );
3741 END IF;
3742 RETURN null;
3743 WHEN OTHERS THEN
3744 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3745 THEN
3746 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3747 , G_MODULE_NAME || l_function_name||'.Others exception'
3748 , 'Exception'
3749 );
3750 END IF;
3751 RETURN null;
3752 END Get_Set_Of_Books_Period;
3753
3754
3755 --========================================================================
3756 -- FUNCTION : Get_Period_Name
3757 -- PARAMETERS: p_movement_transacton Movement Transaction record
3758 -- p_stat_typ_transaction Stat typ tranaction
3759 -- COMMENT : Function that returns the Period Name
3760 --=========================================================================
3761
3762 FUNCTION Get_Period_Name
3763 ( p_movement_transaction IN
3764 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
3765 , p_stat_typ_transaction IN
3766 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
3767 )
3768 RETURN VARCHAR2
3769 IS
3770 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
3771 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
3772 l_function_name CONSTANT VARCHAR2(30) := 'Get_Period_Name';
3773
3774 CURSOR c_period IS
3775 SELECT
3776 period_name
3777 FROM
3778 GL_PERIODS
3779 WHERE period_set_name = l_stat_typ_transaction.period_set_name
3780 AND l_movement_transaction.transaction_date between
3781 (start_date) and (end_date)
3782 AND start_date >= l_stat_typ_transaction.start_date
3783 AND end_date <= l_stat_typ_transaction.end_date
3784 AND period_type = l_stat_typ_transaction.period_type
3785 AND NVL(adjustment_period_flag,'N') = 'N';
3786
3787 CURSOR c_period1 IS
3788 SELECT
3789 period_name
3790 FROM
3791 GL_PERIODS
3792 WHERE period_set_name = l_stat_typ_transaction.period_set_name
3793 AND trunc(l_movement_transaction.transaction_date) between
3794 trunc(start_date) and trunc(end_date)
3795 AND period_type = l_stat_typ_transaction.period_type
3796 AND start_date >= l_stat_typ_transaction.start_date
3797 AND NVL(adjustment_period_flag,'N') = 'N';
3798
3799 BEGIN
3800 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3801 THEN
3802 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3803 , G_MODULE_NAME || l_function_name || '.begin'
3804 ,'enter procedure'
3805 );
3806 END IF;
3807
3808 l_movement_transaction := p_movement_transaction;
3809 l_stat_typ_transaction := p_stat_typ_transaction;
3810
3811 IF (l_stat_typ_transaction.start_date IS NOT NULL)
3812 AND
3813 (l_stat_typ_transaction.end_date IS NOT NULL)
3814 THEN
3815 OPEN c_period;
3816 FETCH c_period
3817 INTO l_movement_transaction.period_name;
3818
3819 IF c_period%NOTFOUND THEN
3820 CLOSE c_period;
3821 ELSE
3822 CLOSE c_period;
3823 END IF;
3824
3825 ELSE
3826 OPEN c_period1;
3827 FETCH c_period1
3828 INTO l_movement_transaction.period_name;
3829
3830 IF c_period1%NOTFOUND THEN
3831 CLOSE c_period1;
3832 ELSE
3833 CLOSE c_period1;
3834 END IF;
3835 END IF;
3836
3837 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3838 THEN
3839 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3840 , G_MODULE_NAME || l_function_name || '.end'
3841 ,'exit procedure'
3842 );
3843 END IF;
3844
3845 RETURN l_movement_transaction.period_name;
3846
3847 EXCEPTION
3848 WHEN OTHERS THEN
3849 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3850 THEN
3851 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
3852 , G_MODULE_NAME || l_function_name||'. Others exception'
3853 , 'Exception'
3854 );
3855 END IF;
3856 RETURN NULL;
3857 END Get_Period_Name;
3858
3859
3860 --========================================================================
3861 -- PROCEDURE : Get_Reference_Date
3862 -- PARAMETERS: x_movement_transacton Movement Transaction record
3863 -- p_stat_typ_transaction Stat typ tranaction
3864 -- COMMENT : Procedure that gets the Reference Date
3865 --=========================================================================
3866
3867 PROCEDURE Get_Reference_Date
3868 ( p_stat_typ_transaction IN
3869 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
3870 , x_movement_transaction IN OUT NOCOPY
3871 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
3872 )
3873 IS
3874 l_transaction_date DATE;
3875 l_invoice_date DATE;
3876 l_pending_date DATE;
3877 l_months NUMBER;
3878 l_no_days NUMBER;
3879 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
3880 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Reference_Date';
3881
3882 BEGIN
3883 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3884 THEN
3885 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3886 , G_MODULE_NAME || l_procedure_name || '.begin'
3887 ,'enter procedure'
3888 );
3889 END IF;
3890
3891 l_stat_typ_transaction := p_stat_typ_transaction;
3892 l_transaction_date := x_movement_transaction.transaction_date;
3893 l_invoice_date := x_movement_transaction.invoice_date_reference;
3894
3895 --Fix bug 4927726
3896 --Find out the original transaction date for a pending record, the current
3897 --transaction date is a reference date calculated before
3898 IF x_movement_transaction.movement_status = 'P'
3899 THEN
3900 -- Bug 5741580. Following code is modified to fetch the transaction date again
3901 -- from original transactions.
3902 IF x_movement_transaction.document_source_type = 'SO'
3903 THEN
3904 SELECT initial_pickup_date
3905 INTO l_transaction_date
3906 FROM wsh_delivery_details_ob_grp_v wdd
3907 , wsh_new_deliveries_ob_grp_v wnd
3908 , wsh_delivery_assignments wda
3909 WHERE wnd.delivery_id = wda.delivery_id
3910 AND wda.delivery_detail_id = wdd.delivery_detail_id
3911 AND wdd.source_line_id = x_movement_transaction.order_line_id
3912 AND wda.delivery_detail_id = x_movement_transaction.picking_line_detail_id
3913 AND wdd.organization_id = x_movement_transaction.organization_id
3914 AND nvl(wnd.customer_id,wdd.customer_id) = x_movement_transaction.ship_to_customer_id
3915 AND rownum = 1;
3916 ELSIF x_movement_transaction.document_source_type = 'INV'
3917 THEN
3918 SELECT transaction_date
3919 INTO l_transaction_date
3920 FROM MTL_MATERIAL_TRANSACTIONS MMT
3921 WHERE MMT.transaction_id = x_movement_transaction.mtl_transaction_id;
3922 ELSE
3923 SELECT transaction_date
3924 INTO l_transaction_date
3925 FROM rcv_transactions
3926 WHERE transaction_id = x_movement_transaction.rcv_transaction_id;
3927 END IF;
3928 END IF;
3929
3930 --Get correct pending date
3931 --Find out the number of days the next month of the transaction date
3932 SELECT to_number(to_char(LAST_DAY(add_months(l_transaction_date,1)),'DD'))
3933 INTO
3934 l_no_days
3935 FROM DUAL;
3936
3937 -- If the pending invoice days is greater than the # of days in the month,
3938 -- then pending_invoice_days is the # of days in the month.
3939 IF l_no_days < NVL(l_stat_typ_transaction.pending_invoice_days,15)
3940 THEN
3941 l_stat_typ_transaction.pending_invoice_days := l_no_days;
3942 END IF;
3943
3944 l_pending_date := to_date( NVL(l_stat_typ_transaction.pending_invoice_days,15)
3945 ||'-'||to_char(add_months(l_transaction_date,1),'MON-YY'),'DD-MON-YY');
3946
3947 -- If there is no invoice ,check if it has passed the invoice
3948 -- threshold date (pending_invoice_days) to determine the movement status
3949 -- the reference date is always same as the pending date.
3950 IF (x_movement_transaction.invoice_id IS NULL)
3951 THEN
3952 --Fix bug 4170403
3953 /*l_months := round(months_between(sysdate, l_transaction_date));
3954
3955 --If the transaction has taken place more than a month ago, or if the
3956 --transaction has taken place previous month and sysdate is greater
3957 --than the pending invoice days, reference date is the following month
3958 --plus the pending_invoice_days.
3959 IF (l_months > 1) OR
3960 (((l_months =1) AND (to_char(sysdate,'DD') >
3961 l_stat_typ_transaction.pending_invoice_days)) AND
3962 (to_char(sysdate,'MM') <> to_char(l_transaction_date,'MM'))) */
3963
3964 IF GREATEST(sysdate ,l_pending_date) = sysdate
3965 THEN
3966 x_movement_transaction.movement_status :='O';
3967 ELSE
3968 -- Wait for the invoice till next month
3969 x_movement_transaction.movement_status :='P';
3970 END IF;
3971
3972 x_movement_transaction.reference_date := l_pending_date;
3973 ELSE -- invoice present
3974 IF (l_transaction_date = l_invoice_date)
3975 THEN
3976 x_movement_transaction.reference_date := x_movement_transaction.transaction_date;
3977 -- Invoice was received before the transaction took place;
3978 ELSIF (GREATEST(l_transaction_date ,l_invoice_date) = l_transaction_date)
3979 THEN
3980 IF (l_transaction_date - l_invoice_date ) >
3981 NVL(l_stat_typ_transaction.prior_invoice_days,30)
3982 THEN
3983 x_movement_transaction.reference_date := l_transaction_date;
3984 ELSE
3985 x_movement_transaction.reference_date := l_invoice_date;
3986 END IF;
3987 ELSE -- invoice is received later
3988 --Fix bug 2365712 yawang
3989 --IF (l_invoice_date - l_transaction_date ) >
3990 -- NVL(l_stat_typ_transaction.pending_invoice_days,15)
3991 IF (GREATEST(l_invoice_date ,l_pending_date) = l_invoice_date)
3992 THEN
3993 x_movement_transaction.reference_date := l_pending_date;
3994 ELSE
3995 x_movement_transaction.reference_date := l_invoice_date;
3996 END IF;
3997 END IF;
3998
3999 x_movement_transaction.movement_status :='O';
4000 END IF;
4001
4002 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4003 THEN
4004 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4005 , G_MODULE_NAME || l_procedure_name || '.end'
4006 ,'exit procedure'
4007 );
4008 END IF;
4009
4010 EXCEPTION
4011 WHEN OTHERS THEN
4012 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4013 THEN
4014 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
4015 , G_MODULE_NAME || l_procedure_name||'. Others exception'
4016 , 'Exception'
4017 );
4018 END IF;
4019 END Get_Reference_Date;
4020
4021
4022 --========================================================================
4023 -- PROCEDURE : Log_Initialize PUBLIC
4024 -- COMMENT : Initializes the log facility. It should be called from
4025 -- the top level procedure of each concurrent program
4026 --=======================================================================--
4027 PROCEDURE Log_Initialize
4028 IS
4029 BEGIN
4030 g_log_level := TO_NUMBER(FND_PROFILE.Value('AFLOG_LEVEL'));
4031 IF g_log_level IS NULL THEN
4032 g_log_mode := 'OFF';
4033 ELSE
4034 IF (TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID')) <> 0) THEN
4035 g_log_mode := 'SRS';
4036 ELSE
4037 g_log_mode := 'SQL';
4038 END IF;
4039 END IF;
4040
4041 END Log_Initialize;
4042
4043
4044 --========================================================================
4045 -- PROCEDURE : Log PUBLIC
4046 -- PARAMETERS: p_level IN priority of the message - from
4047 -- highest to lowest:
4048 -- -- G_LOG_ERROR
4049 -- -- G_LOG_EXCEPTION
4050 -- -- G_LOG_EVENT
4051 -- -- G_LOG_PROCEDURE
4052 -- -- G_LOG_STATEMENT
4053 -- p_msg IN message to be print on the log
4054 -- file
4055 -- COMMENT : Add an entry to the log
4056 --=======================================================================--
4057 PROCEDURE Log
4058 ( p_priority IN NUMBER
4059 , p_msg IN VARCHAR2
4060 )
4061 IS
4062 BEGIN
4063 IF ((g_log_mode <> 'OFF') AND (p_priority >= g_log_level))
4064 THEN
4065 IF g_log_mode = 'SQL'
4066 THEN
4067 -- SQL*Plus session: uncomment the next line during unit test
4068 -- DBMS_OUTPUT.put_line(p_msg);
4069 NULL;
4070 ELSE
4071 -- Concurrent request
4072 FND_FILE.put_line
4073 ( FND_FILE.log
4074 , p_msg
4075 );
4076 END IF;
4077 END IF;
4078 EXCEPTION
4079 WHEN OTHERS THEN
4080 NULL;
4081 END Log;
4082
4083
4084 END INV_MGD_MVT_FIN_MDTR;
[Home] [Help]