AP: R12: ORA-02014 Invoice Validation Error When Organization Parameter is NULL (Doc ID 2771136.1)

Last updated on APRIL 23, 2021

Applies to:

Symptoms

AP Invoice Validation concurrent Request is ending in error when Organization parameter is not provided

ERROR
-----------------------
MSG-00101: Error occured in BATCH_APPROVAL()
MSG-00102: Error Code : -20001
MSG-00103: Error Message : ORA-20001: APP-SQLAP-10000: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. occurred in
with parameters (Run Option = All, Batch Id = , Begin Date = , End Date = DD-MON-YY, Vendor Id = , Pay Group = , Invoice Id = , Entered By = , Concurrent Request Id = <request_id>, Org Id = )
while performing the following operation:
&DEBUG_INFO
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.AP_APPROVAL_PKG", line 11265
MSG-00000: When Others:User-Defined Exception
REP-1419: 'beforereport': PL/SQL program aborted.
REP-0069: Internal error

In FND Log can see:

                         ( SELECT /*+ push_subq no_unnest */ 'Cancelled distributions'
                             FROM AP_INVOICE_DISTRIBUTIONS_ALL D3
                            WHERE D3.INVOICE_ID = AI.INVOICE_ID
                              AND D3.CANCELLATION_FLAG = 'Y'
                         ) AND AI.invoice_date <= :b_end_date  FOR UPDATE SKIP LOCKED"

ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
AP.PLSQL.AP_APPROVAL_PKG.Batch_Approval Others: Exception: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
fnd.plsql.APP_EXCEPTION.RAISE_EXCEPTION.dict_auto_log "APP-SQLAP-10000: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. occurred in


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Invoice Validation concurrent program with Organization parameter NULL
2. Error appears

Changes

Parallel Processing is available in 12.2.4 and was made available for R12.1.x customers via the application of Patch 8547912:R12.AP.B

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.