AME_CONDITION_USAGES_API2.KEY_TO_IDS_3 - X_RULE_KEY Parameter Has Wrong Data Type. (Doc ID 1992504.1)

Last updated on DECEMBER 21, 2016

Applies to:

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

Symptoms

On : 12.1.3 version, FND_LOAD/seed-data issues.

Customer is the migrating AME setup using FNDLOAD facility and they are facing the below error while doing so.

The issue here is that the RULE_KEY is a character where as parameter "X_RULE_KEY" in Procedure KEY_TO_IDS_3 is defined as a number.

If the ldt file being uploaded passes in a condition_id < 0 and a rule_id > 0 then the api calls the internal procedure KEY_TO_IDS_3 and passes
it the rule_key and condition_id so it can derive the rule_id and condition usage count.

The RULE_KEY is (per the AME_RULES table) a varchar2(100) field and generally contains non-numeric characters such as a colon (so it cannot
be accepted into a number variable).

The KEY_TO_IDS_3 procedure has been defined with the X_RULE_KEY parameter having a datatype of NUMBER and hence the FNDLOAD fails
with the below error.

ORA-6502: PL/SQL: numeric or value error: character to number conversion error



ADDITIONAL INFORMATION
1. The customer is using the lct file "amesrulk.lct" (with version 120.2.12010000.1) to download the problematic rule.


2. The ldt file created from this defines an ame condition usage as:



3. An example of a problem condition usage is:



4. The lct file declares the following procedure to import a condition usage:

   


5. So, the code flow is as below:
    ame_condition_usages_api2.LOAD_ROW --> procedure KEY_TO_IDS_3

    The call to OWNER_TO_WHO (line 225 - the first instruction in LOAD_ROW) returns:
    X_CREATED_BY [2864]
    X_LAST_UPDATED_BY [2864]
    X_LAST_UPDATE_LOGIN [0]

    The IF statement (line 231) checks if condition_id > 0. As it is not, the system will move to the first line of code in the ELSE section, starting at line 259.
    The IF statement (line 259) checks if rule_id < 0. As it is not, the system will move to the first line of code in the ELSE section, starting at line 281.

    Line 282 the system will attempt to run the KEY_TO_IDS_3 procedure as follows:

    KEY_TO_IDS_3 (
      X_RULE_KEY,
      X_CONDITION_ID,
      X_RULE_ID_LOAD,
      X_CONDITION_USAGE_COUNT
    );

    The signature of the subroutine KEY_TO_IDS_3 is as below:
    procedure KEY_TO_IDS_3 (
       X_RULE_KEY in NUMBER,
       X_CONDITION_ID in NUMBER,
       X_RULE_ID out nocopy NUMBER,
       X_CONDITION_USAGE_COUNT out nocopy NUMBER
    )

    Here, one can see from the example in Point (3) that the value passed into LOAD_ROW for X_RULE_KEY (which is being passed to KEY_TO_IDS_3 ) contains a ':' - ie. [994615947:10110].
    EVERY ROW IN AME_RULES in customer's instance has a ':' in it. So, it is expected that the datatype for "ROW_KEY" is never a number. As the RULE_KEY field in AME_RULES is declared
    as "varchar2(100)", this is not surprising.



STEPS
The issue can be reproduced at will with the following steps:

1. Download AME rules using FNDLOAD.



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