This appendix covers the following topics:
You can use the PAS_SERIAL_GEN.generate_serial Procedure to implement the following:
Generate custom serial number logic.
Note: Custom serials must belong to a serial type group that is flagged as product-specific.
Use uniqueness check.
The uniqueness check is for custom serial numbers.
Generate serials using the serial_next parameter.
The PAS_SERIAL_GEN.generate_serial procedure is located within the Oracle Pedigree and Serialization Manager (OPSM) database and can be found under the "Packages" folder within the database.
Implementation of the generate custom serial number logic is required. However, implementation for the use uniqueness check and generate serials using the serial_next parameter are optional.
The table below contains a description of all of the parameters used in the PAS_SERIAL_GEN.generate_serial procedure:
Note: Do not change the initial values for the variables that are assigned in this procedure. These variables can be changed when customizing serial number generation logic.
For more information on customizing serial generation logic, see:
| Parameter | Description |
|---|---|
| p_location_id | The location that is used for the Serial Generation Request. |
| p_product_detail_id | The product detail Id (from table pas_s_product_details), if specified in Serial Generation Request, else it will be zero. |
| p_serial_type | The serial type for which the procedure will be used to generate the serial number. |
| p_serial_min | The minimum value for range of values that can be used for the serial number. |
| p_serial_max | The maximum value for range of values that can be used for the serial number. |
| p_serial_number | The serial number created by the procedure. |
| p_serial_unique_check_string | The unique check string created by the procedure which would be used to check the uniqueness of the number. |
| p_unique_serial_number | The unique number created by the procedure which is concatenated with the prefix to generate the serial number. |
| p_start_serial | If the serial generation request has specified the "From" and "To", then this parameter will refer to "From" else it will be zero. |
| p_end_serial | If the serial generation request has specified the "From" and "To", then this parameter will refer to "To" else it will be zero. |
| p_unique_serial_method | The unique serial method specified for a serial type in setup. Values are:
|
| p_unique_serial_increment | The unique serial increment specified for a serial type in setup if p_unique_serial_method is NEXTNUMBER, else it will be zero. |
| p_serial_next | This value contains the Oracle Pedigree and Serialization Manager (OPSM) suggested unique serial number, which can be updated by you if you implement the serial next logic |
| p_random_min | If the p_unique_serial_method is RANDOM, then this parameter holds the minimum value that the unique serial number can be incremented with. |
| p_random_max | If the p_unique_serial_method is RANDOM, then this parameter holds the maximum value that the unique serial number can be incremented with. |
| p_unique | This parameter indicates whether the serial number generated is unique or not. Values are:
2 appears by default which indicates that the uniqueness has not been checked. |
| p_error_string | This holds any error message that you want to display for a particular serial number. |
For each custom serial type that needs to be implemented, add an IF-END IF block.
Note: You will need to add a separate IF-END IF block for every custom serial type.
Note: Custom serials must belong to a serial type group that is flagged as product-specific.
For example, the condition in the if statement must be:
IF (p_serial_type = 'EXAMPLECUSTOMSERIALTYPE-1')
You can replace EXAMPLECUSTOMSERIALTYPE-1 with your custom serial type.
Add all of your custom serial generation logic between this IF-END IF block. Your logic must ensure that one of the following is achieved:
This logic must populate the values in the following parameters:
p_unique_serial_number - This is the number that will be prefixed to create the complete serial number.
p_serial_unique_check_string - This is the string that will be used to check the uniqueness of the serial number generated.
p_serial_number - This is the actual serial number generated which is performed by concatenating the prefix and p_unique_serial_number.
This logic must populate the value in the following parameter:
p_error_string - The error message that you want to be populated for a particular serial number.
Oracle Pedigree and Serialization Manager (OPSM) implements uniqueness check based on the value populated in p_serial_unique_check_string. This value must be unique across the system. If it is not, an error stating that the number is not unique will be issued. You have the option of implementing your own uniqueness check for the serial numbers that their customer serial number logic generates as part of Generating Custom Serial Number Logic above. In that case, the parameter p_serial_unique_check_string will again be used to check the uniqueness. If the customer implements their own uniqueness check logic, they must set the parameter p_unique. The values that can be assigned to p_unique are:
If the number is unique: p_unique = 1 (The parameters in Step 1 in the Generating Custom Serial Number Logic above must also be set)
If the number is not unique: p_unique = 0
OPSM has implemented logic to use the serial next value from the serial range details table for serial generation. This field will contain the next OPSM suggested serial number in the parameter p_serial_next. You can also generate your own serial_next which you can use as the next number. In that case, you will have to implement your own logic for populating the field and you will have to assign that value to p_serial_next and ensure that the corresponding tables are updated correctly.
You can use the PAS_SERIAL_VAL.validate_serial procedure to perform validation on serial numbers. Implementation of the serial number validation logic is required.
The PAS_SERIAL_VAL.validate_serial procedure is located within the Oracle Pedigree and Serialization Manager (OPSM) database and can be found under the "Packages" folder within the database.
Before using this procedure, review the information in this appendix for the PAS_SERIAL_GEN.generate_serial procedure.
For more information on the PAS_SERIAL_GEN.generate_serial procedure, see Using the PAS_SERIAL_GEN.generate_serial Procedure.
The table below contains a description of all of the parameters used in the PAS_SERIAL_VAL.validate_serial procedure:
Note: Do not change the initial values for the variables that are assigned in this procedure. These variables can be changed when performing serial number validation.
For more information on serial number validation, see Validating Serial Numbers.
| Parameter | Description |
|---|---|
| p_serial_number | The item serial number to be imported. |
| p_location_id | The location that is used for this transaction. |
| p_product_detail_id | The product detail Id (from the table pas_s_product_details). |
| p_serial_type | The serial type of the serial to be processed. |
| p_validated | If the item serial number is being validated, the value is 1, otherwise, the value is 0. |
| p_valid_serial | If the passed item serial number is valid the value is 1, otherwise, the value is 0. |
| p_unique_check_string | This is the unique check string, that will be used to check the uniqueness of the item serial number. |
| p_unique_serial_number | The unique serial number that must be numeric and is derived from the item serial number. |
| p_error_message | If the item serial number is not valid, then the error message stating the reason for the item serial number being invalid. |
Serial validation:
p_serial_number is the item serial number that needs to be validated. Whenever an item serial number is validated, the p_validated parameter must be set to "1" (irrespective of whether the serial is valid or not).
Validate the item serial number.
In the sample implementation, the item serial number is validated by matching the formed prefix for a particular serial type and fetching the string of the same size (as of the prefix) from the item serial number and then checking whether the remaining characters in the item serial number are numeric or not (which would be a unique serial number and so that has to be numeric).
If the serial number is valid:
Set p_valid_serial as "1".
Form the unique check string in p_unique_check_string. This will be used to check the uniqueness of the item serial number.
Extract the unique serial number in p_unique_serial_number. This will be used to check whether the item serial number is valid as defined in the setup or not.
Note: If the serial is valid, p_unique_check_string and p_unique_serial_number must be populated.
If the serial number is not valid:
Set p_valid_serial as "0".
Populate the p_error_message with the error message.
Note: If the serial is not valid, p_error_message must be populated.