API WMS_CONTAINER_PUB.GENERATE_LPN Not Returning LPN ID When Finished. (Doc ID 1639906.1)

Last updated on MARCH 20, 2023

Applies to:

Symptoms

The issue with using the API WMS_CONTAINER_PUB.GENERATE_LPN in R12.1.3
The API has the ability to create 1 or more LPNs in a single call to the procedure.
This API accepts (among other parameters) the parameter P_QUANTITY which can be set to a value >=1. In case it is set to a value greater than 1 e.g. 3, it will create 3 LPNs.
The issue is only for the case where multiple LPNs are created through a single API call in R12.1.3. The issue does not exist in the 11i version of the code. There is no issue when a single LPN is being created either.

The expectation is that when an API creates an instance(s), i.e. record(s) of an entity it should return sufficient information (generally in the form of a unique ID of the instance) in the OUT parameters (or otherwise) to allow identification of the records created that can be used for subsequent processing. E.g. If an API creates a Customer, it should return the Customer ID or the Customer Number which can be then used as an input to the Customer Site API to create Sites for the Customer. Without this information Customer Site cannot be created.

In the case i.e. the WMS_CONTAINER_PUB.GENERATE_LPN is used to create multiple LPNs e.g. 3. After calling the API we should be able to identify the LPNs that have been created. This was possible in 11i, but in R12.1.3 we have found this is no longer possible. Hence not able to use the LPN numbers for subsequent processing because we are not able to identify all the LPNs created by the API.

The reason is as follows :
The API WMS_CONTAINER_PUB.GENERATE_LPN has one p_lpn_id_out and one p_process_id OUT parameters. The p_lpn_id_out can hold only one LPN ID, not 3. So using the p_lpn_id_out parameter it is not possible to identify all the 3 LPNs.
Until 11i, however when multiple LPNs were being created the API also inserted records into the WMS_LPN_PROCESS_TEMP table, and grouped them with the same PROCESS_ID. E.g. In case 3 LPNs were being created, there will be 3 records in WMS_LPN_PROCESS_TEMP with different LPN IDs but the same PROCESS_ID. This PROCESS_ID was being returned in the OUT parameter to the WMS_CONTAINER_PUB.GENERATE_LPN. This gave us the ability to subsequently query the WMS_LPN_PROCESS_TEMP table using the P_PROCESS_ID and identify all the LPNs that have been created by the API, and use them for subsequent processing e.g. alerting the user that these LPNs have been created.

In R12.1.3 however, the P_PROCESS_ID is always returning null. Hence we have lost the ability to identify all the LPNs created by the API in R12.1.3

Changes

 Upgraded to R12

Cause

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