Errors In R12 Generic Data Fix (GDF) Patch 17433450: Cannot Fix The Invoices In Future Dates

(Doc ID 2266549.1)

Last updated on MAY 16, 2017

Applies to:

Oracle Payables - Version 12.2.5 and later
Information in this document applies to any platform.

Goal

When applying the AP GDF patch 17433450:R12.AP.B, found some invoices in future date,such as 01-06-3020,cannot been fixed.The script 'update_inv_total_tax_amt_sel.sql' has this INSERT IN THE DRIVER table
/*--------------------------------------------------------------------------*/
EXECUTE IMMEDIATE ' INSERT INTO inv_total_tax_null
SELECT /*+ ROWID(ai) */ ai.*, ''N'' PROCESS_FLAG FROM ap_invoices_all ai
WHERE ai.historical_flag = ''Y''
AND ai.total_tax_amount IS NULL
AND ai.invoice_date between '''||l_start_date||''' and '''||l_end_Date||''' /* bug 17433450 */ --cannot find invoices because of this statement
AND ai.rowid BETWEEN '''|| l_start_rowid||''' AND '''|| l_end_rowid ||'''
AND EXISTS (SELECT ''tax exists''
FROM ap_invoice_lines_all ail
WHERE ai.invoice_id = ail.invoice_id
AND ail.line_type_lookup_code = ''TAX'')' ;
/*--------------------------------------------------------------------------*/
those invoices that have future date does not get inserted into driver table.

This is because the NLS_DATE_FORMAT is DD-MON-RR, so the date 01-06-3020 is treated as 01-JUN-20, which does not fall in the range.
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms