eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PA_INVOICE_CURRENCY

Source


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