[Home] [Help]
PACKAGE BODY: APPS.PA_INVOICE_CURRENCY
Source
1 PACKAGE BODY PA_INVOICE_CURRENCY as
2 /* $Header: PAXICURB.pls 120.19.12020000.3 2013/02/21 12:18:54 paljain ship $ */
3
4 /*------------------- Private Part of The Package ----------------------*/
5
6
7
8 /*--------------------------------------------------------------------------+
9 function to format the currency for multi radix changes. The currency code
10 is fetched from the invoice program (paisql.lpc). This formats the currency
11 and updated the pa_invoices table
12 ----------------------------------------------------------------------------*/
13 FUNCTION format_proj_curr_code
14 RETURN VARCHAR2 IS
15
16 BEGIN
17
18 /* Changed the Field length to 22 from 15 for Bug#2337109 pcchandr 16-May-2002 */
19
20 return(fnd_currency.get_format_mask(pa_invoice_currency.g_currency_code,22));
21
22 -- return(fnd_currency.get_format_mask(pa_invoice_currency.g_currency_code,15));
23
24 EXCEPTION
25 WHEN OTHERS THEN
26 return (SQLCODE);
27
28 END format_proj_curr_code;
29
30
31 /*----------------------------------------------------------------------------+
32 | This Private Procedure Insert_Distrbution_Warning Inserts draft Invoice |
33 | distribution warning. |
34 +----------------------------------------------------------------------------*/
35
36 Procedure Insert_Distrbution_Warning ( P_Project_ID in number,
37 P_Draft_Invoice_Num in number,
38 P_User_ID in number,
39 P_Request_ID in number,
40 P_Invoice_Set_ID in number,
41 P_Lookup_Type in varchar2,
42 P_Error_Message_Code in varchar2) is
43
44 l_error_message pa_lookups.meaning%TYPE;
45
46 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
47
48 BEGIN
49
50 BEGIN
51 SELECT Meaning
52 INTO l_error_message
53 FROM PA_Lookups
54 WHERE Lookup_Type = P_Lookup_Type
55 AND Lookup_Code = P_Error_Message_Code;
56
57 EXCEPTION
58 WHEN NO_DATA_FOUND THEN
59 l_error_message := P_Error_Message_Code;
60 END;
61
62 IF (P_Invoice_Set_ID is NULL) THEN
63
64 INSERT INTO PA_DISTRIBUTION_WARNINGS
65 (
66 PROJECT_ID, DRAFT_INVOICE_NUM, LAST_UPDATE_DATE, LAST_UPDATED_BY,
67 CREATION_DATE, CREATED_BY, REQUEST_ID, WARNING_MESSAGE
68 )
69 VALUES
70 (
71 P_Project_ID, P_Draft_Invoice_Num, sysdate, P_User_ID,
72 sysdate, P_User_ID, P_Request_ID, l_error_message
73 );
74
75 ELSE
76
77 INSERT INTO PA_DISTRIBUTION_WARNINGS
78 (
79 PROJECT_ID, DRAFT_INVOICE_NUM, LAST_UPDATE_DATE, LAST_UPDATED_BY,
80 CREATION_DATE, CREATED_BY, REQUEST_ID, WARNING_MESSAGE
81 )
82 SELECT Project_ID, Draft_Invoice_Num, sysdate, P_User_ID,
83 sysdate, P_User_ID, P_Request_ID, l_error_message
84 FROM PA_Draft_Invoices_ALL
85 WHERE Invoice_Set_ID = P_Invoice_Set_ID
86 AND project_id = p_project_id ; /* Fix for Performance bug 4942339 */
87
88 END IF;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 RAISE;
93 END Insert_Distrbution_Warning;
94
95
96 /*------------------- Public Part of The Package ----------------------*/
97
98 PROCEDURE recalculATE ( P_Project_Id IN NUMBER,
99 P_Draft_Inv_Num IN NUMBER,
100 P_Calling_Module IN VARCHAR2,
101 P_Customer_Id IN NUMBER,
102 P_Inv_Currency_Code IN VARCHAR2,
103 P_Inv_Rate_Type IN VARCHAR2,
104 P_Inv_Rate_Date IN DATE,
105 P_Inv_Exchange_Rate IN NUMBER,
106 P_User_Id IN NUMBER,
107 P_Bill_Thru_Date IN DATE,
108 X_Status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
109 as
110
111
112 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
113
114 l_error_message pa_lookups.meaning%TYPE;
115
116 cursor get_std_lines
117 is
118 select dii.rowid row_id, /** Bug 2324299 **/
119 dii.line_num line_num,
120 dii.amount amount,
121 NVL(di.retention_invoice_flag,'N') retention_invoice_flag
122 /* Added for Payroll Billing ER #11847630 - Start */
123 ,nvl(dii.credit_process_flag,'N') credit_process_flag
124 ,dii.inv_exchange_rate
125 /* Added for Payroll Billing ER #11847630 - End */
126 from pa_draft_invoice_items dii,
127 pa_draft_invoices_all di
128 where dii.project_id = P_Project_Id
129 and dii.draft_invoice_num = P_Draft_Inv_Num
130 and dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
131 and dii.project_id = di.project_id
132 and dii.draft_invoice_num = di.draft_invoice_num
133 AND NVL(di.retention_invoice_flag,'N') ='N'
134 UNION
135 select dii.rowid row_id, /** Bug 2324299 **/
136 dii.line_num line_num,
137 dii.amount amount,
138 NVL(di.retention_invoice_flag,'N') retention_invoice_flag
139 /* Added for Payroll Billing ER #11847630 - Start */
140 ,nvl(dii.credit_process_flag,'N') credit_process_flag
141 ,dii.inv_exchange_rate
142 /* Added for Payroll Billing ER #11847630 - End */
143 from pa_draft_invoice_items dii,
144 pa_draft_invoices_all di
145 where dii.project_id = P_Project_Id
146 and dii.draft_invoice_num = P_Draft_Inv_Num
147 and dii.invoice_line_type = 'RETENTION'
148 and dii.project_id = di.project_id
149 and dii.draft_invoice_num = di.draft_invoice_num
150 AND NVL(di.retention_invoice_flag,'N') ='Y'
151 order by line_num;
152
153 cursor get_ret_lines
154 is
155 select dii.rowid dii_rowid,
156 di.retention_percentage retention,
157 dii.amount amt,
158 dii.line_num line,
159 dii.projfunc_bill_amount,
160 NVL(di.retention_invoice_flag,'N') retention_invoice_flag
161 from pa_draft_invoices di,
162 pa_draft_invoice_items dii
163 where di.project_id = dii.project_id
164 and di.draft_invoice_num = dii.draft_invoice_num
165 and di.project_id = P_Project_Id
166 and di.draft_invoice_num = P_Draft_Inv_Num
167 and dii.invoice_line_type = 'RETENTION'
168 AND NVL(di.retention_invoice_flag,'N') ='N' ;
169
170 l_temp_amount NUMBER := 100; /*Added for bug 12604419 */
171 l_inv_currency_code VARCHAR2(15);
172 l_inv_rate_type VARCHAR2(30);
173 l_inv_rate_date DATE;
174 l_inv_exchange_rate NUMBER;
175 l_func_curr VARCHAR2(15);
176 l_total_inv_amount NUMBER := 0;
177 l_con_amt NUMBER := 0;
178 l_conv_rev_amt NUMBER := 0;/*Added for Payroll Billing Integration bug# 12383803*/
179 l_ret_per NUMBER := 0;
180 l_ret_amt NUMBER := 0;
181 l_tot_proj_amt NUMBER := 0;
182 l_round_off_amt NUMBER := 0;
183 l_inv_ret_amt NUMBER := 0;
184 l_max_line_num NUMBER := 0;
185 l_inv_amt NUMBER;
186 l_denominator NUMBER ;
187 l_numerator NUMBER ;
188 l_rate NUMBER;
189 l_status VARCHAR2(1000);
190
191 -- Mcb Related Changes
192 l_invproc_currency_code VARCHAR2(30);
193 l_project_currency_code VARCHAR2(30);
194 l_funding_currency_code VARCHAR2(30);
195 l_invproc_currency_type VARCHAR2(30);
196
197 l_invtras_rate_flag BOOLEAN := FALSE;
198
199
200 l_projfunc_invtrans_rate NUMBER := 0;
201
202 l_invoice_date DATE;
203
204 l_total_retn_amount NUMBER;
205
206 l_projfunc_Exchange_Rate NUMBER; -- FP_M changes
207 l_ProjFunc_Attr_For_AR_Flag VARCHAR2(1); -- FP_M changes
208 -- l_func_Exchg_Rate_Date_Code VARCHAR2(30);
209 l_projfunc_Exchg_Rate_type VARCHAR2(30);
210 l_projfunc_Exchg_Rate_Date Date;
211
212 l_sum_projfunc_bill_amount NUMBER := 0;
213 l_sum_inv_amount NUMBER := 0; /*Bug 5346566*/
214
215 l_PFC_Exchg_Rate_Date_Code VARCHAR2(30); -- FP_M Changes Bug 3836514
216
217 l_retention_invoice_flag VARCHAR2(1) := 'N'; /* Added for bug 9453939*/
218
219 /* Added for Payroll Billing ER #11847630 - Start */
220 l_first_line_flag VARCHAR2(1);
221 /* Added for Payroll Billing ER #11847630 - End */
222
223 begin
224
225 /* Fetch the project currency from the project table */
226
227 IF g1_debug_mode = 'Y' THEN
228 PA_MCB_INVOICE_PKG.log_message(' Inside Recalculate');
229 END IF;
230
231 l_invoice_date := pa_billing.GetInvoiceDate;
232
233 /* MCB Related Changes */
234
235 select PROJFUNC_CURRENCY_CODE,
236 PROJECT_CURRENCY_CODE,
237 INVPROC_CURRENCY_TYPE,
238 PROJFUNC_BIL_EXCHANGE_RATE, -- FP_M Changes
239 PROJFUNC_BIL_RATE_DATE_CODE,
240 PROJFUNC_BIL_RATE_TYPE,
241 PROJFUNC_BIL_RATE_DATE,
242 ProjFunc_Attr_For_AR_Flag -- FP_M changes
243 into l_func_curr,
244 l_project_currency_code,
245 l_invproc_currency_type,
246 l_projfunc_Exchange_Rate,
247 l_PFC_Exchg_Rate_Date_Code, -- FP_M Changes
248 l_projfunc_Exchg_Rate_type,
249 l_projfunc_Exchg_Rate_Date,
250 l_ProjFunc_Attr_For_AR_Flag
251 from pa_projects_all
252 where project_id = P_Project_Id;
253
254 /*Added the following code for bug9453939*/
255 SELECT NVL(di.retention_invoice_flag,'N')
256 INTO l_retention_invoice_flag
257 FROM pa_draft_invoices_all di
258 WHERE di.project_id = P_Project_Id
259 AND di.draft_invoice_num = P_Draft_Inv_Num;
260
261 IF g1_debug_mode = 'Y' THEN
262 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Retention Invoice Flag ' || l_retention_invoice_flag);
263 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Project Currency Code ' || l_project_currency_code);
264 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Project Functional ' || l_func_curr);
265 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' IPC type ' || l_invproc_currency_type);
266 END IF;
267
268 IF l_invproc_currency_type ='PROJECT_CURRENCY' THEN
269
270 l_invproc_currency_code := l_project_currency_code;
271
272 ELSIF l_invproc_currency_type ='PROJFUNC_CURRENCY' THEN
273
274 l_invproc_currency_code := l_func_curr;
275
276 ELSIF l_invproc_currency_type ='FUNDING_CURRENCY' THEN
277
278 /* SELECT funding_currency_code
279 INTO l_funding_currency_code
280 FROM pa_summary_project_fundings
281 WHERE project_id = p_project_id
282 AND rownum=1
283 GROUP BY funding_currency_code
284 HAVING sum(total_baselined_amount) <> 0; Commented for bug 3147272*/
285
286 /* added the following select statement for bug 3147272*/
287 SELECT funding_currency_code
288 INTO l_funding_currency_code
289 FROM pa_summary_project_fundings
290 WHERE project_id = p_project_id
291 AND rownum=1
292 AND NVL(total_baselined_amount,0) > 0;
293
294
295 l_invproc_currency_code := l_funding_currency_code;
296
297 END IF;
298
299 if P_Calling_Module = 'PAIGEN' then
300
301 IF g1_debug_mode = 'Y' THEN
302 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Selecting Inv Trans Currency COde');
303 END IF;
304
305 /* for Invoice generation, select currency code and conversion attribute from pa_project_customers. */
306
307 select inv_currency_code,
308 -- nvl(inv_rate_date,P_Bill_Thru_Date), /* commented for mcb2 to use invoice_date */
309 nvl(inv_rate_date,NVL(l_invoice_date,P_Bill_Thru_Date)),
310 inv_rate_type,
311 inv_exchange_rate
312 into l_inv_currency_code,
313 l_inv_rate_date,
314 l_inv_rate_type,
315 l_inv_exchange_rate
316 from pa_project_customers
317 where project_id = P_Project_Id
318 and customer_id = P_Customer_id ;
319
320 /*Start of code changes for bug 9453939*/
321 if (PA_RETN_BILLING_PKG.G_INV_BY_BILL_TRANS_CURRENCY = 'Y') and (l_retention_invoice_flag = 'Y') then
322
323 l_inv_currency_code := l_invproc_currency_code;
324
325 end if;
326
327 /*End of code changes for bug 9453939*/
328
329 IF g1_debug_mode = 'Y' THEN
330 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Invoice by Bill Transaction Currency (BTC) ' || PA_RETN_BILLING_PKG.G_INV_BY_BILL_TRANS_CURRENCY);
331 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Invoice Currency Code ' || l_inv_currency_code);
332 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Invoice rate date ' || l_inv_rate_date);
333 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Invoice rate type ' || l_inv_rate_type);
334 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Invoice exch rate ' || l_inv_exchange_rate);
335 END IF;
336
337
338 else
339
340 IF g1_debug_mode = 'Y' THEN
341 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Assign Inv Trans Currency COde');
342 END IF;
343
344 /* for all other cases , copy the input parameter into local placeholder */
345
346 l_inv_currency_code := P_Inv_Currency_Code;
347 l_inv_rate_type := P_Inv_Rate_Type;
348 l_inv_rate_date := P_Inv_Rate_Date;
349 l_inv_exchange_rate := P_Inv_Exchange_Rate;
350
351 end if;
352
353 if (l_invproc_currency_code = l_inv_currency_code) AND (l_inv_currency_code = l_func_curr)
354
355 /* If invoice currency is same as invoice processing currency
356 and invoice currency is same as project functional currency */
357 then
358
359 IF g1_debug_mode = 'Y' THEN
360 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' If (l_invproc_currency_code = l_inv_currency_code) AND (l_inv_currency_code = l_func_curr)' );
361 END IF;
362 X_Status := NULL;
363
364 Update pa_draft_invoices_all
365 set inv_currency_code = l_invproc_currency_code
366 ,inv_rate_type = NULL
367 ,inv_rate_date = NULL
368 ,inv_exchange_rate = NULL
369 ,projfunc_invtrans_rate_type = NULL
370 ,projfunc_invtrans_rate_date = NULL
371 ,projfunc_invtrans_ex_rate = NULL
372 where project_id = P_Project_Id
373 and draft_invoice_num = P_Draft_Inv_Num;
374
375 Update pa_draft_invoice_items
376 set inv_amount = amount
377 where project_id = P_Project_Id
378 and draft_invoice_num = P_Draft_Inv_Num;
379
380 else
381
382 /* If invoice currency is same as invoice processing currency */
383
384 IF (l_invproc_currency_code = l_inv_currency_code) THEN
385 X_Status := NULL;
386 IF g1_debug_mode = 'Y' THEN
387 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' If (l_invproc_currency_code = l_inv_currency_code) ');
388 END IF;
389
390 Update pa_draft_invoices_all
391 set inv_currency_code = l_invproc_currency_code
392 ,inv_rate_type = NULL
393 ,inv_rate_date = NULL
394 ,inv_exchange_rate = NULL
395 where project_id = P_Project_Id
396 and draft_invoice_num = P_Draft_Inv_Num;
397
398 Update pa_draft_invoice_items
399 set inv_amount = amount
400 where project_id = P_Project_Id
401 and draft_invoice_num = P_Draft_Inv_Num;
402
403 END IF;
404
405 -- If invoice processing currency is same as project functional currency
406
407 IF (l_inv_currency_code = l_func_curr) THEN
408
409 X_Status := NULL;
410 IF g1_debug_mode = 'Y' THEN
411 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' If (l_inv_currency_code = l_func_curr) ');
412 END IF;
413
414 /* The following commented and rewritten for bug#2355135
415 invoice currency = projfunc currency <> invproc currency
416 copy projfunc amount and null out other attributes as AR expects the amounts to be same
417 Update pa_draft_invoices_all
418 set projfunc_invtrans_rate_type = NULL
419 ,projfunc_invtrans_rate_date = NULL
420 ,projfunc_invtrans_ex_rate = NULL
421 where project_id = P_Project_Id
422 and draft_invoice_num = P_Draft_Inv_Num;
423 */
424 Update pa_draft_invoices_all
425 set projfunc_invtrans_rate_type = NULL
426 ,projfunc_invtrans_rate_date = NULL
427 ,projfunc_invtrans_ex_rate = NULL
428 ,inv_currency_code = l_inv_currency_code
429 ,inv_rate_type = NULL
430 ,inv_rate_date = NULL
431 ,inv_exchange_rate = NULL
432 where project_id = P_Project_Id
433 and draft_invoice_num = P_Draft_Inv_Num;
434
435 Update pa_draft_invoice_items
436 set inv_amount = projfunc_bill_amount
437 where project_id = P_Project_Id
438 and draft_invoice_num = P_Draft_Inv_Num;
439
440 END IF;
441
442 /* If invoice currency is different from invoice processing currency */
443
444 /* The following commented and rewritten for bug#2355135
445 IF (l_invproc_currency_code <> l_inv_currency_code) THEN
446 */
447 IF (l_invproc_currency_code <> l_inv_currency_code) AND (l_inv_currency_code <> l_func_curr) THEN
448
449 IF g1_debug_mode = 'Y' THEN
450 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' If (l_invproc_currency_code <> l_inv_currency_code) ');
451 END IF;
452
453 /* Added for Payroll Billing ER #11847630 - Start */
454
455 l_first_line_flag := 'N' ;
456
457 /* Added for Payroll Billing ER #11847630 - End */
458
459 for cur_get_std_lines in get_std_lines
460 loop
461 IF g1_debug_mode = 'Y' THEN
462 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Standard INV LINES Loop');
463 END IF;
464
465 /* Added for Payroll Billing ER #11847630 - Start */
466
467 IF (( cur_get_std_lines.credit_process_flag <> 'X') AND
468 ( l_first_line_flag = 'N' )) THEN
469
470 l_first_line_flag := 'Y' ;
471
472 END IF ;
473
474 /* Added for Payroll Billing ER #11847630 - End */
475
476 /* Removed for Payroll Billing ER #11847630 - Start */
477
478 --if cur_get_std_lines.line_num = 1
479 --then
480
481 /* Removed for Payroll Billing ER #11847630 - End */
482
483 /* Added for Payroll Billing ER #11847630 - Start */
484
485 IF (l_first_line_flag = 'Y')
486 THEN
487 l_first_line_flag := 'X';
488
489 /* Added for Payroll Billing ER #11847630 - End */
490
491 /* call the api to convert the line amount in Invoice Processing currency to
492 invoice currency for line number = 1 */
493 l_rate := l_inv_exchange_rate;
494 pa_multi_currency.convert_amount ( P_from_currency => l_invproc_currency_code,
495 P_to_currency => l_Inv_currency_code,
496 P_conversion_date => l_inv_rate_date,
497 P_conversion_type => l_inv_rate_type,
498 P_handle_exception_flag => 'Y',
499 P_amount => cur_get_std_lines.amount,
500 P_user_validate_flag => 'Y',
501 P_converted_amount => l_inv_amt,
502 P_denominator => l_denominator,
503 P_numerator => l_numerator,
504 P_rate => l_rate,
505 X_status => l_status );
506
507 IF g1_debug_mode = 'Y' THEN
508 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' After Convert Call status :' || l_status);
509 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' P_denominator : ' || l_denominator);
510 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' P_numerator : ' || l_numerator);
511 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' P_rate : ' || l_rate);
512 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' l_inv_currency_code : ' || l_inv_currency_code);
513 END IF;
514 if l_status is not null
515 then
516 X_Status := l_status;
517 return;
518 end if;
519
520 l_invtras_rate_flag := TRUE;
521
522 /* Update the invoice header 's invoice currency code and
523 conversion attribute */
524 IF g1_debug_mode = 'Y' THEN
525 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Update DI ');
526 END IF;
527 Update pa_draft_invoices_all
528 set inv_currency_code = l_inv_currency_code
529 ,inv_rate_type = l_inv_rate_type
530 ,inv_rate_date = l_inv_rate_date
531 ,inv_exchange_rate = l_rate
532 where project_id = P_Project_Id
533 and draft_invoice_num = P_Draft_Inv_Num ;
534
535 else
536
537 /* Added for Payroll Billing ER #11847630 - Start */
538 IF ( cur_get_std_lines.credit_process_flag <> 'X' ) THEN
539 /* Added for Payroll Billing ER #11847630 - End */
540
541 /* Convert line amount for all lines except line no: 1 */
542 l_inv_amt := pa_currency.round_trans_currency_amt(((cur_get_std_lines.amount/
543 l_denominator)* l_numerator),l_inv_currency_code);
544
545
546 /* Added for Payroll Billing ER #11847630 - Start */
547
548 ELSE
549 /* Convert line amount for reversl line using
550 existing rate on the line */
551 l_inv_amt := pa_currency.round_trans_currency_amt(
552 ((cur_get_std_lines.amount)* cur_get_std_lines.inv_exchange_rate),
553 l_inv_currency_code);
554
555 END IF;
556
557 /* Added for Payroll Billing ER #11847630 - End */
558
559 end if;
560
561 IF g1_debug_mode = 'Y' THEN
562 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || 'Invoice Number :' || p_draft_inv_num);
563 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || 'Line Number :' || cur_get_std_lines.line_num);
564 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || 'Invoice Amount :' || l_inv_amt);
565 END IF;
566
567 l_max_line_num := cur_get_std_lines.line_num;
568
569 l_total_inv_amount := l_total_inv_amount + l_inv_amt;
570 IF (cur_get_std_lines.credit_process_flag <> 'X') THEN /*Added for Payroll Billing Integration Bug#12383803*/
571 l_tot_proj_amt := l_tot_proj_amt + cur_get_std_lines.amount;
572 END IF; /*Added for Payroll Billing Integration Bug# 12383803*/
573
574 /* Update the line 's invoice currency amount*/
575 IF g1_debug_mode = 'Y' THEN
576 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Update DII ');
577 END IF;
578
579 update pa_draft_invoice_items
580 set inv_amount = l_inv_amt
581 ,inv_rate_type = decode(CREDIT_PROCESs_FLAG,'X',inv_rate_type,l_inv_rate_type)
582 ,inv_rate_date = decode(CREDIT_PROCESs_FLAG,'X',inv_rate_date,l_inv_rate_date)
583 ,inv_exchange_rate = decode(CREDIT_PROCESs_FLAG,'X',inv_exchange_rate,l_rate)
584 where rowid = cur_get_std_lines.row_id; /** Bug 2324299 **/
585
586 end loop;
587
588 /*Added the following for bug 12604419 starts*/
589 IF (l_first_line_flag <> 'X')
590 THEN
591 pa_multi_currency.convert_amount ( P_from_currency => l_invproc_currency_code,
592 P_to_currency => l_Inv_currency_code,
593 P_conversion_date => l_inv_rate_date,
594 P_conversion_type => l_inv_rate_type,
595 P_handle_exception_flag => 'Y',
596 P_amount => l_temp_amount,
597 P_user_validate_flag => 'Y',
598 P_converted_amount => l_temp_amount,
599 P_denominator => l_denominator,
600 P_numerator => l_numerator,
601 P_rate => l_rate,
602 X_status => l_status );
603
604 IF g1_debug_mode = 'Y' THEN
605 PA_MCB_INVOICE_PKG.log_message('recalculATE : ' || ' After Convert Call status :' || l_status);
606 PA_MCB_INVOICE_PKG.log_message('recalculATE : ' || ' P_denominator : ' || l_denominator);
607 PA_MCB_INVOICE_PKG.log_message('recalculATE : ' || ' P_numerator : ' || l_numerator);
608 PA_MCB_INVOICE_PKG.log_message('recalculATE : ' || ' P_rate : ' || l_rate);
609 PA_MCB_INVOICE_PKG.log_message('recalculATE : ' || ' l_inv_currency_code : ' || l_inv_currency_code);
610 END IF;
611 if l_status is not null
612 then
613 X_Status := l_status;
614 return;
615 end if;
616
617 l_invtras_rate_flag := TRUE;
618
619 /* Update the invoice header 's invoice currency code and
620 conversion attribute */
621 IF g1_debug_mode = 'Y' THEN
622 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Update DI ');
623 END IF;
624 Update pa_draft_invoices_all
625 set inv_currency_code = l_inv_currency_code
626 ,inv_rate_type = l_inv_rate_type
627 ,inv_rate_date = l_inv_rate_date
628 ,inv_exchange_rate = l_rate
629 where project_id = P_Project_Id
630 and draft_invoice_num = P_Draft_Inv_Num ;
631
632 END IF;
633
634 /*Added the following for bug 12604419 Ends */
635
636 /* Populate invoice currency amount for retention line */
637
638 l_total_retn_amount :=0;
639
640 for cur_get_ret_lines in get_ret_lines
641 loop
642 l_ret_per := cur_get_ret_lines.retention;
643 l_ret_amt := NVL(l_ret_amt,0)+ NVL(cur_get_ret_lines.amt,0);
644
645 if cur_get_ret_lines.retention_invoice_flag ='Y' THEN
646
647 l_max_line_num := cur_get_ret_lines.line;
648
649 end if;
650
651 l_inv_ret_amt := pa_currency.round_trans_currency_amt(((cur_get_ret_lines.amt/
652 l_denominator)* l_numerator),l_inv_currency_code);
653
654 /* Commented out for Retention Enhancement changes
655 l_inv_ret_amt := (-1)*pa_currency.round_trans_currency_amt(
656 ((cur_get_ret_lines.retention/100)*
657 l_total_inv_amount),l_inv_currency_code); */
658
659 IF g1_debug_mode = 'Y' THEN
660 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Update DII for Retention ');
661 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || 'Retention Amount in IPC : ' || cur_get_ret_lines.amt);
662 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || 'Retention Amount in ITC : ' || l_inv_ret_amt);
663 END IF;
664
665 update pa_draft_invoice_items
666 set inv_amount = l_inv_ret_amt
667 where rowid = cur_get_ret_lines.dii_rowid;
668
669 l_total_retn_amount := NVL(l_total_retn_amount,0) + NVL(l_inv_ret_amt,0);
670
671 end loop;
672
673 /* Adjust Round Off Error */
674 l_tot_proj_amt := l_tot_proj_amt + l_ret_amt;
675 /*Added for Payroll Billing Integration Bug# 12383803*/
676
677 SELECT NVL(sum(amount * inv_exchange_rate),0)
678 INTO l_conv_rev_amt
679 FROM pa_draft_invoice_items
680 WHERE project_id = P_Project_Id
681 AND draft_invoice_num = P_Draft_Inv_Num
682 AND NVL(credit_process_flag ,'N') = 'X';
683 l_con_amt := pa_currency.round_trans_currency_amt((((l_tot_proj_amt/
684 l_denominator)* l_numerator)+l_conv_rev_amt),l_inv_currency_code);
685 /*Added End for Payroll Billing Integration Bug# 12383803*/
686
687 l_total_inv_amount := l_total_inv_amount + l_total_retn_amount;
688
689 l_round_off_amt := l_total_inv_amount - l_con_amt;
690
691
692 IF g1_debug_mode = 'Y' THEN
693 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Update DII for Adjustment ');
694 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Total Invoice Amount : ' || l_total_inv_amount);
695 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Total Retentn Amount : ' || l_total_retn_amount);
696 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Total Calcul Amount : ' || l_con_amt);
697 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Total Round Off Amt : ' || l_round_off_amt);
698 END IF;
699 /*Added nvl for l_round_off_amt for bug 12604419*/
700 Update PA_DRAFT_INVOICE_ITEMS dii
701 SET dii.Inv_amount = pa_currency.round_trans_currency_amt(
702 dii.Inv_amount - nvl(l_round_off_amt,0),l_inv_currency_code)
703 Where dii.project_id = P_Project_Id
704 and dii.Draft_Invoice_num = P_Draft_Inv_Num
705 and dii.Line_Num = l_max_line_num;
706
707
708 /* Set invoice currency amount for net zero line to zero */
709 IF g1_debug_mode = 'Y' THEN
710 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Update DII for net zero ');
711 END IF;
712 update pa_draft_invoice_items
713 set inv_amount = 0
714 where project_id = P_Project_Id
715 and draft_invoice_num = P_Draft_Inv_Num
716 and invoice_line_type = 'NET ZERO ADJUSTMENT';
717
718 end if;
719
720 end if;/* end if for l_inv_currency_code <> l_invproc_currency_code */
721
722 IF g1_debug_mode = 'Y' THEN
723 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' PFC : ' || l_func_curr);
724 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Inv Trans : ' || l_inv_currency_code);
725 END IF;
726
727 -- FP_M Changes #1
728 IF (l_func_curr <> l_inv_currency_code) and (l_invproc_currency_code <> l_func_curr) THEN
729
730 IF g1_debug_mode = 'Y' THEN
731 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' If (l_func_curr <> l_inv_currency_code) ');
732 END IF;
733
734 IF l_ProjFunc_Attr_For_AR_Flag <> 'Y' then
735 -- This if condition is added from FP_M changes bug 3693879
736
737 /* Start of comment for bug 2544659 : To avoid divide by 0 on 0$ invoices
738 SELECT NVL(sum(dii.inv_amount),0)/NVL(sum(dii.projfunc_bill_amount),0)
739 INTO l_projfunc_invtrans_rate
740 FROM pa_draft_invoice_items dii
741 WHERE dii.project_id = P_Project_Id
742 AND dii.draft_invoice_num = P_Draft_Inv_Num;
743 End of comment for bug 2544659*/
744
745 /* Code added for bug 2544659 */
746 /* Code Commented for bug 3436063
747 SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
748 INTO l_projfunc_invtrans_rate
749 FROM pa_draft_invoice_items dii
750 WHERE dii.project_id = P_Project_Id
751 AND dii.draft_invoice_num = P_Draft_Inv_Num
752 AND nvl(dii.projfunc_bill_amount,0) <> 0
753 AND rownum=1;
754 */
755 /****Code added for 3436063****/
756
757 SELECT sum(NVL(dii.projfunc_bill_amount,0))
758 INTO l_sum_projfunc_bill_amount
759 FROM pa_draft_invoice_items dii
760 WHERE dii.project_id = P_Project_Id
761 AND dii.draft_invoice_num = P_Draft_Inv_Num;
762
763 /*** For Bug 5346566 ***/
764 SELECT sum(NVL(dii.inv_amount,0))
765 INTO l_sum_inv_amount
766 FROM pa_draft_invoice_items dii
767 WHERE dii.project_id = P_Project_Id
768 AND dii.draft_invoice_num = P_Draft_Inv_Num;
769 /*** End of code change for Bug 5346566 ***/
770
771 IF l_sum_projfunc_bill_amount <> 0 AND l_sum_inv_amount <> 0 /*** Condition added for bug 5346566 ***/
772 THEN
773 SELECT sum(NVL(dii.inv_amount,0))/sum(NVL(dii.projfunc_bill_amount,0))
774 INTO l_projfunc_invtrans_rate
775 FROM pa_draft_invoice_items dii
776 WHERE dii.project_id = P_Project_Id
777 AND dii.draft_invoice_num = P_Draft_Inv_Num
778 having sum(nvl(dii.projfunc_bill_amount,0)) <> 0;
779 ELSE
780 SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
781 INTO l_projfunc_invtrans_rate
782 FROM pa_draft_invoice_items dii
783 WHERE dii.project_id = P_Project_Id
784 AND dii.draft_invoice_num = P_Draft_Inv_Num
785 AND nvl(dii.projfunc_bill_amount,0) <> 0
786 AND rownum=1;
787 END IF;
788
789 /****End of code added for 3436063****/
790 Update pa_draft_invoices_all
791 set projfunc_invtrans_rate_type = 'User'
792 /* ,projfunc_invtrans_rate_date = sysdate commented for bug 3485407 and modified as follows ..*/
793 ,projfunc_invtrans_rate_date = invoice_date /* for bug 3485407 */
794 ,projfunc_invtrans_ex_rate = NVL(l_projfunc_invtrans_rate,0)
795 where project_id = P_Project_Id
796 and draft_invoice_num = P_Draft_Inv_Num;
797 Else -- If l_ProjFunc_Attr_For_AR_Flag = 'Y' then bug 3693879
798 -- This change is done for FP_M only
799
800 -- Update the conversion rate for ITC to PFC : FP_M changes
801 -- If the Project is implemented with Project Function Attributes for AR flag is
802 -- implemented then update the Project Functional Invoice Transaction
803 -- exchange rate as the Project Functional Exchange Rate
804 -- otherwise update Project Functional Invoice Transaction exchange rate as the
805 -- project functional invoice transaction rate
806
807 --================================
808 -- Newly added code for bug fix Bug 3836514
809 l_Rate := 0;
810 pa_multi_currency.convert_amount (
811 P_from_currency => l_func_curr,
812 P_to_currency => l_invproc_currency_code,
813 P_conversion_date => l_invoice_Date,
814 P_conversion_type => l_projfunc_Exchg_Rate_type,
815 P_Amount => l_inv_amt,
816 P_user_validate_flag => 'Y',
817 P_handle_exception_flag => 'Y',
818 P_converted_amount => l_inv_amt,
819 P_denominator => l_denominator,
820 P_numerator => l_numerator,
821 P_rate => l_rate,
822 X_status => l_status
823
824 );
825
826 --================================
827
828 PA_MCB_INVOICE_PKG.log_message('recalculATE: If l_ProjFunc_Attr_For_AR_Flag = Y');
829 PA_MCB_INVOICE_PKG.log_message('recalculATE: l_invoice_date ' || l_invoice_date);
830 PA_MCB_INVOICE_PKG.log_message('recalculATE: l_PFC_Exchg_Rate_Date_Code ' || l_PFC_Exchg_Rate_Date_Code);
831 PA_MCB_INVOICE_PKG.log_message('recalculATE: l_projfunc_Exchg_Rate_Date ' || l_projfunc_Exchg_Rate_Date);
832 PA_MCB_INVOICE_PKG.log_message('recalculATE: l_projfunc_Exchg_Rate_type ' || l_projfunc_Exchg_Rate_type);
833 PA_MCB_INVOICE_PKG.log_message('recalculATE: l_Rate ' || l_Rate);
834 PA_MCB_INVOICE_PKG.log_message('recalculATE: l_Projfunc_Exchange_Rate ' || l_Projfunc_Exchange_Rate);
835
836 Update pa_draft_invoices_all
837 set projfunc_invtrans_rate_type = l_projfunc_Exchg_Rate_type
838 ,projfunc_invtrans_rate_date = DECODE(l_PFC_Exchg_Rate_Date_Code,
839 'PA_INVOICE_DATE', l_invoice_date, -- Fix for bug 3836514
840 l_projfunc_Exchg_Rate_Date)
841 ,projfunc_invtrans_ex_rate = decode(l_projfunc_Exchg_Rate_type,'User',(1/l_Projfunc_Exchange_Rate),l_Rate)/* Added for bug 7575486*/
842 /*DECODE(l_PFC_Exchg_Rate_Date_Code, 'PA_INVOICE_DATE',
843 decode(l_projfunc_Exchg_Rate_type,'User',l_Projfunc_Exchange_Rate,l_Rate),
844 l_Projfunc_Exchange_Rate)Modified for Bug 7417980, commented for bug 7575486*/
845 where project_id = P_Project_Id
846 and draft_invoice_num = P_Draft_Inv_Num;
847 -- End of FP_M changes
848 End If; -- of l_ProjFunc_Attr_For_AR_Flag value condition
849 ELSIF (l_func_curr <> l_inv_currency_code) and (l_invproc_currency_code = l_func_curr) THEN
850
851 IF g1_debug_mode = 'Y' THEN
852 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' If (l_func_curr <> l_inv_currency_code) and (l_invproc_currency_code = l_func_curr) ');
853 END IF;
854
855 IF (l_invtras_rate_flag) THEN
856
857 IF g1_debug_mode = 'Y' THEN
858 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || 'Invoice Transaction Rate is available ');
859 END IF;
860
861 -- FP_M changes
862 -- If the Project is implemented with Project Function Attributes for AR flag is
863 -- implemented then in draft Invoices, update the Project Functional Invoice Transaction
864 -- exchange rate as the Project Functional Exchange Rate
865 -- otherwise update Project Functional Invoice Transaction exchange rate as the
866 -- invoice exchange rate i.e. derived rate
867
868 -- Modified this Update statement for fixing the bug 3693879
869 -- Commented the below condition for 3693879; when IPC=PFC and PFC <> ITC,
870 -- populate projfunc_invtrans attribute with invoice currency attributes.
871 /*If l_ProjFunc_Attr_For_AR_Flag = 'Y' then
872 --================================
873 -- Newly added code for bug fix Bug 3836514
874 l_Rate := 0;
875 pa_multi_currency.convert_amount (
876 P_from_currency => l_func_curr,
877 P_to_currency => l_invproc_currency_code,
878 P_conversion_date => l_invoice_Date,
879 P_conversion_type => l_projfunc_Exchg_Rate_type,
880 P_Amount => l_inv_amt,
881 P_user_validate_flag => 'Y',
882 P_handle_exception_flag => 'Y',
883 P_converted_amount => l_inv_amt,
884 P_denominator => l_denominator,
885 P_numerator => l_numerator,
886 P_rate => l_rate,
887 X_status => l_status
888
889 );
890
891 --================================
892 Update pa_draft_invoices_all
893 set projfunc_invtrans_rate_type = l_projfunc_Exchg_Rate_type
894 ,projfunc_invtrans_rate_date = DECODE(l_PFC_Exchg_Rate_Date_Code,
895 'PA_INVOICE_DATE', l_invoice_date, -- Fix for Bug 3836514
896 l_projfunc_Exchg_Rate_Date)
897 ,projfunc_invtrans_ex_rate = DECODE(l_PFC_Exchg_Rate_Date_Code, 'PA_INVOICE_DATE', l_Rate,
898 l_Projfunc_Exchange_Rate)
899 where project_id = P_Project_Id
900 and draft_invoice_num = P_Draft_Inv_Num;
901 Else */
902 Update pa_draft_invoices_all
903 set projfunc_invtrans_rate_type = inv_rate_type
904 ,projfunc_invtrans_rate_date = inv_rate_date
905 ,projfunc_invtrans_ex_rate = inv_exchange_rate
906 where project_id = P_Project_Id
907 and draft_invoice_num = P_Draft_Inv_Num;
908 /* End If; */
909 /* Bug fix 2364014 removed the decode */
910 END IF;
911
912 END IF;
913
914 exception
915 when OTHERS
916 then
917 IF g1_debug_mode = 'Y' THEN
918 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Sql Error : ' || sqlerrm);
919 END IF;
920 RAISE;
921 end RECALCULATE;
922
923 /* This procedure will return the invoice Currency code and Conversion attribute
924 for Input Invoice */
925 PROCEDURE get_inv_curr_info ( P_Project_Id IN NUMBER,
926 P_Draft_Inv_Num IN NUMBER,
927 X_Inv_curr_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
928 X_Inv_rate_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
929 X_Inv_rate_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
930 X_Inv_exchange_rate OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
931 IS
932
933 l_error_message pa_lookups.meaning%TYPE;
934
935 Cursor get_info
936 is
937 select Inv_Currency_code,
938 Inv_rate_date,
939 Inv_rate_type,
940 Inv_exchange_rate
941 from pa_draft_invoices
942 where project_id = P_Project_Id
943 and draft_invoice_num = P_Draft_Inv_Num;
944
945 l_inv_curr_code varchar2(15);
946 l_inv_rate_date date;
947 l_inv_rate_type varchar2(30);
948 l_exchange_rate number;
949
950 BEGIN
951
952 open get_info;
953
954 fetch get_info into l_inv_curr_code,l_inv_rate_date,
955 l_inv_rate_type,l_exchange_rate;
956
957 close get_info;
958
959 X_Inv_curr_code := l_inv_curr_code;
960 X_Inv_rate_type := l_inv_rate_type;
961 X_Inv_rate_date := l_inv_rate_date;
962 X_Inv_exchange_rate := l_exchange_rate;
963
964 END get_inv_curr_info;
965
966 /* This procedure is added for Bug 3051294 */
967 /* This procedure will return the Conversion attribute
968 for project functional currency and invoice currency */
969
970 PROCEDURE get_projfunc_inv_curr_info ( P_Project_Id IN NUMBER,
971 P_Draft_Inv_Num IN NUMBER,
972 X_Projfunc_Inv_rate_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
973 X_Projfunc_Inv_rate_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
974 X_Projfunc_Inv_ex_rate OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
975 IS
976
977 Cursor get_info
978 is
979 select Projfunc_invtrans_rate_date,
980 Projfunc_invtrans_rate_type,
981 Projfunc_invtrans_ex_rate
982 from pa_draft_invoices
983 where project_id = P_Project_Id
984 and draft_invoice_num = P_Draft_Inv_Num;
985
986 l_inv_rate_date date;
987 l_inv_rate_type varchar2(30);
988 l_exchange_rate number;
989
990 BEGIN
991
992 open get_info;
993
994 fetch get_info into l_inv_rate_date,l_inv_rate_type,l_exchange_rate;
995
996 close get_info;
997
998 X_Projfunc_Inv_rate_type := l_inv_rate_type;
999 X_Projfunc_Inv_rate_date := l_inv_rate_date;
1000 X_Projfunc_Inv_ex_rate := l_exchange_rate;
1001
1002 END get_projfunc_inv_curr_info;
1003
1004 /* This procedure will fetch the project currency code for input
1005 Project */
1006 PROCEDURE get_proj_curr_info ( P_Project_Id IN NUMBER,
1007 X_Inv_curr_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1008 IS
1009
1010 l_error_message pa_lookups.meaning%TYPE;
1011
1012 /* Earlier this was referring to project_currency_code because PFC and PC are same.
1013 This procedure has to return PFC */
1014 cursor get_proj_cur
1015 is
1016 select PROJFUNC_CURRENCY_CODE
1017 from PA_PROJECTS_ALL
1018 where PROJECT_ID = P_Project_Id;
1019
1020 l_proj_curr PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE;
1021
1022 BEGIN
1023
1024 open get_proj_cur;
1025
1026 fetch get_proj_cur into l_proj_curr;
1027
1028 close get_proj_cur;
1029
1030 X_Inv_curr_code := l_proj_curr;
1031
1032 END get_proj_curr_info;
1033
1034 /*-----------------------------------------------------------------+
1035 | This procedure is only called from PAIGEN. |
1036 +-----------------------------------------------------------------*/
1037 PROCEDURE Update_CRMemo_Invamt ( P_Project_Id IN NUMBER,
1038 P_Draft_Inv_Num IN NUMBER,
1039 P_Draft_Inv_Num_Credited IN NUMBER)
1040 IS
1041
1042 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1043
1044 l_error_message pa_lookups.meaning%TYPE;
1045
1046 cursor get_orig_amt
1047 is
1048 select sum(dii.amount),
1049 sum(dii.inv_amount),
1050 nvl(di.canceled_flag,'N'),max(dii.line_num) /*Added max line for bug 6501526*/
1051 from pa_draft_invoice_items dii,
1052 pa_draft_invoices di
1053 where di.project_id = P_Project_Id
1054 and di.draft_invoice_num = P_Draft_Inv_Num_Credited
1055 and di.project_id = dii.project_id
1056 and di.draft_invoice_num = dii.draft_invoice_num
1057 group by nvl(di.canceled_flag,'N');
1058
1059 cursor get_adjust_cm(l_tot_inv_amt NUMBER,
1060 l_tot_amt NUMBER )
1061 is
1062 /* commented for bug 1633744 .. select modified as below this comment
1063 select cmdii.amount amt,
1064 pa_currency.round_trans_currency_amt(((l_tot_inv_amt/l_tot_amt)
1065 *cmdii.amount),cmdi.inv_currency_code) line_amt,
1066 cmdi.retention_percentage retper,
1067 cmdi.inv_currency_code curcode,
1068 cmdii.line_num line_num
1069 from pa_draft_invoices cmdi,
1070 pa_draft_invoice_items cmdii
1071 where cmdi.project_id = P_Project_Id
1072 and cmdi.draft_invoice_num = P_Draft_Inv_Num
1073 and cmdi.project_id = cmdii.project_id
1074 and cmdi.draft_invoice_num = cmdii.draft_invoice_num
1075 and cmdii.invoice_line_type not in
1076 ('RETENTION','NET ZERO ADJUSTMENT')
1077 for update of cmdii.amount
1078 order by cmdii.line_num;
1079 End of comments for bug 1633744 */
1080 select cmdii.amount amt,
1081 pa_currency.round_trans_currency_amt((cmdii.amount* cmdii1.inv_amount/cmdii1.amount ),cmdi.inv_currency_code) line_amt,
1082 cmdi.retention_percentage retper,
1083 cmdi.inv_currency_code curcode,
1084 cmdii.line_num line_num
1085 from pa_draft_invoices cmdi,
1086 pa_draft_invoice_items cmdii,
1087 pa_draft_invoice_items cmdii1
1088 where cmdi.project_id = P_Project_Id
1089 and cmdi.draft_invoice_num = P_Draft_Inv_Num
1090 and cmdi.project_id = cmdii.project_id
1091 and cmdi.draft_invoice_num = cmdii.draft_invoice_num
1092 and cmdii.project_id = cmdii1.project_id
1093 and cmdii1.draft_invoice_num = cmdi.draft_invoice_num_credited
1094 and cmdii.draft_inv_line_num_credited = cmdii1.line_num
1095 and cmdii1.invoice_line_type not in
1096 ('RETENTION','NET ZERO ADJUSTMENT')
1097 for update of cmdii.inv_amount
1098 order by cmdii.line_num;
1099
1100
1101 cursor get_ret_amt
1102 is
1103 select amount,
1104 line_num
1105 from pa_draft_invoice_items
1106 where project_id = P_Project_Id
1107 and draft_invoice_num = P_Draft_Inv_Num
1108 and invoice_line_type = 'RETENTION'
1109 for update of amount;
1110 /*
1111 Cursor get_mult_ret_amt added to take care of new retention model . Now retention percentage can be at
1112 levels other than the project level as it used to be ..in which case retention_percentage at invoice level will be 0
1113 Bug 2689348
1114 */
1115 Cursor get_mult_ret_amt is
1116 select cmdii.amount amt,
1117 pa_currency.round_trans_currency_amt((cmdii.amount* cmdii1.inv_amount/cmdii1.amount ),cmdi.inv_currency_code) line_amt,
1118 cmdii.line_num line_num
1119 from pa_draft_invoices cmdi,
1120 pa_draft_invoice_items cmdii,
1121 pa_draft_invoice_items cmdii1
1122 where cmdi.project_id = P_Project_Id
1123 and cmdi.draft_invoice_num = P_Draft_Inv_Num
1124 and cmdi.project_id = cmdii.project_id
1125 and cmdi.draft_invoice_num = cmdii.draft_invoice_num
1126 and cmdii.project_id = cmdii1.project_id
1127 and cmdii1.draft_invoice_num = cmdi.draft_invoice_num_credited
1128 and cmdii.draft_inv_line_num_credited = cmdii1.line_num
1129 and cmdii1.invoice_line_type = 'RETENTION'
1130 for update of cmdii.inv_amount
1131 order by cmdii.line_num;
1132
1133
1134 l_total_cnt NUMBER := 0;
1135 l_retper NUMBER := 0;
1136 l_tot_inv_cur_amt NUMBER := 0;
1137 l_tot_proj_cur_amt NUMBER := 0;
1138 l_inv_cur_amt NUMBER;
1139 l_ret_amt NUMBER;
1140 l_line_num NUMBER;
1141 l_max_line_num NUMBER := 0;
1142 l_round_off_amt NUMBER := 0;
1143 l_ret_proj_cur_amt NUMBER := 0;
1144 l_proj_cur_amt NUMBER;
1145 l_cancel_flag VARCHAR2(1);
1146 l_curcode VARCHAR2(16);
1147 l_dummy VARCHAR2(1);
1148 l_max_line NUMBER; /*Added for bug 6501526*/
1149
1150
1151 /* Added to fix bug 2165379 */
1152 l_invproc_currency_code VARCHAR2(30);
1153 l_project_currency_code VARCHAR2(30);
1154 l_funding_currency_code VARCHAR2(30);
1155 l_invproc_currency_type VARCHAR2(30);
1156
1157 l_projfunc_invtrans_rate NUMBER := 0;
1158 l_rate NUMBER := 0;
1159
1160 l_invoice_date DATE;
1161
1162 l_inv_currency_code VARCHAR2(15);
1163 l_inv_rate_type VARCHAR2(30);
1164 l_inv_rate_date DATE;
1165 l_inv_exchange_rate NUMBER;
1166 l_func_curr VARCHAR2(15);
1167
1168 /* End Add to fix bug 2165379 */
1169 l_sum_projfunc_bill_amount NUMBER:=0;
1170 l_sum_inv_amount NUMBER:=0; /*** For bug 5346566 ***/
1171 /* Added for bug 7575486*/
1172 l_projfunc_Exchange_Rate NUMBER;
1173 l_ProjFunc_Attr_For_AR_Flag VARCHAR2(1);
1174 l_projfunc_Exchg_Rate_type VARCHAR2(30);
1175 l_projfunc_Exchg_Rate_Date Date;
1176 l_PFC_Exchg_Rate_Date_Code VARCHAR2(30);
1177 l_inv_amt NUMBER:=0;
1178 l_denominator NUMBER :=0;
1179 l_numerator NUMBER :=0;
1180 l_status VARCHAR2(1000);
1181 /* End bug 7575486*/
1182 BEGIN
1183
1184
1185 /* Added to fix bug 2165379 */
1186
1187 IF g1_debug_mode = 'Y' THEN
1188 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Inside Update cr memo');
1189 END IF;
1190
1191 select PROJFUNC_CURRENCY_CODE,
1192 PROJECT_CURRENCY_CODE,
1193 INVPROC_CURRENCY_TYPE,
1194 PROJFUNC_BIL_EXCHANGE_RATE, -- Added for bug 7575486
1195 PROJFUNC_BIL_RATE_DATE_CODE,
1196 PROJFUNC_BIL_RATE_TYPE,
1197 PROJFUNC_BIL_RATE_DATE,
1198 ProjFunc_Attr_For_AR_Flag -- bug 7575486
1199
1200 into l_func_curr,
1201 l_project_currency_code,
1202 l_invproc_currency_type,
1203 l_projfunc_Exchange_Rate, -- Added for bug 7575486
1204 l_PFC_Exchg_Rate_Date_Code,
1205 l_projfunc_Exchg_Rate_type,
1206 l_projfunc_Exchg_Rate_Date,
1207 l_ProjFunc_Attr_For_AR_Flag -- bug 7575486
1208 from pa_projects_all
1209 where project_id = P_Project_Id;
1210
1211 IF g1_debug_mode = 'Y' THEN
1212 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Project Currency Code ' || l_project_currency_code);
1213 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Project Functional ' || l_func_curr);
1214 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' IPC type ' || l_invproc_currency_type);
1215 END IF;
1216
1217 IF l_invproc_currency_type ='PROJECT_CURRENCY' THEN
1218
1219 l_invproc_currency_code := l_project_currency_code;
1220
1221 ELSIF l_invproc_currency_type ='PROJFUNC_CURRENCY' THEN
1222
1223 l_invproc_currency_code := l_func_curr;
1224
1225 ELSIF l_invproc_currency_type ='FUNDING_CURRENCY' THEN
1226
1227 /* SELECT funding_currency_code
1228 INTO l_funding_currency_code
1229 FROM pa_summary_project_fundings
1230 WHERE project_id = p_project_id
1231 AND rownum=1
1232 GROUP BY funding_currency_code
1233 HAVING sum(total_baselined_amount) <> 0; Commented the code for bug 3147272*/
1234
1235 /* added the following select statement for bug 3147272 */
1236
1237 SELECT funding_currency_code
1238 INTO l_funding_currency_code
1239 FROM pa_summary_project_fundings
1240 WHERE project_id = p_project_id
1241 AND rownum=1
1242 AND NVL(total_baselined_amount,0) > 0;
1243
1244 l_invproc_currency_code := l_funding_currency_code;
1245
1246 END IF;
1247
1248
1249 get_inv_curr_info ( P_Project_Id => p_project_id,
1250 P_Draft_Inv_Num => p_draft_inv_num,
1251 X_Inv_curr_code => l_inv_currency_code,
1252 X_Inv_rate_type => l_inv_rate_type,
1253 X_Inv_rate_date => l_inv_rate_date,
1254 X_Inv_exchange_rate => l_inv_exchange_rate);
1255
1256
1257 /* END Added to fix bug 2165379 */
1258
1259
1260 /* Added to fix bug 2165379 */
1261
1262 /* All code from here in this procedure has been extensively modified to fix bug 2734504 */
1263
1264
1265 if (l_invproc_currency_code = l_inv_currency_code) AND (l_inv_currency_code = l_func_curr)
1266
1267 /* If invoice currency is same as invoice processing currency
1268 and invoice currency is same as project functional currency */
1269 then
1270
1271 IF g1_debug_mode = 'Y' THEN
1272 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' l_invproc_currency_code = l_inv_currency_code = l_func_curr)' );
1273 END IF;
1274
1275 -- X_Status := NULL;
1276
1277 Update pa_draft_invoices_all
1278 set inv_currency_code = l_invproc_currency_code
1279 ,inv_rate_type = NULL
1280 ,inv_rate_date = NULL
1281 ,inv_exchange_rate = NULL
1282 ,projfunc_invtrans_rate_type = NULL
1283 ,projfunc_invtrans_rate_date = NULL
1284 ,projfunc_invtrans_ex_rate = NULL
1285 where project_id = P_Project_Id
1286 and draft_invoice_num = P_Draft_Inv_Num;
1287
1288 Update pa_draft_invoice_items
1289 set inv_amount = amount
1290 where project_id = P_Project_Id
1291 and draft_invoice_num = P_Draft_Inv_Num;
1292
1293 else
1294
1295 IF (l_invproc_currency_code = l_inv_currency_code) THEN
1296
1297 /* If invoice currency is same as invoice processing currency */
1298
1299 -- X_Status := NULL;
1300 IF g1_debug_mode = 'Y' THEN
1301 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' If (l_invproc_currency_code = l_inv_currency_code) ');
1302 END IF;
1303
1304 Update pa_draft_invoices_all
1305 set inv_currency_code = l_invproc_currency_code
1306 ,inv_rate_type = NULL
1307 ,inv_rate_date = NULL
1308 ,inv_exchange_rate = NULL
1309 where project_id = P_Project_Id
1310 and draft_invoice_num = P_Draft_Inv_Num;
1311
1312 Update pa_draft_invoice_items
1313 set inv_amount = amount
1314 where project_id = P_Project_Id
1315 and draft_invoice_num = P_Draft_Inv_Num;
1316
1317 END IF;
1318
1319 IF (l_inv_currency_code = l_func_curr) THEN
1320 -- If invoice currency is same as project functional currency
1321 -- X_Status := NULL;
1322 IF g1_debug_mode = 'Y' THEN
1323 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' If (l_inv_currency_code = l_func_curr) ');
1324 END IF;
1325
1326 Update pa_draft_invoices_all
1327 set projfunc_invtrans_rate_type = NULL
1328 ,projfunc_invtrans_rate_date = NULL
1329 ,projfunc_invtrans_ex_rate = NULL
1330 ,inv_currency_code = l_inv_currency_code
1331 ,inv_rate_type = NULL
1332 ,inv_rate_date = NULL
1333 ,inv_exchange_rate = NULL
1334 where project_id = P_Project_Id
1335 and draft_invoice_num = P_Draft_Inv_Num;
1336
1337 Update pa_draft_invoice_items
1338 set inv_amount = projfunc_bill_amount
1339 where project_id = P_Project_Id
1340 and draft_invoice_num = P_Draft_Inv_Num;
1341
1342 END IF;
1343
1344 IF (l_invproc_currency_code <> l_inv_currency_code) AND (l_inv_currency_code <> l_func_curr) THEN
1345
1346 IF g1_debug_mode = 'Y' THEN
1347 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' l_invproc_currency_code <> l_inv_currency_code ');
1348 END IF;
1349
1350 open get_orig_amt;
1351
1352 fetch get_orig_amt into l_proj_cur_amt,l_inv_cur_amt,l_cancel_flag,l_max_line; /* Modified for bug 6501526*/
1353
1354 close get_orig_amt;
1355
1356 If l_cancel_flag = 'N' Then
1357 -- Write-off and Credit memo processing
1358
1359 -- Updation of Non Retention Line
1360 for cur_get_adjust_cm in get_adjust_cm ( l_inv_cur_amt,
1361 l_proj_cur_amt )
1362 loop
1363 Update pa_draft_invoice_items
1364 set inv_amount = cur_get_adjust_cm.line_amt
1365 where current of get_adjust_cm;
1366
1367 l_tot_inv_cur_amt := l_tot_inv_cur_amt + cur_get_adjust_cm.line_amt;
1368 l_tot_proj_cur_amt := l_tot_proj_cur_amt + cur_get_adjust_cm.amt;
1369 l_max_line_num := cur_get_adjust_cm.line_num;
1370
1371 If l_retper = 0
1372 then
1373 l_retper := cur_get_adjust_cm.retper;
1374 l_curcode := cur_get_adjust_cm.curcode;
1375 end if;
1376 end loop;
1377
1378 if l_retper <> 0 then /* added for 2689348 Carry on with old retention model */
1379 -- Updation of Retention Line
1380 l_ret_amt := (-1) * pa_currency.round_trans_currency_amt((l_tot_inv_cur_amt*l_retper)
1381 /100,l_curcode);
1382
1383 open get_ret_amt;
1384
1385 loop
1386 fetch get_ret_amt into l_ret_proj_cur_amt,l_line_num;
1387
1388 exit when get_ret_amt%notfound;
1389
1390 l_tot_proj_cur_amt := l_tot_proj_cur_amt + l_ret_proj_cur_amt;
1391 l_tot_inv_cur_amt := l_tot_inv_cur_amt + l_ret_amt;
1392
1393 update pa_draft_invoice_items
1394 set inv_amount = l_ret_amt
1395 where current of get_ret_amt;
1396
1397 l_max_line_num := l_line_num;
1398
1399 end loop;
1400
1401 close get_ret_amt;
1402
1403 else /* added for 2689348 for new retention model */
1404
1405 for cur_mult_ret_amt in get_mult_ret_amt
1406 loop
1407 update pa_draft_invoice_items dii
1408 set inv_amount= cur_mult_ret_amt.line_amt
1409 where current of get_mult_ret_amt;
1410
1411 l_tot_inv_cur_amt := l_tot_inv_cur_amt + cur_mult_ret_amt.line_amt;
1412 l_tot_proj_cur_amt := l_tot_proj_cur_amt + cur_mult_ret_amt.amt;
1413 l_max_line_num := cur_mult_ret_amt.line_num;
1414
1415 end loop;
1416 end if ; /* End of code change for bug 2689348 */
1417
1418 if l_proj_cur_amt <>0 then /* added to avoid divide by zero : 1633744 */
1419 -- Adjust Round-Off Error
1420 l_round_off_amt := l_tot_inv_cur_amt -
1421 pa_currency.round_trans_currency_amt((l_inv_cur_amt/l_proj_cur_amt)
1422 *l_tot_proj_cur_amt,l_curcode);
1423
1424 update pa_draft_invoice_items
1425 set inv_amount = inv_amount - l_round_off_amt
1426 where project_id = P_Project_Id
1427 and draft_invoice_num = P_Draft_Inv_Num
1428 and line_num = l_max_line_num
1429 and draft_inv_line_num_credited = l_max_line; /*Added for bug 6501526*/
1430
1431 end if; /* 1633744 end if for l_proj_cur_amt <>0 */
1432
1433 Else
1434
1435 -- For Cancellation Of Invoice
1436 Update pa_draft_invoice_items dii
1437 set dii.inv_amount = ( select (-1)*dii1.inv_amount
1438 from pa_draft_invoice_items dii1
1439 where dii1.project_id = dii.project_id
1440 and dii1.draft_invoice_num = P_Draft_Inv_Num_credited
1441 and dii1.line_num
1442 = dii.draft_inv_line_num_credited)
1443 where dii.project_id = P_Project_Id
1444 and dii.draft_Invoice_Num = P_Draft_Inv_Num;
1445
1446 END IF;
1447
1448
1449 /* Start of comment for bug 2544659 : To avoid divide by 0 on 0$ invoices
1450
1451 SELECT NVL(sum(dii.inv_amount),0)/NVL(sum(dii.amount),0)
1452 INTO l_rate
1453 FROM pa_draft_invoice_items dii
1454 WHERE dii.project_id = P_Project_Id
1455 AND dii.draft_invoice_num = P_Draft_Inv_Num;
1456 End of comments for bug 2544659 */
1457
1458 /* code fix for bug 2544659 */
1459 /*Code commented for 3436063
1460 SELECT NVL(dii.inv_amount,0)/NVL(dii.amount,0)
1461 INTO l_rate
1462 FROM pa_draft_invoice_items dii
1463 WHERE dii.project_id = P_Project_Id
1464 AND dii.draft_invoice_num = P_Draft_Inv_Num
1465 AND nvl(dii.amount,0)<>0
1466 AND rownum=1 ;
1467 */
1468 /****Code added for 3436063****/
1469 /* commented for bug 6501526
1470 SELECT sum(NVL(dii.projfunc_bill_amount,0))
1471 INTO l_sum_projfunc_bill_amount
1472 FROM pa_draft_invoice_items dii
1473 WHERE dii.project_id = P_Project_Id
1474 AND dii.draft_invoice_num = P_Draft_Inv_Num;
1475 */
1476 /*** For Bug 5346566 ***/
1477 /* commented for bug 6501526
1478 SELECT sum(NVL(dii.inv_amount,0))
1479 INTO l_sum_inv_amount
1480 FROM pa_draft_invoice_items dii
1481 WHERE dii.project_id = P_Project_Id
1482 AND dii.draft_invoice_num = P_Draft_Inv_Num;
1483 */
1484 /*** End of code change for Bug 5346566 ***/
1485
1486 /* commented for bug 6501526
1487 IF l_sum_projfunc_bill_amount <> 0 AND l_sum_inv_amount <> 0 /*** Condtion added for bug 5346566 ***
1488 THEN
1489 SELECT sum(NVL(dii.inv_amount,0))/sum(NVL(dii.amount,0))
1490 INTO l_rate
1491 FROM pa_draft_invoice_items dii
1492 WHERE dii.project_id = P_Project_Id
1493 AND dii.draft_invoice_num = P_Draft_Inv_Num
1494 having sum(nvl(dii.amount,0)) <> 0;
1495 ELSE
1496 SELECT NVL(dii.inv_amount,0)/NVL(dii.amount,0)
1497 INTO l_rate
1498 FROM pa_draft_invoice_items dii
1499 WHERE dii.project_id = P_Project_Id
1500 AND dii.draft_invoice_num = P_Draft_Inv_Num
1501 AND nvl(dii.amount,0) <> 0
1502 AND rownum=1;
1503 END IF;
1504 */
1505 /****End of code added for 3436063****/
1506
1507
1508 /* Bug 2689348 */
1509
1510 /* commented for bug 6501526
1511 SELECT di.inv_rate_type,di.inv_rate_date
1512 INTO l_inv_rate_type,l_inv_rate_date
1513 FROM pa_draft_invoices_all di,pa_draft_invoices_all cmdi
1514 WHERE cmdi.draft_invoice_num_credited= di.draft_invoice_num
1515 AND cmdi.project_id=di.project_id
1516 AND cmdi.draft_invoice_num=P_Draft_Inv_Num
1517 AND cmdi.project_id=P_Project_Id;
1518
1519 -- Update the conversion rate for ITC to IC
1520
1521 Update pa_draft_invoices_all
1522 set inv_currency_code = l_inv_currency_code
1523 -- ,inv_rate_type = 'User'
1524 --,inv_rate_date = sysdate Should be picked from main invoice: Bug 2689348
1525 -- code changed for bug 2689348
1526 ,inv_rate_type = l_inv_rate_type
1527 ,inv_rate_date = l_inv_rate_date
1528 ,inv_exchange_rate = l_rate
1529 where project_id = P_Project_Id
1530 and draft_invoice_num = P_Draft_Inv_Num;
1531 */
1532 END IF;
1533 END IF;
1534
1535 IF g1_debug_mode = 'Y' THEN
1536 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' PFC : ' || l_func_curr);
1537 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' Inv Trans : ' || l_inv_currency_code);
1538 END IF;
1539
1540 IF (l_func_curr <> l_inv_currency_code) and (l_invproc_currency_code <> l_func_curr) THEN
1541
1542 IF g1_debug_mode = 'Y' THEN
1543 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' l_func_curr <> l_inv_currency_code and l_invproc_currency_code <> l_func_curr ');
1544 END IF;
1545
1546 IF l_ProjFunc_Attr_For_AR_Flag <> 'Y' then /* Added for bug 7575486*/
1547
1548 /* Start of comment for bug 2544659 : To avoid divide by 0 on 0$ invoices
1549 SELECT NVL(sum(dii.inv_amount),0)/NVL(sum(dii.projfunc_bill_amount),0)
1550 INTO l_projfunc_invtrans_rate
1551 FROM pa_draft_invoice_items dii
1552 WHERE dii.project_id = P_Project_Id
1553 AND dii.draft_invoice_num = P_Draft_Inv_Num;
1554 End of comments for bug 2544659 */
1555
1556 /* Code added for bug 2544659 */
1557 /*Code commented for 3436063
1558 SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
1559 INTO l_projfunc_invtrans_rate
1560 FROM pa_draft_invoice_items dii
1561 WHERE dii.project_id = P_Project_Id
1562 AND dii.draft_invoice_num = P_Draft_Inv_Num
1563 AND nvl(dii.projfunc_bill_amount,0) <> 0
1564 AND rownum=1;
1565 */
1566
1567 /****Code added for 3436063****/
1568 SELECT sum(NVL(dii.projfunc_bill_amount,0))
1569 INTO l_sum_projfunc_bill_amount
1570 FROM pa_draft_invoice_items dii
1571 WHERE dii.project_id = P_Project_Id
1572 AND dii.draft_invoice_num = P_Draft_Inv_Num;
1573
1574 /*** For Bug 5346566 ***/
1575 SELECT sum(NVL(dii.inv_amount,0))
1576 INTO l_sum_inv_amount
1577 FROM pa_draft_invoice_items dii
1578 WHERE dii.project_id = P_Project_Id
1579 AND dii.draft_invoice_num = P_Draft_Inv_Num;
1580 /*** End of code change for Bug 5346566 ***/
1581
1582 IF l_sum_projfunc_bill_amount <> 0 AND l_sum_inv_amount <> 0 /*** Condition added for bug 5346566 ***/
1583 THEN
1584 SELECT sum(NVL(dii.inv_amount,0))/sum(NVL(dii.projfunc_bill_amount,0))
1585 INTO l_projfunc_invtrans_rate
1586 FROM pa_draft_invoice_items dii
1587 WHERE dii.project_id = P_Project_Id
1588 AND dii.draft_invoice_num = P_Draft_Inv_Num
1589 having sum(nvl(dii.projfunc_bill_amount,0)) <> 0;
1590 ELSE
1591 SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
1592 INTO l_projfunc_invtrans_rate
1593 FROM pa_draft_invoice_items dii
1594 WHERE dii.project_id = P_Project_Id
1595 AND dii.draft_invoice_num = P_Draft_Inv_Num
1596 AND nvl(dii.projfunc_bill_amount,0) <> 0
1597 AND rownum=1;
1598 END IF;
1599 /****End of code added for 3436063****/
1600
1601 -- Update the conversion rate for ITC to PFC
1602
1603 Update pa_draft_invoices_all
1604 set projfunc_invtrans_rate_type = 'User'
1605 /* ,projfunc_invtrans_rate_date = sysdate commented for bug 3485407 and modified as follows ..*/
1606 ,projfunc_invtrans_rate_date = invoice_date /* for bug 3485407 */
1607 ,projfunc_invtrans_ex_rate = NVL(l_projfunc_invtrans_rate,0)
1608 where project_id = P_Project_Id
1609 and draft_invoice_num = P_Draft_Inv_Num;
1610
1611 /* Added for bug 7575486*/
1612 ELSE
1613 l_Rate := 0;
1614 l_inv_amt := 1;
1615 select invoice_date
1616 into l_invoice_date
1617 from pa_draft_invoices_all
1618 where project_id = P_Project_Id
1619 and draft_invoice_num = P_Draft_Inv_Num;
1620
1621 pa_multi_currency.convert_amount (
1622 P_from_currency => l_func_curr,
1623 P_to_currency => l_invproc_currency_code,
1624 P_conversion_date => l_invoice_Date,
1625 P_conversion_type => l_projfunc_Exchg_Rate_type,
1626 P_Amount => l_inv_amt,
1627 P_user_validate_flag => 'Y',
1628 P_handle_exception_flag => 'Y',
1629 P_converted_amount => l_inv_amt,
1630 P_denominator => l_denominator,
1631 P_numerator => l_numerator,
1632 P_rate => l_rate,
1633 X_status => l_status
1634 );
1635
1636
1637 PA_MCB_INVOICE_PKG.log_message('recalculATE: If l_ProjFunc_Attr_For_AR_Flag = Y');
1638 PA_MCB_INVOICE_PKG.log_message('recalculATE: l_invoice_date ' || l_invoice_date);
1639 PA_MCB_INVOICE_PKG.log_message('recalculATE: l_PFC_Exchg_Rate_Date_Code ' || l_PFC_Exchg_Rate_Date_Code);
1640 PA_MCB_INVOICE_PKG.log_message('recalculATE: l_projfunc_Exchg_Rate_Date ' || l_projfunc_Exchg_Rate_Date);
1641 PA_MCB_INVOICE_PKG.log_message('recalculATE: l_projfunc_Exchg_Rate_type ' || l_projfunc_Exchg_Rate_type);
1642 PA_MCB_INVOICE_PKG.log_message('recalculATE: l_Rate ' || l_Rate);
1643 PA_MCB_INVOICE_PKG.log_message('recalculATE: l_Projfunc_Exchange_Rate ' || l_Projfunc_Exchange_Rate);
1644
1645 Update pa_draft_invoices_all
1646 set projfunc_invtrans_rate_type = l_projfunc_Exchg_Rate_type
1647 ,projfunc_invtrans_rate_date = DECODE(l_PFC_Exchg_Rate_Date_Code,
1648 'PA_INVOICE_DATE', l_invoice_date,
1649 l_projfunc_Exchg_Rate_Date)
1650 ,projfunc_invtrans_ex_rate = decode(l_projfunc_Exchg_Rate_type,'User',(1/l_Projfunc_Exchange_Rate),l_Rate)
1651 where project_id = P_Project_Id
1652 and draft_invoice_num = P_Draft_Inv_Num;
1653
1654 END IF;
1655 /* Added for bug 7575486*/
1656 ELSIF (l_func_curr <> l_inv_currency_code) and (l_invproc_currency_code = l_func_curr) THEN
1657
1658 IF g1_debug_mode = 'Y' THEN
1659 PA_MCB_INVOICE_PKG.log_message('recalculATE: ' || ' l_func_curr <> l_inv_currency_code and l_invproc_currency_code = l_func_curr ');
1660 END IF;
1661
1662 Update pa_draft_invoices_all
1663 set projfunc_invtrans_rate_type = inv_rate_type
1664 ,projfunc_invtrans_rate_date = inv_rate_date
1665 ,projfunc_invtrans_ex_rate = inv_exchange_rate
1666 -- DECODE(NVL(inv_exchange_rate,0),0,0, 1/NVL(inv_exchange_rate,0))
1667 where project_id = P_Project_Id
1668 and draft_invoice_num = P_Draft_Inv_Num;
1669
1670 /* Bug fix 2364014 removed the decode */
1671
1672 END IF;
1673
1674
1675 /* End Add to fix bug 2165379 */
1676 EXCEPTION
1677 When OTHERS
1678 Then Raise;
1679
1680 END Update_CRMemo_Invamt;
1681
1682 /*------------------------------------------------------------------+
1683 | Added for R11.1 Multi Currency Billing Project . This part will |
1684 | will recalculate the invoice in invoice currency for all all |
1685 | unapproved invoices and update the appropriate fields of invoice |
1686 | Header and Details. This procedure is only called from PAIGEN. |
1687 |__________________________________________________________________*/
1688
1689 Procedure Recalculate_Driver( P_Request_ID in number,
1690 P_User_ID in number,
1691 P_Project_ID in number,
1692 p_calling_process IN VARCHAR2 DEFAULT 'PROJECT_INVOICES')
1693 IS
1694
1695 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1696
1697 l_error_message pa_lookups.meaning%TYPE;
1698
1699 /* Cursor for Select All Unapproved invoices created in This Run */
1700 /* Bug 5413168: skip invoices without any invoice line. generation_
1701 error_flag not stamped yet for this case until paicnl */
1702 CURSOR UNAPP_INV_CUR is
1703 SELECT i.project_id,
1704 i.draft_invoice_num,
1705 decode(i.draft_invoice_num_credited, NULL, 'INVOICE',
1706 'CREDIT_MEMO') invoice_class,
1707 agr.customer_id,
1708 i.bill_through_date,
1709 i.draft_invoice_num_credited
1710 FROM pa_draft_invoices i,
1711 pa_agreements_all agr
1712 WHERE i.request_id = P_Request_ID
1713 AND nvl(i.generation_error_flag, 'N') = 'N'
1714 AND i.project_id+0 = P_Project_ID
1715 AND i.agreement_id = agr.agreement_id
1716 AND p_calling_process = 'PROJECT_INVOICES'
1717 AND EXISTS (SELECT 1 from PA_DRAFT_INVOICE_ITEMS dii
1718 WHERE dii.project_id = i.project_id
1719 AND dii.draft_invoice_num = i.draft_invoice_num)
1720 UNION
1721 SELECT i.project_id,
1722 i.draft_invoice_num,
1723 decode(i.draft_invoice_num_credited, NULL, 'INVOICE',
1724 'CREDIT_MEMO') invoice_class,
1725 agr.customer_id,
1726 i.bill_through_date,
1727 i.draft_invoice_num_credited
1728 FROM pa_draft_invoices i,
1729 pa_agreements_all agr
1730 WHERE i.request_id = P_Request_ID
1731 AND nvl(i.generation_error_flag, 'N') = 'N'
1732 AND i.project_id+0 = P_Project_ID
1733 AND i.agreement_id = agr.agreement_id
1734 AND p_calling_process = 'RETENTION_INVOICES'
1735 AND i.retention_invoice_flag ='Y'
1736 AND EXISTS (SELECT 1 from PA_DRAFT_INVOICE_ITEMS dii
1737 WHERE dii.project_id = i.project_id
1738 AND dii.draft_invoice_num = i.draft_invoice_num);
1739
1740
1741 l_out_status varchar2(1000);
1742 l_project_id number;
1743 l_draft_invoice_num number;
1744 l_customer_id number;
1745 l_cr_inv_num number;
1746 l_bill_thru_date date;
1747 l_invoice_class varchar2(15);
1748 l_invoice_amount number;
1749 l_dummy number;
1750
1751 BEGIN
1752
1753 IF g1_debug_mode = 'Y' THEN
1754 PA_MCB_INVOICE_PKG.log_message ('Inside recalculate driver');
1755 END IF;
1756
1757 OPEN UNAPP_INV_CUR;
1758
1759 LOOP
1760 FETCH UNAPP_INV_CUR into l_project_id,
1761 l_draft_invoice_num,
1762 l_invoice_class,l_customer_id,
1763 l_bill_thru_date,l_cr_inv_num;
1764
1765 EXIT WHEN UNAPP_INV_CUR%NOTFOUND;
1766
1767 IF l_invoice_class = 'INVOICE'
1768 THEN
1769 IF g1_debug_mode = 'Y' THEN
1770 PA_MCB_INVOICE_PKG.log_message ('Calling Recalculate');
1771 END IF;
1772 PA_INVOICE_CURRENCY.RECALCULATE ( P_Project_Id =>l_project_id,
1773 P_Draft_Inv_Num =>l_draft_invoice_num,
1774 P_Calling_Module =>'PAIGEN',
1775 P_Customer_Id =>l_customer_id,
1776 P_Inv_currency_code =>NULL,
1777 P_Inv_Rate_Type =>NULL,
1778 P_Inv_Rate_Date =>NULL,
1779 P_Inv_Exchange_Rate =>NULL,
1780 P_User_Id =>NULL,
1781 P_Bill_Thru_Date =>l_bill_thru_date,
1782 X_Status =>l_out_status);
1783 IF l_out_status IS NOT NULL
1784 THEN
1785 IF g1_debug_mode = 'Y' THEN
1786 PA_MCB_INVOICE_PKG.log_message ('recalculATE: ' || 'Insert distribution warning ');
1787 END IF;
1788 Insert_Distrbution_Warning ( P_Project_ID =>l_Project_ID,
1789 P_Draft_Invoice_Num =>l_draft_invoice_num,
1790 P_User_ID =>P_User_ID,
1791 P_Request_ID =>P_Request_ID,
1792 P_Invoice_Set_ID =>NULL,
1793 P_Lookup_Type =>'INVOICE_CURRENCY',
1794 P_Error_Message_Code=>l_out_status);
1795
1796 /* Bug 2450414 - Inserted the Invoice Generation Error */
1797 IF l_out_status = 'PA_NO_EXCH_RATE_EXISTS' OR l_out_status ='PA_CURR_NOT_VALID'
1798 OR l_out_status = 'PA_USR_RATE_NOT_ALLOWED'
1799 THEN
1800 IF g1_debug_mode = 'Y' THEN
1801 PA_MCB_INVOICE_PKG.log_message ('recalculATE: ' || 'Invoice Generation Error is set....'||l_out_status);
1802 END IF;
1803 UPDATE pa_draft_invoices_all
1804 SET generation_error_flag='Y',
1805 transfer_rejection_reason= (SELECT meaning FROM pa_lookups
1806 WHERE lookup_type='INVOICE_CURRENCY'
1807 AND lookup_code=l_out_status)
1808 WHERE project_id=l_Project_ID
1809 AND draft_invoice_num=l_Draft_Invoice_Num;
1810 END IF;
1811 /* Fix for Bug 2450414 Ends here */
1812
1813 END IF;
1814 ELSIF l_invoice_class = 'CREDIT_MEMO'
1815 THEN
1816 IF g1_debug_mode = 'Y' THEN
1817 PA_MCB_INVOICE_PKG.log_message ('recalculATE: ' || 'Calling Upddate_crmemo_invamt ');
1818 END IF;
1819 PA_INVOICE_CURRENCY.Update_CRMemo_Invamt
1820 (P_Project_Id =>l_Project_ID,
1821 P_Draft_Inv_Num =>l_draft_invoice_num,
1822 P_Draft_Inv_Num_Credited =>l_cr_inv_num );
1823 END IF;
1824 END LOOP;
1825
1826 CLOSE UNAPP_INV_CUR;
1827
1828 EXCEPTION
1829 WHEN OTHERS
1830 THEN
1831 RAISE;
1832 END Recalculate_Driver;
1833
1834 END PA_INVOICE_CURRENCY;