1 PACKAGE BODY INV_MGD_MVT_FIN_MDTR AS
2 /* $Header: INVFMDRB.pls 120.21.12020000.2 2012/07/05 07:27:34 ntungare 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'
170
167 , l_last_dayofperiod
168 );
169 END IF ;
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;
297 THEN
294 l_currency_conversion_rate := 1;
295 return l_currency_conversion_rate;
296 WHEN OTHERS
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
434 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Cto_Amount_Drp';
431 , x_cm_extended_amount OUT NOCOPY NUMBER
432 )
433 IS
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 /* Bug 8435314 */
550 /*CURSOR l_cto_amt_rma IS
551 SELECT
552 SUM(ratl.extended_amount)
553 FROM
554 RA_CUSTOMER_TRX_ALL rat
555 , RA_CUSTOMER_TRX_LINES_ALL ratl
556 , RA_CUST_TRX_TYPES_ALL ratt
557 , oe_order_lines_all oola
558 WHERE rat.customer_trx_id = ratl.customer_trx_id
559 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
560 AND rat.org_id = ratt.org_id
561 AND ratt.type IN ('CM','DM')
562 AND ratl.line_type = 'LINE' --yawang
563 AND ratl.quantity_credited IS NOT NULL
564 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
565 AND NVL(ratl.org_id,0) = NVL(p_org_id,0)
566 AND ratl.interface_line_attribute6 = to_char(oola.line_id)
567 AND ratl.sales_order = p_order_number
568 AND oola.top_model_line_id = p_order_line_id
569 AND ratl.interface_line_context <> 'INTERCOMPANY';*/
570
571 --
572 -- Bug 10220870
573 -- Modified query to add to_char for header_id and line_id
574 -- when being compared with return_attribute1 and return_attribute2
575 --
576 CURSOR l_cto_amt_rma IS
577 SELECT
578 SUM(ratl.extended_amount)
579 FROM
580 RA_CUSTOMER_TRX_ALL rat
581 , RA_CUSTOMER_TRX_LINES_ALL ratl
582 , RA_CUST_TRX_TYPES_ALL ratt
583 WHERE rat.customer_trx_id = ratl.customer_trx_id
584 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
585 AND rat.org_id = ratt.org_id
586 AND ratt.type IN ('CM','DM')
587 AND ratl.line_type = 'LINE' --yawang
588 AND ratl.quantity_credited IS NOT NULL
589 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
590 AND NVL(ratl.org_id,0) = NVL(p_org_id,0)
591 AND ratl.interface_line_attribute6 In
592 ( SELECT DISTINCT To_Char(ol.line_id) FROM oe_order_lines_all ol
593 WHERE (return_attribute1,return_attribute2) IN
594 (SELECT to_char(header_id),to_char(line_id) FROM oe_order_lines_all ol1
595 WHERE (header_id,Top_model_line_id) IN
596 (SELECT return_attribute1,return_attribute2 FROM oe_order_lines_all ol
597 WHERE ol.line_id=p_order_line_id )))
598 AND ratl.interface_line_attribute1 = p_order_number
599 AND ratl.interface_line_context <> 'INTERCOMPANY';
600 /*End bug 8435314 */
601
602 BEGIN
603 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
604 THEN
605 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
606 , G_MODULE_NAME || l_procedure_name || '.begin'
607 ,'enter procedure'
608 );
609 END IF;
610
611 --No need to check cursor not found, because cursor with SUM
612 --function always return true
613 OPEN l_cto_amt_rma;
614 FETCH l_cto_amt_rma INTO
615 x_extended_amount;
616 CLOSE l_cto_amt_rma;
617
618 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
619 THEN
620 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
621 , G_MODULE_NAME || l_procedure_name || '.end'
622 ,'exit procedure'
623 );
624 END IF;
625
626 EXCEPTION
627 WHEN OTHERS THEN
628 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
629 THEN
630 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
631 , G_MODULE_NAME || l_procedure_name||'. Others exception'
632 , 'Exception'
633 );
634 END IF;
635 END Calc_Cto_Amount_Rma;
636
637 -- ========================================================================
638 -- PROCEDURE : Calc_Cto_Amount_Ar Private
639 -- PARAMETERS: p_order_line_id IN order line id
640 -- x_extended_amount OUT invoice amount
641 -- COMMENT : Procedure to calcualte the invoice amount for CTO items
642 -- ========================================================================
643 PROCEDURE Calc_Cto_Amount_Ar
644 ( p_order_line_id IN NUMBER
645 , p_order_number IN VARCHAR2
646 , x_extended_amount OUT NOCOPY NUMBER
647 )
648 IS
649 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Cto_Amount_Ar';
650
651 --Cursor to calculate invoice amount for ar intercompany invoice for CTO item
652 CURSOR l_cto_amt_ar IS
653 SELECT
654 SUM(ratl.extended_amount)
655 FROM
656 RA_CUSTOMER_TRX_LINES_ALL ratl
657 , oe_order_lines_all oola
658 WHERE ratl.line_type = 'LINE'
659 AND ratl.interface_line_attribute6 = to_char(oola.line_id)
660 AND ratl.sales_order = p_order_number
661 AND oola.top_model_line_id = p_order_line_id
662 AND ratl.interface_line_context = 'INTERCOMPANY';
663 BEGIN
664 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
665 THEN
666 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
667 , G_MODULE_NAME || l_procedure_name || '.begin'
668 ,'enter procedure'
669 );
670 END IF;
671
672 --No need to check cursor not found, because cursor with SUM
673 --function always return true
674 OPEN l_cto_amt_ar;
675 FETCH l_cto_amt_ar INTO
676 x_extended_amount;
677 CLOSE l_cto_amt_ar;
678
679 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
680 THEN
681 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
682 , G_MODULE_NAME || l_procedure_name || '.end'
683 ,'exit procedure'
684 );
685 END IF;
686
687 EXCEPTION
688 WHEN OTHERS THEN
689 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
690 THEN
691 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
692 , G_MODULE_NAME || l_procedure_name||'. Others exception'
693 , 'Exception'
694 );
695 END IF;
696 END Calc_Cto_Amount_Ar;
697
698 -- ========================================================================
699 -- PROCEDURE : Calc_Cto_Amount_Ap Private
700 -- PARAMETERS: p_order_line_id IN order line id
701 -- x_extended_amount OUT invoice amount
702 -- COMMENT : Procedure to calcualte the invoice amount for CTO items
703 -- ========================================================================
704 PROCEDURE Calc_Cto_Amount_Ap
705 ( p_order_line_id IN NUMBER
706 , p_order_number IN VARCHAR2
707 , x_extended_amount OUT NOCOPY NUMBER
708 )
709 IS
710 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Cto_Amount_Ap';
711
715 -- OR rcta.trx_number = aia.invoice_num)'
712 -- Bug 5440432: Following query is modified to replace where clause
713 -- 'AND rcta.trx_number||'-'||rcta.org_id = aia.invoice_num' with
714 -- 'AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
716 -- This change was introduced because of bug 4180686 in 11.5.10
717
718 --Cursor to calculate invoice amount for ap intercompany invoice for CTO item
719 CURSOR l_cto_amt_ap IS
720 SELECT
721 SUM(NVL(aila.amount,rctla.extended_amount))
722 FROM
723 AP_INVOICES_ALL aia
724 , AP_INVOICE_LINES_ALL aila
725 , RA_CUSTOMER_TRX_LINES_ALL rctla
726 , ra_customer_trx_all rcta
727 , oe_order_lines_all oola
728 WHERE aia.invoice_id = aila.invoice_id
729 AND aia.cancelled_date IS NULL
730 AND aila.line_type_lookup_code = 'ITEM'
731 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
732 AND aila.reference_1 = TO_CHAR(rctla.customer_trx_line_id)
733 AND rctla.customer_trx_id = rcta.customer_trx_id
734 AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
735 OR rcta.trx_number = aia.invoice_num)
736 AND rctla.sales_order = p_order_number
737 AND rctla.interface_line_attribute6 = to_char(oola.line_id)
738 AND oola.top_model_line_id = p_order_line_id
739 AND nvl(aila.discarded_flag, 'N') <> 'Y'
740 AND NOT EXISTS (SELECT 'Unreleased holds exist'
741 FROM ap_holds_all aha
742 WHERE aha.invoice_id = aia.invoice_id
743 AND aha.release_lookup_code is null)
744 AND EXISTS (SELECT 'Invoice is approved'
745 FROM ap_invoice_distributions_all aida
746 WHERE aida.invoice_id = aia.invoice_id
747 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
748
749 BEGIN
750 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
751 THEN
752 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
753 , G_MODULE_NAME || l_procedure_name || '.begin'
754 ,'enter procedure'
755 );
756 END IF;
757
758 --No need to check cursor not found, because cursor with SUM
759 --function always return true
760 OPEN l_cto_amt_ap;
761 FETCH l_cto_amt_ap INTO
762 x_extended_amount;
763 CLOSE l_cto_amt_ap;
764
765 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
766 THEN
767 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
768 , G_MODULE_NAME || l_procedure_name || '.end'
769 ,'exit procedure'
770 );
771 END IF;
772
773 EXCEPTION
774 WHEN OTHERS THEN
775 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
776 THEN
777 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
778 , G_MODULE_NAME || l_procedure_name||'. Others exception'
779 , 'Exception'
780 );
781 END IF;
782 END Calc_Cto_Amount_Ap;
783
784
785 -- ========================================================================
786 -- PROCEDURE : Calc_Line_Charge Private
787 -- PARAMETERS: p_line_id IN order line id
788 -- p_invoiced_line_qty IN invoiced quantity for this line
789 -- x_line_freight_charge OUT line level freight charge
790 -- COMMENT : Procedure to calcualte the line level freight charge
791 -- ========================================================================
792 PROCEDURE Calc_Line_Charge
793 ( p_line_id IN NUMBER
794 , p_invoiced_line_qty IN NUMBER
795 , x_line_freight_charge OUT NOCOPY NUMBER
796 )
797 IS
798 l_line_freight_unit_amt NUMBER;
799 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Line_Charge';
800
801 --Find out line level freight charge per unit
802 CURSOR c_adj_line_freight
803 IS
804 SELECT
805 SUM(NVL(ADJUSTED_AMOUNT_PER_PQTY,0))
806 FROM
807 oe_price_adjustments
808 WHERE line_id = p_line_id
809 AND modifier_level_code = 'LINE'
810 AND list_line_type_code = 'FREIGHT_CHARGE';
811
812 BEGIN
813 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
814 THEN
815 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
816 , G_MODULE_NAME || l_procedure_name || '.begin'
817 ,'enter procedure'
818 );
819 END IF;
820
821 OPEN c_adj_line_freight;
822 FETCH c_adj_line_freight INTO l_line_freight_unit_amt;
823 CLOSE c_adj_line_freight;
824
825 --Check null value. Can not use cursor%notfound,because sum
826 --function in cursor select always returns a row (found)
827 IF l_line_freight_unit_amt IS NULL
828 THEN
829 l_line_freight_unit_amt := 0;
830 END IF;
831
832 --Use invoiced quantity to calculate the line level charge, because the
833 --invoiced qty may be different from the ordered quantity
834 x_line_freight_charge := l_line_freight_unit_amt * nvl(p_invoiced_line_qty,0);
835
836 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
837 THEN
838 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
839 , G_MODULE_NAME || l_procedure_name || '.end'
840 ,'exit procedure'
841 );
842 END IF;
843
844 EXCEPTION
845 WHEN OTHERS THEN
846 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
847 THEN
848 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
849 , G_MODULE_NAME || l_procedure_name||'. Others exception'
850 , 'Exception'
851 );
852 END IF;
853 END Calc_Line_Charge;
854
858 -- x_total_line_charge OUT line level charge for all lines
855 -- ========================================================================
856 -- PROCEDURE : Calc_Total_Line_Charge Private
857 -- PARAMETERS: p_movement_transaction IN movement transaction record
859 -- x_total_invoiced_qty OUT total invoiced qty for this order
860 -- COMMENT : Procedure to calcualte total line level charge
861 -- ========================================================================
862 PROCEDURE Calc_Total_Line_Charge
863 ( p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
864 , x_this_line_charge OUT NOCOPY NUMBER
865 , x_total_line_charge OUT NOCOPY NUMBER
866 , x_total_invoiced_qty OUT NOCOPY NUMBER
867 )
868 IS
869 l_line_id NUMBER;
870 l_model_line_id NUMBER;
871 l_item_type_code oe_order_lines_all.item_type_code%TYPE;
872 l_invoiced_line_qty NUMBER;
873 l_total_invoiced_qty NUMBER :=0;
874 l_line_freight_charge NUMBER;
875 l_total_line_charge NUMBER := 0;
876 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Total_Line_Charge';
877
878 --Find out all the order lines which share an invoice with the processing
879 --order line
880 CURSOR c_order_lines IS
881 SELECT DISTINCT
882 TO_NUMBER(interface_line_attribute6)
883 FROM
884 ra_customer_trx_lines_all
885 WHERE sales_order = to_char(p_movement_transaction.order_number)
886 AND line_type = 'LINE'
887 AND customer_trx_id IN
888 (SELECT customer_trx_id
889 FROM ra_customer_trx_lines_all
890 WHERE interface_line_attribute6 = to_char(p_movement_transaction.order_line_id)
891 AND sales_order = to_char(p_movement_transaction.order_number))
892 ORDER BY TO_NUMBER(interface_line_attribute6);
893
894 --Find out if this item is a CTO item
895 CURSOR c_cto IS
896 SELECT
897 item_type_code
898 , top_model_line_id
899 FROM
900 oe_order_lines_all
901 WHERE line_id = p_movement_transaction.order_line_id;
902
903 --Calculate the invoice quantity for an order line
904 CURSOR c_invoiced_line_qty IS
905 SELECT
906 SUM(NVL(ratl.quantity_invoiced,0))
907 FROM
908 RA_CUSTOMER_TRX_ALL rat
909 , RA_CUSTOMER_TRX_LINES_ALL ratl
910 , RA_CUST_TRX_TYPES_ALL ratt
911 WHERE rat.customer_trx_id = ratl.customer_trx_id
912 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
913 AND rat.org_id = ratt.org_id
914 AND ratt.type NOT IN ('CM','DM')
915 AND NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
916 NOT IN (SELECT TO_CHAR(price_adjustment_id)
917 FROM oe_price_adjustments
918 WHERE header_id = p_movement_transaction.order_header_id
919 AND (line_id = l_line_id
920 OR (line_id IS NULL AND modifier_level_code = 'ORDER')))
921 AND ratl.line_type = 'LINE'
922 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
923 AND ratl.sales_order = to_char(p_movement_transaction.order_number)
924 AND ratl.interface_line_attribute6 = l_line_id
925 AND ratl.interface_line_context <> 'INTERCOMPANY';
926 BEGIN
927 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
928 THEN
929 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
930 , G_MODULE_NAME || l_procedure_name || '.begin'
931 ,'enter procedure'
932 );
933 END IF;
934
935 OPEN c_order_lines;
936 LOOP
937 FETCH c_order_lines INTO l_line_id;
938 EXIT WHEN c_order_lines%NOTFOUND;
939
940 --Check if this order line is of CTO item
941 OPEN c_cto;
942 FETCH c_cto INTO
943 l_item_type_code
944 , l_model_line_id;
945 CLOSE c_cto;
946
947 IF l_item_type_code = 'CONFIG'
948 THEN
949 l_line_id := l_model_line_id;
950 END IF;
951
952 --Calculate invoiced qty for this line in the line loop
953 OPEN c_invoiced_line_qty;
954 FETCH c_invoiced_line_qty INTO l_invoiced_line_qty;
955 CLOSE c_invoiced_line_qty;
956
957 IF l_invoiced_line_qty IS NULL
958 THEN
959 l_invoiced_line_qty := 0;
960 END IF;
961
962 --Calculate line charge (using invoiced qty) for this line in
963 --the line loop
964 Calc_Line_Charge
965 ( p_line_id => l_line_id
966 , p_invoiced_line_qty => l_invoiced_line_qty
967 , x_line_freight_charge => l_line_freight_charge
968 );
969
970 --The line charge for the processing line in calling program. This value will
971 --be passed back to calling program so that the calling program does not need
972 --to call calc_line_charge again
973 IF l_line_id = p_movement_transaction.order_line_id
974 THEN
975 x_this_line_charge := l_line_freight_charge;
976 END IF;
977
978 --Total invoiced qty and total line charge
979 l_total_invoiced_qty := l_total_invoiced_qty + l_invoiced_line_qty;
980 l_total_line_charge := l_total_line_charge + l_line_freight_charge;
981 END LOOP;
982 CLOSE c_order_lines;
983
984 x_total_invoiced_qty := l_total_invoiced_qty;
985 x_total_line_charge := l_total_line_charge;
986
987 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
988 THEN
989 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
990 , G_MODULE_NAME || l_procedure_name || '.end'
991 ,'exit procedure'
992 );
993 END IF;
994
995 EXCEPTION
996 WHEN OTHERS THEN
997 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
998 THEN
1002 );
999 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1000 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1001 , 'Exception'
1003 END IF;
1004 END Calc_Total_Line_Charge;
1005
1006 -- ========================================================================
1007 -- PROCEDURE : Calc_Processed_Ret_Data Private
1008 -- PARAMETERS: p_movement_transaction IN movement transaction record
1009 -- x_processed_ret_amt OUT line level charge for all lines
1010 -- x_processed_ret_qty OUT total invoiced qty for this order
1011 -- COMMENT : Procedure to calcualte processed invoice amount and quantity
1012 -- for RTV and RMA
1013 -- ========================================================================
1014 PROCEDURE Calc_Processed_Ret_Data
1015 ( p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1016 , x_processed_ret_amt OUT NOCOPY NUMBER
1017 , x_processed_ret_qty OUT NOCOPY NUMBER
1018 )
1019 IS
1020 l_rtv_count NUMBER;
1021 l_processed_rtv_trans_qty NUMBER;
1022 l_total_rtv_trans_qty NUMBER;
1023 l_rma_count NUMBER;
1024 l_parent_transaction_id NUMBER;
1025 l_rtv_extended_amount NUMBER;
1026 l_rtv_invoice_quantity NUMBER;
1027 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Processed_Ret_Data';
1028
1029 --Cursor to get processed rma count for a given SO
1030 --Fix bug 2861110
1031 CURSOR l_rma IS
1032 SELECT
1033 count(transaction_id)
1034 FROM
1035 rcv_transactions rt
1036 , oe_order_lines_all oola
1037 WHERE rt.oe_order_line_id = oola.line_id
1038 AND oola.reference_line_id = to_char(p_movement_transaction.order_line_id)
1039 AND rt.mvt_stat_status = 'PROCESSED'
1040 AND rt.source_document_code = 'RMA';
1041
1042 --Cursor to get processed rma invoice for a given SO
1043 --Fix bug 2861110
1044 CURSOR l_rma_processed IS
1045 SELECT
1046 SUM(NVL(ratl.extended_amount,0))
1047 , SUM(NVL(ratl.quantity_credited,0))
1048 FROM
1049 RA_CUSTOMER_TRX_ALL rat
1050 , RA_CUSTOMER_TRX_LINES_ALL ratl
1051 , RA_CUST_TRX_TYPES_ALL ratt
1052 , OE_ORDER_LINES_ALL oola
1053 , RCV_TRANSACTIONS rt
1054 WHERE rat.customer_trx_id = ratl.customer_trx_id
1055 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
1056 AND rat.org_id = ratt.org_id
1057 AND ratt.type IN ('CM','DM')
1058 AND ratl.line_type = 'LINE' --yawang
1059 AND ratl.quantity_credited IS NOT NULL
1060 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
1061 -- AND NVL(ratl.org_id,0) = NVL(p_movement_transaction.org_id,0)
1062 AND ratl.interface_line_attribute6 = to_char(oola.line_id)
1063 AND oola.reference_line_id = to_char(p_movement_transaction.order_line_id)
1064 AND rt.oe_order_line_id = oola.line_id
1065 AND rt.mvt_stat_status = 'PROCESSED'
1066 AND rt.transaction_type = 'DELIVER'
1067 AND ratl.interface_line_context <> 'INTERCOMPANY'
1068 AND oola.line_id NOT IN (SELECT order_line_id
1069 FROM mtl_movement_statistics
1070 WHERE entity_org_id = p_movement_transaction.entity_org_id
1071 AND zone_code = p_movement_transaction.zone_code
1072 AND usage_type = p_movement_transaction.usage_type
1073 AND stat_type = p_movement_transaction.stat_type
1074 AND document_source_type = 'RMA'
1075 AND rcv_transaction_id = rt.transaction_id);
1076
1077 --Cursor to check if this PO receipt has any processed RTV
1078 --Fix bug 2861110
1079 CURSOR l_rtv IS
1080 SELECT
1081 COUNT(transaction_id)
1082 FROM
1083 rcv_transactions
1084 WHERE transaction_type = 'RETURN TO VENDOR'
1085 AND parent_transaction_id = p_movement_transaction.rcv_transaction_id
1086 AND mvt_stat_status = 'PROCESSED';
1087
1088 --Cursor to get total rtv transaction quantity for Italian LE
1089 --fix bug 2861110
1090 CURSOR l_total_rtv_quantity IS
1091 SELECT
1092 SUM(quantity)
1093 FROM
1094 rcv_transactions
1095 WHERE po_header_id = p_movement_transaction.po_header_id
1096 AND transaction_type = 'RETURN TO VENDOR';
1097
1098 --Cursor to get processed rtv transaction quantity exclude
1099 --those RTV that created in next period, which will create
1100 --seperate MS record with its own credit memo as invoice info
1101 CURSOR l_netted_rtv_quantity IS
1102 SELECT
1103 SUM(quantity)
1104 FROM
1105 rcv_transactions rt
1106 WHERE po_header_id = p_movement_transaction.po_header_id
1107 AND transaction_type = 'RETURN TO VENDOR'
1108 AND mvt_stat_status = 'PROCESSED'
1109 AND transaction_id NOT IN (SELECT rcv_transaction_id
1110 FROM mtl_movement_statistics
1111 WHERE document_source_type = 'RTV'
1112 AND po_header_id = rt.po_header_id
1113 AND entity_org_id = p_movement_transaction.entity_org_id
1114 AND zone_code = p_movement_transaction.zone_code
1115 AND usage_type = p_movement_transaction.usage_type
1116 AND stat_type = p_movement_transaction.stat_type);
1117
1118 --Cursor for Credit memos that is associated with RTV transaction
1119 --in case of receipt based matching
1120 -- Bug 5655040.Cursor has been modified to AP Line tables in place of Distributions
1121 --CURSOR l_rtv_cm_receipt_based IS
1122 ---SELECT
1126 -- AP_INVOICES_ALL api,
1123 -- SUM(apid.amount)
1124 --, SUM(apid.quantity_invoiced)
1125 --FROM
1127 -- AP_INVOICE_DISTRIBUTIONS_ALL apid
1128 --WHERE api.invoice_id = apid.invoice_id
1129 --AND api.invoice_type_lookup_code in ('CREDIT','DEBIT')
1130 --AND apid.rcv_transaction_id = l_parent_transaction_id
1131 --AND apid.line_type_lookup_code = 'ITEM' --yawang, limit for good cost only
1132 --AND NVL(apid.quantity_invoiced,0) < 0
1133 --AND api.cancelled_date IS NULL
1134 --AND (NVL(apid.match_status_flag,'N') = 'A'
1135 -- OR (NVL(apid.match_status_flag,'N') = 'T'
1136 -- AND api.wfapproval_status = 'NOT REQUIRED'));
1137
1138 CURSOR l_rtv_cm_receipt_based IS
1139 SELECT
1140 SUM(aila.amount)
1141 , SUM(aila.quantity_invoiced)
1142 FROM
1143 AP_INVOICES_ALL aia,
1144 AP_INVOICE_LINES_ALL aila
1145 WHERE aia.invoice_id = aila.invoice_id
1146 AND aia.invoice_type_lookup_code in ('CREDIT','DEBIT')
1147 AND aila.rcv_transaction_id = l_parent_transaction_id
1148 AND aila.line_type_lookup_code = 'ITEM'
1149 AND NVL(aila.quantity_invoiced,0) < 0
1150 AND aia.cancelled_date IS NULL
1151 AND nvl(aila.discarded_flag, 'N') <> 'Y'
1152 AND NOT EXISTS (SELECT 'Unreleased holds exist'
1153 FROM ap_holds_all aha
1154 WHERE aha.invoice_id = aia.invoice_id
1155 AND aha.release_lookup_code is null)
1156 AND EXISTS (SELECT 'Invoice is approved'
1157 FROM ap_invoice_distributions_all aida
1158 WHERE aida.invoice_id = aia.invoice_id
1159 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
1160
1161 -- Cursor for RTV's where credit memo is to be associated with
1162 -- RTV in case of PO based matching.
1163 -- Bug 5655040.Cursor has been modified to AP Line tables in place of Distributions
1164 --CURSOR l_rtv_cm_po_based IS
1165 --SELECT
1166 -- SUM(a.amount) --yawang
1167 --, SUM(quantity_invoiced)
1168 --FROM
1169 -- PO_HEADERS_ALL b
1170 --, PO_DISTRIBUTIONS_ALL c
1171 --, AP_INVOICES_ALL d
1172 --, AP_INVOICE_DISTRIBUTIONS_ALL a
1173 --WHERE b.po_header_id = c.po_header_id
1174 --AND d.invoice_id = a.invoice_id
1175 --AND c.po_distribution_id = a.po_distribution_id
1176 --AND (NVL(a.match_status_flag,'N') = 'A'
1177 -- OR (NVL(a.match_status_flag,'N') = 'T'
1178 -- AND d.wfapproval_status = 'NOT REQUIRED'))
1179 --AND d.invoice_type_lookup_code in ('CREDIT','DEBIT')
1180 --AND d.cancelled_date IS NULL
1181 --AND a.line_type_lookup_code = 'ITEM' --yawang, limit for good cost only
1182 --AND NVL(a.quantity_invoiced,0) < 0
1183 --AND b.po_header_id = p_movement_transaction.po_header_id
1184 --AND c.line_location_id = p_movement_transaction.po_line_location_id;
1185 CURSOR l_rtv_cm_po_based IS
1186 SELECT
1187 SUM(aila.amount) --yawang
1188 , SUM(aila.quantity_invoiced)
1189 FROM
1190 PO_HEADERS_ALL pha
1191 , PO_DISTRIBUTIONS_ALL pda
1192 , AP_INVOICES_ALL aia
1193 , AP_INVOICE_LINES_ALL aila
1194 WHERE pha.po_header_id = pda.po_header_id
1195 AND aia.invoice_id = aila.invoice_id
1196 AND pda.po_header_id = aila.po_header_id /*Bug 7446311 Joined to imporve performance*/
1197 AND pda.po_distribution_id = aila.po_distribution_id
1198 AND aia.invoice_type_lookup_code in ('CREDIT','DEBIT')
1199 AND aia.cancelled_date IS NULL
1200 AND aila.line_type_lookup_code = 'ITEM' --yawang, limit for good cost only
1201 AND NVL(aila.quantity_invoiced,0) < 0
1202 AND pha.po_header_id = p_movement_transaction.po_header_id
1203 AND pda.line_location_id = p_movement_transaction.po_line_location_id
1204 AND nvl(aila.discarded_flag, 'N') <> 'Y'
1205 AND NOT EXISTS (SELECT 'Unreleased holds exist'
1206 FROM ap_holds_all aha
1207 WHERE aha.invoice_id = aia.invoice_id
1208 AND aha.release_lookup_code is null)
1209 AND EXISTS (SELECT 'Invoice is approved'
1210 FROM ap_invoice_distributions_all aida
1211 WHERE aida.invoice_id = aia.invoice_id
1212 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
1213
1214 BEGIN
1215 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1216 THEN
1217 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1218 , G_MODULE_NAME || l_procedure_name || '.begin'
1219 ,'enter procedure'
1220 );
1221 END IF;
1222
1223 IF p_movement_transaction.document_source_type = 'SO'
1224 THEN
1225 OPEN l_rma;
1226 FETCH l_rma INTO
1227 l_rma_count;
1228 CLOSE l_rma;
1229
1230 IF l_rma_count > 0
1231 THEN
1232 OPEN l_rma_processed;
1233 FETCH l_rma_processed INTO
1234 x_processed_ret_amt
1235 , x_processed_ret_qty;
1236 CLOSE l_rma_processed;
1237 END IF;
1238 ELSIF p_movement_transaction.document_source_type = 'PO'
1239 THEN
1240 OPEN l_rtv;
1241 FETCH l_rtv INTO
1242 l_rtv_count;
1243 CLOSE l_rtv;
1244
1245 IF l_rtv_count > 0
1246 THEN
1247 --RTV is child of PO. To find RTV credit memo, needs to know the
1248 --parent id of the rtv. Parent id of rtv is the rcv transaction id of
1249 --current PO, which is used in receipt based matching
1250 l_parent_transaction_id := p_movement_transaction.rcv_transaction_id;
1251
1252 --Open receipt based matching rtv credit memo cursor
1253 OPEN l_rtv_cm_receipt_based;
1254 FETCH l_rtv_cm_receipt_based INTO
1255 l_rtv_extended_amount
1256 , l_rtv_invoice_quantity;
1257
1258 --If not receipt based credit memo, open po based credit memo for rtv
1259 IF l_rtv_cm_receipt_based%NOTFOUND OR l_rtv_extended_amount IS NULL
1260 THEN
1261 OPEN l_rtv_cm_po_based;
1262 FETCH l_rtv_cm_po_based INTO
1263 l_rtv_extended_amount
1264 , l_rtv_invoice_quantity;
1265
1266 IF l_rtv_cm_po_based%NOTFOUND OR l_rtv_extended_amount IS NULL
1267 THEN
1268 l_rtv_extended_amount := 0;
1269 l_rtv_invoice_quantity := 0;
1270 END IF;
1271
1272 CLOSE l_rtv_cm_po_based;
1273 END IF;
1274 CLOSE l_rtv_cm_receipt_based;
1275
1276 --Find total rtv transaction quantity
1277 OPEN l_total_rtv_quantity;
1278 FETCH l_total_rtv_quantity INTO
1279 l_total_rtv_trans_qty;
1280 CLOSE l_total_rtv_quantity;
1281
1282 --Find netted rtv transaction quantity
1283 OPEN l_netted_rtv_quantity;
1284 FETCH l_netted_rtv_quantity INTO
1285 l_processed_rtv_trans_qty;
1286 CLOSE l_netted_rtv_quantity;
1287
1288 --Find processed rtv invoice amount and invoice quantity
1289 IF l_total_rtv_trans_qty IS NOT NULL
1290 THEN
1291 x_processed_ret_amt := (l_processed_rtv_trans_qty/l_total_rtv_trans_qty)
1292 * l_rtv_extended_amount;
1293 x_processed_ret_qty := (l_processed_rtv_trans_qty/l_total_rtv_trans_qty)
1294 * l_rtv_invoice_quantity;
1295 END IF;
1296 END IF;
1297 END IF;
1298
1299 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1300 THEN
1301 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1302 , G_MODULE_NAME || l_procedure_name || '.end'
1303 ,'exit procedure'
1304 );
1305 END IF;
1306
1307 EXCEPTION
1308 WHEN OTHERS THEN
1309 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1310 THEN
1311 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1312 , G_MODULE_NAME || l_procedure_name||'. Others exception'
1313 , 'Exception'
1314 );
1315 END IF;
1316 END Calc_Processed_Ret_Data;
1317
1318
1319 --====================
1320 --Public procedure
1321 --====================
1322
1323 --========================================================================
1324 -- PROCEDURE : Exchange_Rate_Calc PUBLIC
1325 -- PARAMETERS:
1326 -- p_stat_typ_transaction mtl_stat_type_usages data record
1327 -- p_movement_transaction movement transaction data record
1328 -- COMMENT : This function returns the exchange rate based on
1329 -- the conversion date that is set up in the
1330 -- statistical type info form.
1331 --=======================================================================
1332
1333 PROCEDURE Calc_Exchange_Rate
1334 ( x_movement_transaction IN OUT NOCOPY
1335 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1336 , p_stat_typ_transaction IN
1337 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
1338 )
1339 IS
1340 l_gl_set_of_books_id VARCHAR2(15);
1341 l_last_dayofperiod DATE;
1342 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Exchange_Rate';
1343
1344 BEGIN
1345 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1346 THEN
1347 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1348 , G_MODULE_NAME || l_procedure_name || '.begin'
1349 ,'enter procedure'
1350 );
1351 END IF;
1352
1353 --Get the end date of the period
1354 SELECT end_date
1355 INTO
1356 l_last_dayofperiod
1357 FROM
1358 GL_PERIODS
1359 WHERE period_name = x_movement_transaction.period_name
1360 AND period_set_name = p_stat_typ_transaction.period_set_name
1361 AND period_type = p_stat_typ_transaction.period_type;
1362
1363 IF UPPER(p_stat_typ_transaction.conversion_option) = 'CO_LAST_DAY'
1364 THEN
1365 x_movement_transaction.currency_conversion_rate := GL_CURRENCY_API.Get_Rate
1366 ( x_from_currency => x_movement_transaction.currency_code
1367 , x_to_currency => x_movement_transaction.gl_currency_code
1368 , x_conversion_date => l_last_dayofperiod
1369 , x_conversion_type => x_movement_transaction.currency_conversion_type
1370 );
1371
1372 x_movement_transaction.currency_conversion_date := l_last_dayofperiod;
1373 ELSE
1374 x_movement_transaction.currency_conversion_date :=
1375 NVL(x_movement_transaction.invoice_date_reference, x_movement_transaction.transaction_date);
1376
1377 x_movement_transaction.currency_conversion_rate := GL_CURRENCY_API.Get_Rate
1378 ( x_from_currency => x_movement_transaction.currency_code
1382 );
1379 , x_to_currency => x_movement_transaction.gl_currency_code
1380 , x_conversion_date => x_movement_transaction.currency_conversion_date
1381 , x_conversion_type => x_movement_transaction.currency_conversion_type
1383 END IF;
1384
1385 IF x_movement_transaction.currency_conversion_rate IS NULL
1386 THEN
1387 x_movement_transaction.currency_conversion_rate := 1;
1388 x_movement_transaction.currency_conversion_type := null;
1389 x_movement_transaction.currency_conversion_date := null;
1390 END IF;
1391
1392 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1393 THEN
1394 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1395 , G_MODULE_NAME || l_procedure_name || '.end'
1396 ,'exit procedure'
1397 );
1398 END IF;
1399 EXCEPTION
1400 WHEN GL_CURRENCY_API.no_rate
1401 THEN
1402 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1403 THEN
1404 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1405 , G_MODULE_NAME || l_procedure_name||'.No rate exception'
1406 , 'Exception'
1407 );
1408 END IF;
1409 x_movement_transaction.currency_conversion_rate := 1;
1410 x_movement_transaction.currency_conversion_type := null;
1411 x_movement_transaction.currency_conversion_date := null;
1412 WHEN NO_DATA_FOUND
1413 THEN
1414 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1415 THEN
1416 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1417 , G_MODULE_NAME || l_procedure_name||'.No data found exception'
1418 , 'Exception'
1419 );
1420 END IF;
1421 x_movement_transaction.currency_conversion_rate := 1;
1422 x_movement_transaction.currency_conversion_type := null;
1423 x_movement_transaction.currency_conversion_date := null;
1424 WHEN OTHERS
1425 THEN
1426 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1427 THEN
1428 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1429 , G_MODULE_NAME || l_procedure_name||'.Others exception'
1430 , 'Exception'
1431 );
1432 END IF;
1433 x_movement_transaction.currency_conversion_rate := 1;
1434 x_movement_transaction.currency_conversion_type := null;
1435 x_movement_transaction.currency_conversion_date := null;
1436 END Calc_Exchange_Rate;
1437
1438 --========================================================================
1439 -- FUNCTION : Calc_Movement_AMount PUBLIC
1440 -- PARAMETERS:
1441 -- p_movement_transaction movement transaction data record
1442 -- COMMENT : Calculates and returns the Movement Amount value
1443 --=======================================================================
1444
1445 FUNCTION Calc_Movement_Amount
1446 ( p_movement_transaction IN
1447 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1448 )
1449 RETURN NUMBER
1450 IS
1451 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1452 l_report_price NUMBER;
1453 l_tr_value NUMBER;
1454 l_inv_uom VARCHAR2(10);
1455 l_trans_conv_inv_rate NUMBER;
1456 l_inv_qty NUMBER;
1457 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Movement_Amount';
1458 l_invoice_currency VARCHAR2(10);
1459 l_currency_conversion_rate NUMBER;
1460
1461 CURSOR inv_uom IS
1462 SELECT
1463 uom_code
1464 FROM
1465 ra_customer_trx_lines_all
1466 WHERE customer_trx_line_id = l_movement_transaction.customer_trx_line_id;
1467
1468 /* Bug 6158521 - Start */
1469 /* Define cursor to the invoice UOM for AP invoices */
1470 CURSOR poinv_uom IS
1471 SELECT
1472 UOM.UOM_CODE
1473 FROM
1474 AP_INVOICE_DISTRIBUTIONS_ALL AID,MTL_UNITS_OF_MEASURE UOM
1475 WHERE AID.invoice_id = l_movement_transaction.invoice_id
1476 AND AID.distribution_line_number = l_movement_transaction.distribution_line_number
1477 AND AID.MATCHED_UOM_LOOKUP_CODE=UOM.UNIT_OF_MEASURE;
1478
1479 /* Bug 6158521 - End */
1480
1481 BEGIN
1482
1483 l_currency_conversion_rate := 1; -- 6889669
1484 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1485 THEN
1486 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1487 , G_MODULE_NAME || l_procedure_name || '.begin'
1488 ,'enter procedure'
1489 );
1490 END IF;
1491
1492 l_movement_transaction := p_movement_transaction;
1493
1494 --Debug: Part of bugfix 12635623
1495 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1496 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1497 , G_MODULE_NAME || l_procedure_name
1498 ,'document_source_type:' || l_movement_transaction.document_source_type ||
1499 ':invoice_id:' || l_movement_transaction.invoice_id ||
1500 ':customer_trx_line_id:' || l_movement_transaction.customer_trx_line_id ||
1501 ':distribution_line_number:' || l_movement_transaction.distribution_line_number
1502 );
1503 END IF;
1504
1505 -----------------------------------------------
1506 -- INVOICE_ID is not null --
1507 -----------------------------------------------
1508 IF l_movement_transaction.invoice_id is not null
1509 AND (l_movement_transaction.document_source_type IN ('SO','RMA', 'IO') -- 6889669
1510 OR l_movement_transaction.document_source_type IN ('PO','RTV'))
1511 THEN
1512 --Invoice quantity may have different uom than transaction qty
1513 --convert invoice qty to same uom as transaction qty
1514 --This is for SO only
1515 IF (l_movement_transaction.document_source_type IN ('SO','RMA')
1516 AND l_movement_transaction.customer_trx_line_id IS NOT NULL)
1517 THEN
1518 OPEN inv_uom;
1519 FETCH inv_uom INTO l_inv_uom;
1520 IF inv_uom%NOTFOUND
1521 THEN
1522 l_inv_uom := l_movement_transaction.transaction_uom_code;
1523 END IF;
1524 CLOSE inv_uom;
1525
1526 /* Bug 6158521 - Start */
1527 /* Call INV UOM conversion if invoice UOM is different from txn UOM */
1528 ELSE
1529 IF (l_movement_transaction.distribution_line_number IS NOT NULL)
1530 THEN
1531 OPEN poinv_uom;
1532 FETCH poinv_uom INTO l_inv_uom;
1533 IF poinv_uom%NOTFOUND
1534 Then
1535 l_inv_uom := l_movement_transaction.transaction_uom_code;
1536 END IF;
1537 CLOSE poinv_uom;
1538 ELSE
1539 l_inv_uom := l_movement_transaction.transaction_uom_code;
1540 END IF;
1541 END IF;
1542
1543 IF l_movement_transaction.transaction_uom_code <> l_inv_uom
1544 THEN
1545 INV_CONVERT.Inv_Um_Conversion
1546 ( from_unit => l_movement_transaction.transaction_uom_code
1547 , to_unit => l_inv_uom
1548 , item_id => l_movement_transaction.inventory_item_id
1549 , uom_rate => l_trans_conv_inv_rate
1550 );
1551 ELSE
1552 l_trans_conv_inv_rate := 1;
1553 END IF;
1554
1555 /* Special case for Internal Orders - 6889669 - BEGIN */
1556 IF (l_movement_transaction.document_source_type = 'IO')
1557 THEN
1558 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1559 THEN
1560 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1561 , G_MODULE_NAME || l_procedure_name
1562 ,'in Calc Movement Amount for IO'
1563 );
1564 END IF;
1565
1566 /* Invoice conversion for I/C AP - 6889669 - Start*/
1567 /* For an IO Arrival, ensure that the invoice currency is the
1568 same as the SOB currency, if not do the necessary conversion
1569 Note: This does NOT have to be done for IO dispatch as the currency
1570 is pulled from the shipping transaction whereas for an IO arrival,
1571 the currency is pulled from the Receiving transactions */
1572
1573 IF (l_movement_transaction.movement_type = 'A') THEN
1574 BEGIN
1575 SELECT
1576 NVL(invoice_currency_code, -1)
1577 INTO
1578 l_invoice_currency
1579 FROM
1580 AP_INVOICES_ALL
1581 WHERE
1582 invoice_id = l_movement_transaction.invoice_id ;
1583
1584 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1585 THEN
1586 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1587 , G_MODULE_NAME || l_procedure_name
1588 ,'The I/C AP invoice currency is '||l_invoice_currency
1589 );
1590 END IF;
1591
1592 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1593 THEN
1594 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1595 , G_MODULE_NAME || l_procedure_name
1596 ,'The GL currency code is '||l_movement_transaction.gl_currency_code
1597 );
1598 END IF;
1599
1600 IF (l_invoice_currency <> l_movement_transaction.gl_currency_code) THEN
1601 l_currency_conversion_rate := Get_Conversion_Rate(l_invoice_currency,l_movement_transaction );
1602 END IF;
1603 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1604 THEN
1605 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1606 , G_MODULE_NAME || l_procedure_name
1607 ,'The currency conversion is '||l_currency_conversion_rate
1608 );
1609 END IF;
1610
1611 EXCEPTION
1612 WHEN NO_DATA_FOUND THEN
1613 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1614 THEN
1615 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1616 , G_MODULE_NAME || l_procedure_name
1617 ,'No data found for Internal Order I/C AP'
1618 );
1619 END IF;
1620 WHEN OTHERS THEN
1621 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1622 THEN
1623 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1624 , G_MODULE_NAME || l_procedure_name
1625 ,'Others exception for Internal Order I/C AP'
1626 );
1627 END IF;
1628 END;
1629 END IF ;
1630
1631 /* Invoice conversion for I/C AP - 6889669 - End*/
1632
1633 OPEN inv_uom;
1634 FETCH inv_uom INTO l_inv_uom;
1635 IF inv_uom%NOTFOUND
1636 THEN
1637 l_inv_uom := l_movement_transaction.transaction_uom_code;
1638 END IF;
1639 CLOSE inv_uom;
1640 -- Bug 7833114
1641 -- Removed else condition as it is reverting Invoice_Uom to transation_uom if it is not IO
1642 -- skolluku
1643 --ELSE
1644 -- l_inv_uom := l_movement_transaction.transaction_uom_code;
1645 END IF;
1646
1647 IF l_movement_transaction.transaction_uom_code <> l_inv_uom
1648 THEN
1649 INV_CONVERT.Inv_Um_Conversion
1650 ( from_unit => l_movement_transaction.transaction_uom_code
1651 , to_unit => l_inv_uom
1652 , item_id => l_movement_transaction.inventory_item_id
1653 , uom_rate => l_trans_conv_inv_rate
1654 );
1655 ELSE
1656 l_trans_conv_inv_rate := 1;
1657 END IF;
1658 /* Special case for Internal Orders - 6889669 - BEGIN */
1659
1660 l_inv_qty := ROUND(l_movement_transaction.invoice_quantity / l_trans_conv_inv_rate,2);
1661
1662 --Debug: Part of bugfix 12635623
1663 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1664 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1665 , G_MODULE_NAME || l_procedure_name
1666 ,'invoice_quantity:' || l_movement_transaction.invoice_quantity ||
1667 ':l_trans_conv_inv_rate:' || l_trans_conv_inv_rate ||
1668 ':l_inv_qty:' || l_inv_qty ||
1669 ':transaction_quantity:' || l_movement_transaction.transaction_quantity ||
1670 ':document_unit_price:' || l_movement_transaction.document_unit_price ||
1671 ':outside_unit_price:' || l_movement_transaction.outside_unit_price ||
1672 ':invoice_line_ext_value:' || l_movement_transaction.invoice_line_ext_value
1673 );
1674 END IF;
1675
1676 --transaction quantity maybe negative for Italian RTV and RMA
1677 --when this procedurre is called in update_invoice. bug 2861110
1678 IF abs(l_inv_qty) = abs(l_movement_transaction.transaction_quantity)
1679 THEN
1680 l_tr_value := l_movement_transaction.invoice_line_ext_value;
1681
1682 --Debug: Part of bugfix 12635623
1683 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1684 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1685 , G_MODULE_NAME || l_procedure_name
1686 ,'l_tr_value1:' || l_tr_value
1687 );
1688 END IF;
1689
1690 ELSE
1691 IF abs(l_inv_qty) < abs(l_movement_transaction.transaction_quantity)
1692 THEN
1693 l_report_price := NVL(l_movement_transaction.document_unit_price,0)
1694 + NVL(l_movement_transaction.outside_unit_price,0);
1695
1696 l_tr_value := (abs(l_movement_transaction.transaction_quantity)
1697 - abs(NVL(l_inv_qty,0))) * l_report_price
1698 +abs(l_movement_transaction.invoice_line_ext_value);
1699
1700 --Debug: Part of bugfix 12635623
1701 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1702 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1703 , G_MODULE_NAME || l_procedure_name
1704 ,'l_report_price:' || l_report_price ||
1705 ':l_tr_value2:' || l_tr_value
1706 );
1707 END IF;
1708
1709 ELSE
1710 --Bug 3665762, in the case of update invoice, the movement amount should not
1711 --be updated to 0 for dropshipment, set the movement amount to invoice value
1712 IF (l_movement_transaction.transaction_nature = '17'
1713 AND l_movement_transaction.transaction_quantity = 0)
1714 THEN
1715 l_tr_value := l_movement_transaction.invoice_line_ext_value;
1716 ELSE
1717 l_tr_value := abs(l_movement_transaction.transaction_quantity)
1718 * l_movement_transaction.invoice_unit_price * l_trans_conv_inv_rate;
1719 END IF;
1720 END IF;
1721
1722 /*
1723 IF (l_movement_transaction.document_source_type='RTV') THEN
1724 IF (abs(l_movement_transaction.invoice_quantity)) =
1725 (abs(l_movement_transaction.transaction_quantity)) THEN
1726 l_tr_value := l_movement_transaction.invoice_line_ext_value;
1727 END IF;
1728 END IF;
1729 */
1730
1731 END IF;
1732 --------------------------------------------------
1733 -- INVOICE_ID is null --
1734 --------------------------------------------------
1735 ELSE
1736 IF abs(NVL(l_movement_transaction.invoice_quantity,0)) =
1737 abs(l_movement_transaction.transaction_quantity)
1738 THEN
1739 l_tr_value := l_movement_transaction.document_line_ext_value;
1740
1741 --Debug: Part of bugfix 12635623
1742 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1743 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1744 , G_MODULE_NAME || l_procedure_name
1745 ,'l_tr_value3:' || l_tr_value
1746 );
1747 END IF;
1748
1749 ELSE
1750 -- if DOCUMENT_SOURCE_TYPE is a Sales Order
1751 -- or DOCUMENT_SOURCE_TYPE is a Purchase Order
1752 -- or DOCUMENT_SOURCE_TYPE is a Return of Merchandize
1753 -- Authorization
1754 -- or DOCUMENT_SOURCE_TYPE is miscellaneous
1755 -- with a price not equal to 0
1756
1757 --Debug: Part of bugfix 12635623
1758 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1759 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1760 , G_MODULE_NAME || l_procedure_name
1761 ,'invoice_quantity:' || l_movement_transaction.invoice_quantity ||
1762 ':l_trans_conv_inv_rate:' || l_trans_conv_inv_rate ||
1763 ':l_inv_qty:' || l_inv_qty ||
1764 ':transaction_quantity:' || l_movement_transaction.transaction_quantity ||
1765 ':document_unit_price:' || l_movement_transaction.document_unit_price ||
1766 ':outside_unit_price:' || l_movement_transaction.outside_unit_price ||
1767 ':invoice_line_ext_value:' || l_movement_transaction.invoice_line_ext_value
1768 );
1769 END IF;
1770
1771 IF l_movement_transaction.document_source_type IN ('SO', 'IO') -- 6889669
1772 OR l_movement_transaction.document_source_type = 'PO'
1773 OR l_movement_transaction.document_source_type = 'RTV'
1774 OR l_movement_transaction.document_source_type = 'RMA'
1775 OR (l_movement_transaction.document_source_type = 'MISC'
1776 AND NVL(l_movement_transaction.document_unit_price,0) <> 0)
1777 THEN
1778 l_report_price := NVL(l_movement_transaction.document_unit_price,0)
1779 + NVL(l_movement_transaction.outside_unit_price,0);
1780 ELSE
1781 l_report_price := NVL(l_movement_transaction.item_cost,0)
1782 + NVL(l_movement_transaction.outside_unit_price,0);
1783 END IF;
1784
1785 l_tr_value := (abs(l_movement_transaction.transaction_quantity)
1786 - abs(NVL(l_movement_transaction.invoice_quantity,0)))
1787 * l_report_price;
1788
1789 --Debug: Part of bugfix 12635623
1790 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1791 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1792 , G_MODULE_NAME || l_procedure_name
1793 ,'l_tr_value4:' || l_tr_value ||
1794 ':l_report_price:' || l_report_price
1795 );
1796 END IF;
1797
1798 END IF;
1799 END IF;
1800
1801 /* Special case for 6889669 */
1802 IF (l_movement_transaction.document_source_type = 'IO') AND (l_movement_transaction.movement_type = 'A')
1803 AND (nvl(l_currency_conversion_rate, 1)<>1) THEN
1804 l_tr_value := round (l_tr_value * l_currency_conversion_rate, 2);
1805 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1806 THEN
1807 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1808 , G_MODULE_NAME || l_procedure_name
1809 ,'l_tr_value for IO::Inside If:'||l_tr_value
1810 );
1811 END IF;
1812 ELSE
1813 l_tr_value := round (l_tr_value * NVL(l_movement_transaction.currency_conversion_rate,1),2);
1814
1815 --Debug: Part of bugfix 12635623
1816 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1817 THEN
1818 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1819 , G_MODULE_NAME || l_procedure_name
1820 ,'l_tr_value for IO::Inside Else:'||l_tr_value
1821 );
1822 END IF;
1823
1824 END IF ;
1825
1826
1827 --RTV and RMA are special (AA and DA) in Italian,the movement amount in
1828 --Italian should be negative.If this procedure is called in update_invoice,the
1829 --movement type is already set to AA and DA, so we can use this criteria
1830 --to make movement amount negative,therwise always make it positive,for Italian
1831 --RTV and RMA,it will be set to negative later.bug2861110
1832 IF ((l_movement_transaction.document_source_type = 'RTV'
1833 AND NVL(l_movement_transaction.movement_type,'D') = 'AA')
1834 OR (l_movement_transaction.document_source_type = 'RMA'
1835 AND NVL(l_movement_transaction.movement_type,'A') = 'DA'))
1836 THEN
1837 l_tr_value := 0 - abs(l_tr_value);
1838 ELSE
1839 l_tr_value := abs(l_tr_value);
1840 END IF;
1841
1842 --Debug: Part of bugfix 12635623
1843 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1844 THEN
1845 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1846 , G_MODULE_NAME || l_procedure_name
1847 ,'in Calc Movement Amount is:'||l_tr_value
1848 );
1849 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1850 , G_MODULE_NAME || l_procedure_name || '.end'
1851 ,'exit procedure'
1852 );
1853 END IF;
1854
1855 RETURN(l_tr_value);
1856
1857 EXCEPTION
1858 WHEN NO_DATA_FOUND THEN
1859 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1860 THEN
1861 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1862 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
1863 , 'Exception'
1864 );
1865 END IF;
1866 RETURN(0);
1867 WHEN OTHERS THEN
1868 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1869 THEN
1870 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1871 , G_MODULE_NAME || l_procedure_name||'. Others exception:' || sqlerrm
1872 , 'Exception'
1873 );
1874 END IF;
1875 RETURN(0);
1876
1877 END Calc_Movement_Amount;
1878
1879 --========================================================================
1880 -- FUNCTION : Calc_Statistics_Value PUBLIC
1881 -- PARAMETERS:
1882 -- p_movement_transaction movement transaction data record
1883 -- VERSION : current version 1.0
1884 -- initial version 1.0
1885 -- COMMENT : Calculates and returns the Statistics value
1886 --=======================================================================
1887
1888 FUNCTION Calc_Statistics_Value
1889 ( p_movement_transaction IN
1890 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1891 )
1892 RETURN NUMBER
1893 IS
1894 l_stat_value NUMBER;
1895 l_inv_total_freight_amt NUMBER;
1896 l_order_freight_charge NUMBER;
1897 l_order_freight_charge_to_line NUMBER;
1898 l_line_freight_charge NUMBER;
1899 l_freight_charge NUMBER;
1900 --l_invoiced_line_qty NUMBER := p_movement_transaction.invoice_quantity;
1901 l_movement_amount NUMBER;
1902
1903 --Stat adjusted amount needs to be kept if this procedure is called
1904 --after updating invoice and the stat adj amt is not null (entered
1905 --through Movement Statistics form)
1906 l_stat_adj_amount NUMBER;
1907
1908 l_total_line_charge NUMBER;
1909 l_total_invoiced_qty NUMBER;
1910 l_stat_ext_value NUMBER;
1911 l_conversion_rate NUMBER;
1912 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Statistics_Value';
1913
1914 --Total freight amount on the invoices for this order line
1915 CURSOR c_inv_freight_amt
1916 IS
1917 SELECT
1918 SUM(extended_amount)
1919 FROM
1920 ra_customer_trx_lines_all
1921 WHERE (sales_order = to_char(p_movement_transaction.order_number)
1922 OR sales_order IS NULL) --for manual invoice
1923 AND line_type = 'FREIGHT'
1924 AND customer_trx_id IN
1925 (SELECT customer_trx_id
1926 FROM ra_customer_trx_lines_all
1927 WHERE interface_line_attribute6 = to_char(p_movement_transaction.order_line_id)
1928 AND sales_order = to_char(p_movement_transaction.order_number));
1929
1930 BEGIN
1931 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1932 THEN
1933 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1934 , G_MODULE_NAME || l_procedure_name || '.begin'
1935 ,'enter procedure'
1936 );
1937 END IF;
1938
1939 --Initialize variables
1940 l_movement_amount := p_movement_transaction.movement_amount;
1941 l_stat_adj_amount := NVL(p_movement_transaction.stat_adj_amount,0);
1942 l_conversion_rate := p_movement_transaction.currency_conversion_rate;
1943
1944 --Statistics value should be same as movement amount in most cases, however
1945 --there are cases where the two values are different. For example when
1946 --freight charge is included in the same SO invoice, we need to include that
1947 --freight charge into statistics value to do Intrastat report
1948
1949 --The following calcualtion trys to cover manual invoice case too. But it's
1950 --very compilicated in manual case. There could be freight charge on manual
1951 --invoice different from that in price adjustment table. There could be
1952 --invoiced quantity different from ordered quantity. There could be multiple
1953 --shipments with one invoice or one shipment transaction with multiple invoices
1954 --and each of the invoice has freight charge. There could be credit memo with
1955 --freight charge on it......
1956
1957 --Only calculate freight charge for SO where invoice and freight charge is
1958 --existed
1959
1960 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1961 THEN
1962 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1963 , G_MODULE_NAME || l_procedure_name || '.movement amt in stat value is '
1964 ,p_movement_transaction.movement_amount
1965 );
1966 END IF;
1967
1968
1969 IF (p_movement_transaction.invoice_id IS NOT NULL
1970 AND p_movement_transaction.document_source_type = 'SO')
1971 THEN
1972 --Find out total freight charge on all related invoices for this order line
1973 OPEN c_inv_freight_amt;
1974 FETCH c_inv_freight_amt INTO
1975 l_inv_total_freight_amt;
1976 CLOSE c_inv_freight_amt;
1977
1978 --Check if freight amount on invoice is null, cursor with sum function
1979 --is always return row, so can not use cursor%notfound to check
1980 IF l_inv_total_freight_amt IS NULL
1981 THEN
1982 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1983 THEN
1984 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1985 , G_MODULE_NAME || l_procedure_name
1986 || '.end no freight amt'
1987 ,'exit procedure'
1988 );
1989 END IF;
1990 RETURN (l_movement_amount + l_stat_adj_amount);
1991 END IF;
1992
1993 --Calculate total line freight charge from invoiced quantity for
1994 --all lines
1995 Calc_Total_Line_Charge
1996 ( p_movement_transaction => p_movement_transaction
1997 , x_this_line_charge => l_line_freight_charge
1998 , x_total_line_charge => l_total_line_charge
1999 , x_total_invoiced_qty => l_total_invoiced_qty
2000 );
2001
2002 --Freight charge 1: Calculate order level freight charge
2003 --To cover manual invoice scenario, the total order freight charge will
2004 --be the result of total freight charge on invoices substract the total
2005 --line charge for all the lines. The charge is in functional currency
2006 l_order_freight_charge := l_inv_total_freight_amt * l_conversion_rate - l_total_line_charge;
2007
2008 --Distribute this order level charge to each line
2009 IF (l_order_freight_charge <> 0
2010 AND l_total_invoiced_qty <> 0)
2011 THEN
2012 l_order_freight_charge_to_line :=
2013 ROUND(((l_order_freight_charge/l_total_invoiced_qty)
2014 * p_movement_transaction.invoice_quantity),5);
2015 ELSE
2016 l_order_freight_charge_to_line := 0;
2017 END IF;
2018
2022 --Comment out following code, because the line charge is calculated and
2019 --Freight charge2: line level freight charge
2020 --Can use oe_price_adjustments.adjusted_amount_per_pqty to calculate total
2021 --line charge for this line.
2023 --returned from the call of Calc_Total_Line_Charge
2024 /*Calc_Total_Line_Charge
2025 ( p_movement_transaction => p_movement_transaction
2026 , x_this_line_charge => l_line_freight_charge
2027 , x_total_line_charge => l_total_line_charge
2028 , x_total_invoiced_qty => l_total_invoiced_qty
2029 );*/
2030
2031 --Total freight charge for each line including order level and line level
2032 l_freight_charge := ROUND((l_order_freight_charge_to_line + l_line_freight_charge),2);
2033
2034 --Include freight charge into statistical value
2035 l_stat_ext_value := l_movement_amount + l_freight_charge + l_stat_adj_amount;
2036 ELSE
2037 l_stat_ext_value := l_movement_amount + l_stat_adj_amount;
2038 END IF;
2039
2040 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2041 THEN
2042 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2043 , G_MODULE_NAME || l_procedure_name || '.end'
2044 ,'exit procedure'
2045 );
2046 END IF;
2047
2048 RETURN(l_stat_ext_value);
2049
2050 EXCEPTION
2051 WHEN NO_DATA_FOUND THEN
2052 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2053 THEN
2054 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2055 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
2056 , 'Exception'
2057 );
2058 END IF;
2059 RETURN(l_movement_amount + l_stat_adj_amount);
2060 WHEN OTHERS THEN
2061 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2062 THEN
2063 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2064 , G_MODULE_NAME || l_procedure_name||'. Others exception'
2065 , 'Exception'
2066 );
2067 END IF;
2068 RETURN(l_movement_amount + l_stat_adj_amount);
2069 END Calc_Statistics_Value;
2070
2071 -- ========================================================================
2072 -- PROCEDURE : Calc_Invoice_Info PUBLIC
2073 -- PARAMETERS: x_movement_transaction IN OUT Movement Statistics Record
2074 -- p_stat_typ_transaction IN Stat type Usages record
2075 -- COMMENT : Procedure to calcualte the invoice information
2076 -- Calculation of Invoice Information
2077 -- The verification program calls this Procedure to populate the
2078 -- invoice information to the Movement Statistics table.
2079 -- ========================================================================
2080 PROCEDURE Calc_Invoice_Info
2081 ( p_stat_typ_transaction IN
2082 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
2083 , x_movement_transaction IN OUT NOCOPY
2084 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
2085 )
2086 IS
2087 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
2088 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
2089 l_unit_price NUMBER;
2090 l_invoice_quantity NUMBER;
2091 l_rtv_invoice_quantity NUMBER :=0;
2092 l_tran_curr_code VARCHAR2(15);
2093 l_tran_curr_type VARCHAR2(30);
2094 l_tran_curr_rate NUMBER;
2095 l_tran_curr_date DATE;
2096 l_extended_amount NUMBER;
2097 l_cm_extended_amount NUMBER :=0;
2098 l_rtv_extended_amount NUMBER :=0;
2099 l_uom_code VARCHAR2(3);
2100 l_parent_transaction_id NUMBER;
2101 l_so_le_id NUMBER;
2102 l_shipping_le_id NUMBER;
2103 l_item_type_code OE_ORDER_LINES_ALL.Item_Type_Code%TYPE;
2104 l_model_line_id OE_ORDER_LINES_ALL.Line_Id%TYPE;
2105 l_cto_line_id OE_ORDER_LINES_ALL.Line_Id%TYPE;
2106
2107 --fix bug2861110
2108 l_processed_ret_amt NUMBER;
2109 l_processed_ret_qty NUMBER;
2110 l_total_rtv_trans_qty NUMBER;
2111
2112 --fix bug 2695323
2113 l_total_rma_qty NUMBER;
2114
2115 -- bug 5440432, new variables intorduced.
2116 l_ar_invoiced_amount NUMBER;
2117 l_ar_invoiced_qty NUMBER;
2118
2119 --R12 PO price and qty correction
2120 l_prc_amount NUMBER; --price correction amt
2121 l_qtc_amount NUMBER; --qty correction amt
2122 l_qtc_qty NUMBER; --qty corrected in qty correction
2123
2124 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Invoice_Info';
2125
2126 -- Cursor to get all the invoice information after we get the invoice_id
2127
2128 CURSOR l_arc IS
2129 SELECT
2130 rat.trx_date
2131 , rat.batch_id
2132 , NVL(rat.exchange_rate,1)
2133 , rat.exchange_rate_type
2134 , rat.exchange_date
2135 , NVL(rat.invoice_currency_code,l_movement_transaction.currency_code)
2136 FROM
2137 RA_CUSTOMER_TRX_ALL rat
2138 WHERE rat.customer_trx_id = x_movement_transaction.invoice_id;
2139
2140 -- Cursor for SO invoices which are non credit/debit memos.
2141
2142 CURSOR l_sum_arc IS
2143 SELECT
2144 MAX(ratl.customer_trx_line_id)
2145 , MAX(rat.customer_trx_id)
2146 , SUM(ratl.extended_amount)
2147 , SUM(NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity))
2148 FROM
2149 RA_CUSTOMER_TRX_ALL rat
2150 , RA_CUSTOMER_TRX_LINES_ALL ratl
2151 , RA_CUST_TRX_TYPES_ALL ratt
2152 WHERE rat.customer_trx_id = ratl.customer_trx_id
2153 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2154 AND rat.org_id = ratt.org_id
2155 AND ratt.type NOT IN ('CM','DM')
2156 AND NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
2160 AND (line_id = l_movement_transaction.order_line_id
2157 NOT IN (SELECT TO_CHAR(price_adjustment_id)
2158 FROM oe_price_adjustments
2159 WHERE header_id = l_movement_transaction.order_header_id
2161 OR (line_id IS NULL AND modifier_level_code = 'ORDER')))
2162 AND ratl.line_type = 'LINE'
2163 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
2164 AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2165 AND ratl.interface_line_attribute6 =
2166 to_char(l_movement_transaction.order_line_id)
2167 AND ratl.interface_line_context <> 'INTERCOMPANY';
2168
2169 -- Cursor for RMA transactions
2170
2171 CURSOR l_sum_rma_arc IS
2172 SELECT
2173 MAX(ratl.customer_trx_line_id)
2174 , MAX(rat.customer_trx_id)
2175 , SUM(ratl.extended_amount)
2176 , SUM(NVL(ratl.quantity_credited,l_movement_transaction.transaction_quantity))
2177 FROM
2178 RA_CUSTOMER_TRX_ALL rat
2179 , RA_CUSTOMER_TRX_LINES_ALL ratl
2180 , RA_CUST_TRX_TYPES_ALL ratt
2181 WHERE rat.customer_trx_id = ratl.customer_trx_id
2182 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2183 AND rat.org_id = ratt.org_id
2184 AND ratt.type IN ('CM','DM')
2185 AND ratl.line_type = 'LINE' --yawang
2186 AND ratl.quantity_credited IS NOT NULL
2187 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
2188 --AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2189 AND ratl.interface_line_attribute1 = to_char(l_movement_transaction.order_number)
2190 AND NVL(ratl.org_id,0) = NVL(l_movement_transaction.org_id,0)
2191 AND ratl.interface_line_attribute6 =
2192 to_char(l_movement_transaction.order_line_id)
2193 AND ratl.interface_line_context <> 'INTERCOMPANY';
2194
2195 -- Cursor for SO Credit/Debit memos.
2196
2197 CURSOR l_sum_cm_arc IS
2198 SELECT
2199 SUM(ratl.extended_amount)
2200 FROM
2201 RA_CUSTOMER_TRX_ALL rat
2202 , RA_CUSTOMER_TRX_LINES_ALL ratl
2203 , RA_CUST_TRX_TYPES_ALL ratt
2204 WHERE rat.customer_trx_id = ratl.customer_trx_id
2205 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2206 AND rat.org_id = ratt.org_id
2207 AND ((ratt.type IN ('CM','DM'))
2208 OR (NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
2209 IN (SELECT TO_CHAR(price_adjustment_id)
2210 FROM oe_price_adjustments
2211 WHERE header_id = l_movement_transaction.order_header_id
2212 AND (line_id = l_movement_transaction.order_line_id
2213 OR (line_id IS NULL AND modifier_level_code = 'ORDER')) )))
2214 AND ratl.line_type = 'LINE' --yawang
2215 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
2216 AND ratl.quantity_credited IS NULL
2217 AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2218 AND ratl.interface_line_attribute6 =
2219 to_char(l_movement_transaction.order_line_id)
2220 AND ratl.interface_line_context <> 'INTERCOMPANY';
2221
2222 /* Bug 8435314 Add New Cursor for config Item in RMA*/
2223 CURSOR l_rma_config IS
2224 SELECT DISTINCT 'CONFIG' FROM mtl_system_items
2225 WHERE inventory_item_id = l_movement_transaction.inventory_item_id
2226 AND auto_created_config_flag = 'Y'
2227 AND base_item_id IS NOT null;
2228
2229 CURSOR l_rma_model_id IS
2230 SELECT DISTINCT ol.line_id FROM oe_order_lines_all ol
2231 WHERE (return_attribute1,return_attribute2) IN
2232 (SELECT header_id,Top_model_line_id FROM oe_order_lines_all ol1
2233 WHERE (header_id,Top_model_line_id) IN
2234 (SELECT header_id,Top_model_line_id FROM oe_order_lines_all ol2
2235 WHERE (header_id,line_id ) IN
2236 (SELECT return_attribute1,return_attribute2 FROM oe_order_lines_all ol3
2237 WHERE ol3.line_id=l_movement_transaction.order_line_id
2238 AND ol3.header_id=l_movement_transaction.order_header_id)))
2239 AND ol.header_id=l_movement_transaction.order_header_id;
2240 /*End bug 8435314*/
2241
2242 -- Cursor for drop shipment transactions
2243
2244 CURSOR l_sum_drparc IS
2245 SELECT
2246 MAX(ratl.customer_trx_line_id)
2247 , MAX(rat.customer_trx_id)
2248 , SUM(ratl.extended_amount)
2249 , SUM(NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity))
2250 FROM
2251 RA_CUSTOMER_TRX_ALL rat
2252 , RA_CUSTOMER_TRX_LINES_ALL ratl
2253 , RA_CUST_TRX_TYPES_ALL ratt --add in for fixing bug 2447381
2254 WHERE rat.customer_trx_id = ratl.customer_trx_id
2255 --AND ratl.quantity_invoiced = ratl.quantity_ordered
2256 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2257 AND rat.org_id = ratt.org_id
2258 AND ratt.type NOT IN ('CM','DM')
2259 AND NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
2260 NOT IN (SELECT TO_CHAR(price_adjustment_id)
2261 FROM oe_price_adjustments
2262 WHERE header_id = l_movement_transaction.order_header_id
2263 AND (line_id = l_movement_transaction.order_line_id
2264 OR (line_id IS NULL AND modifier_level_code = 'ORDER')))
2265 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
2266 AND ratl.line_type = 'LINE' --yawang
2267 AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2268 AND ratl.interface_line_attribute6 =
2269 to_char(l_movement_transaction.order_line_id)
2270 AND rat.complete_flag = 'Y'
2271 AND ratl.interface_line_context <> 'INTERCOMPANY';
2272
2273 -- Cursor for drop shipment transactions with credit memo/debit memos
2274
2275 CURSOR l_sum_cm_drparc IS
2276 SELECT
2277 SUM(ratl.extended_amount)
2278 FROM
2279 RA_CUSTOMER_TRX_ALL rat
2280 , RA_CUSTOMER_TRX_LINES_ALL ratl
2281 , RA_CUST_TRX_TYPES_ALL ratt
2282 WHERE rat.customer_trx_id = ratl.customer_trx_id
2283 --AND ratl.quantity_invoiced = ratl.quantity_ordered
2284 AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2285 AND rat.org_id = ratt.org_id
2286 AND ((ratt.type IN ('CM','DM'))
2287 OR (NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
2288 IN (SELECT TO_CHAR(price_adjustment_id)
2289 FROM oe_price_adjustments
2290 WHERE header_id = l_movement_transaction.order_header_id
2291 AND (line_id = l_movement_transaction.order_line_id
2292 OR (line_id IS NULL AND modifier_level_code = 'ORDER')) )))
2293 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
2294 AND ratl.line_type = 'LINE' --yawang
2295 AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2296 AND ratl.interface_line_attribute6 =
2297 to_char(l_movement_transaction.order_line_id)
2298 AND rat.complete_flag = 'Y'
2299 AND ratl.interface_line_context <> 'INTERCOMPANY';
2300
2301 --Cursor for ar intercompany invoice
2302 /*Bugfix 12635623: Modifying the cursor.*/
2303 --Bugfix 12949295: Bringing this cursor back for RMA. This was commented as part
2304 --of bugfix 12635623. For RMA, if you receive the RMA quantity using multiple
2305 --receipts, the SO line will get split. For each SO line, there would be one movement
2306 --record and one invoice line. Hence this cursor can be used even with max and sum
2307 --functions.
2308 CURSOR l_ar_intercompany_rma IS
2309 SELECT
2310 MAX(ratl.customer_trx_line_id)
2311 , MAX(rat.customer_trx_id)
2312 , SUM(ratl.extended_amount)
2313 --, SUM(NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity))
2314 , SUM(NVL(ratl.quantity_credited,l_movement_transaction.transaction_quantity))
2315 FROM
2316 RA_CUSTOMER_TRX_ALL rat
2317 , RA_CUSTOMER_TRX_LINES_ALL ratl
2318 WHERE rat.customer_trx_id = ratl.customer_trx_id
2319 AND ratl.line_type = 'LINE'
2320 AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2321 AND ratl.interface_line_attribute6 =
2322 to_char(l_movement_transaction.order_line_id)
2323 AND ratl.interface_line_context = 'INTERCOMPANY';
2324
2325 --Cursor to get AR Intercompany Invoice information.
2326 CURSOR l_ar_intercompany IS
2327 SELECT
2328 ratl.customer_trx_line_id
2329 , rat.customer_trx_id
2330 , ratl.extended_amount
2331 , NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity)
2332 FROM
2333 RA_CUSTOMER_TRX_ALL rat
2334 , RA_CUSTOMER_TRX_LINES_ALL ratl
2335 , mtl_material_transactions mmt
2336 WHERE rat.customer_trx_id = ratl.customer_trx_id
2337 AND ratl.line_type = 'LINE'
2338 AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2339 AND ratl.interface_line_attribute6 = to_char(l_movement_transaction.order_line_id)
2340 AND ratl.interface_line_context = 'INTERCOMPANY'
2341 AND ratl.interface_line_attribute7 = mmt.transaction_id
2342 AND mmt.picking_line_id = l_movement_transaction.picking_line_detail_id
2343
2344 UNION ALL
2345
2346 SELECT
2347 ratl.customer_trx_line_id
2348 , rat.customer_trx_id
2349 , ratl.extended_amount
2350 , NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity)
2351 FROM
2352 RA_CUSTOMER_TRX_ALL rat
2353 , RA_CUSTOMER_TRX_LINES_ALL ratl
2354 WHERE rat.customer_trx_id = ratl.customer_trx_id
2355 AND ratl.line_type = 'LINE'
2356 AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2357 AND ratl.interface_line_attribute6 = to_char(l_movement_transaction.order_line_id)
2358 AND ratl.interface_line_context = 'INTERCOMPANY'
2359 AND ratl.interface_line_attribute7 IN
2360 (SELECT mmt_logical.transaction_id
2361 FROM mtl_material_transactions mmt_logical,
2362 mtl_material_transactions mmt_physical
2363 WHERE mmt_physical.picking_line_id = l_movement_transaction.picking_line_detail_id
2364 AND mmt_logical.parent_transaction_id = mmt_physical.parent_transaction_id
2365 AND mmt_logical.trx_source_line_id = mmt_physical.trx_source_line_id
2366 AND mmt_physical.transaction_type_id = 33 -- to identify physical
2367 AND mmt_logical.transaction_type_id = 11 -- to identify logical
2368 );
2369
2370 -- Bug 5440432: Following query is modified to replace where clause
2371 -- 'AND rcta.trx_number||'-'||rcta.org_id = aia.invoice_num' with
2372 -- 'AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
2373 -- OR rcta.trx_number = aia.invoice_num)'
2374 -- This change was introduced because of bug 4180686 in 11.5.10
2375 -- Apart from that a new cursor l_ap_intercompany_invoiced_qty is
2376 -- introduced to get quantity/Amount from AR Invoice tables if
2377 -- AP details are missing.
2378 --Cursor for ap intercompany invoice
2379 --CURSOR l_ap_intercompany IS
2380 --SELECT
2381 -- MAX(aida.invoice_id)
2382 --, MAX(distribution_line_number)
2383 --, SUM(NVL(aida.amount,rctla.extended_amount))
2384 --, SUM(NVL(aida.quantity_invoiced,rctla.quantity_invoiced))
2385 --FROM
2386 -- AP_INVOICES_ALL aia
2387 --, AP_INVOICE_DISTRIBUTIONS_ALL aida
2388 --, RA_CUSTOMER_TRX_LINES_ALL rctla
2389 --, ra_customer_trx_all rcta
2390 --WHERE aia.invoice_id = aida.invoice_id
2391 -- AND (NVL(aida.match_status_flag,'N') = 'A'
2392 -- OR (NVL(aida.match_status_flag,'N') = 'T'
2393 -- AND aia.wfapproval_status = 'NOT REQUIRED'))
2394 -- AND aia.cancelled_date IS NULL
2395 -- AND aida.line_type_lookup_code = 'ITEM' --yawang, limit for good cost only
2396 -- AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2397 -- AND aida.reference_1 = TO_CHAR(rctla.customer_trx_line_id)
2398 -- AND rctla.customer_trx_id = rcta.customer_trx_id
2399 -- AND rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
2400 -- AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2401 -- AND rctla.interface_line_attribute6 =
2402 -- to_char(l_movement_transaction.order_line_id);
2403
2404 /*Bugfix 12635623: Modifying the cursor.
2405 CURSOR l_ap_intercompany IS
2406 SELECT
2407 MAX(aia.invoice_id)
2408 , MAX(aila.line_number)
2409 , SUM(NVL(aila.amount, 0))
2410 , SUM(NVL(aila.quantity_invoiced,0))
2411 FROM
2412 AP_INVOICES_ALL aia
2413 , AP_INVOICE_LINES_ALL aila
2414 , RA_CUSTOMER_TRX_LINES_ALL rctla
2415 , ra_customer_trx_all rcta
2416 WHERE aia.invoice_id = aila.invoice_id
2417 AND aia.cancelled_date IS NULL
2418 AND aila.line_type_lookup_code = 'ITEM'
2419 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2420 AND aila.reference_1 = TO_CHAR(rctla.customer_trx_line_id)
2421 AND rctla.customer_trx_id = rcta.customer_trx_id
2422 AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
2423 OR rcta.trx_number = aia.invoice_num)
2424 AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2425 AND rctla.interface_line_attribute6 =
2426 to_char(l_movement_transaction.order_line_id)
2427 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2428 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2429 FROM ap_holds_all aha
2430 WHERE aha.invoice_id = aia.invoice_id
2431 AND aha.release_lookup_code is null)
2432 AND EXISTS (SELECT 'Invoice is approved'
2433 FROM ap_invoice_distributions_all aida
2434 WHERE aida.invoice_id = aia.invoice_id
2435 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2436 */
2437
2438 --Bugfix 12949295: This cursor is for IO arrival only.
2439 CURSOR l_ap_intercompany_io_arrival IS
2440 SELECT
2441 aia.invoice_id
2442 , aila.line_number
2443 , NVL(aila.amount, 0)
2444 , NVL(aila.quantity_invoiced,0)
2445 FROM
2446 AP_INVOICES_ALL aia
2447 , AP_INVOICE_LINES_ALL aila
2448 , RA_CUSTOMER_TRX_LINES_ALL rctla
2449 , ra_customer_trx_all rcta
2450 , rcv_transactions rcv
2451 , rcv_shipment_lines rsl
2452 , mtl_material_transactions mmt
2453 WHERE aia.invoice_id = aila.invoice_id
2454 AND aia.cancelled_date IS NULL
2455 AND aila.line_type_lookup_code = 'ITEM'
2456 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2457 AND aila.reference_1 = TO_CHAR(rctla.customer_trx_line_id)
2458 AND rctla.customer_trx_id = rcta.customer_trx_id
2459 AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
2460 OR rcta.trx_number = aia.invoice_num)
2461 AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2462 AND rctla.interface_line_attribute6 =
2463 to_char(l_movement_transaction.order_line_id)
2464 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2465 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2466 FROM ap_holds_all aha
2467 WHERE aha.invoice_id = aia.invoice_id
2468 AND aha.release_lookup_code is null)
2469 AND EXISTS (SELECT 'Invoice is approved'
2470 FROM ap_invoice_distributions_all aida
2471 WHERE aida.invoice_id = aia.invoice_id
2472 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'))
2473 AND aila.reference_2 = mmt.transaction_id
2474 AND mmt.transaction_id = rsl.mmt_transaction_id
2475 AND rsl.shipment_line_id = rcv.shipment_line_id
2476 AND rcv.transaction_id = l_movement_transaction.rcv_transaction_id;
2477
2478 --Bugfix 12949295: AP Invoice cursor for SO Arrival transactions.
2479 --SO arrival details are not present in rcv_transactions. So l_ap_intercompany_io_arrival
2480 --cannot be used for SO arrivals.
2481 CURSOR l_ap_intercompany_so_arrival IS
2482 SELECT
2483 aia.invoice_id
2484 , aila.line_number
2485 , NVL(aila.amount, 0)
2486 , NVL(aila.quantity_invoiced,0)
2487 FROM
2488 AP_INVOICES_ALL aia
2489 , AP_INVOICE_LINES_ALL aila
2490 , RA_CUSTOMER_TRX_LINES_ALL rctla
2491 , ra_customer_trx_all rcta
2492 , mtl_material_transactions mmt
2493 WHERE aia.invoice_id = aila.invoice_id
2494 AND aia.cancelled_date IS NULL
2495 AND aila.line_type_lookup_code = 'ITEM'
2496 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2497 AND aila.reference_1 = TO_CHAR(rctla.customer_trx_line_id)
2498 AND rctla.customer_trx_id = rcta.customer_trx_id
2499 AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
2500 OR rcta.trx_number = aia.invoice_num)
2501 AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2502 AND rctla.interface_line_attribute6 =
2503 to_char(l_movement_transaction.order_line_id)
2504 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2505 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2506 FROM ap_holds_all aha
2507 WHERE aha.invoice_id = aia.invoice_id
2508 AND aha.release_lookup_code is null)
2509 AND EXISTS (SELECT 'Invoice is approved'
2510 FROM ap_invoice_distributions_all aida
2511 WHERE aida.invoice_id = aia.invoice_id
2512 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'))
2513 AND aila.reference_2 = mmt.transaction_id
2514 AND mmt.picking_line_id = l_movement_transaction.picking_line_detail_id
2515
2516 UNION ALL
2517
2518 SELECT
2519 aia.invoice_id
2520 , aila.line_number
2521 , NVL(aila.amount, 0)
2522 , NVL(aila.quantity_invoiced,0)
2523 FROM
2524 AP_INVOICES_ALL aia
2525 , AP_INVOICE_LINES_ALL aila
2526 , RA_CUSTOMER_TRX_LINES_ALL rctla
2527 , ra_customer_trx_all rcta
2528 WHERE aia.invoice_id = aila.invoice_id
2529 AND aia.cancelled_date IS NULL
2530 AND aila.line_type_lookup_code = 'ITEM'
2531 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2532 AND aila.reference_1 = TO_CHAR(rctla.customer_trx_line_id)
2533 AND rctla.customer_trx_id = rcta.customer_trx_id
2534 AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
2535 OR rcta.trx_number = aia.invoice_num)
2536 AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2537 AND rctla.interface_line_attribute6 =
2538 to_char(l_movement_transaction.order_line_id)
2539 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2540 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2541 FROM ap_holds_all aha
2542 WHERE aha.invoice_id = aia.invoice_id
2543 AND aha.release_lookup_code is null)
2544 AND EXISTS (SELECT 'Invoice is approved'
2545 FROM ap_invoice_distributions_all aida
2546 WHERE aida.invoice_id = aia.invoice_id
2547 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'))
2548 AND aila.reference_2 in
2549 (SELECT mmt_logical.transaction_id
2550 FROM mtl_material_transactions mmt_logical,
2551 mtl_material_transactions mmt_physical
2552 WHERE mmt_physical.picking_line_id = l_movement_transaction.picking_line_detail_id
2553 AND mmt_logical.parent_transaction_id = mmt_physical.parent_transaction_id
2554 AND mmt_logical.trx_source_line_id = mmt_physical.trx_source_line_id
2555 AND mmt_physical.transaction_type_id = 33 -- to identify physical
2556 AND mmt_logical.transaction_type_id = 11 -- to identify logical
2557 );
2558
2559 --Bugfix 12949295: AP Invoice cursor for RMA transactions.
2560 --RMA information is not available in wdd. Also, the field rcv_shipment_lines.mmt_transaction_id
2561 --is null for RMA. SO the first two AP Intercompany cursors cannot be used.
2562 CURSOR l_ap_intercompany_rma IS
2563 SELECT
2564 aia.invoice_id
2565 , aila.line_number
2566 , NVL(aila.amount, 0)
2567 , NVL(aila.quantity_invoiced,0)
2568 FROM
2569 AP_INVOICES_ALL aia
2570 , AP_INVOICE_LINES_ALL aila
2571 , RA_CUSTOMER_TRX_LINES_ALL rctla
2572 , ra_customer_trx_all rcta
2573 , mtl_material_transactions mmt
2574 WHERE aia.invoice_id = aila.invoice_id
2575 AND aia.cancelled_date IS NULL
2576 AND aila.line_type_lookup_code = 'ITEM'
2577 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2578 AND aila.reference_1 = TO_CHAR(rctla.customer_trx_line_id)
2579 AND rctla.customer_trx_id = rcta.customer_trx_id
2580 AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
2581 OR rcta.trx_number = aia.invoice_num)
2582 AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2583 AND rctla.interface_line_attribute6 =
2584 to_char(l_movement_transaction.order_line_id)
2585 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2586 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2587 FROM ap_holds_all aha
2588 WHERE aha.invoice_id = aia.invoice_id
2589 AND aha.release_lookup_code is null)
2590 AND EXISTS (SELECT 'Invoice is approved'
2591 FROM ap_invoice_distributions_all aida
2592 WHERE aida.invoice_id = aia.invoice_id
2593 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'))
2594 AND aila.reference_2 = mmt.transaction_id
2595 AND mmt.rcv_transaction_id = l_movement_transaction.rcv_transaction_id;
2596
2597 /*Bugfix 12635623: Modifying this cursor.
2598 CURSOR l_ap_intercompany_invoiced_qty IS
2599 SELECT
2600 SUM(NVL(rctla.extended_amount,0)) InvoiceAmount
2601 ,SUM(NVL(rctla.quantity_invoiced,0)) InvoicedQuantity
2602 FROM
2603 AP_INVOICES_ALL aia
2604 , RA_CUSTOMER_TRX_LINES_ALL rctla
2605 WHERE aia.invoice_id = x_movement_transaction.invoice_id
2606 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2607 AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2608 AND rctla.interface_line_attribute6 =
2609 to_char(l_movement_transaction.order_line_id);
2610 */
2611
2612 --Bugfix 12949295: This cursor is for Intercompany IO arrival only.
2613 CURSOR l_ap_ic_inv_qty_io_arr IS
2614 SELECT
2615 NVL(rctla.extended_amount,0) InvoiceAmount
2616 ,NVL(rctla.quantity_invoiced,0) InvoicedQuantity
2617 FROM
2618 AP_INVOICES_ALL aia
2619 , RA_CUSTOMER_TRX_LINES_ALL rctla
2620 , rcv_transactions rcv
2621 , rcv_shipment_lines rsl
2622 , mtl_material_transactions mmt
2623 WHERE aia.invoice_id = x_movement_transaction.invoice_id
2624 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2625 AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2626 AND rctla.interface_line_attribute6 =
2627 to_char(l_movement_transaction.order_line_id)
2628 AND rctla.interface_line_attribute7 = mmt.transaction_id
2629 AND mmt.transaction_id = rsl.mmt_transaction_id
2630 AND rsl.shipment_line_id = rcv.shipment_line_id
2631 AND rcv.transaction_id = l_movement_transaction.rcv_transaction_id;
2632
2636 SELECT
2633 --Bugfix 12949295: Quantity cursor for Intercompany SO arrival.
2634 /*
2635 CURSOR l_ap_ic_inv_qty_so_arr IS
2637 NVL(rctla.extended_amount,0) InvoiceAmount
2638 ,NVL(rctla.quantity_invoiced,0) InvoicedQuantity
2639 FROM
2640 AP_INVOICES_ALL aia
2641 , RA_CUSTOMER_TRX_LINES_ALL rctla
2642 , mtl_material_transactions mmt
2643 WHERE aia.invoice_id = x_movement_transaction.invoice_id
2644 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2645 AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2646 AND rctla.interface_line_attribute6 =
2647 to_char(l_movement_transaction.order_line_id)
2648 AND rctla.interface_line_attribute7 = mmt.transaction_id
2649 AND mmt.picking_line_id = l_movement_transaction.picking_line_detail_id;
2650 */
2651
2652 CURSOR l_ap_ic_inv_qty_so_arr IS
2653 SELECT
2654 NVL(rctla.extended_amount,0) InvoiceAmount
2655 ,NVL(rctla.quantity_invoiced,0) InvoicedQuantity
2656 FROM
2657 AP_INVOICES_ALL aia
2658 , RA_CUSTOMER_TRX_LINES_ALL rctla
2659 , mtl_material_transactions mmt
2660 WHERE aia.invoice_id = x_movement_transaction.invoice_id
2661 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2662 AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2663 AND rctla.interface_line_attribute6 =
2664 to_char(l_movement_transaction.order_line_id)
2665 AND rctla.interface_line_attribute7 = mmt.transaction_id
2666 AND mmt.picking_line_id = l_movement_transaction.picking_line_detail_id
2667
2668 UNION ALL
2669
2670 SELECT
2671 NVL(rctla.extended_amount,0) InvoiceAmount
2672 ,NVL(rctla.quantity_invoiced,0) InvoicedQuantity
2673 FROM
2674 AP_INVOICES_ALL aia
2675 , RA_CUSTOMER_TRX_LINES_ALL rctla
2676 WHERE aia.invoice_id = x_movement_transaction.invoice_id
2677 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2678 AND rctla.sales_order = to_char(l_movement_transaction.order_number)
2679 AND rctla.interface_line_attribute6 =
2680 to_char(l_movement_transaction.order_line_id)
2681 AND rctla.interface_line_attribute7 IN
2682 (SELECT mmt_logical.transaction_id
2683 FROM mtl_material_transactions mmt_logical,
2684 mtl_material_transactions mmt_physical
2685 WHERE mmt_physical.picking_line_id = l_movement_transaction.picking_line_detail_id
2686 AND mmt_logical.parent_transaction_id = mmt_physical.parent_transaction_id
2687 AND mmt_logical.trx_source_line_id = mmt_physical.trx_source_line_id
2688 AND mmt_physical.transaction_type_id = 33 -- to identify physical
2689 AND mmt_logical.transaction_type_id = 11 -- to identify logical
2690 );
2691
2692 --No changes are needed in this cursor. Consider the following scenario:
2693 --RMA is created for 30 quantities. Whille receiving, the receipt is created for
2694 --15,10 and 5 quantities. This would split the original RMA line into 3 with
2695 --quantities of 15,10 and 5. 3 RCV transactions would get created which would create
2696 --3 movement records with proper RMA line_id stamped. The SUM function would still
2697 --return the correct data.
2698 /* 7165989 - Intercompany AP invoice for RMA */
2699 CURSOR l_ap_intercompany_rma_inv_qty IS
2700 SELECT
2701 SUM(NVL(rctla.extended_amount,0)) InvoiceAmount
2702 , SUM(NVL(rctla.quantity_credited,l_movement_transaction.transaction_quantity)) InvoicedQuantity
2703 FROM
2704 AP_INVOICES_ALL aia
2705 , RA_CUSTOMER_TRX_LINES_ALL rctla
2706 WHERE aia.invoice_id = x_movement_transaction.invoice_id
2707 AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2708 AND rctla.interface_line_attribute1 = to_char(l_movement_transaction.order_number)
2709 AND rctla.interface_line_attribute6 =
2710 to_char(l_movement_transaction.order_line_id);
2711
2712 /* 7165989 - End */
2713
2714 -- Cursor to get the invoice info based on the invoice_id
2715 CURSOR l_ap_intercompany_invo IS
2716 SELECT
2717 ap.invoice_currency_code
2718 , NVL(ap.exchange_rate,NVL(rctl.exchange_rate, 1))
2719 , NVL(ap.exchange_rate_type,rctl.exchange_rate_type)
2720 , NVL(ap.exchange_date,rctl.exchange_date)
2721 , ap.batch_id
2722 , ap.invoice_date
2723 FROM
2724 AP_INVOICES_ALL ap
2725 , RA_CUSTOMER_TRX_ALL rctl
2726 WHERE ap.invoice_id = x_movement_transaction.invoice_id
2727 AND ap.reference_1 = rctl.customer_trx_id;
2728
2729 -- Cursor to get all the invoice information for drop shipments after fetching
2730 -- the invoice_id.
2731
2732 CURSOR l_drparc IS
2733 SELECT
2734 rat.trx_date
2735 , rat.batch_id
2736 , NVL(rat.exchange_rate,1)
2737 , rat.exchange_rate_type
2738 , rat.exchange_date
2739 , NVL(rat.invoice_currency_code,l_movement_transaction.currency_code)
2740 FROM
2741 RA_CUSTOMER_TRX_ALL rat
2742 , RA_CUSTOMER_TRX_LINES_ALL ratl
2743 WHERE rat.customer_trx_id = ratl.customer_trx_id
2744 --AND ratl.quantity_invoiced = ratl.quantity_ordered
2745 AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
2746 AND ratl.interface_line_attribute6 = to_char(l_movement_transaction.order_line_id)
2747 AND ratl.customer_trx_line_id = x_movement_transaction.customer_trx_line_id;
2748
2749 -- Cursor to get the invoice info based on the invoice_id for PO
2750
2751 CURSOR l_apc IS
2752 SELECT
2753 ap.invoice_currency_code
2754 , NVL(ap.exchange_rate,1)
2755 , ap.exchange_rate_type
2756 , ap.exchange_date
2757 , ap.batch_id
2758 , ap.invoice_date
2759 FROM
2760 AP_INVOICES_ALL ap
2764 -- modified for AP invoice lines uptake in R12
2761 WHERE ap.invoice_id = x_movement_transaction.invoice_id;
2762
2763 -- Cursor for PO based matching regular invoice
2765 CURSOR l_po_inv IS
2766 SELECT
2767 SUM(aila.amount)
2768 , MAX(aila.line_number)
2769 , MAX(aia.invoice_id)
2770 , SUM(aila.quantity_invoiced)
2771 FROM
2772 ap_invoices_all aia
2773 , ap_invoice_lines_all aila
2774 WHERE aia.invoice_id = aila.invoice_id
2775 AND aia.invoice_type_lookup_code in ('STANDARD','MIXED')
2776 AND aia.cancelled_date IS NULL
2777 AND aila.line_type_lookup_code = 'ITEM'
2778 AND aila.match_type = 'ITEM_TO_PO'
2779 AND aila.po_line_location_id = l_movement_transaction.po_line_location_id
2780 -- Bug 5655040. Commented as condition is modified and few more conditioned
2781 -- added to whereclause to check hold and disregard status.
2782 --AND NOT EXISTS (SELECT 1
2783 -- FROM ap_invoice_distributions_all aida
2784 -- WHERE aida.invoice_id = aia.invoice_id
2785 -- AND aida.invoice_line_number = aila.line_number
2786 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2787 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2788 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2789 FROM ap_holds_all aha
2790 WHERE aha.invoice_id = aia.invoice_id
2791 AND aha.release_lookup_code is null)
2792 AND EXISTS (SELECT 'Invoice is approved'
2793 FROM ap_invoice_distributions_all aida
2794 WHERE aida.invoice_id = aia.invoice_id
2795 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2796
2797 /*bug 8435322 Added new cursor to fetch max distribution_line_number for max Invoice Id*/
2798 -- Bug 13441599.pdube
2799 -- Added clause to filter based on po_line_location_id along with invoice_id because
2800 -- one invoice can have multiple po_line records.
2801 /*
2802 CURSOR l_po_line_number IS
2803 SELECT
2804 MAX(distribution_line_number)
2805 FROM
2806 AP_INVOICES_ALL b
2807 , AP_INVOICE_DISTRIBUTIONS_ALL a
2808 WHERE b.invoice_id = x_movement_transaction.invoice_id
2809 AND b.invoice_id = a.invoice_id
2810 AND (NVL(a.match_status_flag,'N') = 'A'
2811 OR (NVL(a.match_status_flag,'N') = 'T'
2812 AND b.wfapproval_status = 'NOT REQUIRED'))
2813 AND a.line_type_lookup_code = 'ITEM' ;
2814 */
2815
2816 CURSOR l_po_line_number IS
2817 SELECT
2818 MAX(distribution_line_number)
2819 FROM
2820 AP_INVOICES_ALL b
2821 , AP_INVOICE_DISTRIBUTIONS_ALL a
2822 , PO_DISTRIBUTIONS_ALL c
2823 WHERE b.invoice_id = x_movement_transaction.invoice_id
2824 AND b.invoice_id = a.invoice_id
2825 AND c.po_distribution_id = a.po_distribution_id
2826 AND c.line_location_id = x_movement_transaction.po_line_location_id
2827 AND (NVL(a.match_status_flag,'N') = 'A'
2828 OR (NVL(a.match_status_flag,'N') = 'T'
2829 AND b.wfapproval_status = 'NOT REQUIRED'))
2830 AND a.line_type_lookup_code = 'ITEM' ;
2831 /*End bug 8435322 */
2832
2833 -- Cursor for price correction in case of PO based matching.
2834 -- Modified for AP invoice lines uptake in R12
2835 -- Although price correction in AP directly reflects price correction for certain
2836 -- quantity, but for us we display only one invoice for each transaction, we still
2837 -- need to average the price for each qty after price adjustment. So here we only
2838 -- take the amount.
2839 --CURSOR l_po_cm_inv IS
2840 CURSOR l_po_prc_inv IS
2841 SELECT
2842 SUM(aila.amount)
2843 FROM
2844 ap_invoices_all aia
2845 , ap_invoice_lines_all aila
2846 WHERE aia.invoice_id = aila.invoice_id
2847 AND aia.cancelled_date IS NULL
2848 AND aila.line_type_lookup_code = 'ITEM'
2849 AND aila.match_type = 'PRICE_CORRECTION'
2850 AND aila.po_line_location_id = l_movement_transaction.po_line_location_id
2851 -- Bug 5655040. Commented as condition is modified and few more conditioned
2852 -- added to whereclause to check hold and disregard status.
2853 --AND NOT EXISTS (SELECT 1
2854 -- FROM ap_invoice_distributions_all aida
2855 -- WHERE aida.invoice_id = aia.invoice_id
2856 -- AND aida.invoice_line_number = aila.line_number
2857 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2858 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2859 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2860 FROM ap_holds_all aha
2861 WHERE aha.invoice_id = aia.invoice_id
2862 AND aha.release_lookup_code is null)
2863 AND EXISTS (SELECT 'Invoice is approved'
2864 FROM ap_invoice_distributions_all aida
2865 WHERE aida.invoice_id = aia.invoice_id
2866 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2867 -- Cursor for quantity correction in case of PO based matching.
2868 -- Modified for AP invoice lines uptake in R12
2869 CURSOR l_po_qtc_inv IS
2870 SELECT
2871 SUM(aila.quantity_invoiced)
2872 , SUM(aila.amount)
2873 FROM
2874 ap_invoices_all aia
2875 , ap_invoice_lines_all aila
2876 WHERE aia.invoice_id = aila.invoice_id
2877 AND aia.cancelled_date IS NULL
2878 AND aila.line_type_lookup_code = 'ITEM'
2879 AND aila.match_type = 'QTY_CORRECTION'
2880 AND aila.po_line_location_id = l_movement_transaction.po_line_location_id
2881 -- Bug 5655040. Commented as condition is modified and few more conditioned
2882 -- added to whereclause to check hold and disregard status.
2883 --AND NOT EXISTS (SELECT 1
2884 -- FROM ap_invoice_distributions_all aida
2885 -- WHERE aida.invoice_id = aia.invoice_id
2889 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2886 -- AND aida.invoice_line_number = aila.line_number
2887 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2888 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2890 FROM ap_holds_all aha
2891 WHERE aha.invoice_id = aia.invoice_id
2892 AND aha.release_lookup_code is null)
2893 AND EXISTS (SELECT 'Invoice is approved'
2894 FROM ap_invoice_distributions_all aida
2895 WHERE aida.invoice_id = aia.invoice_id
2896 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2897
2898 -- Cursor for RTV's where credit memo is to be associated with
2899 -- RTV in case of PO based matching.
2900 -- modified for AP invoice lines uptake in R12
2901 CURSOR l_po_rtv_cm_inv IS
2902 SELECT
2903 SUM(aila.amount)
2904 , MAX(aila.line_number)
2905 , MAX(aia.invoice_id)
2906 , SUM(aila.quantity_invoiced)
2907 FROM
2908 ap_invoices_all aia
2909 , ap_invoice_lines_all aila
2910 WHERE aia.invoice_id = aila.invoice_id
2911 AND aia.invoice_type_lookup_code in ('CREDIT','DEBIT')
2912 AND aia.cancelled_date IS NULL
2913 AND aila.line_type_lookup_code = 'ITEM'
2914 AND aila.match_type = 'ITEM_TO_PO'
2915 AND NVL(aila.quantity_invoiced,0) < 0
2916 AND aila.po_line_location_id = l_movement_transaction.po_line_location_id
2917 -- Bug 5655040. Commented as condition is modified and few more conditioned
2918 -- added to whereclause to check hold and disregard status.
2919 --AND NOT EXISTS (SELECT 1
2920 -- FROM ap_invoice_distributions_all aida
2921 -- WHERE aida.invoice_id = aia.invoice_id
2922 -- AND aida.invoice_line_number = aila.line_number
2923 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2924 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2925 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2926 FROM ap_holds_all aha
2927 WHERE aha.invoice_id = aia.invoice_id
2928 AND aha.release_lookup_code is null)
2929 AND EXISTS (SELECT 'Invoice is approved'
2930 FROM ap_invoice_distributions_all aida
2931 WHERE aida.invoice_id = aia.invoice_id
2932 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2933
2934 -- Cursor for Receipt based matching regular invoice
2935 -- Modified for AP invoice lines uptake in R12
2936 CURSOR l_ap_inv IS
2937 SELECT
2938 sum(aila.amount)
2939 , MAX(aila.line_number)
2940 , MAX(aia.invoice_id)
2941 , sum(aila.quantity_invoiced)
2942 FROM
2943 ap_invoices_all aia
2944 , ap_invoice_lines_all aila
2945 WHERE aia.invoice_id = aila.invoice_id
2946 AND aia.invoice_type_lookup_code in ('STANDARD','MIXED')
2947 AND aia.cancelled_date IS NULL
2948 AND aila.line_type_lookup_code = 'ITEM'
2949 AND aila.match_type = 'ITEM_TO_RECEIPT'
2950 AND aila.rcv_transaction_id = l_movement_transaction.rcv_transaction_id
2951 -- Bug 5655040. Commented as condition is modified and few more conditioned
2952 -- added to whereclause to check hold and disregard status.
2953 --AND NOT EXISTS (SELECT 1
2954 -- FROM ap_invoice_distributions_all aida
2955 -- WHERE aida.invoice_id = aia.invoice_id
2956 -- AND aida.invoice_line_number = aila.line_number
2957 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2958 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2959 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2960 FROM ap_holds_all aha
2961 WHERE aha.invoice_id = aia.invoice_id
2962 AND aha.release_lookup_code is null)
2963 AND EXISTS (SELECT 'Invoice is approved'
2964 FROM ap_invoice_distributions_all aida
2965 WHERE aida.invoice_id = aia.invoice_id
2966 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
2967
2968 -- Cursor for price correction in case of Receipt based matching.
2969 -- Modified for AP invoice lines uptake in R12
2970 -- Although price correction in AP directly reflects price correction for certain
2971 -- quantity, but for us we display only one invoice for each transaction, we still
2972 -- need to average the price for each qty after price adjustment. So here we only
2973 -- take the amount.
2974 --CURSOR l_ap_cm_inv IS
2975 CURSOR l_ap_prc_inv IS
2976 SELECT
2977 SUM(aila.amount)
2978 FROM
2979 ap_invoices_all aia
2980 , ap_invoice_lines_all aila
2981 WHERE aia.invoice_id = aila.invoice_id
2982 AND aia.cancelled_date IS NULL
2983 AND aila.line_type_lookup_code = 'ITEM'
2984 AND aila.match_type = 'PRICE_CORRECTION'
2985 AND aila.rcv_transaction_id = l_movement_transaction.rcv_transaction_id
2986 -- Bug 5655040. Commented as condition is modified and few more conditioned
2987 -- added to whereclause to check hold and disregard status.
2988 --AND NOT EXISTS (SELECT 1
2989 -- FROM ap_invoice_distributions_all aida
2990 -- WHERE aida.invoice_id = aia.invoice_id
2991 -- AND aida.invoice_line_number = aila.line_number
2992 -- AND NVL(aida.match_status_flag,'N') <> 'A');
2993 AND nvl(aila.discarded_flag, 'N') <> 'Y'
2994 AND NOT EXISTS (SELECT 'Unreleased holds exist'
2995 FROM ap_holds_all aha
2996 WHERE aha.invoice_id = aia.invoice_id
2997 AND aha.release_lookup_code is null)
2998 AND EXISTS (SELECT 'Invoice is approved'
2999 FROM ap_invoice_distributions_all aida
3000 WHERE aida.invoice_id = aia.invoice_id
3001 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
3002
3003 -- Cursor for quantity correction in case of Receipt based matching.
3004 -- Modified for AP invoice lines uptake in R12
3005 CURSOR l_ap_qtc_inv IS
3006 SELECT
3007 SUM(aila.quantity_invoiced)
3008 , SUM(aila.amount)
3009 FROM
3010 ap_invoices_all aia
3011 , ap_invoice_lines_all aila
3012 WHERE aia.invoice_id = aila.invoice_id
3013 AND aia.cancelled_date IS NULL
3014 AND aila.line_type_lookup_code = 'ITEM'
3015 AND aila.match_type = 'QTY_CORRECTION'
3016 AND aila.rcv_transaction_id = l_movement_transaction.rcv_transaction_id
3017 -- Bug 5655040. Commented as condition is modified and few more conditioned
3018 -- added to whereclause to check hold and disregard status.
3019 --AND NOT EXISTS (SELECT 1
3020 -- FROM ap_invoice_distributions_all aida
3021 -- WHERE aida.invoice_id = aia.invoice_id
3022 -- AND aida.invoice_line_number = aila.line_number
3023 -- AND NVL(aida.match_status_flag,'N') <> 'A');
3024 AND nvl(aila.discarded_flag, 'N') <> 'Y'
3025 AND NOT EXISTS (SELECT 'Unreleased holds exist'
3026 FROM ap_holds_all aha
3027 WHERE aha.invoice_id = aia.invoice_id
3028 AND aha.release_lookup_code is null)
3029 AND EXISTS (SELECT 'Invoice is approved'
3030 FROM ap_invoice_distributions_all aida
3031 WHERE aida.invoice_id = aia.invoice_id
3032 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
3033
3034 -- Cursor for Credit memos that is associated with RTV transaction
3035 -- in case of receipt based matching.
3036 -- modified in R12 to use line table
3037 CURSOR l_ap_rtv_cm_inv IS
3038 SELECT
3039 sum(aila.amount)
3040 , MAX(aila.line_number)
3041 , MAX(aia.invoice_id)
3042 , sum(aila.quantity_invoiced)
3043 FROM
3044 ap_invoices_all aia
3045 , ap_invoice_lines_all aila
3046 WHERE aia.invoice_id = aila.invoice_id
3047 AND aia.invoice_type_lookup_code in ('CREDIT','DEBIT')
3048 AND aia.cancelled_date IS NULL
3049 AND aila.line_type_lookup_code = 'ITEM'
3050 AND aila.match_type = 'ITEM_TO_RECEIPT'
3051 AND NVL(aila.quantity_invoiced,0) < 0
3052 AND aila.rcv_transaction_id = l_parent_transaction_id
3053 -- Bug 5655040. Commented as condition is modified and few more conditioned
3054 -- added to whereclause to check hold and disregard status.
3055 --AND NOT EXISTS (SELECT 1
3056 -- FROM ap_invoice_distributions_all aida
3057 -- WHERE aida.invoice_id = aia.invoice_id
3058 -- AND aida.invoice_line_number = aila.line_number
3059 -- AND NVL(aida.match_status_flag,'N') <> 'A');
3060 AND nvl(aila.discarded_flag, 'N') <> 'Y'
3061 AND NOT EXISTS (SELECT 'Unreleased holds exist'
3062 FROM ap_holds_all aha
3063 WHERE aha.invoice_id = aia.invoice_id
3064 AND aha.release_lookup_code is null)
3065 AND EXISTS (SELECT 'Invoice is approved'
3066 FROM ap_invoice_distributions_all aida
3067 WHERE aida.invoice_id = aia.invoice_id
3068 AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
3069
3070 --Cursor to check if this SO is for CTO items
3071 CURSOR l_cto IS
3072 SELECT
3073 item_type_code
3074 , top_model_line_id
3075 FROM
3076 oe_order_lines_all
3077 WHERE line_id = l_movement_transaction.order_line_id;
3078
3079 --Cursor to get total rtv transaction quantity
3080 CURSOR l_total_rtv_quantity IS
3081 SELECT
3082 SUM(quantity)
3083 FROM
3084 rcv_transactions
3085 WHERE po_header_id = l_movement_transaction.po_header_id
3086 AND transaction_type = 'RETURN TO VENDOR';
3087
3088 BEGIN
3089
3090 --Consigned support move condition from INVUINTB.pls to here
3091 -- IF (x_movement_transaction.document_source_type IN ('IO','INV')
3092 IF (x_movement_transaction.document_source_type IN ('INV')
3093 OR x_movement_transaction.consigned_flag = 'Y'
3094 OR x_movement_transaction.financial_document_flag = 'NOT_REQUIRED_CORRECT')
3095 THEN
3096 RETURN;
3097
3098 ELSE
3099 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3100 THEN
3101 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3102 , G_MODULE_NAME || l_procedure_name || '.begin'
3103 ,'enter procedure'
3104 );
3105 END IF;
3106
3107 l_movement_transaction := x_movement_transaction;
3108 l_stat_typ_transaction := p_stat_typ_transaction;
3109
3110 --Now do all the calculations for currency.
3111 --x_movement_transaction.currency_conversion_type :=
3112 --l_stat_typ_transaction.conversion_type;
3113
3114 l_tran_curr_code := x_movement_transaction.currency_code;
3115 l_tran_curr_rate := x_movement_transaction.currency_conversion_rate;
3116 l_tran_curr_date := x_movement_transaction.currency_conversion_date;
3117 l_tran_curr_type := x_movement_transaction.currency_conversion_type;
3118
3119 --IF l_movement_transaction.document_source_type IN ( 'SO','RMA')
3120 IF l_movement_transaction.document_source_type IN ( 'IO','SO','RMA')
3121 THEN
3122 --Find out the legal entity where this SO is created
3123 l_so_le_id := INV_MGD_MVT_UTILS_PKG.Get_SO_Legal_Entity
3124 (p_order_line_id => l_movement_transaction.order_line_id);
3125
3126 --Find out the legal entity where this SO is shipped
3127 l_shipping_le_id := INV_MGD_MVT_UTILS_PKG.Get_Shipping_Legal_Entity
3128 (p_warehouse_id => l_movement_transaction.organization_id);
3129
3130 --Debug: Part of bugfix 12635623
3131 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3132 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3133 , G_MODULE_NAME || l_procedure_name
3134 ,'SO LE:' || l_so_le_id ||
3135 ':Ship LE:' || l_shipping_le_id ||
3136 ':MVT LE:' || l_movement_transaction.entity_org_id
3137 );
3138 END IF;
3139
3140 --Check if this order line is of CTO item
3141 OPEN l_cto;
3142 FETCH l_cto INTO
3143 l_item_type_code
3144 , l_model_line_id;
3145 CLOSE l_cto;
3146
3147 IF l_item_type_code = 'CONFIG'
3148 THEN
3149 l_cto_line_id := l_movement_transaction.order_line_id;
3150
3151 --Set order line to model line id to calculate correct invoice
3152 l_movement_transaction.order_line_id := l_model_line_id;
3153 END IF;
3154
3155 -- Check if the transaction is a drop shipment; if it is not then
3156 -- fetch the l_sum_arc cursor and l_sum_cm_arc which gets the
3157 -- credit/memo transactions.
3158 IF l_movement_transaction.po_header_id IS NULL
3159 OR l_movement_transaction.po_header_id = FND_API.G_MISS_NUM
3160 THEN
3161 --Not drop shipment
3162 IF l_movement_transaction.document_source_type = 'SO'
3163 THEN
3164 --If this SO is created and shipped in different legal entity
3165 --and we are in invoice based triangulation mode,we need
3166 -- intercompany invoice
3167 IF (l_so_le_id IS NOT NULL
3168 AND l_so_le_id <> l_shipping_le_id
3169 AND NVL(l_stat_typ_transaction.triangulation_mode,'INVOICE_BASED') = 'INVOICE_BASED')
3170 THEN
3171 --If this processor is run at the legal entity where this SO
3172 --is created and the record created is Arrival intercompany SO
3173 --we need an intercompany ap invoice
3174 IF (l_so_le_id = l_movement_transaction.entity_org_id
3175 AND l_movement_transaction.movement_type = 'A')
3176 THEN
3177
3178 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3179 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3180 , G_MODULE_NAME || l_procedure_name
3181 ,'Opening l_ap_intercompany_so_arrival.'
3182 );
3183 END IF;
3184
3185 --intercompany ap invoice
3186 OPEN l_ap_intercompany_so_arrival;
3187 FETCH l_ap_intercompany_so_arrival INTO
3188 x_movement_transaction.invoice_id
3189 , x_movement_transaction.distribution_line_number
3190 , l_extended_amount
3191 , x_movement_transaction.invoice_quantity;
3192 CLOSE l_ap_intercompany_so_arrival;
3193
3194 --Debug: Part of bugfix 12635623
3195 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3196 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3197 , G_MODULE_NAME || l_procedure_name
3198 ,'invoice_id:' || x_movement_transaction.invoice_id ||
3199 ':distribution_line_number:' || x_movement_transaction.distribution_line_number ||
3200 ':l_extended_amount:' || l_extended_amount ||
3201 ':invoice_quantity:' || x_movement_transaction.invoice_quantity
3202 );
3203 END IF;
3204
3205 IF x_movement_transaction.invoice_id IS NOT NULL
3206 THEN
3207 --Recalculate invoice amount for ap intercompany CTO item
3208 IF l_item_type_code = 'CONFIG'
3209 THEN
3210 Calc_Cto_Amount_ap
3211 ( p_order_line_id => l_movement_transaction.order_line_id
3212 , p_order_number => to_char(l_movement_transaction.order_number)
3213 , x_extended_amount => l_extended_amount
3214 );
3215 END IF;
3216
3217 -- bug 5440432, Following IF block is added to get details from AR invoice
3218 -- if Amount or Quantity is missing from AP Invoice.
3219 IF l_extended_amount = 0 OR x_movement_transaction.invoice_quantity = 0 THEN
3220
3221 --Bugfix 12949295: Changed the cursor.
3222 OPEN l_ap_ic_inv_qty_so_arr;
3223 FETCH l_ap_ic_inv_qty_so_arr INTO
3224 l_ar_invoiced_amount,
3225 l_ar_invoiced_qty;
3226 CLOSE l_ap_ic_inv_qty_so_arr;
3227
3228 --Debug: Part of bugfix 12635623
3229 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3230 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3231 , G_MODULE_NAME || l_procedure_name
3232 ,'l_ar_invoiced_amount:' || l_ar_invoiced_amount ||
3233 ':l_ar_invoiced_qty:' || l_ar_invoiced_qty
3234 );
3235 END IF;
3236
3237 IF l_extended_amount = 0 THEN
3238 l_extended_amount := l_ar_invoiced_amount;
3239 END IF;
3240
3241 IF x_movement_transaction.invoice_quantity = 0 THEN
3242 x_movement_transaction.invoice_quantity := l_ar_invoiced_qty;
3243 END IF;
3244 END IF;
3245
3246 --Open cursor to get other invoice information
3247 OPEN l_ap_intercompany_invo;
3248 FETCH l_ap_intercompany_invo INTO
3249 x_movement_transaction.currency_code
3250 , x_movement_transaction.currency_conversion_rate
3251 , x_movement_transaction.currency_conversion_type
3252 , x_movement_transaction.currency_conversion_date
3253 , x_movement_transaction.invoice_batch_id
3254 , x_movement_transaction.invoice_date_reference;
3258 x_movement_transaction.currency_code := l_tran_curr_code;
3255
3256 IF l_ap_intercompany_invo%NOTFOUND
3257 THEN
3259 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
3260 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
3261 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
3262 x_movement_transaction.invoice_batch_id := null;
3263 x_movement_transaction.invoice_date_reference := null;
3264 END IF;
3265 CLOSE l_ap_intercompany_invo;
3266 END IF; --yawang
3267
3268 --If this processor is run at the legal entity where this SO
3269 --is created but the record created is a virtual Dispatch to customer
3270 --we need an regular ar invoice to customer
3271 ELSIF (l_so_le_id = l_movement_transaction.entity_org_id
3272 AND l_movement_transaction.movement_type = 'D')
3273 THEN
3274
3275 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3276 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3277 , G_MODULE_NAME || l_procedure_name
3278 ,'Opening l_sum_arc.'
3279 );
3280 END IF;
3281
3282 --Regular SO invoice
3283 OPEN l_sum_arc;
3284 FETCH l_sum_arc INTO
3285 x_movement_transaction.customer_trx_line_id
3286 , x_movement_transaction.invoice_id
3287 , l_extended_amount
3288 , x_movement_transaction.invoice_quantity;
3289 CLOSE l_sum_arc;
3290
3291 --Debug: Part of bugfix 12635623
3292 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3293 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3294 , G_MODULE_NAME || l_procedure_name
3295 ,'invoice_id:' || x_movement_transaction.invoice_id ||
3296 ':customer_trx_line_id:' || x_movement_transaction.customer_trx_line_id ||
3297 ':l_extended_amount:' || l_extended_amount ||
3298 ':invoice_quantity:' || x_movement_transaction.invoice_quantity
3299 );
3300 END IF;
3301
3302 IF x_movement_transaction.invoice_id IS NOT NULL
3303 THEN
3304 OPEN l_sum_cm_arc;
3305 FETCH l_sum_cm_arc INTO
3306 l_cm_extended_amount;
3307 CLOSE l_sum_cm_arc;
3308
3309 --Recalculate invoice amount for CTO item
3310 IF l_item_type_code = 'CONFIG'
3311 THEN
3312 Calc_Cto_Amount_So
3313 ( p_order_line_id => l_movement_transaction.order_line_id
3314 , p_order_number => to_char(l_movement_transaction.order_number)
3315 , x_extended_amount => l_extended_amount
3316 , x_cm_extended_amount => l_cm_extended_amount
3317 );
3318 END IF;
3319 END IF;
3320
3321 --If this processor is run at the legal entity where this SO
3322 --is shipped, we need an ar intercompany invoice
3323 ELSIF (l_shipping_le_id = l_movement_transaction.entity_org_id)
3324 THEN
3325
3326 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3327 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3328 , G_MODULE_NAME || l_procedure_name
3329 ,'l_ar_intercompany.'
3330 );
3331 END IF;
3332
3333 OPEN l_ar_intercompany;
3334 FETCH l_ar_intercompany INTO
3335 x_movement_transaction.customer_trx_line_id
3336 , x_movement_transaction.invoice_id
3337 , l_extended_amount
3338 , x_movement_transaction.invoice_quantity;
3339 CLOSE l_ar_intercompany;
3340
3341 --Debug: Part of bugfix 12635623
3342 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3343 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3344 , G_MODULE_NAME || l_procedure_name
3345 ,'invoice_id:' || x_movement_transaction.invoice_id ||
3346 ':customer_trx_line_id:' || x_movement_transaction.customer_trx_line_id ||
3347 ':l_extended_amount:' || l_extended_amount ||
3348 ':invoice_quantity:' || x_movement_transaction.invoice_quantity
3349 );
3350 END IF;
3351
3352 --Recalculate invoice amount for ar intercompany CTO item
3353 IF l_item_type_code = 'CONFIG'
3354 AND x_movement_transaction.customer_trx_line_id IS NOT NULL
3355 THEN
3356 Calc_Cto_Amount_ar
3357 ( p_order_line_id => l_movement_transaction.order_line_id
3358 , p_order_number => to_char(l_movement_transaction.order_number)
3359 , x_extended_amount => l_extended_amount
3360 );
3361 END IF;
3362 END IF;
3363
3364 ELSE
3365 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3366 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3367 , G_MODULE_NAME || l_procedure_name
3368 ,'Opening l_sum_arc for regular SO invoice.'
3369 );
3370 END IF;
3371
3372 --Regular SO invoice
3373 OPEN l_sum_arc;
3377 , l_extended_amount
3374 FETCH l_sum_arc INTO
3375 x_movement_transaction.customer_trx_line_id
3376 , x_movement_transaction.invoice_id
3378 , x_movement_transaction.invoice_quantity;
3379 CLOSE l_sum_arc;
3380
3381 --Debug: Part of bugfix 12635623
3382 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3383 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3384 , G_MODULE_NAME || l_procedure_name
3385 ,'invoice_id:' || x_movement_transaction.invoice_id ||
3386 ':customer_trx_line_id:' || x_movement_transaction.customer_trx_line_id ||
3387 ':l_extended_amount:' || l_extended_amount ||
3388 ':invoice_quantity:' || x_movement_transaction.invoice_quantity
3389 );
3390 END IF;
3391
3392 IF x_movement_transaction.invoice_id IS NOT NULL
3393 THEN
3394 OPEN l_sum_cm_arc;
3395 FETCH l_sum_cm_arc INTO
3396 l_cm_extended_amount;
3397 CLOSE l_sum_cm_arc;
3398
3399 --Recalculate invoice amount for CTO item
3400 IF l_item_type_code = 'CONFIG'
3401 THEN
3402 Calc_Cto_Amount_So
3403 ( p_order_line_id => l_movement_transaction.order_line_id
3404 , p_order_number => to_char(l_movement_transaction.order_number)
3405 , x_extended_amount => l_extended_amount
3406 , x_cm_extended_amount => l_cm_extended_amount
3407 );
3408 END IF;
3409
3410 --Fix Italian bug 2861110.Update_invoice_info will revert any netted invoice
3411 --amt and qty back to original SO invoice amt and qty. The following code
3412 --will net the invoice amt and qty again for processed RMA
3413 IF l_stat_typ_transaction.returns_processing = 'AGGRTN'
3414 THEN
3415 Calc_Processed_Ret_Data
3416 (p_movement_transaction => l_movement_transaction
3417 , x_processed_ret_amt => l_processed_ret_amt
3418 , x_processed_ret_qty => l_processed_ret_qty
3419 );
3420
3421 --Net processed rma amt and qty to SO
3422 l_extended_amount := l_extended_amount + NVL(l_processed_ret_amt,0);
3423 x_movement_transaction.invoice_quantity :=
3424 x_movement_transaction.invoice_quantity + NVL(l_processed_ret_qty,0);
3425 END IF;
3426 END IF; --yawang end of invoice not null
3427 END IF;
3428 END IF; --End document_source_type = 'SO'
3429
3430
3431 /*********************************** Special case for an IO - BEGIN **************************************/
3432
3433 IF l_movement_transaction.document_source_type = 'IO'
3434 THEN
3435
3436 --Debug: Part of bugfix 12635623
3437 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3438 THEN
3439 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3440 , G_MODULE_NAME || l_procedure_name
3441 ,'movement_type:' || l_movement_transaction.movement_type
3442 );
3443 END IF;
3444
3445 --If this processor is run at the legal entity where this SO
3446 --is created and the record created is Arrival intercompany SO
3447 --we need an intercompany ap invoice
3448
3449 -- IF (l_so_le_id = l_movement_transaction.entity_org_id
3450 -- AND l_movement_transaction.movement_type = 'A')
3451 -- kdevadas
3452 --FOR an Internal ORDER, the so le Id would always be the ship TO LE -
3453 --will NOT be the same AS the entity_org_id WHERE MSP IS RUN
3454 IF (l_movement_transaction.movement_type = 'A')
3455 THEN
3456 --intercompany ap invoice
3457 --Bugfix 12949295: Changing the cursor
3458 OPEN l_ap_intercompany_io_arrival;
3459 FETCH l_ap_intercompany_io_arrival INTO
3460 x_movement_transaction.invoice_id
3461 , x_movement_transaction.distribution_line_number
3462 , l_extended_amount
3463 , x_movement_transaction.invoice_quantity;
3464
3465 --Debug: Part of bugfix 12635623
3466 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3467 THEN
3468 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3469 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANY IO ARRIVAL INVOICE ID ********************* '
3470 , x_movement_transaction.invoice_id
3471 );
3472 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3473 , G_MODULE_NAME || l_procedure_name
3474 , 'Distribution_line_number:' || x_movement_transaction.distribution_line_number ||
3475 ':l_extended_amount:' || l_extended_amount ||
3476 ':invoice_quantity:' || x_movement_transaction.invoice_quantity
3477 );
3478 END IF ;
3479
3480 --Bugfix 12949295: Changing the cursor
3481 CLOSE l_ap_intercompany_io_arrival;
3482
3483
3484 IF x_movement_transaction.invoice_id IS NOT NULL
3485 THEN
3486
3487 --Recalculate invoice amount for ap intercompany CTO item
3488 IF l_item_type_code = 'CONFIG'
3489 THEN
3490 Calc_Cto_Amount_ap
3491 ( p_order_line_id => l_movement_transaction.order_line_id
3495 END IF;
3492 , p_order_number => to_char(l_movement_transaction.order_number)
3493 , x_extended_amount => l_extended_amount
3494 );
3496 -- bug 5411006, Following IF block is added to get details from AR invoice
3497 -- if Amount or Quantity is missing from AP Invoice.
3498 IF l_extended_amount = 0 OR x_movement_transaction.invoice_quantity = 0 THEN
3499 --Bugfix 12949295: Changing the cursor
3500 OPEN l_ap_ic_inv_qty_io_arr;
3501 FETCH l_ap_ic_inv_qty_io_arr INTO
3502 l_ar_invoiced_amount,
3503 l_ar_invoiced_qty;
3504 CLOSE l_ap_ic_inv_qty_io_arr;
3505
3506 --Debug: Part of bugfix 12635623
3507 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3508 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3509 , G_MODULE_NAME || l_procedure_name
3510 ,'l_ar_invoiced_amount:' || l_ar_invoiced_amount ||
3511 ':l_ar_invoiced_qty:' || l_ar_invoiced_qty
3512 );
3513 END IF;
3514
3515 IF l_extended_amount = 0 THEN
3516 l_extended_amount := l_ar_invoiced_amount;
3517 END IF;
3518
3519 IF x_movement_transaction.invoice_quantity = 0 THEN
3520 x_movement_transaction.invoice_quantity := l_ar_invoiced_qty;
3521 END IF;
3522
3523 END IF;
3524
3525 --Open cursor to get other invoice information
3526 OPEN l_ap_intercompany_invo;
3527 FETCH l_ap_intercompany_invo INTO
3528 x_movement_transaction.currency_code
3529 , x_movement_transaction.currency_conversion_rate
3530 , x_movement_transaction.currency_conversion_type
3531 , x_movement_transaction.currency_conversion_date
3532 , x_movement_transaction.invoice_batch_id
3533 , x_movement_transaction.invoice_date_reference;
3534
3535 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3536 THEN
3537 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3538 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANY CURRENCY CODE ********************* '
3539 , x_movement_transaction.currency_code
3540 );
3541 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3542 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANY currency_conversion_type ********************* '
3543 , x_movement_transaction.currency_conversion_rate
3544 );
3545 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3546 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANYcurrency_conversion_typeE ********************* '
3547 , x_movement_transaction.currency_conversion_type
3548 );
3549 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3550 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANY currency_conversion_date********************* '
3551 , x_movement_transaction.currency_conversion_date
3552 );
3553 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3554 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANY invoice_batch_id ********************* '
3555 , x_movement_transaction.invoice_batch_id
3556 );
3557
3558 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3559 , G_MODULE_NAME || l_procedure_name || 'AP INTERCOMPANY invoice_date_reference ********************* '
3560 , x_movement_transaction.invoice_date_reference
3561 );
3562 END IF ;
3563
3564 IF l_ap_intercompany_invo%NOTFOUND
3565 THEN
3566 x_movement_transaction.currency_code := l_tran_curr_code;
3567 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
3568 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
3569 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
3570 x_movement_transaction.invoice_batch_id := null;
3571 x_movement_transaction.invoice_date_reference := null;
3572 END IF;
3573
3574 CLOSE l_ap_intercompany_invo;
3575 END IF; --yawang
3576
3577 --If this processor is run at the legal entity where this SO
3578 --is shipped, we need an ar intercompany invoice
3579 ELSIF (l_shipping_le_id = l_movement_transaction.entity_org_id)
3580 THEN
3581 OPEN l_ar_intercompany;
3582 FETCH l_ar_intercompany INTO
3583 x_movement_transaction.customer_trx_line_id
3584 , x_movement_transaction.invoice_id
3585 , l_extended_amount
3586 , x_movement_transaction.invoice_quantity;
3587 CLOSE l_ar_intercompany;
3588
3589 --Debug: Part of bugfix 12635623
3590 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3591 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3592 , G_MODULE_NAME || l_procedure_name
3593 ,'customer_trx_line_id:' || x_movement_transaction.customer_trx_line_id ||
3594 ':invoice_id:' || x_movement_transaction.invoice_id ||
3595 ':l_extended_amount:' || l_extended_amount ||
3596 ':invoice_quantity:' || x_movement_transaction.invoice_quantity
3597 );
3598 END IF;
3599
3603 THEN
3600 --Recalculate invoice amount for ar intercompany CTO item
3601 IF l_item_type_code = 'CONFIG'
3602 AND x_movement_transaction.customer_trx_line_id IS NOT NULL
3604 Calc_Cto_Amount_ar
3605 ( p_order_line_id => l_movement_transaction.order_line_id
3606 , p_order_number => to_char(l_movement_transaction.order_number)
3607 , x_extended_amount => l_extended_amount
3608 );
3609 END IF;
3610
3611 END IF; -- end if elsif
3612 END IF ; -- end of IO loop
3613
3614
3615 /*********************************** Special case for an IO - END**************************************/
3616
3617 IF l_movement_transaction.document_source_type = 'RMA'
3618 THEN
3619 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3620 THEN
3621 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3622 , G_MODULE_NAME || l_procedure_name || '.begin'
3623 ,'************ 2 RMA ******************'
3624 );
3625 END IF;
3626
3627 /*bug 8435314 */
3628 Open l_rma_config;
3629 Fetch l_rma_config
3630 into l_item_type_code;
3631 Close l_rma_config;
3632
3633 IF l_item_type_code = 'CONFIG'
3634 Then
3635 Open l_rma_model_id;
3636 Fetch l_rma_model_id into l_model_line_id;
3637 Close l_rma_model_id;
3638
3639 IF l_model_line_id is not null then
3640 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3641 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3642 , G_MODULE_NAME || l_procedure_name
3643 , 'The l_movement_transaction.order_line_id 1 : '||l_movement_transaction.order_line_id);
3644 END IF;
3645
3646 l_movement_transaction.order_line_id :=l_model_line_id;
3647
3648 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3649 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3650 , G_MODULE_NAME || l_procedure_name
3651 , 'The l_movement_transaction.order_line_id 2 : '||l_movement_transaction.order_line_id);
3652 END IF;
3653 End if;
3654 End if;
3655 /*End bug 8435314*/
3656 /* bug# 7165989 Intercompany AP/AR Invoice for RMA*/
3657 --If this RMA is created and shipped in different legal entity
3658 --and we are in invoice based triangulation mode,we need
3659 -- intercompany invoice
3660
3661 IF (l_so_le_id IS NOT NULL
3662 AND l_so_le_id <> l_shipping_le_id
3663 AND NVL(l_stat_typ_transaction.triangulation_mode,'INVOICE_BASED') ='INVOICE_BASED')
3664 THEN
3665 --If this processor is run at the legal entity where this SO
3666 --is created and the record created is Arrival intercompany SO
3667 --we need an intercompany ap invoice
3668 IF (l_so_le_id = l_movement_transaction.entity_org_id
3669 AND l_movement_transaction.movement_type = 'D')--change from A to D
3670 THEN
3671 --Debug as part of bugfix 12949295
3672 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3673 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3674 , G_MODULE_NAME || l_procedure_name
3675 , 'Opening l_ap_intercompany_rma');
3676 END IF;
3677
3678 --intercompany ap invoice
3679 --Bugfix 12949295: Changing the cursor.
3680 OPEN l_ap_intercompany_rma;
3681 FETCH l_ap_intercompany_rma INTO
3682 x_movement_transaction.invoice_id
3683 , x_movement_transaction.distribution_line_number
3684 , l_extended_amount
3685 , x_movement_transaction.invoice_quantity;
3686 CLOSE l_ap_intercompany_rma;
3687
3688 --Debug: Part of bugfix 12949295
3689 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3690 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3691 , G_MODULE_NAME || l_procedure_name
3692 , 'invoice_id:'||x_movement_transaction.invoice_id ||
3693 ':distribution_line_number:' || x_movement_transaction.distribution_line_number ||
3694 ':l_extended_amount:' || l_extended_amount ||
3695 ':invoice_quantity:' || x_movement_transaction.invoice_quantity);
3696 END IF;
3697
3698 IF x_movement_transaction.invoice_id IS NOT NULL
3699 THEN
3700 --Recalculate invoice amount for ap intercompany CTO item
3701 IF l_item_type_code = 'CONFIG'
3702 THEN
3703 Calc_Cto_Amount_ap
3704 ( p_order_line_id => l_movement_transaction.order_line_id
3705 , p_order_number => to_char(l_movement_transaction.order_number)
3706 , x_extended_amount => l_extended_amount
3707 );
3708 END IF;
3709
3710 -- bug 5411006, Following IF block is added to get details from AR invoice
3711 -- if Amount or Quantity is missing from AP Invoice.
3712
3713 /* 7165989 - new cursor defined for intercompany RMA Dispatch */
3714 /* The invoice quantity is fetched from quantity_credited in RA_CUSTOMER_TRX_LINES_ALL*/
3715 /* as AP_INVOICES_ALL does not have the invoice quantity */
3716 IF l_extended_amount = 0 OR x_movement_transaction.invoice_quantity = 0 THEN
3717
3718 OPEN l_ap_intercompany_rma_inv_qty;
3719 FETCH l_ap_intercompany_rma_inv_qty INTO
3720 l_ar_invoiced_amount, l_ar_invoiced_qty;
3721 CLOSE l_ap_intercompany_rma_inv_qty;
3722
3723 --Debug: Part of bugfix 12949295
3724 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3725 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3726 , G_MODULE_NAME || l_procedure_name
3727 , 'l_ar_invoiced_amount:'|| l_ar_invoiced_amount ||
3728 ':l_ar_invoiced_qty:' || l_ar_invoiced_qty);
3729 END IF;
3730
3731 IF l_extended_amount = 0 THEN
3732 l_extended_amount := l_ar_invoiced_amount;
3733 END IF;
3734
3735 IF x_movement_transaction.invoice_quantity = 0 THEN
3736 x_movement_transaction.invoice_quantity := l_ar_invoiced_qty;
3737 END IF;
3738
3739 --Debug: Part of bugfix 12949295
3740 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3741 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3742 , G_MODULE_NAME || l_procedure_name
3743 , 'The invoice qty(2) is : '||x_movement_transaction.invoice_quantity);
3744 END IF;
3745
3746 END IF;
3747
3748 --Open cursor to get other invoice information
3749 OPEN l_ap_intercompany_invo;
3750 FETCH l_ap_intercompany_invo INTO
3751 x_movement_transaction.currency_code
3752 , x_movement_transaction.currency_conversion_rate
3753 , x_movement_transaction.currency_conversion_type
3754 , x_movement_transaction.currency_conversion_date
3755 , x_movement_transaction.invoice_batch_id
3756 , x_movement_transaction.invoice_date_reference;
3757
3758 --Debug: Part of bugfix 12949295
3759 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3760 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3761 , G_MODULE_NAME || l_procedure_name
3762 , 'currency_code:'|| x_movement_transaction.currency_code ||
3763 ':rate:'|| x_movement_transaction.currency_conversion_rate ||
3764 ':type:'|| x_movement_transaction.currency_conversion_type ||
3765 ':date:'|| x_movement_transaction.currency_conversion_date ||
3766 ':batch_id:'|| x_movement_transaction.invoice_batch_id ||
3767 ':date_reference:'|| x_movement_transaction.invoice_date_reference);
3768 END IF;
3769
3770 IF l_ap_intercompany_invo%NOTFOUND
3771 THEN
3772 x_movement_transaction.currency_code := l_tran_curr_code;
3773 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
3774 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
3775 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
3776 x_movement_transaction.invoice_batch_id := null;
3777 x_movement_transaction.invoice_date_reference := null;
3778 END IF;
3779
3780 CLOSE l_ap_intercompany_invo;
3781 END IF; -- Intercompany AR Invoice
3782
3783 --If this processor is run at the legal entity where this SO
3784 --is created but the record created is a virtual Dispatch to customer
3785 --we need an regular ar invoice to customer
3786 ELSIF (l_so_le_id = l_movement_transaction.entity_org_id
3787 AND l_movement_transaction.movement_type = 'A')--change from D to A
3788 THEN
3789 --Debug as part of bugfix 12949295
3790 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3791 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3792 , G_MODULE_NAME || l_procedure_name
3793 , 'Opening l_sum_rma_arc');
3794 END IF;
3795
3796 --Check for Credit/debit memo transaction type against RMA.
3797 OPEN l_sum_rma_arc;
3798 FETCH l_sum_rma_arc INTO
3799 x_movement_transaction.customer_trx_line_id
3800 , x_movement_transaction.invoice_id
3801 , l_extended_amount
3802 , x_movement_transaction.invoice_quantity;
3803 CLOSE l_sum_rma_arc;
3804
3805 --Debug: Part of bugfix 12949295
3806 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3807 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3808 , G_MODULE_NAME || l_procedure_name
3809 , 'line_id:'|| l_movement_transaction.order_line_id ||
3810 ':order_number:'|| l_movement_transaction.order_number ||
3811 ':org_id:'|| l_movement_transaction.org_id ||
3812 ':customer_trx_line_id:' || x_movement_transaction.customer_trx_line_id ||
3813 ':invoice_id:' || x_movement_transaction.invoice_id ||
3814 ':l_extended_amount:' || l_extended_amount ||
3815 ':invoice_quantity:' || x_movement_transaction.invoice_quantity);
3816 END IF;
3817
3818 --Recalculate invoice amount for rma CTO item
3819 IF l_item_type_code = 'CONFIG'
3820 AND x_movement_transaction.customer_trx_line_id IS NOT NULL
3821 THEN
3822 Calc_Cto_Amount_Rma
3823 ( p_order_line_id => l_movement_transaction.order_line_id
3824 , p_order_number => to_char(l_movement_transaction.order_number)
3825 , p_org_id => l_movement_transaction.org_id
3826 , x_extended_amount => l_extended_amount
3827 );
3828
3829 FND_FILE.put_line(FND_FILE.log, 'The l_extended_amount 3.1 : '||l_extended_amount);
3830 END IF;
3831
3832 --If this processor is run at the legal entity where this SO
3833 --is shipped, we need an ar intercompany invoice
3834 ELSIF (l_shipping_le_id = l_movement_transaction.entity_org_id
3835 AND l_movement_transaction.movement_type = 'A')
3836 THEN
3837 --Debug as part of bugfix 12949295
3838 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3839 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3840 , G_MODULE_NAME || l_procedure_name
3841 , 'Opening l_ar_intercompany_rma');
3842 END IF;
3843
3844 OPEN l_ar_intercompany_rma;
3845 FETCH l_ar_intercompany_rma INTO
3846 x_movement_transaction.customer_trx_line_id
3847 , x_movement_transaction.invoice_id
3848 , l_extended_amount
3849 , x_movement_transaction.invoice_quantity;
3850 CLOSE l_ar_intercompany_rma;
3851
3852 --Debug: Part of bugfix 12635623
3853 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3854 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3855 , G_MODULE_NAME || l_procedure_name
3856 ,'customer_trx_line_id:' || x_movement_transaction.customer_trx_line_id ||
3857 ':invoice_id:' || x_movement_transaction.invoice_id ||
3858 ':l_extended_amount:' || l_extended_amount ||
3859 ':invoice_quantity:' || x_movement_transaction.invoice_quantity
3860 );
3861 END IF;
3862
3863 --Recalculate invoice amount for ar intercompany CTO item
3864 IF l_item_type_code = 'CONFIG'
3865 AND x_movement_transaction.customer_trx_line_id IS NOT NULL
3866 THEN
3867 Calc_Cto_Amount_ar
3868 ( p_order_line_id => l_movement_transaction.order_line_id
3869 , p_order_number => to_char(l_movement_transaction.order_number)
3870 , x_extended_amount => l_extended_amount
3871 );
3872 END IF;
3873
3874 END IF;
3875 ELSE
3876 --Debug as part of bugfix 12949295
3877 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3878 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3879 , G_MODULE_NAME || l_procedure_name
3880 , 'Opening l_sum_rma_arc');
3881 END IF;
3882
3883 /* bug# 7165989 End Intercompany AP/AR Invoice for RMA*/
3884 --Check for Credit/debit memo transaction type against SO.
3885 OPEN l_sum_rma_arc;
3886 FETCH l_sum_rma_arc INTO
3887 x_movement_transaction.customer_trx_line_id
3888 , x_movement_transaction.invoice_id
3889 , l_extended_amount
3890 , x_movement_transaction.invoice_quantity;
3891 CLOSE l_sum_rma_arc;
3892
3893 --Debug: Part of bugfix 12949295
3894 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3895 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3896 , G_MODULE_NAME || l_procedure_name
3897 , 'customer_trx_line_id:' || x_movement_transaction.customer_trx_line_id ||
3898 ':invoice_id:' || x_movement_transaction.invoice_id ||
3899 ':l_extended_amount:' || l_extended_amount ||
3900 ':invoice_quantity:' || x_movement_transaction.invoice_quantity);
3901 END IF;
3902
3903 --Recalculate invoice amount for rma CTO item
3904 IF l_item_type_code = 'CONFIG'
3905 AND x_movement_transaction.customer_trx_line_id IS NOT NULL
3906 THEN
3907 FND_FILE.put_line(FND_FILE.log, 'The l_movement_transaction.order_line_id 4 : '||l_movement_transaction.order_line_id);
3908 FND_FILE.put_line(FND_FILE.log, 'The l_movement_transaction.order_number 4: '||l_movement_transaction.order_number);
3909 FND_FILE.put_line(FND_FILE.log, 'The l_extended_amount 1 4: '||l_extended_amount);
3910 FND_FILE.put_line(FND_FILE.log, 'The l_movement_transaction.org_id 4 : '||l_movement_transaction.org_id);
3911
3912 Calc_Cto_Amount_Rma( p_order_line_id => l_movement_transaction.order_line_id
3913 , p_order_number => to_char(l_movement_transaction.order_number)
3914 , p_org_id => l_movement_transaction.org_id
3915 , x_extended_amount => l_extended_amount
3916 );
3917 FND_FILE.put_line(FND_FILE.log, 'The l_extended_amount 4.1: '||l_extended_amount);
3918 END IF;
3919
3920 /*--Fix bug 2695323
3921 --In the case of multiple receipts for a KIT RMA, the kit order line
3922 --is not split for the multiple receipts. One invoice for the order line
3923 --and multiple receipts. Do a proportional calculation for the invoice
3924 --qty for each receipt
3925 --Get total return qty
3926 SELECT ordered_quantity
3927 INTO l_total_rma_qty
3928 FROM oe_order_lines_all
3929 WHERE line_id = l_movement_transaction.order_line_id;
3930
3931 x_movement_transaction.invoice_quantity :=
3932 (l_movement_transaction.transaction_quantity/l_total_rma_qty)
3933 * x_movement_transaction.invoice_quantity;
3934 l_extended_amount :=
3935 (l_movement_transaction.transaction_quantity/l_total_rma_qty)
3936 * l_extended_amount;*/
3937 END IF;
3938 End if;/*7165989 -- RMA ends*/
3939
3940 --Get other invoice information
3941 --Bug 6035548. Invoice details for RMA should always be calculated
3942 --even when selling org is diff than shipping org.
3943 IF (l_so_le_id <> l_shipping_le_id
3944 AND l_so_le_id = l_movement_transaction.entity_org_id
3945 AND l_movement_transaction.movement_type = 'A'
3946 AND l_movement_transaction.document_source_type <> 'RMA' )
3947 THEN
3948 NULL;
3949 ELSE
3950 /* Bug 7165989 - For IO arrival, look only at the AP invoice, not the AR invoice */
3951 IF (l_movement_transaction.movement_type = 'A'
3952 AND l_movement_transaction.document_source_type = 'IO')
3953 OR (l_movement_transaction.movement_type = 'D'
3954 AND l_movement_transaction.document_source_type = 'RMA') THEN
3955 NULL;
3956
3957 ELSE
3958 OPEN l_arc;
3959 FETCH l_arc INTO
3960 x_movement_transaction.invoice_date_reference
3961 , x_movement_transaction.invoice_batch_id
3962 , x_movement_transaction.currency_conversion_rate
3963 , x_movement_transaction.currency_conversion_type
3964 , x_movement_transaction.currency_conversion_date
3965 , x_movement_transaction.currency_code;
3966
3967 IF l_arc%NOTFOUND
3968 THEN
3969 x_movement_transaction.currency_code := l_tran_curr_code;
3970 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
3971 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
3972 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
3973 x_movement_transaction.invoice_batch_id := null;
3974 x_movement_transaction.invoice_date_reference := null;
3975 END IF;
3976 CLOSE l_arc;
3977
3978 --Debug: Part of bugfix 12949295
3979 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3980 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3981 , G_MODULE_NAME || l_procedure_name
3982 , 'currency_code:'|| x_movement_transaction.currency_code ||
3983 ':rate:'|| x_movement_transaction.currency_conversion_rate ||
3984 ':type:'|| x_movement_transaction.currency_conversion_type ||
3985 ':date:'|| x_movement_transaction.currency_conversion_date ||
3986 ':batch_id:'|| x_movement_transaction.invoice_batch_id ||
3987 ':date_reference:'|| x_movement_transaction.invoice_date_reference);
3988 END IF;
3989 END IF;
3990 END IF;
3991
3992 ELSE --drop shimement
3993 OPEN l_sum_drparc;
3994 FETCH l_sum_drparc INTO
3995 x_movement_transaction.customer_trx_line_id
3996 , x_movement_transaction.invoice_id
3997 , l_extended_amount
3998 , x_movement_transaction.invoice_quantity;
3999 CLOSE l_sum_drparc;
4000
4001 IF x_movement_transaction.invoice_id IS NOT NULL
4002 THEN
4003 OPEN l_sum_cm_drparc;
4004 FETCH l_sum_cm_drparc INTO
4005 l_cm_extended_amount;
4006 CLOSE l_sum_cm_drparc;
4007 END IF;
4008
4009 OPEN l_drparc;
4010 FETCH l_drparc INTO
4011 x_movement_transaction.invoice_date_reference
4012 , x_movement_transaction.invoice_batch_id
4013 , x_movement_transaction.currency_conversion_rate
4014 , x_movement_transaction.currency_conversion_type
4015 , x_movement_transaction.currency_conversion_date
4016 , x_movement_transaction.currency_code;
4017 CLOSE l_drparc;
4018
4019 --Recalculate invoice amount for dropship CTO item
4020 IF l_item_type_code = 'CONFIG'
4021 AND x_movement_transaction.customer_trx_line_id IS NOT NULL
4022 THEN
4023 Calc_Cto_Amount_Drp
4024 ( p_order_line_id => l_movement_transaction.order_line_id
4025 , p_order_number => to_char(l_movement_transaction.order_number)
4026 , x_extended_amount => l_extended_amount
4027 , x_cm_extended_amount => l_cm_extended_amount
4031
4028 );
4029 END IF;
4030 END IF;
4032 IF l_extended_amount IS NULL
4033 THEN
4034 x_movement_transaction.invoice_line_ext_value := null;
4035 x_movement_transaction.invoice_unit_price := null;
4036 x_movement_transaction.invoice_quantity := null;
4037
4038 ELSE
4039 l_extended_amount := l_extended_amount + NVL(l_cm_extended_amount,0);
4040 x_movement_transaction.invoice_line_ext_value := l_extended_amount;
4041
4042 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4043 THEN
4044 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4045 , G_MODULE_NAME || l_procedure_name || 'EXTENDED AMOUNT IS ********************* '
4046 , l_extended_amount
4047 );
4048 END IF ;
4049
4050 x_movement_transaction.invoice_quantity :=
4051 NVL(x_movement_transaction.invoice_quantity, x_movement_transaction.transaction_quantity);
4052
4053 IF (x_movement_transaction.invoice_quantity IS NOT NULL
4054 AND x_movement_transaction.invoice_quantity <> 0)
4055 THEN
4056 x_movement_transaction.invoice_unit_price :=
4057 l_extended_amount / x_movement_transaction.invoice_quantity;
4058 -- IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4059 -- THEN
4060 -- FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4061 -- , G_MODULE_NAME || l_procedure_name || 'unit_price IS ********************* '
4062 -- , x_movement_transaction.invoice_unit_price
4063 -- );
4064 -- END IF ;
4065
4066 -- IF (x_movement_transaction.invoice_id IS NOT NULL)
4067 -- THEN
4068 -- /* if intercompany invoice exists, the document unit price will be the same as the invoice unit price - 6889669 */
4069 -- --x_movement_transaction.document_unit_price := x_movement_transaction.invoice_unit_price;
4070 -- x_movement_transaction.document_line_ext_value := x_movement_transaction.invoice_unit_price *
4071 -- x_movement_transaction.transaction_quantity;
4072 -- END IF;
4073 ELSE
4074 x_movement_transaction.invoice_unit_price := null;
4075 END IF;
4076 END IF;
4077
4078 ELSIF l_movement_transaction.document_source_type IN ('PO')
4079 THEN
4080 -- Fetch the cursor for non credit memo type transactions. This is
4081 -- against receipt based matching if one exists;
4082
4083 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4084 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4085 ,G_MODULE_NAME || l_procedure_name
4086 ,' Inside doc_src_type = PO'
4087 );
4088 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4089 ,G_MODULE_NAME || l_procedure_name
4090 ,' Opening l_ap_inv for rcv_trx_id:' || l_movement_transaction.rcv_transaction_id
4091 );
4092 END IF;
4093
4094 OPEN l_ap_inv ;
4095 FETCH l_ap_inv INTO
4096 l_extended_amount
4097 , x_movement_transaction.distribution_line_number
4098 , x_movement_transaction.invoice_id
4099 , l_invoice_quantity;
4100 CLOSE l_ap_inv;
4101
4102 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4103 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4104 ,G_MODULE_NAME || l_procedure_name
4105 ,' l_ap_inv::l_extended_amount:' || l_extended_amount
4106 ||' l_ap_inv::distribution_line_number:' || x_movement_transaction.distribution_line_number
4107 ||' l_ap_inv::invoice_id:' || x_movement_transaction.invoice_id
4108 ||' l_ap_inv::l_invoice_quantity:' || l_invoice_quantity
4109 );
4110 END IF;
4111
4112 IF x_movement_transaction.invoice_id IS NOT NULL
4113 THEN
4114 -- Fetch the cursor for price correction.
4115 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4116 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4117 ,G_MODULE_NAME || l_procedure_name
4118 ,' Opening l_ap_cm_inv for rcv_trx_id:' || l_movement_transaction.rcv_transaction_id
4119 );
4120 END IF;
4121
4122 OPEN l_ap_prc_inv;
4123 FETCH l_ap_prc_inv INTO
4124 l_prc_amount;
4125 CLOSE l_ap_prc_inv;
4126
4127 -- Fetch the cursor for quantity correction.
4128 OPEN l_ap_qtc_inv;
4129 FETCH l_ap_qtc_inv INTO
4130 l_qtc_qty
4131 , l_qtc_amount;
4132 CLOSE l_ap_qtc_inv;
4133 ELSE
4134 -- If receipt based matching does not exists, check if it matched agains
4135 -- a PO; check non credit memo based transactions;
4136
4137 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4138 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4139 ,G_MODULE_NAME || l_procedure_name
4140 ,' Opening l_po_inv for po_header_id:' || l_movement_transaction.po_header_id
4141 ||' po_line_location_id:' || l_movement_transaction.po_line_location_id
4142 );
4143 END IF;
4144
4145 OPEN l_po_inv ;
4146 FETCH l_po_inv INTO
4147 l_extended_amount
4148 , x_movement_transaction.distribution_line_number
4149 , x_movement_transaction.invoice_id
4150 , l_invoice_quantity;
4151 CLOSE l_po_inv;
4152
4153 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4154 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4155 ,G_MODULE_NAME || l_procedure_name
4156 ,' l_po_inv::l_extended_amount:' || l_extended_amount
4157 ||' l_po_inv::distribution_line_number:' || x_movement_transaction.distribution_line_number
4158 ||' l_po_inv::invoice_id:' || x_movement_transaction.invoice_id
4159 ||' l_po_inv::l_invoice_quantity:' || l_invoice_quantity
4160 );
4161 END IF;
4162
4163 IF x_movement_transaction.invoice_id IS NOT NULL
4164 THEN
4165 /*bug 8435322 Added new cursor to fetch max distribution_line_number for max Invoice Id*/
4166
4167 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4168 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4169 ,G_MODULE_NAME || l_procedure_name
4170 ,' Opening l_po_line_number for invoice_id:' || x_movement_transaction.invoice_id
4171 ||' po_line_location_id:' || x_movement_transaction.po_line_location_id
4172 );
4173 END IF;
4174
4175 OPEN l_po_line_number ;
4176 FETCH l_po_line_number INTO
4177 x_movement_transaction.distribution_line_number;
4178 CLOSE l_po_line_number;
4179 /*End bug 8435322*/
4180
4181 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4182 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4183 ,G_MODULE_NAME || l_procedure_name
4184 ,' l_po_line_number::distribution_line_number:' || x_movement_transaction.distribution_line_number
4185 );
4186 END IF;
4187
4188 -- Fetch the cursor for price correction.
4189 OPEN l_po_prc_inv ;
4190 FETCH l_po_prc_inv INTO
4191 l_prc_amount;
4192 CLOSE l_po_prc_inv;
4193
4194 -- Fetch the cursor for quantity correction.
4195 OPEN l_po_qtc_inv;
4196 FETCH l_po_qtc_inv INTO
4197 l_qtc_qty
4198 , l_qtc_amount;
4199 CLOSE l_po_qtc_inv;
4200 ELSE
4201 x_movement_transaction.currency_code := l_tran_curr_code;
4202 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
4203 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
4204 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
4205 x_movement_transaction.invoice_batch_id := null;
4206 x_movement_transaction.invoice_date_reference := null;
4207 x_movement_transaction.invoice_id := null;
4208 x_movement_transaction.invoice_quantity := null;
4209 x_movement_transaction.invoice_unit_price := null;
4210 x_movement_transaction.invoice_line_ext_value := null;
4211 x_movement_transaction.distribution_line_number := null;
4212 END IF;
4213 END IF; -- finish check receipt/po based invoice
4214
4215 --Fix italian bug 2861110.Update_invoice_info will revert any netted invoice
4216 --amt and qty back to original PO invoice amt and qty. The following code
4217 --will net the invoice amt and qty again for processed RTV
4218 IF (l_stat_typ_transaction.returns_processing = 'AGGRTN'
4219 AND x_movement_transaction.invoice_id IS NOT NULL)
4220 THEN
4221 Calc_Processed_Ret_Data
4222 (p_movement_transaction => l_movement_transaction
4223 , x_processed_ret_amt => l_processed_ret_amt
4224 , x_processed_ret_qty => l_processed_ret_qty
4225 );
4226
4227 --The correct netted PO invoce amount and quantity
4228 l_extended_amount := l_extended_amount + NVL(l_processed_ret_amt,0);
4229 l_invoice_quantity := l_invoice_quantity + NVL(l_processed_ret_qty,0);
4230 END IF; --end bug 2861110
4231
4232 IF l_extended_amount IS NULL
4233 THEN
4234 x_movement_transaction.invoice_line_ext_value := null;
4235 x_movement_transaction.invoice_unit_price := null;
4236 x_movement_transaction.invoice_quantity := null;
4237 ELSE
4238 l_extended_amount := l_extended_amount + NVL(l_prc_amount,0) + NVL(l_qtc_amount, 0);
4239 x_movement_transaction.invoice_line_ext_value := l_extended_amount;
4240 x_movement_transaction.invoice_quantity :=
4241 NVL(l_invoice_quantity,x_movement_transaction.transaction_quantity) + NVL(l_qtc_qty, 0);
4242
4243 --Fix bug 2340128, decide to use extended amount to calculate unit
4244 --price
4245 IF (x_movement_transaction.invoice_quantity IS NOT NULL
4246 AND x_movement_transaction.invoice_quantity <> 0)
4247 THEN
4248 x_movement_transaction.invoice_unit_price :=
4249 l_extended_amount / x_movement_transaction.invoice_quantity;
4250 ELSE
4251 x_movement_transaction.invoice_unit_price := null;
4252 END IF;
4253 END IF;
4254
4255 OPEN l_apc;
4256 FETCH l_apc INTO
4257 x_movement_transaction.currency_code
4258 , x_movement_transaction.currency_conversion_rate
4259 , x_movement_transaction.currency_conversion_type
4260 , x_movement_transaction.currency_conversion_date
4261 , x_movement_transaction.invoice_batch_id
4262 , x_movement_transaction.invoice_date_reference;
4263
4264 IF l_apc%NOTFOUND
4265 THEN
4266 x_movement_transaction.currency_code := l_tran_curr_code;
4267 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
4268 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
4272 x_movement_transaction.invoice_id := null;
4269 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
4270 x_movement_transaction.invoice_batch_id := null;
4271 x_movement_transaction.invoice_date_reference := null;
4273 END IF;
4274
4275 CLOSE l_apc;
4276
4277 ELSIF l_movement_transaction.document_source_type IN ('RTV')
4278 THEN
4279 --Get parent transaction id for RTV transaction
4280 --Used in open l_ap_rtv_cm_inv
4281 BEGIN
4282 IF l_movement_transaction.rcv_transaction_id IS NOT NULL
4283 THEN
4284 SELECT parent_transaction_id
4285 INTO l_parent_transaction_id
4286 FROM rcv_transactions
4287 WHERE transaction_id = l_movement_transaction.rcv_transaction_id;
4288 END IF;
4289
4290 EXCEPTION
4291 WHEN OTHERS THEN
4292 l_parent_transaction_id := -1;
4293 END;
4294
4295 --First check if this rtv matched to any receipt based credit memo
4296 OPEN l_ap_rtv_cm_inv ;
4297 FETCH l_ap_rtv_cm_inv INTO
4298 l_rtv_extended_amount
4299 , x_movement_transaction.distribution_line_number
4300 , x_movement_transaction.invoice_id
4301 , l_rtv_invoice_quantity;
4302 CLOSE l_ap_rtv_cm_inv;
4303
4304 -- if the Credit/Debit memo is matched against Purchase Orders rather than
4305 -- receipts.
4306 IF x_movement_transaction.invoice_id IS NULL
4307 THEN
4308 OPEN l_po_rtv_cm_inv ;
4309 FETCH l_po_rtv_cm_inv INTO
4310 l_rtv_extended_amount
4311 , x_movement_transaction.distribution_line_number
4312 , x_movement_transaction.invoice_id
4313 , l_rtv_invoice_quantity;
4314 CLOSE l_po_rtv_cm_inv;
4315 END IF; /*bug 8250147 Change end if condition to call l_apc cursor properly for ever case*/
4316
4317 IF x_movement_transaction.invoice_id IS NULL
4318 THEN
4319 x_movement_transaction.currency_code := l_tran_curr_code;
4320 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
4321 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
4322 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
4323 x_movement_transaction.invoice_batch_id := null;
4324 x_movement_transaction.invoice_date_reference := null;
4325 x_movement_transaction.invoice_id := null;
4326 x_movement_transaction.invoice_quantity := null;
4327 x_movement_transaction.invoice_unit_price := null;
4328 x_movement_transaction.invoice_line_ext_value := null;
4329 x_movement_transaction.distribution_line_number := null;
4330 ELSE
4331 OPEN l_apc;
4332 FETCH l_apc INTO
4333 x_movement_transaction.currency_code
4334 , x_movement_transaction.currency_conversion_rate
4335 , x_movement_transaction.currency_conversion_type
4336 , x_movement_transaction.currency_conversion_date
4337 , x_movement_transaction.invoice_batch_id
4338 , x_movement_transaction.invoice_date_reference;
4339
4340 IF l_apc%NOTFOUND
4341 THEN
4342 x_movement_transaction.currency_code := l_tran_curr_code;
4343 x_movement_transaction.currency_conversion_rate := l_tran_curr_rate;
4344 x_movement_transaction.currency_conversion_type := l_tran_curr_type;
4345 x_movement_transaction.currency_conversion_date := l_tran_curr_date;
4346 x_movement_transaction.invoice_batch_id := null;
4347 x_movement_transaction.invoice_date_reference := null;
4348 END IF;
4349 CLOSE l_apc;
4350 END IF; --second invoice id null
4351 /*END IF; bug 8250147 Change end if condition to call l_apc cursor properly for ever case*/
4352
4353 IF l_rtv_extended_amount IS NULL
4354 THEN
4355 x_movement_transaction.invoice_line_ext_value := null;
4356 x_movement_transaction.invoice_unit_price := null;
4357 x_movement_transaction.invoice_quantity := null;
4358 ELSE
4359 --In case there are multiple rtv associated with creidt memo, calculate
4360 --inoice amt and qty for this rtv
4361 --First find total rtv transaction quantity
4362 OPEN l_total_rtv_quantity;
4363 FETCH l_total_rtv_quantity INTO
4364 l_total_rtv_trans_qty;
4365 CLOSE l_total_rtv_quantity;
4366
4367 --Amount and quantity for this rtv
4368 IF (l_total_rtv_trans_qty IS NOT NULL
4369 AND l_total_rtv_trans_qty <> 0)
4370 THEN
4371 l_rtv_invoice_quantity :=
4372 (x_movement_transaction.transaction_quantity/l_total_rtv_trans_qty)
4373 * l_rtv_invoice_quantity;
4374 l_rtv_extended_amount :=
4375 (x_movement_transaction.transaction_quantity/l_total_rtv_trans_qty)
4376 *l_rtv_extended_amount;
4377 END IF;
4378
4379 --Set rtv qty and amt
4380 x_movement_transaction.invoice_quantity :=
4381 NVL(l_rtv_invoice_quantity,x_movement_transaction.transaction_quantity);
4382 x_movement_transaction.invoice_line_ext_value := l_rtv_extended_amount;
4383
4384 IF (l_rtv_invoice_quantity <> 0
4385 AND l_rtv_extended_amount IS NOT NULL)
4386 THEN
4387 x_movement_transaction.invoice_unit_price
4388 := l_rtv_extended_amount/l_rtv_invoice_quantity;
4389 ELSE
4390 x_movement_transaction.invoice_unit_price := NULL;
4391 END IF;
4392 END IF; -- end rtv_extended_amount null
4393 END IF;
4394
4395 /*--If there is no invoice
4396 IF x_movement_transaction.invoice_id IS NULL
4397 THEN
4398 --If transaction currency is same as functional currency
4399 IF NVL(x_movement_transaction.currency_code,FND_API.G_MISS_CHAR) =
4400 NVL(l_stat_typ_transaction.gl_currency_code,FND_API.G_MISS_CHAR)
4401 THEN
4402 x_movement_transaction.currency_conversion_rate := 1;
4403 x_movement_transaction.currency_conversion_type := null;
4404 x_movement_transaction.currency_conversion_date := null;
4405
4406 --If transaction currency is different from functional currency and
4407 --transaction conversion rate/type is not populated in PO/SO document
4408 --calc rate from GL package
4409 ELSIF (x_movement_transaction.currency_conversion_rate IS NULL
4410 OR x_movement_transaction.currency_conversion_type IS NULL)
4411 THEN
4412 Calc_Exchange_Rate( x_movement_transaction => x_movement_transaction
4413 , p_stat_typ_transaction => l_stat_typ_transaction
4414 );
4415 END IF;
4416 END IF; */
4417
4418 IF NVL(x_movement_transaction.currency_code,FND_API.G_MISS_CHAR) =
4419 NVL(l_stat_typ_transaction.gl_currency_code,FND_API.G_MISS_CHAR)
4420 THEN
4421 x_movement_transaction.currency_conversion_rate := 1;
4422 x_movement_transaction.currency_conversion_date := null;
4423 x_movement_transaction.currency_conversion_type := null;
4424 ELSE
4425 --Fix bug 4285335, if user setup a conversion type on parameter form, always take the type
4426 --from parameter form, the two parameters are modified to optional
4427 IF l_stat_typ_transaction.conversion_type IS NOT NULL
4428 THEN
4429 x_movement_transaction.currency_conversion_type := l_stat_typ_transaction.conversion_type;
4430
4431 Calc_Exchange_Rate
4432 ( x_movement_transaction => x_movement_transaction
4433 , p_stat_typ_transaction => l_stat_typ_transaction
4434 );
4435 END IF;
4436 END IF;
4437 END IF;
4438
4439 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4440 THEN
4441 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4442 , G_MODULE_NAME || l_procedure_name || '.end'
4443 ,'exit procedure'
4444 );
4445 END IF;
4446
4447 EXCEPTION
4448 WHEN NO_DATA_FOUND
4449 THEN
4450 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4451 THEN
4452 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
4453 , G_MODULE_NAME || l_procedure_name||'.No data found exception'
4454 , 'Exception'
4455 );
4456 END IF;
4457 x_movement_transaction := l_movement_transaction;
4458
4459 --Switch back the order line id for CTO item
4460 IF (l_item_type_code = 'CONFIG' AND l_cto_line_id IS NOT NULL)
4461 THEN
4462 x_movement_transaction.order_line_id := l_cto_line_id;
4463 END IF;
4464 WHEN TOO_MANY_ROWS
4465 THEN
4466 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4467 THEN
4468 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
4469 , G_MODULE_NAME || l_procedure_name||'.too many rows exception'
4470 , 'Exception'
4471 );
4472 END IF;
4473 x_movement_transaction := l_movement_transaction;
4474
4475 --Switch back the order line id for CTO item
4476 IF (l_item_type_code = 'CONFIG' AND l_cto_line_id IS NOT NULL)
4477 THEN
4478 x_movement_transaction.order_line_id := l_cto_line_id;
4479 END IF;
4480 WHEN OTHERS
4481 THEN
4482 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4483 THEN
4484 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
4485 , G_MODULE_NAME || l_procedure_name||'.Others exception:' || sqlerrm
4486 , 'Exception'
4487 );
4488 END IF;
4489 x_movement_transaction := l_movement_transaction;
4490
4491 --Switch back the order line id for CTO item
4492 IF (l_item_type_code = 'CONFIG' AND l_cto_line_id IS NOT NULL)
4493 THEN
4494 x_movement_transaction.order_line_id := l_cto_line_id;
4495 END IF;
4496 END Calc_Invoice_Info;
4497
4498
4499 --========================================================================
4500 -- FUNCTION : Get_Set_Of_Books_Period
4501 -- PARAMETERS: p_legal_entity_id Legal Entity
4502 -- p_period_date Invoice date or transaction date
4503 -- COMMENT : Function that returns the Period Name
4504 -- based on invoice date or movement date if invoice date is null
4505 --=========================================================================
4506 /* Bug: 5291257. Function defintion is modified to remove parameter
4507 p_period_type. */
4508 FUNCTION Get_Set_Of_Books_Period
4509 ( p_legal_entity_id IN VARCHAR2
4510 , p_period_date IN DATE
4511 --, p_period_type IN VARCHAR2
4512 )
4513 RETURN VARCHAR2
4514 IS
4515 l_set_of_books_period VARCHAR2(15);
4516 l_function_name CONSTANT VARCHAR2(30) := 'Get_Set_Of_Books_Period';
4517
4518 /* Bug: 5291257. Following cursor definition is modified and p_period_type is replaced with
4519 gllv.accounted_period_type */
4520 CURSOR c_pname IS
4521 SELECT
4522 glp.period_name
4523 FROM
4524 gl_periods glp
4525 , gl_ledger_le_v gllv
4526 WHERE gllv.period_set_name = glp.period_set_name
4527 AND gllv.legal_entity_id = p_legal_entity_id
4528 AND gllv.ledger_category_code = 'PRIMARY'
4529 AND glp.period_type = gllv.accounted_period_type
4530 AND NVL(glp.adjustment_period_flag,'N') = 'N'
4531 AND trunc(p_period_date) BETWEEN trunc(glp.start_date) AND trunc(glp.end_date);
4535 THEN
4532
4533 BEGIN
4534 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4536 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4537 , G_MODULE_NAME || l_function_name || '.begin'
4538 ,'enter procedure'
4539 );
4540 END IF;
4541
4542 OPEN c_pname;
4543 FETCH c_pname
4544 INTO l_set_of_books_period;
4545
4546 IF c_pname%NOTFOUND THEN
4547 l_set_of_books_period:= null;
4548 END IF;
4549
4550 CLOSE c_pname;
4551
4552 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4553 THEN
4554 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4555 , G_MODULE_NAME || l_function_name || '.end'
4556 ,'exit procedure'
4557 );
4558 END IF;
4559
4560 RETURN l_set_of_books_period;
4561
4562 EXCEPTION
4563 WHEN NO_DATA_FOUND THEN
4564 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4565 THEN
4566 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
4567 , G_MODULE_NAME || l_function_name||'.No data found exception'
4568 , 'Exception'
4569 );
4570 END IF;
4571 RETURN null;
4572 WHEN TOO_MANY_ROWS
4573 THEN
4574 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4575 THEN
4576 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
4577 , G_MODULE_NAME || l_function_name||'.too many rows exception'
4578 , 'Exception'
4579 );
4580 END IF;
4581 RETURN null;
4582 WHEN OTHERS THEN
4583 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4584 THEN
4585 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
4586 , G_MODULE_NAME || l_function_name||'.Others exception'
4587 , 'Exception'
4588 );
4589 END IF;
4590 RETURN null;
4591 END Get_Set_Of_Books_Period;
4592
4593
4594 --========================================================================
4595 -- FUNCTION : Get_Period_Name
4596 -- PARAMETERS: p_movement_transacton Movement Transaction record
4597 -- p_stat_typ_transaction Stat typ tranaction
4598 -- COMMENT : Function that returns the Period Name
4599 --=========================================================================
4600
4601 FUNCTION Get_Period_Name
4602 ( p_movement_transaction IN
4603 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
4604 , p_stat_typ_transaction IN
4605 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
4606 )
4607 RETURN VARCHAR2
4608 IS
4609 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
4610 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
4611 l_function_name CONSTANT VARCHAR2(30) := 'Get_Period_Name';
4612
4613 CURSOR c_period IS
4614 SELECT
4615 period_name
4616 FROM
4617 GL_PERIODS
4618 WHERE period_set_name = l_stat_typ_transaction.period_set_name
4619 AND l_movement_transaction.transaction_date between
4620 (start_date) and (end_date)
4621 AND start_date >= l_stat_typ_transaction.start_date
4622 AND end_date <= l_stat_typ_transaction.end_date
4623 AND period_type = l_stat_typ_transaction.period_type
4624 AND NVL(adjustment_period_flag,'N') = 'N';
4625
4626 CURSOR c_period1 IS
4627 SELECT
4628 period_name
4629 FROM
4630 GL_PERIODS
4631 WHERE period_set_name = l_stat_typ_transaction.period_set_name
4632 AND trunc(l_movement_transaction.transaction_date) between
4633 trunc(start_date) and trunc(end_date)
4634 AND period_type = l_stat_typ_transaction.period_type
4635 AND start_date >= l_stat_typ_transaction.start_date
4636 AND NVL(adjustment_period_flag,'N') = 'N';
4637
4638 BEGIN
4639 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4640 THEN
4641 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4642 , G_MODULE_NAME || l_function_name || '.begin'
4643 ,'enter procedure'
4644 );
4645 END IF;
4646
4647 l_movement_transaction := p_movement_transaction;
4648 l_stat_typ_transaction := p_stat_typ_transaction;
4649
4650 IF (l_stat_typ_transaction.start_date IS NOT NULL)
4651 AND
4652 (l_stat_typ_transaction.end_date IS NOT NULL)
4653 THEN
4654 OPEN c_period;
4655 FETCH c_period
4656 INTO l_movement_transaction.period_name;
4657
4658 IF c_period%NOTFOUND THEN
4659 CLOSE c_period;
4660 ELSE
4661 CLOSE c_period;
4662 END IF;
4663
4664 ELSE
4665 OPEN c_period1;
4666 FETCH c_period1
4667 INTO l_movement_transaction.period_name;
4668
4669 IF c_period1%NOTFOUND THEN
4670 CLOSE c_period1;
4671 ELSE
4672 CLOSE c_period1;
4673 END IF;
4674 END IF;
4675
4676 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4677 THEN
4678 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4679 , G_MODULE_NAME || l_function_name || '.end'
4680 ,'exit procedure'
4681 );
4682 END IF;
4683
4684 RETURN l_movement_transaction.period_name;
4685
4686 EXCEPTION
4687 WHEN OTHERS THEN
4688 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4689 THEN
4690 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
4691 , G_MODULE_NAME || l_function_name||'. Others exception'
4692 , 'Exception'
4693 );
4694 END IF;
4698
4695 RETURN NULL;
4696 END Get_Period_Name;
4697
4699 --========================================================================
4700 -- PROCEDURE : Get_Reference_Date
4701 -- PARAMETERS: x_movement_transacton Movement Transaction record
4702 -- p_stat_typ_transaction Stat typ tranaction
4703 -- COMMENT : Procedure that gets the Reference Date
4704 --=========================================================================
4705
4706 PROCEDURE Get_Reference_Date
4707 ( p_stat_typ_transaction IN
4708 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
4709 , x_movement_transaction IN OUT NOCOPY
4710 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
4711 )
4712 IS
4713 l_transaction_date DATE;
4714 l_invoice_date DATE;
4715 l_pending_date DATE;
4716 l_months NUMBER;
4717 l_no_days NUMBER;
4718 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
4719 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Reference_Date';
4720
4721 BEGIN
4722 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4723 THEN
4724 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4725 , G_MODULE_NAME || l_procedure_name || '.begin'
4726 ,'enter procedure'
4727 );
4728 END IF;
4729
4730 l_stat_typ_transaction := p_stat_typ_transaction;
4731 l_transaction_date := x_movement_transaction.transaction_date;
4732 l_invoice_date := x_movement_transaction.invoice_date_reference;
4733
4734 --Fix bug 4927726
4735 --Find out the original transaction date for a pending record, the current
4736 --transaction date is a reference date calculated before
4737 IF x_movement_transaction.movement_status = 'P'
4738 THEN
4739 -- Bug 5741580. Following code is modified to fetch the transaction date again
4740 -- from original transactions.
4741 IF x_movement_transaction.document_source_type = 'SO'
4742 THEN
4743 SELECT initial_pickup_date
4744 INTO l_transaction_date
4745 FROM wsh_delivery_details_ob_grp_v wdd
4746 , wsh_new_deliveries_ob_grp_v wnd
4747 , wsh_delivery_assignments wda
4748 WHERE wnd.delivery_id = wda.delivery_id
4749 AND wda.delivery_detail_id = wdd.delivery_detail_id
4750 AND wdd.source_line_id = x_movement_transaction.order_line_id
4751 AND wda.delivery_detail_id = x_movement_transaction.picking_line_detail_id
4752 AND wdd.organization_id = x_movement_transaction.organization_id
4753 AND nvl(wnd.customer_id,wdd.customer_id) = x_movement_transaction.ship_to_customer_id
4754 AND rownum = 1;
4755 ELSIF x_movement_transaction.document_source_type = 'INV'
4756 THEN
4757 SELECT transaction_date
4758 INTO l_transaction_date
4759 FROM MTL_MATERIAL_TRANSACTIONS MMT
4760 WHERE MMT.transaction_id = x_movement_transaction.mtl_transaction_id;
4761 ELSE
4762 SELECT transaction_date
4763 INTO l_transaction_date
4764 FROM rcv_transactions
4765 WHERE transaction_id = x_movement_transaction.rcv_transaction_id;
4766 END IF;
4767 END IF;
4768
4769 --Get correct pending date
4770 --Find out the number of days the next month of the transaction date
4771 SELECT to_number(to_char(LAST_DAY(add_months(l_transaction_date,1)),'DD'))
4772 INTO
4773 l_no_days
4774 FROM DUAL;
4775
4776 -- If the pending invoice days is greater than the # of days in the month,
4777 -- then pending_invoice_days is the # of days in the month.
4778 IF l_no_days < NVL(l_stat_typ_transaction.pending_invoice_days,15)
4779 THEN
4780 l_stat_typ_transaction.pending_invoice_days := l_no_days;
4781 END IF;
4782
4783 l_pending_date := to_date( NVL(l_stat_typ_transaction.pending_invoice_days,15)
4784 ||'-'||to_char(add_months(l_transaction_date,1),'MON-YY'),'DD-MON-YY');
4785
4786 -- If there is no invoice ,check if it has passed the invoice
4787 -- threshold date (pending_invoice_days) to determine the movement status
4788 -- the reference date is always same as the pending date.
4789 IF (x_movement_transaction.invoice_id IS NULL)
4790 THEN
4791 --Fix bug 4170403
4792 /*l_months := round(months_between(sysdate, l_transaction_date));
4793
4794 --If the transaction has taken place more than a month ago, or if the
4795 --transaction has taken place previous month and sysdate is greater
4796 --than the pending invoice days, reference date is the following month
4797 --plus the pending_invoice_days.
4798 IF (l_months > 1) OR
4799 (((l_months =1) AND (to_char(sysdate,'DD') >
4800 l_stat_typ_transaction.pending_invoice_days)) AND
4801 (to_char(sysdate,'MM') <> to_char(l_transaction_date,'MM'))) */
4802
4803 IF GREATEST(sysdate ,l_pending_date) = sysdate
4804 THEN
4805 x_movement_transaction.movement_status :='O';
4806 ELSE
4807 -- Wait for the invoice till next month
4808 x_movement_transaction.movement_status :='P';
4809 END IF;
4810
4811 x_movement_transaction.reference_date := l_pending_date;
4812 ELSE -- invoice present
4813 IF (l_transaction_date = l_invoice_date)
4814 THEN
4815 x_movement_transaction.reference_date := x_movement_transaction.transaction_date;
4816 -- Invoice was received before the transaction took place;
4817 ELSIF (GREATEST(l_transaction_date ,l_invoice_date) = l_transaction_date)
4818 THEN
4819 IF (l_transaction_date - l_invoice_date ) >
4820 NVL(l_stat_typ_transaction.prior_invoice_days,30)
4821 THEN
4822 x_movement_transaction.reference_date := l_transaction_date;
4823 ELSE
4824 x_movement_transaction.reference_date := l_invoice_date;
4825 END IF;
4826 ELSE -- invoice is received later
4827 --Fix bug 2365712 yawang
4828 --IF (l_invoice_date - l_transaction_date ) >
4829 -- NVL(l_stat_typ_transaction.pending_invoice_days,15)
4830 IF (GREATEST(l_invoice_date ,l_pending_date) = l_invoice_date)
4831 THEN
4832 x_movement_transaction.reference_date := l_pending_date;
4833 ELSE
4834 x_movement_transaction.reference_date := l_invoice_date;
4835 END IF;
4836 END IF;
4837
4838 x_movement_transaction.movement_status :='O';
4839 END IF;
4840
4841 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4842 THEN
4843 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4844 , G_MODULE_NAME || l_procedure_name || '.end'
4845 ,'exit procedure'
4846 );
4847 END IF;
4848
4849 EXCEPTION
4850 WHEN OTHERS THEN
4851 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
4852 THEN
4853 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
4854 , G_MODULE_NAME || l_procedure_name||'. Others exception'
4855 , 'Exception'
4856 );
4857 END IF;
4858 END Get_Reference_Date;
4859
4860
4861 --========================================================================
4862 -- PROCEDURE : Log_Initialize PUBLIC
4863 -- COMMENT : Initializes the log facility. It should be called from
4864 -- the top level procedure of each concurrent program
4865 --=======================================================================--
4866 PROCEDURE Log_Initialize
4867 IS
4868 BEGIN
4869 g_log_level := TO_NUMBER(FND_PROFILE.Value('AFLOG_LEVEL'));
4870 IF g_log_level IS NULL THEN
4871 g_log_mode := 'OFF';
4872 ELSE
4873 IF (TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID')) <> 0) THEN
4874 g_log_mode := 'SRS';
4875 ELSE
4876 g_log_mode := 'SQL';
4877 END IF;
4878 END IF;
4879
4880 END Log_Initialize;
4881
4882
4883 --========================================================================
4884 -- PROCEDURE : Log PUBLIC
4885 -- PARAMETERS: p_level IN priority of the message - from
4886 -- highest to lowest:
4887 -- -- G_LOG_ERROR
4888 -- -- G_LOG_EXCEPTION
4889 -- -- G_LOG_EVENT
4890 -- -- G_LOG_PROCEDURE
4891 -- -- G_LOG_STATEMENT
4892 -- p_msg IN message to be print on the log
4893 -- file
4894 -- COMMENT : Add an entry to the log
4895 --=======================================================================--
4896 PROCEDURE Log
4897 ( p_priority IN NUMBER
4898 , p_msg IN VARCHAR2
4899 )
4900 IS
4901 BEGIN
4902 IF ((g_log_mode <> 'OFF') AND (p_priority >= g_log_level))
4903 THEN
4904 IF g_log_mode = 'SQL'
4905 THEN
4906 -- SQL*Plus session: uncomment the next line during unit test
4907 -- DBMS_OUTPUT.put_line(p_msg);
4908 NULL;
4909 ELSE
4910 -- Concurrent request
4911 FND_FILE.put_line
4912 ( FND_FILE.log
4913 , p_msg
4914 );
4915 END IF;
4916 END IF;
4917 EXCEPTION
4918 WHEN OTHERS THEN
4919 NULL;
4920 END Log;
4921
4922
4923 END INV_MGD_MVT_FIN_MDTR;
[Home] [Help]