Fix broken sequence on parent-child relationship tables for PUB schema

Whenever you find an error like this (for category-product relationship for example)

ERROR [nucleusNamespace.atg.commerce.catalog.ProductCatalog-ver]  Error reading list or array index from the database. Expected: "0", got "1". The following property was not read: "
{fixedChildProducts,pType=List,IDesc=[ItemDesc: category],table=dcs_cat_chldprd,cols=child_prd_id
  ,pBI=atg.beans.MergedDynamicBeanInfo@5409e0ad,pIDesc=null
  ,cType=interface atg.repository.RepositoryItem,cBI=[ItemDesc: product],cIDesc=[ItemDesc: product],colHandle=null}", for item id: "2350:36". This means the data
base table holding this property does not have sequential integers starting with 0 in its multi-column. This should only happen if the database table was modifie
d directly (outside of Dynamo).: java.lang.Exception

It means sequence_num column doesn't contain a consecutive sequence number for the category on all of its products, this is, if category has 4 child products then category must have sequence_num values 0,1,2,3 but if for some reason you see something like 0,2,3,4 then ATG is not able to determine the sequence of products and will result on an error, this apply to both PUB and CATA/B schemas impacting of course BCC and Store

On BCC you'll get an error like:

And store will just break on the page where you're using the child products

Please notice, this affects also product-sku relationship, stored in CATA/B schema on table: dcs_prd_chldsku

An easy way to detect this problem is with this query on PUB schema:

-- Query to find all incorrect sequence_num in category > product relationship
SELECT dcc.* 
FROM dcs_cat_chldprd dcc
INNER JOIN dcs_category dc ON (dc.category_id = dcc.category_id AND dc.asset_version = dcc.asset_version AND dc.is_head = '1')
INNER JOIN dcs_product dp ON (dp.product_id = dcc.child_prd_id AND dp.asset_version = dcc.sec_asset_version AND dp.is_head = '1')
WHERE dcc.category_id IN (SELECT category_id
FROM (SELECT category_id, asset_version, MAX(sequence_num) AS "MAX_SEQ", count(*) -1 AS "COUNT"
FROM (SELECT DISTINCT dcc.category_id, dcc.asset_version, dcc.sequence_num, dcc.child_prd_id
FROM dcs_category dc
INNER JOIN dcs_cat_chldprd dcc ON (dcc.category_id = dc.category_id)
WHERE dc.asset_version = dcc.asset_version
AND dc.is_head = 1
)
GROUP BY category_id, asset_version
)
WHERE max_seq <> count)
ORDER BY dcc.category_id, dcc.sequence_num;

-- Query to find all incorrect sequence_num in product > sku relationship
SELECT dpc.* 
FROM dcs_prd_chldsku dpc
INNER JOIN dcs_product dp ON (dp.product_id = dpc.product_id AND dp.asset_version = dpc.asset_version AND dp.is_head = '1')
INNER JOIN dcs_sku ds ON (ds.sku_id = dpc.sku_id AND ds.asset_version = dpc.sec_asset_version AND ds.is_head = '1')
WHERE dpc.product_id IN (SELECT product_id
FROM (SELECT product_id, MAX(sequence_num) AS "MAX_SEQ", count(*) -1 AS "COUNT"
FROM (SELECT DISTINCT dpc.product_id, dpc.sequence_num, dpc.sku_id
FROM dcs_product dp
INNER JOIN dcs_prd_chldsku dpc ON (dpc.product_id = dp.product_id)
WHERE dp.asset_version = dpc.asset_version
AND dp.is_head = 1
)
GROUP BY product_id
)
WHERE max_seq <> count)
ORDER BY dpc.product_id, dpc.sequence_num;

For this, you can use a PL/SQL for fixing relationship by defining a consecutive number on sequence_num field for either dcs_cat_chldprd or dcs_prd_chldsku tables.

I'll be working on the PL/SQL for this later...