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

Last updated on DECEMBER 11, 2023

Applies to:

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.

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:

•     DEFINE AME_CONDITION_USAGES
•     KEY RULE_KEY VARCHAR2(100)
•     KEY CONDITION_KEY VARCHAR2(100)
•     BASE RULE_ID VARCHAR2(100)
•     BASE CONDITION_ID VARCHAR2(100)
•     BASE LAST_UPDATE_DATE VARCHAR2(19)
•     CTX OWNER VARCHAR2(100)
•     END AME_CONDITION_USAGES

3. An example of a problem condition usage is:

•     BEGIN AME_CONDITION_USAGES "994615947:10110" "SEED-2934754471:10328"
•     RULE_ID = "13012"
•     CONDITION_ID = "-134"
•     LAST_UPDATE_DATE = "2014/12/08 16:11:21"
•     OWNER = "KNASSKAU"
•     END AME_CONDITION_USAGES

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.

Changes

Cause

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