WIP_EAM_WORKREQUEST.WORK_REQUEST_IMPORT Error Handling For Invalid Date Throws Errors (Doc ID 1463857.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Enterprise Asset Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, INTF

Business Requirement:
Sending work requests into Oracle EBS from an external mobile system. On occasion, when the mobile device is delayed in syncing, a work request is sent to EBS that has a request date prior to the date that it is submitted. In this case, Oracle throws an error that the date is invalid. This is expected and fine. However, that validation error message, unlike the other examples that I gave like a missing request date or a missing owning department throws a second error internally that it shouldn't throw. There's a problem in the error handling in WIP_EAM_WORKREQUEST_PUB.WORK_REQUEST_IMPORT that throws the second unnecessary error. The error is confusing to our users since they then see that error and think that there's something wrong in addition to the invalid date when it's just a bug in the error handling. It should be fixed to avoid confusion for users of the public API.

ISSUE STATEMENT
----------------------------
When attempting to create work requests using WIP_EAM_WORKREQUEST_PUB.WORK_REQUEST_IMPORT with request date prior to the date that it is submitted, the following error occurs.

ERROR
-----------------------
Error 1: The attribute Request By Date: 02-MAY-12 is invalid.
Error 2: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.FND_MSG_PUB", line 279
ORA-06512: at "APPS.WIP_EAM_WORKREQUEST_PVT", line 3223
ORA-06510: PL/SQL: unhandled user-defined exception in Package WIP_EAM_WORKREQUEST_PUB Procedure work_request_import(30)
work request create status: U
work request message count: 2
work request message data:
work request entity id:


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run the attached wip_eam_workrequest.sql.
2. As coded, it has a request date one day in the past.
3. You can then edit the script and either NULL out the date or change the owning department to compare errors that are handled properly versus the invalid date error.

Cause

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