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