[Home] [Help]
PACKAGE BODY: APPS.FV_DUE_DATE_CALCULATION
Source
1 PACKAGE BODY fv_Due_Date_Calculation AS
2 --$Header: FVXFODDB.pls 120.22.12010000.2 2008/08/04 11:43:24 gnrajago ship $
3 g_module_name VARCHAR2(100);
4 v_sob NUMBER;
5
6 /* Select records from ap_invoices which is not of type
7 interest */
8
9 CURSOR c1_main_select IS
10 SELECT api.invoice_id,
11 api.terms_date,
12 api.terms_id,
13 --aid.po_distribution_id,
14 --aid.rcv_transaction_id,
15 api.vendor_id,
16 api.invoice_num,
17 aps.discount_date,
18 aps.second_discount_date,
19 aps.third_discount_date,
20 api.goods_received_date,
21 api.invoice_date
22 FROM
23 ap_invoices api,
24 ap_terms apt,
25 ap_terms_lines apl,
26 fv_terms_types fvt,
27 --ap_invoice_distributions aid,
28 ap_payment_schedules aps
29 WHERE api.cancelled_amount IS NULL
30 AND api.invoice_type_lookup_code <> 'INTEREST'
31 AND ap_invoices_pkg.get_approval_status(
32 api.invoice_id,
33 api.invoice_amount,
34 api.payment_status_flag,
35 api.invoice_type_lookup_code)='APPROVED'
36 AND api.wfapproval_status IN ('NOT REQUIRED','MANUALLY APPROVED',
37 'WFAPPROVED')
38 AND api.set_of_books_id = v_sob
39 AND api.payment_status_flag <> 'Y'
40 AND NOT EXISTS (SELECT 'x' FROM
41 ap_holds aph
42 WHERE aph.invoice_id = api.invoice_id
43 AND aph.release_lookup_code IS NULL)
44 AND api.terms_id = apt.term_id
45 AND apt.term_id = fvt.term_id
46 AND APL.TERM_ID = APT.TERM_ID
47 AND NVL(apl.due_days,0) > 0
48 AND terms_type = 'PROMPT PAY'
49 --AND api.invoice_id = aid.invoice_id
50 --AND aid.match_status_flag = 'A'
51 AND 1 = ( SELECT COUNT(*)
52 FROM ap_payment_schedules aps2
53 WHERE aps2.invoice_id = api.invoice_id
54 AND checkrun_id IS NULL) -- modified for bug 5454497
55 AND 1 = ( SELECT COUNT(*)
56 FROM ap_terms_lines
57 WHERE term_id = apt.term_id)
58 AND api.invoice_id = aps.invoice_id
59 AND (NOT EXISTS ( SELECT 'x'
60 FROM fv_inv_selected_duedate fiv
61 WHERE fiv.invoice_id = api.invoice_id)
62 or aps.payment_status_flag <> 'Y'); -- added for bug 5454497;
63 -- transaction_type equals to ACCEPT, RECEIVE.
64 --LGOEL: Add shipment_header_id in the select list
65
66 CURSOR c2_accept (p_invoice_id NUMBER,
67 p_type VARCHAR2) IS
68 SELECT rcv.transaction_id, aid.rcv_transaction_id,
69 rcv.parent_transaction_id,
70 rcv.quantity,
71 rcv.transaction_type,
72 rcv.po_line_location_id,
73 rcv.transaction_date,
74 rcv.po_header_id,
75 rcv.shipment_header_id
76 --pol.quantity_billed
77 FROM ap_invoice_distributions aid,
78 rcv_transactions rcv,
79 po_line_locations pol,
80 po_distributions po
81 WHERE aid.invoice_id = p_invoice_id
82 AND aid.match_status_flag = 'A'
83 AND po.po_distribution_id = aid.po_distribution_id
84 AND po.line_location_id = rcv.po_line_location_id
85 AND po.line_location_id = pol.line_location_id
86 AND rcv.transaction_type = p_type
87 -- AND rcv.transaction_id = NVL(aid.rcv_transaction_id,rcv.transaction_id)
88 -- and not exists (select 'x' from ap_invoice_distributions aid
89 -- where aid.rcv_transaction_id = rcv.transaction_id)
90 ORDER BY rcv.transaction_type,
91 rcv.transaction_date,
92 rcv.po_line_location_id;
93
94 --LGOEL: Declaration of shipment_header_id
95 v_shipment_header_id rcv_transactions.shipment_header_id%TYPE;
96
97 v_po_header_id rcv_transactions.po_header_id%TYPE;
98
99 v_rcv_transaction_id rcv_transactions.transaction_id%TYPE;
100
101 --LGOEL: Declare cursor to fetch acceptance date for receipt matched invoices
102 --This was done to fix bug 1406383 as there can be multiple occurence of
103 --acceptance transaction type for same receipt
104 /* -- Commented out for bug 5454497
105 CURSOR c3_receipt_accept(p_po_header_id NUMBER) IS
106
107 SELECT DISTINCT transaction_id,transaction_date
108 FROM rcv_transactions
109 WHERE
110 --shipment_header_id=v_shipment_header_id
111 po_header_id=p_po_header_id
112 AND transaction_type='ACCEPT'
113 START WITH transaction_type='RECEIVE'
114 CONNECT BY parent_transaction_id = PRIOR transaction_id
115 ORDER BY transaction_date desc;
116 */
117 ---------------------------------------------------------------
118 PROCEDURE get_next_business_day
119 (
120 p_sob IN NUMBER,
121 p_date_out IN OUT NOCOPY DATE
122 ) IS
123 l_module_name VARCHAR2(200);
124 l_error_mesg VARCHAR2(1024);
125 l_date_found BOOLEAN := FALSE;
126 l_dummy VARCHAR2(1);
127 l_hol_day VARCHAR2(20);
128 BEGIN
129 l_module_name := g_module_name || 'get_next_business_day';
130
131 <<HOLIDAY_LOOP>>
132 LOOP --
133 BEGIN
134 SELECT 'x'
135 INTO l_dummy
136 FROM fv_holiday_dates
137 WHERE TRUNC(holiday_date) = TRUNC(p_date_out)
138 AND set_of_books_id = p_sob;
139
140 p_date_out := p_date_out + 1;
141 GOTO HOLIDAY_LOOP;
142 EXCEPTION
143 /* Check for week end */
144 WHEN NO_DATA_FOUND THEN
145 NULL;
146 END;
147
148 l_hol_day := TO_CHAR(p_date_out ,'day');
149 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
150 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'YES ' || l_hol_day);
151 END IF;
152
153 IF(SUBSTR(l_hol_day,1,8) = 'saturday') THEN
154 p_date_out := p_date_out + 2;
155 ELSIF(SUBSTR(l_hol_day,1,6) = 'sunday') THEN
156 p_date_out := p_date_out + 1;
157 ELSE
158 EXIT;
159 END IF;
160 END LOOP;
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 l_error_mesg := 'When others ' || SQLERRM;
165 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
166 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,l_error_mesg);
167 END IF;
168 RAISE;
169 END;
170
171 ---------------------------------------------------------------
172 PROCEDURE main (errbuf OUT NOCOPY VARCHAR2,
173 retcode OUT NOCOPY VARCHAR2,
174 x_run_mode IN VARCHAR2)
175 IS
176 CURSOR c2_parent_receive (p_po_header_id NUMBER)
177 IS
178 SELECT DISTINCT transaction_id, transaction_date
179 FROM rcv_transactions
180 WHERE
181 --shipment_header_id=p_shipment_header_id
182 po_header_id=p_po_header_id
183 AND transaction_type='RECEIVE'
184 START WITH transaction_type='ACCEPT'
185 CONNECT BY transaction_id = PRIOR parent_transaction_id
186 ORDER BY transaction_date desc;
187
188 -- CURSOR c1_main_select selects a set of INVOICE_ID records from
189 -- AP_SELECTED_INVOICES table. The variable v_invoice_id is to be
190 -- used in CURSOR c2_accept.
191 v_invoice_id ap_selected_invoices.invoice_id%TYPE;
192 v_invoice_num ap_selected_invoices.invoice_num%TYPE;
193 v_terms_id ap_invoices.terms_id%TYPE;
194 v_terms_date ap_invoices.terms_date%TYPE;
195 v_terms_type fv_terms_types.terms_type%TYPE;
196 v_po_distribution_id ap_invoice_distributions.po_distribution_id%TYPE;
197 v_transaction_type rcv_transactions.transaction_type%TYPE;
198 v_po_line_location_id rcv_transactions.po_line_location_id%TYPE;
199 v_transaction_date rcv_transactions.transaction_date%TYPE;
200 v_transaction_id rcv_transactions.transaction_id%TYPE;
201 v_quantity rcv_transactions.quantity%TYPE;
202 v_quantity_billed po_line_locations.quantity_billed%TYPE;
203 v_due_days ap_terms_lines.due_days%TYPE;
204 --v_receipt_acceptance_days
205 --financials_system_parameters.receipt_acceptance_days%TYPE;
206 v_vendor_id NUMBER;
207 v_pay_thru_date ap_invoice_selection_criteria.pay_thru_date%TYPE;
208 v_correct_quantity NUMBER;
209 v_total_quantity NUMBER;
210 v_total_due_date DATE;
211 x_err_code NUMBER;
212 x_err_stage VARCHAR2(255);
213 v_sob_name VARCHAR2(30);
214 v_org_due_date DATE;
215 v_due_date_flag VARCHAR2(2);
216 v_disc_date_flag VARCHAR2(2);
217 v_discount_date DATE;
218 v_new_discount_date DATE;
219 v_rec_transaction_date DATE;
220 v_invoice_date DATE;
221 v_invoice_return_days NUMBER;
222 v_parent_transaction_id rcv_transactions.transaction_id%TYPE;
223 v_diff_days NUMBER;
224 v_con_acc_days NUMBER;
225 v_discount_days NUMBER;
226 v_dummy VARCHAR2(2);
227 v_tot_inv_retn NUMBER;
228 v_last_transaction_type rcv_transactions.transaction_type%TYPE;
229 v_type rcv_transactions.transaction_type%TYPE;
230 v_transaction_id_org rcv_transactions.transaction_id%TYPE;
231 v_parent_transaction_id_org rcv_transactions.transaction_id%TYPE;
232 v_transaction_type_org rcv_transactions.transaction_type%TYPE;
233 v_rec_txn_type rcv_transactions.transaction_type%TYPE;
234 v_transaction_date_org DATE;
235 v_final_shipment_header_id rcv_transactions.shipment_header_id%TYPE;
236 v_final_transaction_date rcv_transactions.transaction_date%TYPE;
237 v_hol_day VARCHAR2(10);
238 v_user NUMBER;
239 v_rec_trxn_flag VARCHAR2(1);
240 v_exists VARCHAR2(1);
241 v_exists_due_date date;
242 v_exists_1_dis_date date;
243 v_exists_2_dis_date date;
244 v_exists_3_dis_date date;
245 l_module_name VARCHAR2(200);
246 l_save_date DATE;
247 v_second_disc_date ap_payment_schedules.second_discount_date%TYPE;
248 v_third_disc_date ap_payment_schedules.third_discount_date%TYPE;
249 v_discount_days_2 ap_terms_lines.discount_days_2%TYPE;
250 v_discount_days_3 ap_terms_lines.discount_days_3%TYPE;
251 v_new_second_disc_date date;
252 v_new_third_disc_date date;
253 v_2_disc_date_flag varchar2(1);
254 v_3_disc_date_flag varchar2(1);
255 v_goods_rec_date ap_invoices.goods_received_date%TYPE;
256 cnt NUMBER ;
257
258 v_rcv_trans_count NUMBER;
259 TYPE v_ref_cur IS REF CURSOR ;
260 c3_receipt_accept v_ref_cur ;
261 l_statement VARCHAR2(2000);
262 l_operating_unit NUMBER ;
263 l_ledger_name GL_LEDGERS.Name%TYPE ;
264 v_req_id NUMBER ;
265
266 BEGIN /* Procedure Due_Date_Calculation */
267 -- initialize variables
268 l_module_name := g_module_name || 'main';
269 BEGIN
270 -- Delete from Temp table by Org_id
271 l_operating_unit := MO_GLOBAL.get_current_org_id ;
272 MO_UTILS.get_ledger_info
273 (
274 p_operating_unit => l_operating_unit ,
275 p_ledger_id => v_sob ,
276 p_ledger_name => l_ledger_name
277 );
278 v_sob_name := to_char ( MO_GLOBAL.get_current_org_id );
279 v_user := TO_NUMBER(fnd_profile.value('USER_ID'));
280
281 -- bug 2088857 fix, added nvl around set_of_bks_name
282 -- since this holds org_id value which will be null in non-multiorg
283
284 DELETE FROM fv_inv_selected_duedate_temp
285 WHERE NVL(set_of_bks_name,-99) = NVL(v_sob_name,-99);
286 COMMIT;
287 EXCEPTION
288 WHEN NO_DATA_FOUND THEN
289 NULL;
290 END;
291 /*=========================================================
292 Fetch records from a set of records with transaction_type equals
293 to ACCEPT,RECEIVE
294 ========================================================= */
295 OPEN c1_main_select;
296 LOOP /* C1_main_select */
297 <<INVOICE_FETCH>>
298 FETCH c1_main_select
299 INTO v_invoice_id,
300 v_terms_date,
301 v_terms_id,
302 --v_po_distribution_id,
303 --v_rcv_transaction_id,
304 v_vendor_id,
305 v_invoice_num,
306 v_discount_date,
307 v_second_disc_date,
308 v_third_disc_date,
309 v_goods_rec_date,
310 v_invoice_date;
311 EXIT WHEN c1_main_select%NOTFOUND;
312 /* Get the original Due date from ap_payment_schedules */
313
314 --fnd_file.put_line(FND_FILE.LOG,'v_goods_rec_date = '||v_goods_rec_date);
315 SELECT due_date
316 INTO v_org_due_date
317 FROM ap_payment_schedules
318 WHERE invoice_id = v_invoice_id;
319
320 v_total_due_date := v_org_due_date;
321 v_total_quantity := 0;
322 v_due_date_flag := 'N';
323 v_disc_date_flag := 'N';
324 v_2_disc_date_flag := 'N';
325 v_3_disc_date_flag := 'N';
326 v_new_discount_date := v_discount_date;
327 v_new_second_disc_date := v_second_disc_date;
328 v_new_third_disc_date := v_third_disc_date;
329
330 /* Fetching Due days and receipt acceptance days,discount_days */
331 x_err_code := 1;
332 SELECT due_days,
333 discount_days,
334 discount_days_2,
335 discount_days_3
336 INTO v_due_days,
337 v_discount_days,
338 v_discount_days_2,
339 v_discount_days_3
340 FROM AP_TERMS_LINES
341 WHERE term_id = v_terms_id;
342
343
344 FND_FILE.PUT_LINE(FND_FILE.LOG,'Due days are '|| v_due_days);
345
346 ---------------------------------------------------------------
347 -- derive the original dates ,discount date ,if the
348 -- invoice been already picked up
349 ------------------------------------------------------------
350
351 begin
352 v_exists_due_date := null;
353 v_exists_1_dis_date := null;
354 v_exists_2_dis_date := null;
355 v_exists_3_dis_date := null;
356 SELECT new_due_date, new_DISCOUNT_DATE,NEW_SECOND_DISC_DATE,NEW_THIRD_DISC_DATE
357 into v_exists_due_date , v_exists_1_dis_date,v_exists_2_dis_date,v_exists_3_dis_date
358 FROM fv_inv_selected_duedate fiv
359 WHERE fiv.invoice_id = v_invoice_id;
360
361 v_total_due_date := v_invoice_date + v_due_days;
362 if v_discount_days is not null then
363 v_new_discount_date := v_invoice_date + v_discount_days;
364 End if;
365 if v_discount_days_2 is not null then
366 v_new_second_disc_date := v_invoice_date + v_discount_days_2;
367 End if;
368 if v_discount_days_3 is not null then
369 v_new_third_disc_date := v_invoice_date + v_discount_days_3;
370 End if;
371 exception
372 when no_data_found then
373 null;
374 End;
375 ---------------------------------------------------------------------
376
377
378
379 /*
380 x_err_code := 2;
381 SELECT NVL(receipt_acceptance_days,0)
382 INTO v_receipt_acceptance_days
383 FROM AP_SYSTEM_PARAMETERS
384 WHERE set_of_books_id = v_sob;
385 */
386
387 x_err_code := 3;
388 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Receipt acc days are :' ||v_receipt_acceptance_days);
389 SELECT constructive_acceptance_days,
390 invoice_return_days
391 INTO v_con_acc_days,
392 v_invoice_return_days
393 FROM fv_terms_types
394 WHERE term_id = v_terms_id;
395
396 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cons acceptance days are :'||v_con_acc_days);
400 SELECT SUM(quantity_invoiced) INTO v_quantity_billed
397 /* Calculating due date for invoice which has po distribution */
398 v_rec_trxn_flag := 'N';
399
401 FROM ap_invoice_distributions
402 WHERE invoice_id = v_invoice_id;
403
404 FND_FILE.PUT_LINE(FND_FILE.LOG,'Quantity Invoiced is: ' || v_quantity_billed);
405
406 -- if(v_po_distribution_id is not null) then
407 v_transaction_date := null;
408 v_shipment_header_id := null;
409 cnt := 0;
410
411 FOR type_id IN 1..2
412 LOOP
413 v_total_quantity := 0;
414 IF type_id =1 THEN
415 v_type := 'ACCEPT';
416 ELSE
417 v_type := 'RECEIVE';
418 END IF;
419
420 x_err_code := 4;
421
422 OPEN c2_accept (v_invoice_id,v_type);
423 v_last_transaction_type:=v_type;
424
425 LOOP /* C2_ACCEPT */
426 FETCH c2_accept INTO
427 v_transaction_id_org,
428 v_rcv_transaction_id,
429 v_parent_transaction_id_org,
430 v_quantity,
431 v_transaction_type_org,
432 v_po_line_location_id,
433 v_transaction_date_org,
434 v_po_header_id,
435 v_shipment_header_id;
436 --v_quantity_billed;
437 IF c2_accept%NOTFOUND THEN
438 v_transaction_id_org:= null;
439 v_rcv_transaction_id:= null;
440 v_parent_transaction_id_org:= null;
441 v_quantity:= 0 ;
442 v_transaction_type_org:= null;
443 v_po_line_location_id:= null;
444 v_transaction_date_org:= null;
445 v_shipment_header_id:= null;
446 v_po_header_id :=null;
447 v_transaction_id := null;
448 v_parent_transaction_id := null;
449 v_transaction_type := null;
450 v_transaction_date := null;
451 v_correct_quantity := 0;
452 v_final_transaction_date :=null;
453 v_final_shipment_header_id:=null;
454
455 EXIT WHEN c2_accept%NOTFOUND;
456 END IF;
457 v_rec_trxn_flag := 'Y';
458
459 v_transaction_id := v_transaction_id_org;
460 v_parent_transaction_id := v_parent_transaction_id_org;
461 v_transaction_type := v_transaction_type_org;
462 v_transaction_date := v_transaction_date_org;
463
464 /* Checking for new transaction_date from closed period */
465 BEGIN
466 SELECT actual_transaction_date
467 INTO v_transaction_date
468 FROM fv_rcv_transactions
469 WHERE transaction_id = v_transaction_id_org;
470 EXCEPTION
471 WHEN NO_DATA_FOUND THEN
472 v_transaction_date := v_transaction_date_org;
473 END;
474
475 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
476 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ID :' || V_TRANSACTION_ID ||
477 ' Type : ' || v_transaction_type
478 || ' Qty ' || TO_CHAR(v_quantity) || ' Billed ' ||
479 TO_CHAR(v_quantity_billed) );
480 END IF;
481 /*FND_FILE.PUT_LINE(FND_FILE.LOG, 'Id :' || v_transaction_id ||
482 ' Type : ' || v_transaction_type
483 || ' Qty ' || to_char(v_quantity) || ' Billed ' ||
484 to_char(v_quantity_billed) ); */
485 -- summing the corrected qty for transaction type RECEIVE
486 -- AND RETURN TO VENDOR Return to vendor qty sign is
487 -- flipped to add instead of subtracting
488 SELECT NVL(SUM(DECODE(transaction_type ,
489 'CORRECT',quantity, quantity * -1)),0)
490 INTO v_correct_quantity
491 FROM rcv_transactions
492 WHERE transaction_type IN ('CORRECT','RETURN TO VENDOR')
493 AND parent_transaction_id = v_transaction_id;
494
495 /* summing total quandity */
496 v_total_quantity := v_quantity + v_correct_quantity +
497 v_total_quantity;
498 /* Total Qty accepted is greater than equal to the billed qty */
499 v_last_transaction_type := v_transaction_type;
500
501 -- IF(v_total_quantity > v_quantity_billed) THEN
502 -- EXIT; /* Because Billed qty = invoiced qty */
503 -- END IF;
504
505 if cnt = 0 then
506 v_final_transaction_date := v_transaction_date;
507 v_final_shipment_header_id := v_shipment_header_id;
508 elsif cnt>0 then
509 if v_transaction_date < v_final_transaction_date then
510 v_transaction_date := v_final_transaction_date;
511 v_shipment_header_id := v_final_shipment_header_id;
512 end if;
513 end if;
514 v_final_transaction_date := v_transaction_date;
515 v_final_shipment_header_id := v_shipment_header_id;
516 cnt := cnt +1;
517
518 IF(v_total_quantity >= v_quantity_billed) THEN
519 EXIT; /* Because Billed qty = invoiced qty */
520 END IF;
521
522
523 END LOOP; /* C2_ACCEPT */
524 CLOSE C2_ACCEPT;
525
526 IF(v_total_quantity >= v_quantity_billed) THEN
527 EXIT; /* Because Billed qty = invoiced qty */
528 END IF;
529 END LOOP ; /* For accept loop */
530
531
535 IF v_rec_trxn_flag = 'N' THEN
532 IF(v_rec_trxn_flag = 'Y') or (v_rec_trxn_flag = 'N' and
533 v_goods_rec_date is not null) THEN
534
536
537 -- this is for customers who are using the goods_received_date instead
538 -- of receiving features in payables to capture the receive date.
539 -- bug fix 2178745
540 v_transaction_date := v_goods_rec_date;
541 -- fnd_file.put_line(FND_FILE.LOG,'v_transaction_date = '||v_transaction_date);
542
543 ELSE
544
545 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
546 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRANSACTION DATE ' ||
547 TO_CHAR(v_transaction_date));
548 END IF;
549 /*FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction date ' ||
550 to_char(v_transaction_date)); */
551 /* selecting latest Qty recevied date using
552 parent transaction */
553 IF(v_con_acc_days IS NOT NULL )
554 AND (v_transaction_type = 'ACCEPT') THEN
555
556 --LGOEL: Change where condition for fetching receipt date in 11i
557 --Cannot use parent_transaction_id directly because that may return the
558 --'TRANSFER' transaction type
559 --Fix bug 1425906
560 OPEN c2_parent_receive(v_po_header_id) ;
561 FETCH c2_parent_receive INTO v_parent_transaction_id, v_rec_transaction_date ;
562 CLOSE c2_parent_receive ;
563 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
564 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECEIPT DATE IS '||TO_CHAR(V_REC_TRANSACTION_DATE));
565 END IF;
566 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Receipt date is '||to_char(v_rec_transaction_date));
567
568 /* Read new transaction date,if any defined on */
569 BEGIN
570 SELECT actual_transaction_date
571 INTO v_rec_transaction_date
572 FROM fv_rcv_transactions
573 WHERE transaction_id = v_parent_transaction_id;
574 EXCEPTION
575 WHEN NO_DATA_FOUND THEN
576 NULL;
577 END;
578
579 /* adding constractive acceptence days */
580 v_rec_transaction_date := v_rec_transaction_date
581 + v_con_acc_days;
582 IF (v_rec_transaction_date < v_transaction_date) THEN
583 v_transaction_date := v_rec_transaction_date;
584 END IF;
585 END IF;
586 --LGOEL: For Receipt matching in 11i, fetch the first
587 --occurence of the acceptance date
588
589 IF (v_rcv_transaction_id IS NOT NULL) AND
590 (v_transaction_type = 'RECEIVE') THEN
591
592 v_rec_transaction_date := v_transaction_date;
593
594 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
595 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DOING RECEIPT MATCHING. ' ||
596 'Trans. Id ' || TO_CHAR(v_rcv_transaction_id)|| ' Rec. date '
597 || TO_CHAR(v_transaction_date,'DD-MON-YYYY'));
598 END IF;
599 -- Modified for bug 5454497
600 v_rcv_trans_count := 0;
601
602 SELECT count(*)
603 INTO v_rcv_trans_count
604 FROM rcv_transactions acc, rcv_transactions rec
605 WHERE acc.po_header_id = v_po_header_id
606 AND acc.transaction_type='ACCEPT'
607 AND acc.parent_transaction_id = rec.transaction_id
608 AND rec.transaction_type <> 'RECEIVE';
609
610 IF v_rcv_trans_count > 0 THEN
611
612 l_statement := ' SELECT DISTINCT transaction_id,transaction_date
613 FROM rcv_transactions
614 WHERE po_header_id = :v_po_header_id
615 AND transaction_type = ''ACCEPT''
616 START WITH transaction_type =''RECEIVE''
617 CONNECT BY parent_transaction_id = PRIOR transaction_id
618 ORDER BY transaction_date DESC ';
619 ELSE
620
621 l_statement := ' SELECT DISTINCT acc.transaction_id,acc.transaction_date
622 FROM rcv_transactions rec, rcv_transactions acc
623 WHERE rec.po_header_id = :v_po_header_id
624 AND rec.transaction_type = ''RECEIVE''
625 AND rec.transaction_id = acc.parent_transaction_id
626 AND acc.transaction_type = ''ACCEPT''
627 ORDER BY acc.transaction_date DESC ';
628 END IF;
629
630 OPEN c3_receipt_accept FOR l_statement using v_po_header_id;
631
632 --OPEN c3_receipt_accept(v_po_header_id);
633 -- End modification for bug 5454497
634
635
636
637 cnt := 0;
638 LOOP
639
640 FETCH c3_receipt_accept
641 INTO v_transaction_id, v_transaction_date;
642
643 EXIT WHEN c3_receipt_accept%NOTFOUND OR
644 c3_receipt_accept%NOTFOUND IS NULL;
645
646 /* Read new transaction date,if any defined on */
647 BEGIN
651 WHERE transaction_id = v_transaction_id;
648 SELECT actual_transaction_date
649 INTO v_transaction_date
650 FROM fv_rcv_transactions
652 EXCEPTION
653 WHEN NO_DATA_FOUND THEN
654 NULL;
655 END;
656 if cnt = 0 then
657 v_final_transaction_date := v_transaction_date;
658 elsif cnt >0 then
659 if v_transaction_date < v_final_transaction_date then
660 v_transaction_date := v_final_transaction_date;
661 end if;
662 end if;
663 v_final_transaction_date := v_transaction_date;
664
665 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
666 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FETCHED ACCEPTANCE DATE IS '
667 || TO_CHAR(v_transaction_date,'DD-MON-YYYY'));
668 END IF;
669 /*FND_FILE.PUT_LINE(FND_FILE.LOG,'Fetched Acceptance Date is '
670 || to_char(v_transaction_date,'DD-MON-YYYY'));*/
671
672 /* adding constructive acceptence days */
673 IF (v_con_acc_days IS NOT NULL ) THEN
674 v_rec_transaction_date := v_rec_transaction_date
675 + v_con_acc_days;
676 IF (v_rec_transaction_date < v_transaction_date) THEN
677 v_transaction_date := v_rec_transaction_date;
678 END IF;
679 END IF;
680 cnt := cnt + 1;
681 EXIT;
682
683 END LOOP; /*c3_receipt_accept*/
684
685 CLOSE c3_receipt_accept;
686
687 END IF; --rcv_transaction_id /*LGOEL*/
688 END IF; -- v_rec_trxn_flag
689
690 /* Comparing the transaction date against the due date */
691 IF v_transaction_date > v_terms_date THEN
692 v_total_due_date := v_transaction_date +
693 v_due_days;-- + v_receipt_acceptance_days;
694 v_due_date_flag := 'Y';
695 ELSE
696
697 /* Reset the original duedate to check wether due date falls
698 on any week end or Holiday */
699 v_total_due_date := v_ORG_DUE_DATE;
700 END IF;
701
702 END IF ; /* v_rex_trxn_flag */
703 -- End if; /* Po distribution is null */
704 IF(v_discount_date IS NOT NULL) THEN
705 IF(v_invoice_date IS NOT NULL) THEN
706 v_new_discount_date := v_invoice_date + NVL(v_discount_days,0);
707 -- + NVL(v_receipt_acceptance_days,0);
708 v_disc_date_flag := 'Y';
709
710 IF (v_second_disc_date is not null
711 and v_discount_days_2 is not null) THEN
712 v_new_second_disc_date := v_invoice_date + NVL(v_discount_days_2,0);
713 -- + NVL(v_receipt_acceptance_days,0);
714 v_2_disc_date_flag := 'Y';
715 END IF;
716 IF (v_third_disc_date is not null
717 and v_discount_days_3 is not null) THEN
718 v_new_third_disc_date :=
719 v_invoice_date + NVL(v_discount_days_3,0);
720 -- + NVL(v_receipt_acceptance_days,0);
721 v_3_disc_date_flag := 'Y';
722 END IF;
723
724 END IF;
725 END IF;
726
727 v_diff_days := 0;
728
729 SELECT COUNT(*)
730 INTO v_tot_inv_retn
731 FROM fv_invoice_returns
732 WHERE invoice_id = v_invoice_id;
733
734 IF(v_tot_inv_retn > 0) THEN
735 /* Check Invoice return days is null */
736
737 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
738 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CHECKING INV RETURN DAYS NOT NULL');
739 END IF;
740
741 --FND_FILE.put_line(FND_FILE.LOG,'checking inv return days not null');
742 IF(v_invoice_return_days IS NOT NULL ) THEN
743 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
744 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'YES IT IS NOT NULL');
745 END IF;
746 SELECT COUNT(*)
747 INTO v_tot_inv_retn
748 FROM fv_invoice_return_dates
749 WHERE invoice_returned_date > (original_invoice_received_date
750 + v_invoice_return_days)
751 AND invoice_id = v_invoice_id;
752 IF(v_tot_inv_retn > 0) THEN /* Total No of Invoice returned */
753
754 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
755 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'YES FOUND SOME INVOICE
756 returned later THAN the stipulated TIME');
757 END IF;
758
759 SELECT SUM(invoice_returned_date -
760 original_invoice_received_date -
761 v_invoice_return_days)
762 INTO v_diff_days
763 FROM fv_invoice_return_dates
764 WHERE (invoice_returned_date -
765 original_invoice_received_date)
766 > v_invoice_return_days
767 AND invoice_id = v_invoice_id;
768
769 v_due_date_flag := 'Y';
770
771 -- Added new line here
772 v_total_due_date := v_total_due_date - v_diff_days;
773 ELSE
774
775 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
776 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BUT DO NOT FIND ANY INVOICE
777 RETURN later THAN the stipulated TIME');
778 END IF;
779 v_due_date_flag := 'N';
780 END IF; /* Total No of Invoice returned */
781 END IF; /* invoice return days is not null */
782 END IF;
783
784 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
785 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DUE DATE UPDATE FLAG ' || V_DUE_DATE_FLAG);
786 END IF;
787
788 --FND_FILE.PUT_LINE(FND_FILE.LOG,'due date update flag ' || v_due_date_flag);
789
790 ---------------------------------------------------------
791 /* check whether due_date falls on any weekend or holiday */
792
793 l_save_date := v_total_due_date;
794 get_next_business_day
795 (
796 p_sob => v_sob,
797 p_date_out => v_total_due_date
798 );
799
800 IF (v_total_due_date <> l_save_date) THEN
801 v_due_date_flag := 'Y';
802 END IF;
803
804 l_save_date := v_new_discount_date;
805 get_next_business_day
806 (
807 p_sob => v_sob,
808 p_date_out => v_new_discount_date
809 );
810
811 IF (v_new_discount_date <> l_save_date) THEN
812 v_disc_date_flag := 'Y';
813 END IF;
814
815 IF (v_new_second_disc_date is not null) THEN
816 l_save_date := v_new_second_disc_date;
817 get_next_business_day
818 (
819 p_sob => v_sob,
820 p_date_out => v_new_second_disc_date
821 );
822
823 IF (v_new_second_disc_date <> l_save_date) THEN
824 v_2_disc_date_flag := 'Y';
825 END IF;
826 END IF;
827
828 IF (v_new_third_disc_date is not null) THEN
829 l_save_date := v_new_third_disc_date;
830 get_next_business_day
831 (
832 p_sob => v_sob,
833 p_date_out => v_new_third_disc_date
834 );
835
836 IF (v_new_third_disc_date <> l_save_date) THEN
837 v_3_disc_date_flag := 'Y';
838 END IF;
839 END IF;
840
841 ----------------------------------------------------------
842 IF (x_run_mode = 'F') THEN
843 BEGIN
844
845 x_err_code := 5;
846 INSERT INTO fv_inv_selected_duedate
847 (INVOICE_ID,
848 INVOICE_NUM,
849 TERMS_DATE ,
850 ORG_DUE_DATE,
851 NEW_DUE_DATE,
852 VENDOR_ID,
853 SET_OF_BKS_NAME,
854 ORG_DISCOUNT_DATE,
855 NEW_DISCOUNT_DATE,
856 ORG_second_disc_date,
857 new_second_disc_date,
858 org_third_disc_date,
859 new_third_disc_date,
860 transaction_id,
861 transaction_date,
862 po_distribution_id,
863 created_by,
864 creation_date,
865 last_update_date,
866 last_updated_by,
867 set_of_books_id)
868 VALUES
869 (v_invoice_id,
870 v_invoice_num,
871 v_terms_date,
872 v_ORG_DUE_DATE,
873 DECODE(v_due_date_flag,'Y',v_total_due_date,NULL),
874 v_vendor_id,
875 v_sob_name,
876 DECODE(v_disc_date_flag,'Y',v_DISCOUNT_DATE,NULL),
880 DECODE(v_3_disc_date_flag,'Y',v_third_DISC_DATE,NULL),
877 DECODE(v_disc_date_flag,'Y',v_NEW_DISCOUNT_DATE,NULL),
878 DECODE(v_2_disc_date_flag,'Y',v_second_DISC_DATE,NULL),
879 DECODE(v_2_disc_date_flag,'Y',v_NEW_second_DISC_DATE,NULL),
881 DECODE(v_3_disc_date_flag,'Y',v_NEW_third_DISC_DATE,NULL),
882 v_transaction_id,
883 v_transaction_date,
884 v_po_distribution_id,
885 fnd_global.user_id,
886 SYSDATE,
887 SYSDATE,
888 fnd_global.user_id ,
889 v_sob);
890 EXCEPTION
891 WHEN DUP_VAL_ON_INDEX THEN
892 /* If invoice exist update the duedate,this will happen
893 if an invoice have more then one distributions */
894
895 UPDATE fv_inv_selected_duedate
896 SET NEW_DUE_DATE = DECODE(v_due_date_flag,'Y',
897 v_total_due_date,NULL)
898 WHERE invoice_id = v_invoice_id;
899 END ;
900 END IF; /* run mode = final */
901
902 IF( v_due_date_flag = 'Y' OR v_disc_date_flag = 'Y') THEN
903 IF (x_run_mode = 'F') THEN
904 x_err_code := 6;
905
906 UPDATE ap_payment_schedules
907 SET due_date = DECODE(v_due_date_flag,'Y', v_total_due_date,
908 due_date), discount_date = DECODE(v_disc_date_flag,
909 'Y',v_new_discount_date,discount_date),
910 second_discount_date = decode(v_2_disc_date_flag,'Y',
911 v_new_second_disc_date,second_discount_date),
912 third_discount_date = decode(v_3_disc_date_flag,'Y',
913 v_new_third_disc_date,third_discount_date),
914 last_update_login = fnd_global.login_id,
915 last_updated_by = fnd_global.user_id,
916 last_update_date = SYSDATE
917 WHERE invoice_id = v_invoice_id;
918
919 END IF; /* run mode = final */
920 -------------------------------------------------------------
921 -- following lined added to aovid showing the same date as new
922 -- PPA date for invoices that are picked to run in subsequent run
923 -- if there is no change
924 ----------------------------------------------------------------
925 if trunc(v_exists_due_date) = trunc(v_total_due_date) then
926 v_total_due_date := null;
927 End if;
928 if trunc(v_exists_1_dis_date) = trunc(v_new_discount_date) then
929 v_new_discount_date := null;
930 End if;
931 if trunc(v_exists_2_dis_date) = trunc(v_new_second_disc_date) then
932 v_second_disc_date := null;
933 End if;
934 if trunc(v_exists_3_dis_date) = trunc(v_new_third_disc_date) then
935 v_new_third_disc_date := null;
936 End if;
937 --------------------------------------------------------------
938
939 BEGIN
940 INSERT INTO fv_inv_selected_duedate_temp
941 (INVOICE_ID,
942 INVOICE_NUM,
943 TERMS_DATE ,
944 ORG_DUE_DATE,
945 NEW_DUE_DATE,
946 VENDOR_ID,
947 SET_OF_BKS_NAME,
948 ORG_DISCOUNT_DATE,
949 NEW_DISCOUNT_DATE,
950 ORG_second_disc_date,
951 new_second_disc_date,
952 org_third_disc_date,
953 new_third_disc_date,
954 SET_OF_BOOKS_ID)
955 VALUES
956 (v_invoice_id,
957 v_invoice_num,
958 v_terms_date,
959 v_ORG_DUE_DATE,
960 v_total_due_date,
961 v_vendor_id,
962 v_sob_name,
963 v_DISCOUNT_DATE,
964 v_NEW_DISCOUNT_DATE,
965 v_second_DISC_DATE,
966 v_NEW_second_DISC_DATE,
967 v_third_DISC_DATE,
968 v_NEW_third_DISC_DATE,
969 v_sob );
970 EXCEPTION
971 /* If invoice exist update the duedate,this will happen
972 if an invoice have more then one distributions */
973
974 WHEN DUP_VAL_ON_INDEX THEN
975
976 UPDATE fv_inv_selected_duedate_temp
977 SET NEW_DUE_DATE = DECODE(v_due_date_flag,'Y',
978 v_total_due_date ,NULL)
979 WHERE invoice_id = v_invoice_id;
980 END ;
981 END IF; /* Due date flag or discount flag = 'Y' */
982 END LOOP; /* C1_MAIN_SELECT */
983
984 COMMIT;
985 retcode := 0;
986 errbuf := '** Due date Calculation Process Completed Sucessfully ** ';
987 IF (c3_receipt_accept%isopen) THEN
988 CLOSE c3_receipt_accept;
989 END IF;
990 cleanup;
991
992 fnd_request.set_org_id ( l_operating_unit );
993 v_req_id := fnd_request.submit_request
994 (
995 application => 'FV' ,
996 program => 'FVXDUDRP' ,
997 argument1 => x_run_mode
998 );
999 IF ( v_req_id = 0 ) THEN
1000 errbuf := 'Error in Submit_request Procedure, ' || 'while executing Due Date Calculation Execution Report';
1001
1002 else
1003 COMMIT;
1004 END IF;
1005
1006 RETURN;
1007
1008 EXCEPTION
1009
1010 WHEN NO_DATA_FOUND THEN
1011 retcode := -1;
1012 IF(x_err_code = 1) THEN
1013 errbuf := 'No Due date defined in AP_TERMS_LINES';
1014 ELSIF(x_err_code = 2) THEN
1015 errbuf := 'Receipt acceptance days is not defined in
1016 FINANCIAL_SYSTEM_PARAMETERS' ;
1017 ELSIF(x_err_code = 5) THEN
1018 errbuf := 'Update failed in ap_payment_schedules';
1019 ELSIF(x_err_code = 3) THEN
1020 errbuf := 'Constructive acceptence days is not defined in
1021 FV_TERMS_LINES';
1022 ELSIF(x_err_code = 6) THEN
1023 errbuf := 'Insert failed in Fv_inv_selected_duedate_temp';
1024 ELSIF(x_err_code = 5) THEN
1025 errbuf := 'Main_select' || SQLERRM;
1026 ELSIF(x_err_code = 4) THEN
1027 errbuf := 'C2_ACCEPT' || SQLERRM;
1028 END IF;
1029 cleanup;
1030 RETURN;
1031 WHEN OTHERS THEN
1032 errbuf := 'When others ' || SQLERRM;
1033 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,errbuf);
1034 RETURN;
1035 END MAIN; /* Procedure Due_Date_Calculation */
1036 -------------------------------------------------------------------------
1037 /* Prcecure to rollback the transactions */
1038 PROCEDURE cleanup IS
1039 l_module_name VARCHAR2(200);
1040 errbuf VARCHAR2(200);
1041 BEGIN
1042 l_module_name := g_module_name || 'cleanup';
1043 IF (c1_main_select%ISOPEN) THEN
1044 CLOSE c1_main_select;
1045 END IF;
1046 IF (c2_accept%isopen) THEN
1047 CLOSE c2_accept;
1048 END IF;
1049 /* IF (c3_receipt_accept%isopen) THEN
1050 CLOSE c3_receipt_accept;
1051 END IF; */
1052 ROLLBACK;
1053 RETURN;
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 errbuf := 'When others ' || SQLERRM;
1057 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1058 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,errbuf);
1059 END IF;
1060 RAISE;
1061 END cleanup;
1062 ---------------------------------------------------------------------------
1063 BEGIN
1064 -- global initialization to avoid File.Sql.35
1065 g_module_name := 'fv.plsql.fv_Due_Date_Calculation.';
1066 END ;