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