Custom Control Item (Risk) Numbering - Control Item Number Client Extension

Within Oracle Project Management there is a functionality called Issue Management. Issues are getting a number when one is created. These number can be manually entered by the user or automatically created based on a sequence. The implementation option for this can be found within the Control Item Type setup (Project Super User - Projects: Setup - Control Item Types). Within the numbering region of the setup page a choice can be made between Automatic (with an optional prefix) and Manual numbering.

You can make issue numbering dependant on the related project, meaning that the project number is included in the number assigned to the issue. This can be achieved by using the Client Extension for Control Item Numbering. This is a package within the database which you can alter to use your own, customized and client specific, issue numbering. Note that you don't change the naming, in and out variables of the procedures within those client extensions.
The Control Item Type setting for numbering must be set to Automatic to let Oracle E-Business Suite call the extension during issue creation.

The name of the Client Extension package is PA_CI_NUMBER_CLIENT_EXTN within the APPS schema. In this package one procedure is available named GET_NEXT_NUMBER which has the variable p_next_number as an output value. This value is assigned to the issue when this is created.

The default procedure is defined as below:

procedure GET_NEXT_NUMBER (
         p_object1_type         IN  VARCHAR2   := FND_API.g_miss_char
        ,p_object1_pk1_value    IN  NUMBER     := FND_API.g_miss_num
        ,p_object2_type         IN  VARCHAR2   := FND_API.g_miss_char
        ,p_object2_pk1_value    IN  NUMBER     := FND_API.g_miss_num
        ,p_next_number          IN  OUT NOCOPY VARCHAR2
        ,x_return_status        OUT  NOCOPY VARCHAR2
        ,x_msg_count            OUT  NOCOPY NUMBER
        ,x_msg_data             OUT  NOCOPY VARCHAR2) IS
BEGIN
    x_return_status := FND_API.G_RET_STS_SUCCESS;
    p_next_number   := p_next_number;
END GET_NEXT_NUMBER;

To get the project number in the issue numbering I need to derive the related project number for which the issue is created. Thank you Oracle that they deliver the project_id as an IN parameter for the procedure :-). The parameter p_object1_pk1_value is the project_id of the related project. So the final procedure with our client specific, project related numbering, becomes the following:

procedure GET_NEXT_NUMBER (
         p_object1_type         IN  VARCHAR2   := FND_API.g_miss_char
        ,p_object1_pk1_value    IN  NUMBER     := FND_API.g_miss_num
        ,p_object2_type         IN  VARCHAR2   := FND_API.g_miss_char
        ,p_object2_pk1_value    IN  NUMBER     := FND_API.g_miss_num
        ,p_next_number          IN  OUT NOCOPY VARCHAR2
        ,x_return_status        OUT  NOCOPY VARCHAR2
        ,x_msg_count            OUT  NOCOPY NUMBER
        ,x_msg_data             OUT  NOCOPY VARCHAR2) IS
l_project_number PA_PROJECTS_ALL.SEGMENT1%TYPE;
BEGIN
 
    -- cschaik
    -- derive project number based on project_id being the p_object1_pk1_value
    SELECT SEGMENT1
    INTO l_project_number
    FROM PA_PROJECTS_ALL
    WHERE PROJECT_ID = p_object1_pk1_value;
   
    if l_project_number is not null then
       x_return_status := FND_API.G_RET_STS_SUCCESS;
       p_next_number   := l_project_number || '-' || p_next_number;    
    else
      x_return_status := FND_API.G_RET_STS_ERROR;
      x_msg_count := 1;
      x_msg_data := 'The system was unable to derive the project details for the item.';
    end if;
   
END GET_NEXT_NUMBER;

I added a small variable l_project_number with the segment1 (project number) TYPE definition. In the first lines I retrieve the project number (SEGMENT1) from the PA_PROJECTS_ALL table with the help of the IN parameter p_object1_pk1_value. I know that the project number always exists so I will assign the project number to the p_next_number OUT variable and concatenate this also with it. By default the p_next_number will get an autonumber value.

Compile your extension and the result will be that every issue created for the project will have a number containing the project number and an autonumbered value (ie 100234-1, 100234-2, 100235-1 etc). Note that numbering will restart for every project number.

Make your own customized version when needed.