Shipment Remark Unique Constraint with VPD Profile - "Cannot add the SHIPMENT_REMARK record. [SHIPMENT_GID, REMARK_SEQUENCE] must be unique." (Doc ID 2496032.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

When a user with a VDP profile where table SHIPMENT_REMARK only allows a view of a certain type of remark tried to add a new remark a unique constraint error is set.

ERROR
-----------------------
Error

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1) Shipment Management > Shipment Management > Buy Shipments. Inquire on an existing shipment and add 2 remarks.

2) DBA Tools > SQL Servlet. Run the following SQL.

SQL = select * from shipment_remark where shipment_gid = 'shipment ID'.
Note the results show column REMARK_SEQUENCE is part of the primary key.

3) Configuration and Administration > VPD Profile. Create a VDP profile where table SHIPMENT_REMARK only allows a view of the REM remark.

4) Configuration and Administration > User Management > User Role. Create a user role that uses the ONLY_REM_REMARKS VPD Profile.

5) Configuration and Administration > User Management > User Manager. Create a user that uses the ONLY_REM_REMARKS role.

6) Log into to OTM as the new user.

7) Shipment Management > Shipment Management > Buy Shipments. Inquire on the existing shipment and view remarks. Note only the REM remark can be seen. Try and add another remark and the error occurs.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot add new remarks when secured out of certain remark types.

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.