[Home] [Help]
PACKAGE BODY: APPS.FV_ECON_BENF_DISC_PVT
Source
1 PACKAGE BODY FV_ECON_BENF_DISC_PVT AS
2 -- $Header: FVAPEBDB.pls 120.15 2011/03/04 10:53:51 amaddula ship $
3 g_module_name VARCHAR2(100) := 'fv.plsql.FV_ECON_BENF_DISC_PVT.';
4 g_org_id number;
5 g_sob number;
6 -- Bug 11834795: Make Discount_pct global
7 Discount_Pct NUMBER;
8
9
10 Function PAYDT_BEFORE_DISCDT(X_Payment_Date IN DATE,
11 X_Discount_Date IN DATE)RETURN BOOLEAN IS
12 l_module_name VARCHAR2(200) := g_module_name || 'PAYDT_BEFORE_DISCDT';
13 l_errbuf VARCHAR2(1024);
14 Begin
15 If X_Payment_Date <= X_Discount_Date then
16 RETURN TRUE;
17 Else
18 RETURN FALSE;
19 End If;
20 EXCEPTION
21 WHEN OTHERS THEN
22 l_errbuf := SQLERRM;
23 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
24 RAISE;
25 End PAYDT_BEFORE_DISCDT;
26 /*----------------------------------------------*/
27 Function ROW_EXISTS(X_Invoice_Id IN NUMBER,
28 X_Err_Num OUT NOCOPY NUMBER,
29 X_Err_Stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
30 l_module_name VARCHAR2(200) := g_module_name || 'ROW_EXISTS';
31 Inv_Nbr NUMBER(15);
32 BEGIN
33 BEGIN
34 select invoice_id
35 into
36 Inv_Nbr
37 from FV_DISCOUNTED_INVOICES
38 where
39 invoice_id = X_Invoice_Id;
40 X_Err_Num := 0;
41
42 RETURN TRUE;
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45 X_Err_Num := 1;
46 X_Err_Stage := 'No row found for Invoice '||to_char(X_Invoice_Id);
47 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',X_Err_Stage);
48 RETURN FALSE;
49 WHEN TOO_MANY_ROWS THEN
50 X_Err_Num := 2;
51 X_Err_Stage := 'There is more than one row for the Invoice '||to_char(X_Invoice_Id);
52 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error2',X_Err_Stage);
53 RETURN FALSE;
54 WHEN OTHERS THEN
55 X_Err_Stage := SQLERRM;
56 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',X_Err_Stage);
57 RAISE;
58 END;
59 End ROW_EXISTS;
60 /*-----------------------------------------------------------------*/
61 -- Version 1.3.2 Added ROW_EXISTS for FV_ASSIGN_REASON_CODES RCW.
62 /*-----------------------------------------------------------------*/
63 Function ROW_EXISTS_FVRC(X_Invoice_Id IN NUMBER,
64 X_Err_Num OUT NOCOPY NUMBER,
65 X_Err_Stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
66 l_module_name VARCHAR2(200) := g_module_name || 'ROW_EXISTS_FVRC';
67 Inv_id NUMBER(15);
68 ent_source VARCHAR2(15);
69 BEGIN
70 BEGIN
71 select invoice_id, entry_source
72 into
73 Inv_id, ent_source
74 from FV_ASSIGN_REASON_CODES
75 where
76 invoice_id = X_Invoice_Id
77 and entry_source = 'EBD';
78 X_Err_Num := 0;
79
80 RETURN TRUE;
81 EXCEPTION
82 WHEN NO_DATA_FOUND THEN
83 X_Err_Num := 1;
84 X_Err_Stage := 'No row found for Invoice '||to_char(X_Invoice_Id);
85 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',X_Err_Stage);
86 RETURN FALSE;
87 WHEN TOO_MANY_ROWS THEN
88 X_Err_Num := 2;
89 X_Err_Stage := 'There is more than one row for the FVRC Invoice '||to_char(X_Invoice_Id);
90 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',X_Err_Stage);
91 RETURN FALSE;
92 WHEN OTHERS THEN
93 X_Err_Stage := SQLERRM;
94 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',X_Err_Stage);
95 RAISE;
96 END;
97 End ROW_EXISTS_FVRC;
98 /*------------- END 1.3.2 --------------------------------*/
99
100 Procedure CALCULATE_DISCOUNT(X_Invoice_Id IN NUMBER,
101 X_Discount_Amount IN NUMBER,
102 X_Invoice_Amount IN NUMBER,
103 X_Due_Date IN OUT NOCOPY DATE,
104 X_Discount_Date IN OUT NOCOPY DATE,
105 X_Terms_Date IN DATE,
106 X_Invoice_Date IN DATE,
107 X_Invoice_Received_Date IN DATE,
108 X_Goods_Received_Date IN DATE,
109 X_Effective_Discount_Rate IN OUT NOCOPY NUMBER,
110 X_Err_Num OUT NOCOPY NUMBER,
111 X_Err_Stage OUT NOCOPY VARCHAR2,
112 X_Payment_Date IN DATE) IS
113 l_module_name VARCHAR2(200) := g_module_name || 'CALCULATE_DISCOUNT';
114
115 Days_In_Year NUMBER := 360;
116 Due_Days NUMBER;
117 Total_Disc_Days NUMBER;
118 Days_Left_In_Disc_Period NUMBER;
119 BEGIN
120
121
122 /*Discount_Pct := (X_Discount_Amount / X_Invoice_Amount);*/
123 -- Bug 11834795: Discount_Pct is null if there are multiple rows in ap_terms_lines
124 IF Discount_Pct IS NULL THEN
125 Discount_Pct := (X_Discount_Amount / X_Invoice_Amount);
126 END IF;
127
128
129 /* To check whether any changes made to original due date,if so
130 using the orginal due date from fv_inv_selected_duedate */
131 /*
132 begin
133 select org_due_date,org_discount_date
134 into x_due_date ,x_discount_date
135 from fv_inv_selected_duedate
136 where invoice_id = x_invoice_id;
137 exception
138 when no_data_found then
139 null;
140 when too_many_rows then
141 null;
142 End ;
143 commenting for bug#8550230 */
144
145 ------------------------------------
146
147 Due_days := TRUNC(X_Due_Date) - TRUNC(NVL(X_Invoice_Received_Date,X_Invoice_date));
148 -- No. Of days for Payment Due
149 Total_Disc_Days := TRUNC(X_Discount_Date) - TRUNC(X_Invoice_date);
150 -- Total No. Of Days of Discount
151 --Days_Left_In_Disc_Period :=Total_Disc_Days -(TRUNC(NVL(X_Invoice_Received_Date, X_Invoice_date)) - TRUNC(X_Invoice_date));
152 -- Bug 11834795: Consider payment date while calculating no. of discount days left
153 Days_Left_In_Disc_Period := TRUNC(X_Discount_Date) - TRUNC(x_payment_date);
154
155
156 -- Bug 5486026 (R12.FIN.A.QA.XB.9X: ERROR WHILE SUBMITING A PAYMENT PROCESS REQUEST)
157 -- This was caused by divide by zero error when discount_amount = invoice_amount
158 -- and therefore Discount_Pct = 1. This is a limiting case and in this case we
159 -- hardcode the discount rate to a very high value so that the invoice is included
160 -- for payment
161
162 IF ((Due_days - Days_Left_In_Disc_Period) <> 0 AND (1 - Discount_Pct) <> 0) THEN
163 X_Effective_Discount_Rate := 100*((Discount_Pct / (1 - Discount_Pct)) *
164 (Days_In_Year / (Due_days - Days_Left_In_Disc_Period)));
165
166 ELSIF ((Due_days - Days_Left_In_Disc_Period) = 0 AND (1 - Discount_Pct) <> 0) THEN
167 X_Effective_Discount_Rate := 100*(Discount_Pct / (1 - Discount_Pct));
168 ELSE -- (1 - Discount_Pct) = 0
169 X_Effective_Discount_Rate := 10000;
170 END IF;
171
172
173 X_Err_Num := 0;
174 EXCEPTION
175 WHEN ZERO_DIVIDE THEN
176 X_Err_Num := 2;
177 X_Err_Stage := 'There has been a division by ZERO while processing Invoice '||to_char(X_Invoice_Id);
178 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',X_Err_Stage);
179 WHEN OTHERS THEN
180 X_Err_Num := SQLCODE;
181 X_Err_Stage := SQLERRM;
182 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',X_Err_Stage);
183 End CALCULATE_DISCOUNT;
184 /*---------------------------------------------*/
185
186 Procedure INSERT_FV_DISCOUNTED_INVOICES(X_Invoice_Id IN NUMBER,
187 X_Discount_Taken_Flag IN VARCHAR2,
188 X_Discount_Status_Code IN VARCHAR2,
189 X_Payment_Date IN DATE,
190 X_Effective_Discount_Rate IN NUMBER,
191 X_CVOF_Rate IN NUMBER,
192 X_Err_Num OUT NOCOPY NUMBER,
193 X_Err_Stage OUT NOCOPY VARCHAR2) IS
194 PRAGMA AUTONOMOUS_TRANSACTION; --bug 5705668, AP Autoselect process uses EBD_CHECK as where clause of a query, which raised error. Hence made this as Autonomous
195 l_module_name VARCHAR2(200) := g_module_name || 'INSERT_FV_DISCOUNTED_INVOICES';
196 Begin
197 BEGIN
198 INSERT INTO
199 FV_DISCOUNTED_INVOICES(Invoice_Id,
200 Last_Update_Date,
201 Last_Updated_By ,
202 Last_Update_Login,
203 Creation_Date ,
204 Created_By ,
205 Discount_Taken_Flag ,
206 Payment_Date ,
207 Effective_Discount_Percent,
208 CURR_VALUE_OF_FUNDS_PERCENT ,
209 Discount_Status_Code,
210 Request_Id,
211 Program_Application_Id,
212 Program_Id,
213 Program_Update_Date)
214 VALUES(X_Invoice_Id ,
215 SYSDATE,
216 FND_GLOBAL.USER_ID,
217 FND_GLOBAL.LOGIN_ID,
218 SYSDATE ,
219 FND_GLOBAL.USER_ID,
220 X_Discount_Taken_Flag ,
221 X_Payment_Date ,
222 X_Effective_Discount_Rate ,
223 X_CVOF_Rate,
224 X_Discount_Status_Code,
225 FND_GLOBAL.CONC_REQUEST_ID,
226 FND_GLOBAL.PROG_APPL_ID,
227 FND_GLOBAL.CONC_PROGRAM_ID,
228 SYSDATE );
229 COMMIT; --bug 5705668
230
231 X_Err_Num := 0;
232 EXCEPTION
233 WHEN DUP_VAL_ON_INDEX THEN
234 X_Err_Num := 2;
235 X_Err_Stage := 'Row already exists for the Invoice '||to_char(X_Invoice_Id)||'. Hence Insert failed';
236 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',X_Err_Stage);
237 WHEN OTHERS THEN
238 X_Err_Num := SQLCODE;
239 X_Err_Stage := 'Insert Failed '||SQLERRM;
240 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',X_Err_Stage);
241 END;
242 END INSERT_FV_DISCOUNTED_INVOICES;
243
244 /*-----------------------------------------------------------------*/
245 -- Version 1.2 Added Procedure INSERT_FV_ASSIGN_REASON_CODES RCW.
246 /*------------------------------------------------------------------*/
247 Procedure INSERT_FV_ASSIGN_REASON_CODES(X_Invoice_Id IN NUMBER,
248 x_Batch_Name IN VARCHAR2,
249 X_Err_Num OUT NOCOPY NUMBER,
250 X_Err_Stage OUT NOCOPY VARCHAR2) IS
251
252 PRAGMA AUTONOMOUS_TRANSACTION; --bug 5705668, AP Autoselect process uses EBD_CHECK as where clause of a query, which raised error. Hence made this as Autonomous
253 l_module_name VARCHAR2(200) := g_module_name || 'INSERT_FV_ASSIGN_REASON_CODES';
254 -- v_sob number; --global variable
255 -- v_sob_name VARCHAR2(50);
256 /*--------------------------------------------------*/
257 -- Version 1.4 RCW.
258 /*--------------------------------------------------*/
259 -- v_org_id number; --global variable
260 /*-- end 1.4 RCW --------------------------------*/
261
262 Begin
263 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
264 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Starting insert_fv_assign_reason_codes');
265 END IF;
266
267 /*--------------------------------------------------*/
268 -- Version 1.4 RCW.
269 /*--------------------------------------------------*/
270 --LA Uptake
271 -- v_org_id := to_number(fnd_profile.Value('ORG_ID'));
272 -- v_org_id := MO_GLOBAL.get_current_org_id; --global variable
273
274 /*--------------------------------------------------*/
275 -- Version 1.4 RCW.
276 /*-------------------------------------------------*/
277 --LA Uptake
278 -- v_sob := to_number(fnd_profile.Value('GL_SET_OF_BKS_ID'));
279 -- MO_UTILS.get_ledger_info(v_org_id,v_sob,v_sob_name); --global variable
280
281
282 /*-- end 1.4 RCW -------------------------------*/
283
284
285 BEGIN
286 INSERT INTO FV_ASSIGN_REASON_CODES(Invoice_Id,
287 Set_of_Books_Id,
288 /*--------------------------------------------------*/
289 -- Version 1.4 RCW.
290 /*--------------------------------------------------*/
291 Org_id,
292 /*-- end 1.4 RCW -------------------------------*/
293 Entry_Mode,
294 Entry_Source,
295 Checkrun_name,
296 Last_Update_Date,
297 Last_Updated_By,
298 Last_Update_Login,
299 Creation_Date,
300 Created_By)
301 VALUES(X_Invoice_Id ,
302 g_sob,
303 /*--------------------------------------------------*/
304 -- Version 1.4 RCW.
305 /*--------------------------------------------------*/
306 g_org_id,
307 /*-- end 1.4 RCW -------------------------------*/
308 'SYSTEM',
309 'EBD',
310 x_Batch_Name,
311 SYSDATE,
312 FND_GLOBAL.USER_ID,
313 FND_GLOBAL.LOGIN_ID,
314 SYSDATE,
315 FND_GLOBAL.USER_ID
316 );
317 COMMIT; --Bug 5705668
318 X_Err_Num := 0;
319 EXCEPTION
320 WHEN DUP_VAL_ON_INDEX THEN
321 X_Err_Num := 2;
322 X_Err_Stage := 'Row already exists for the Invoice '||to_char(X_Invoice_Id)||'.
323 Insert failed';
324 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',X_Err_Stage);
325 WHEN OTHERS THEN
326 X_Err_Num := SQLCODE;
327 X_Err_Stage := 'Insert Failed '||SQLERRM;
328 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception1',X_Err_Stage);
329 END;
330 EXCEPTION
331 WHEN OTHERS THEN
332 X_Err_Num := SQLCODE;
333 X_Err_Stage := SQLERRM;
334 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',X_Err_Stage);
335 END INSERT_FV_ASSIGN_REASON_CODES;
336
337 /*----------- end 1.2 RCW ----------------------------------------*/
338
339 Procedure DELETE_FV_DISCOUNTED_INVOICES(X_Invoice_Id IN NUMBER,
340 X_Err_Num OUT NOCOPY NUMBER,
341 X_Err_Stage OUT NOCOPY VARCHAR2) IS
342
343 PRAGMA AUTONOMOUS_TRANSACTION; --bug 5705668, AP Autoselect process uses EBD_CHECK as where clause of a query, which raised error. Hence made this as Autonomous
344 l_module_name VARCHAR2(200) := g_module_name || 'DELETE_FV_DISCOUNTED_INVOICES';
345 Begin
346 delete from FV_DISCOUNTED_INVOICES
347 where
348 invoice_id = X_Invoice_Id;
349 COMMIT; --Bug 5705668
350 X_Err_Num := 0;
351 If SQL%ROWCOUNT = 0 then
352 X_Err_Num := 1;
353 X_Err_Stage := 'There were no rows deleted from FV_DISCOUNTED_INVOICES for the Invoice '||to_char(X_Invoice_Id);
354 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',X_Err_Stage);
355 End If;
356 EXCEPTION
357 WHEN OTHERS THEN
358 X_Err_Num := SQLCODE;
359 X_Err_Stage := SQLERRM;
360 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',X_Err_Stage);
361 End DELETE_FV_DISCOUNTED_INVOICES;
362
363 /*-----------------------------------------------------------------------*/
364
365 Procedure UPDATE_FV_DISCOUNTED_INVOICES(X_Invoice_Id IN NUMBER,
366 X_Payment_Date IN DATE,
367 X_Err_Num OUT NOCOPY NUMBER,
368 X_Err_Stage OUT NOCOPY VARCHAR2) IS
369 PRAGMA AUTONOMOUS_TRANSACTION; --bug 5705668, AP Autoselect process uses EBD_CHECK as where clause of a query, which raised error. Hence made this as Autonomous
370
371 l_module_name VARCHAR2(200) := g_module_name || 'UPDATE_FV_DISCOUNTED_INVOICES';
372 Existing_Flag VARCHAR2(1);
373
374 Begin
375 select discount_taken_flag
376 into Existing_Flag
377 from FV_DISCOUNTED_INVOICES
378 where Invoice_Id = X_Invoice_Id;
379
380 If Existing_Flag = 'N' then
381 update FV_DISCOUNTED_INVOICES
382 set Payment_Date = X_Payment_Date,
383 Last_Update_Date = SYSDATE,
384 Last_Updated_By = FND_GLOBAL.USER_ID,
385 Last_Update_Login = FND_GLOBAL.LOGIN_ID
386 where Invoice_Id = X_Invoice_Id;
387
388 Elsif Existing_Flag = 'Y' then
389 update FV_DISCOUNTED_INVOICES
390 set Payment_Date = X_Payment_Date,
391 Discount_Taken_Flag = 'N',
392 Effective_Discount_Percent = NULL,
393 Curr_Value_Of_Funds_Percent = NULL,
394 Discount_Status_Code = 'PAYMENT_DATE_PAST',
395 Last_Update_Date = SYSDATE,
396 Last_Updated_By = FND_GLOBAL.USER_ID,
397 Last_Update_Login = FND_GLOBAL.LOGIN_ID
398 where Invoice_Id = X_Invoice_Id;
399
400
401 End If;
402 COMMIT; --bug 5705668
403 X_Err_Num := 0;
404 If SQL%NOTFOUND THEN
405 X_Err_Num := 1;
406 X_Err_Stage := 'There were no rows updated for the Invoice '||to_char(X_Invoice_Id);
407 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',X_Err_Stage);
408 End If;
409
410
411 EXCEPTION
412 WHEN OTHERS THEN
413 X_Err_Num := SQLCODE;
414 X_Err_Stage := SQLERRM;
415 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',X_Err_Stage);
416 End UPDATE_FV_DISCOUNTED_INVOICES;
417
418 /*-----------------------------------------------------------------*/
419 -- Version 1.2 Added Procedure UPDATE_FV_ASSIGN_REASON_CODES RCW.
420 /*------------------------------------------------------------------*/
421 Procedure UPDATE_FV_ASSIGN_REASON_CODES(X_Invoice_Id IN NUMBER,
422 x_Batch_Name IN VARCHAR2,
423 X_Err_Num OUT NOCOPY NUMBER,
424 X_Err_Stage OUT NOCOPY VARCHAR2) IS
425
426 PRAGMA AUTONOMOUS_TRANSACTION; --bug 5705668, AP Autoselect process uses EBD_CHECK as where clause of a query, which raised error. Hence made this as Autonomous
427
428 l_module_name VARCHAR2(200) := g_module_name || 'UPDATE_FV_ASSIGN_REASON_CODES';
429 Begin
430
431 update FV_ASSIGN_REASON_CODES
432 set Checkrun_name = x_Batch_Name,
433 Entry_mode = 'SYSTEM',
434 Last_Update_Date = SYSDATE,
435 Last_Updated_By = FND_GLOBAL.USER_ID,
436 Last_Update_Login = FND_GLOBAL.LOGIN_ID
437 where
438 Invoice_Id = X_Invoice_Id
439 and Entry_Source = 'EBD';
440
441 COMMIT; --Bug 5705668
442
443 X_Err_Num := 0;
444 If SQL%NOTFOUND THEN
445 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
446 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'1-No rows in fv_assign_reason_codes');
447 END IF;
448 X_Err_Num := 1;
449 X_Err_Stage := 'There were no rows updated for the Invoice '||to_char(X_Invoice_Id);
450 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',X_Err_Stage);
451 End If;
452
453
454 EXCEPTION
455 WHEN OTHERS THEN
456 X_Err_Num := SQLCODE;
457 X_Err_Stage := SQLERRM;
458 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',X_Err_Stage);
459 End UPDATE_FV_ASSIGN_REASON_CODES;
460 /*----------- end 1.2 RCW ----------------------------------------*/
461
462
463 /*--------------------------------------------------------------------*/
464 Procedure GET_CVOF_RATE(X_Payment_Date IN DATE,
465 X_CVOF_Rate IN OUT NOCOPY NUMBER,
466 X_Err_Num OUT NOCOPY NUMBER,
467 X_Err_Stage OUT NOCOPY VARCHAR2) IS
468 l_module_name VARCHAR2(200) := g_module_name || 'GET_CVOF_RATE';
469 X_Current_Value_Of_Funds_Rate NUMBER;
470 BEGIN
471 select CURR_VALUE_OF_FUNDS_PERCENT
472 into X_Current_Value_Of_Funds_Rate
473 from fv_value_of_fund_periods
474 where trunc(X_Payment_Date) between trunc(effective_start_date)
475 and trunc(nvl(effective_end_date, X_Payment_Date));
476
477 X_CVOF_Rate := X_Current_Value_Of_Funds_Rate;
478 X_Err_Num := 0;
479 EXCEPTION
480 WHEN NO_DATA_FOUND THEN
481 X_Err_Num := 1;
482 X_Err_Stage := 'No CVOF Rate available for the Payment Date '||to_char(X_Payment_Date);
483 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',X_Err_Stage);
484 WHEN OTHERS THEN
485 X_Err_Num := SQLCODE;
486 X_Err_Stage := SQLERRM;
487 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',X_Err_Stage);
488 END GET_CVOF_RATE;
489 /*-----------------------------------------------------------------*/
490 FUNCTION EBD_CHECK(x_batch_name IN VARCHAR2,
491 x_invoice_id IN NUMBER,
492 x_check_date IN DATE,
493 x_inv_due_date IN DATE,
494 x_discount_amount IN NUMBER,
495 x_discount_date IN DATE) RETURN CHAR AS
496
497 -- This function will return 'N' if the invoice should NOT be included in this
498 -- batch because it is not economically beneficial. Otherwise, 'Y' will be
499 -- returned to include the invoice.
500
501 l_module_name VARCHAR2(200) := g_module_name || 'EBD_CHECK';
502 l_discount_date DATE;
503 -- we are removing ap_payment_schedules from this join because this is called in
504 -- a update statement by AP where they are updating ap_payment_schedules,
505 -- this is causing some problems. See bug 4745133.
506 -- Instead AP will pass the discount_amount and discount_date parameters
507 -- and we will refrain from joining with ap_payment_schedules.
508
509 cursor c1 is
510 select
511 ai.invoice_amount,
512 ai.invoice_date,
513 ai.invoice_received_date,
514 ai.goods_received_date,
515 ai.org_id,
516 ai.set_of_books_id,
517 ai.terms_date,
518 --aps.discount_amount_available, --Now passed as parameter
519 --aps.discount_date, --Now passed as parameter
520 ftt.terms_type,
521 ai.terms_id -- Bug 11834795
522 from
523 FV_TERMS_TYPES ftt,
524 AP_INVOICES ai
525 -- AP_PAYMENT_SCHEDULES aps
526 where
527 ftt.term_id = ai.terms_id and
528 ai.invoice_id = x_invoice_id and
529 --aps.invoice_id = ai.invoice_id and
530 -- aps.discount_amount_available > 0;
531 x_discount_amount > 0;
532
533 err_message varchar2(5000);
534 /* Fetch Variables for Cursor c1 */
535 X_Invoice_Num AP_INVOICES_ALL.INVOICE_NUM%TYPE;
536 X_Invoice_Amount AP_INVOICES_ALL.INVOICE_AMOUNT%TYPE;
537 X_Invoice_Date AP_INVOICES_ALL.INVOICE_DATE%TYPE;
538 X_Invoice_Received_Date AP_INVOICES_ALL.INVOICE_RECEIVED_DATE%TYPE;
539 X_Goods_Received_Date AP_INVOICES_ALL.GOODS_RECEIVED_DATE%TYPE;
540 X_Terms_Date AP_INVOICES_ALL.TERMS_DATE%TYPE;
541 /*-------------Comments----------------------------------------------
542 X_Discount_Amount AP_PAYMENT_SCHEDULES.DISCOUNT_AMOUNT_AVAILABLE%TYPE;
543 X_Discount_Date AP_PAYMENT_SCHEDULES.DISCOUNT_DATE%TYPE;
544 --------------End of Comments---------------------------------------*/
545 x_Due_Date AP_SELECTED_INVOICES.DUE_DATE%TYPE;
546 -- Bug 11834795
547 X_Terms_Id AP_INVOICES.TERMS_ID%TYPE;
548 X_Payment_Date DATE;
549 X_Effective_Discount_Rate FV_DISCOUNTED_INVOICES.EFFECTIVE_DISCOUNT_PERCENT%TYPE;
550 X_CVOF_Rate FV_VALUE_OF_FUND_PERIODS.CURR_VALUE_OF_FUNDS_PERCENT%TYPE;
551 X_terms_type FV_TERMS_TYPES.TERMS_TYPE%TYPE;
552 X_Err_Nbr NUMBER ;
553 X_Err_Stage VARCHAR2(120);
554 --MOAC changes: Removed the org_id parameter in the call to FV_INSTALL.enabled
555 -- x_org_id number := to_number(fnd_profile.value('ORG_ID'));
556 v_fv_enabled BOOLEAN;
557 errbuf varchar2(1000);
558 retcode varchar2(2);
559 Begin
560
561 select invoice_num
562 into X_Invoice_Num
563 from ap_invoices_all
564 where invoice_id = X_Invoice_id;
565
566 --MOAC changes: Removed the org_id parameter in the call to FV_INSTALL.enabled
567 v_fv_enabled := fv_install.enabled; -- check if FV is enabled
568
569 IF v_fv_enabled then
570 -- FV is enabled continue with code
571
572 open c1;
573
574 -- get the org and set of books from invoice id instead of getting org from current org and then finding set
575 -- of books from that org
576
577 LOOP
578 FETCH c1 into
579 X_Invoice_Amount,
580 X_Invoice_Date,
581 X_Invoice_Received_Date,
582 X_Goods_Received_Date,
583 g_org_id,
584 g_sob,
585 X_Terms_Date,
586 -- X_Discount_Amount,
587 -- X_Discount_Date,
588 X_Terms_Type,
589 -- Bug 11834795
590 X_Terms_Id;
591 EXIT when c1%NOTFOUND;
592
593 /*Initialization of Variables */
594 X_Effective_Discount_Rate := 0;
595 X_CVOF_Rate := 0;
596
597 /* Assigning Check date to Payment Date */
598 X_Payment_Date := X_Check_Date;
599
600 /* Assigned Invoice due date to variable x_due_date */
601 x_due_date := trunc(x_inv_due_date);
602
603 IF PAYDT_BEFORE_DISCDT(X_Payment_Date,X_Discount_Date) then --2nd If
604
605 If ROW_EXISTS(X_Invoice_Id,X_Err_Nbr,X_Err_Stage) then -- 4th If
606
607 DELETE_FV_DISCOUNTED_INVOICES(X_Invoice_Id, X_Err_Nbr, X_Err_Stage);
608 RETCODE := to_char(X_Err_Nbr);
609 ERRBUF := X_Err_Stage;
610 Else
611 If X_Err_Nbr = 2 then
612 err_message := x_err_stage;
613 fnd_message.set_name('FV','FV_FAI_GENERAL');
614 fnd_message.set_token('msg',err_message);
615 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
616 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_ERROR, l_module_name);
617 END IF;
618 app_exception.raise_exception;
619 End If;
620 End If ; -- End of 4th If
621 --IN parameter x_discount_date cannot be used as a target of an assignment hence
622 --local variable l_discount_date passed to x_discount_date in CALCULATE_DISCOUNT
623 --which is defined as IN OUT parameter in CALCULATE_DISCOUNT.
624 l_discount_date:=x_discount_date;
625
626 -- Bug 11834795 : Pick discount % from ap_terms_lines table instead of calculating
627 BEGIN
628 SELECT discount_percent/100 into
629 Discount_Pct
630 FROM ap_terms_lines
631 WHERE term_id = x_terms_id;
632 EXCEPTION
633 WHEN OTHERS then
634 Discount_Pct := NULL;
635 END;
636
637
638 CALCULATE_DISCOUNT(X_Invoice_Id,
639 X_Discount_Amount,
640 X_Invoice_Amount,
641 X_Due_Date,
642 l_discount_date,
643 X_Terms_Date,
644 X_Invoice_Date,
645 X_Invoice_Received_Date,
646 X_Goods_Received_Date,
647 X_Effective_Discount_Rate,
648 X_Err_Nbr,
649 X_Err_Stage,
650 X_Payment_Date);
651 RETCODE := to_char(X_Err_Nbr);
652 ERRBUF := X_Err_Stage;
653 If ((X_Err_Nbr = 2) or (X_Err_Nbr < 0)) then
654 err_message := x_err_stage;
655 fnd_message.set_name('FV','FV_FAI_GENERAL');
656 fnd_message.set_token('msg',err_message);
657 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
658 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_ERROR, l_module_name);
659 END IF;
660 app_exception.raise_exception;
661 End If;
662
663 /* Get the CVOF Rate */
664 GET_CVOF_RATE(X_Payment_Date ,
665 X_CVOF_Rate,
666 X_Err_Nbr,
667 X_Err_Stage);
668 RETCODE := to_char(X_Err_Nbr);
669 ERRBUF := X_Err_Stage;
670 If X_Err_Nbr <> 0 then -- 5th If
671 /* CVOF Rate was unavailable for the given Date range*/
672 INSERT_FV_DISCOUNTED_INVOICES(X_Invoice_Id,
673 'Y',
674 'CVOF_RATE_UNAVAILABLE',
675 X_Payment_Date,
676 X_Effective_Discount_Rate,
677 X_CVOF_Rate,
678 X_Err_Nbr ,
679 X_Err_Stage);
680 RETCODE := to_char(X_Err_Nbr);
681 ERRBUF := X_Err_Stage;
682 If ((X_Err_Nbr = 2) or (X_Err_Nbr < 0)) then
683 err_message := x_err_stage;
684 fnd_message.set_name('FV','FV_FAI_GENERAL');
685 fnd_message.set_token('msg',err_message);
686 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
687 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_ERROR, l_module_name);
688 END IF;
689 app_exception.raise_exception;
690 End If;
691 Else
692 /* CVOF Rate Available */
693 /* Disc_Rate less than CVOF Rate */
694 If X_Effective_Discount_Rate <= X_CVOF_Rate then
695 /* Insert invoice into FV_DISCOUNTED_INVOICES */
696 INSERT_FV_DISCOUNTED_INVOICES(X_Invoice_Id,
697 'N',
698 'NOT_EBD',
699 X_Payment_Date,
700 X_Effective_Discount_Rate,
701 X_CVOF_Rate,
702 X_Err_Nbr ,
703 X_Err_Stage);
704 RETCODE := to_char(X_Err_Nbr);
705 ERRBUF := X_Err_Stage;
706 If ((X_Err_Nbr = 2) or (X_Err_Nbr < 0)) then
707 err_message := x_err_stage;
708 fnd_message.set_name('FV','FV_FAI_GENERAL');
709 fnd_message.set_token('msg',err_message);
710 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
711 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_ERROR, l_module_name);
712 END IF;
713 app_exception.raise_exception;
714 End If;
715
716 -- This invoice does not meet the EBD requirements so do NOT include
717 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, 'Invoice Number, '||x_invoice_num||', will NOT be included in the Payment Batch because it');
718 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, 'does not meet EBD Requirements.');
719 RETURN 'N';
720
721
722 Elsif X_Effective_Discount_Rate > X_CVOF_Rate then
723 /* Disc_Rate greater than CVOF Rate */
724 INSERT_FV_DISCOUNTED_INVOICES(X_Invoice_Id,
725 'Y',
726 NULL,
727 X_Payment_Date,
728 X_Effective_Discount_Rate,
729 X_CVOF_Rate,
730 X_Err_Nbr ,
731 X_Err_Stage);
732 RETCODE := to_char(X_Err_Nbr);
733 ERRBUF := X_Err_Stage;
734 If ((X_Err_Nbr = 2) or (X_Err_Nbr < 0)) then
735 err_message := x_err_stage;
736 fnd_message.set_name('FV','FV_FAI_GENERAL');
737 fnd_message.set_token('msg',err_message);
738 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
739 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_ERROR, l_module_name);
740 END IF;
741 app_exception.raise_exception;
742 End If;
743 End If;
744 End If; -- End of 5th If
745
746 Else
747 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, 'Payment Date is after Discount Date');
748
749 /* Payment Date after Discount Date */
750 If ROW_EXISTS(X_Invoice_Id,X_Err_Nbr,X_Err_Stage) then --3rd If
751 UPDATE_FV_DISCOUNTED_INVOICES(X_Invoice_Id,X_Payment_Date,
752 X_Err_Nbr,X_Err_Stage);
753 RETCODE := to_char(X_Err_Nbr);
754 ERRBUF := X_Err_Stage;
755
756 /*----------------------------------------------------------*/
757 -- Ver 1.2 Added Procedure UPDATE_FV_ASSIGN_REASON_CODES RCW.
758 /*-----------------------------------------------------------*/
759 IF fnd_profile.value('USE_DISCOUNT_LOST_REASON_CODES') = 'Y'
760 AND X_Terms_Type = 'PROMPT PAY' THEN
761 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
762 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Using Discount Lost Reason Codes');
763 END IF;
764 UPDATE_FV_ASSIGN_REASON_CODES(X_Invoice_Id,x_Batch_Name,
765 X_Err_Nbr,X_Err_Stage);
766 RETCODE := to_char(X_Err_Nbr);
767 ERRBUF := X_Err_Stage;
768 END IF;
769 /*----------- end 1.2 RCW -------------------------------*/
770
771 Else
772 IF X_Err_Nbr = 1 then -- 3A
773 INSERT_FV_DISCOUNTED_INVOICES(X_Invoice_Id,
774 'N',
775 'PAYMENT_DATE_PAST',
776 X_Payment_Date,
777 X_Effective_Discount_Rate,
778 X_CVOF_Rate,
779 X_Err_Nbr ,
780 X_Err_Stage);
781 RETCODE := to_char(X_Err_Nbr);
782 ERRBUF := X_Err_Stage;
783 If ((X_Err_Nbr = 2) or (X_Err_Nbr < 0)) then
784 err_message := x_err_stage;
785 fnd_message.set_name('FV','FV_FAI_GENERAL');
786 fnd_message.set_token('msg',err_message);
787 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
788 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_ERROR, l_module_name);
789 END IF;
790 app_exception.raise_exception;
791 End If;
792
793 /*---------------------------------------------------------*/
794 -- Ver 1.2 Added Procedure INSERT_FV_ASSIGN_REASON_CODES RCW
795 /*----------------------------------------------------------*/
796 IF fnd_profile.value('USE_DISCOUNT_LOST_REASON_CODES') = 'Y'
797 AND X_Terms_Type = 'PROMPT PAY' THEN
798 If ROW_EXISTS_FVRC(X_Invoice_Id,X_Err_Nbr,X_Err_Stage) then
799 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
800 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Record exists in FVRC');
801 END IF;
802
803 UPDATE_FV_ASSIGN_REASON_CODES(X_Invoice_Id,x_Batch_Name,
804 X_Err_Nbr,X_Err_Stage);
805 RETCODE := to_char(X_Err_Nbr);
806 ERRBUF := X_Err_Stage;
807
808 Else -- ROW DOESN'T EXIST
809 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
810 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'x_err_nbr = '||to_char(x_err_nbr));
811 END IF;
812 IF X_Err_Nbr = 1 then --no records found
813
814 INSERT_FV_ASSIGN_REASON_CODES(X_Invoice_Id,
815 x_Batch_Name,
816 X_Err_Nbr,
817 X_Err_Stage);
818 RETCODE := to_char(X_Err_Nbr);
819 ERRBUF := X_Err_Stage;
820 If ((X_Err_Nbr = 2) or (X_Err_Nbr < 0)) then
821 err_message := x_err_stage;
822 fnd_message.set_name('FV','FV_FAI_GENERAL');
823 fnd_message.set_token('msg',err_message);
824 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
825 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_ERROR, l_module_name);
826 END IF;
827 app_exception.raise_exception;
828 End If;
829 ELSIF X_Err_Nbr = 2 then --too many rows
830 err_message := x_err_stage;
831 fnd_message.set_name('FV','FV_FAI_GENERAL');
832 fnd_message.set_token('msg',err_message);
833 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
834 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_ERROR, l_module_name);
835 END IF;
836 app_exception.raise_exception;
837
838 END IF;
839 End If; --ROW_EXISTS_FVRC
840 END IF;
841
842 /*------- end 1.2 RCW --------------------------------*/
843 ELSIF X_Err_Nbr = 2 then
844 err_message := x_err_stage;
845 fnd_message.set_name('FV','FV_FAI_GENERAL');
846 fnd_message.set_token('msg',err_message);
847 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_ERROR, l_module_name);
849 END IF;
850 app_exception.raise_exception;
851
852 END IF; -- End of 3A
853 END IF; --End of 3rd IF
854 END IF ; -- End of 2nd If
855 END LOOP;
856
857 IF c1%NOTFOUND THEN
858 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, 'Invoice Number, '||x_invoice_num||', will be included in the Payment Batch because it');
859 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, 'meets EBD Requirements.');
860 RETURN 'Y';
861 END IF;
862
863 close c1;
864
865 ELSE
866 -- FV is not enabled
867 RETURN 'Y';
868 END IF;
869 EXCEPTION
870 WHEN OTHERS THEN
871 err_message := SQLERRM;
872 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',err_message);
873 RAISE;
874 END EBD_CHECK;
875 /*-----------------------------------------------------------------------*/
876 END FV_ECON_BENF_DISC_PVT;