HI Akila,

Please review the order_items table and see if there is a duplicate row having the same item_id.

Try this query to review:

select * from order_items
where order_id = 'SV2204280869@@1'

If there is a duplicate item_id in the results, delete one row using the order_items.order_items_id  as the primary key column.

What is not clear is how this happened but the error message indicates this is the problem.

Phil, there are no duplicated rows on order_items that having 30 rows for the service  order generated from contract. However in the OOS lines of the contract and ccontl table there are two duplicated rows with similar serial number and different 'To Date' while the first line is been cancelled. Is this incorrect on adding OOS lines for the contract.

Hi Akila,

Ok -this is incorrect. There should not be overlapping ccontl lines for the same item in the contract.  As the one for 01.12.2021 to 30.11.2022 has a zero value it is a candidate for removal.  By any chance was this contract line canceled?  (it was, I just checked the database)

So, the customer apparently added a new line later after canceling this line… I would probably remove it via SQL as it is not possible via the application.

select item_id, cconth_id, ccontl_id, node_price, is_cancelled, last_change_date from ccontl where cconth_id = 'C00000847'
and item_id in (select item_id from item where serial_no = '1134922-3');

select * from ccont_rec_rev where ccontl_id = 36646

select * from contract_log where ccontl_id = 36646

However, I don’t think this is possible yet as there are also other tables that are linked to the same contract line such as ccont_rec_rev and contract_log thus may cause key constraint errors.

Thank you @Phil Seifert