-- Add blcRootCategory
INSERT INTO BLC_CATEGORY (CATEGORY_ID,DESCRIPTION,NAME,ACTIVE_START_DATE) VALUES (-99999,'blcRootCategory','blcRootCategory',CURRENT_TIMESTAMP);
-- Update orphaned categories to have blcRootCategory as parent
INSERT INTO BLC_CATEGORY_XREF (CATEGORY_XREF_ID, SUB_CATEGORY_ID, CATEGORY_ID) SELECT -1 * (category.category_id + 100), category.category_id, -99999 FROM BLC_CATEGORY category where category.category_id not in (-99999) AND category.category_id not in (select xref.sub_category_id from BLC_CATEGORY_XREF xref);
-- Update Assets
-- Add blcRootFolder
INSERT INTO BLC_ASSET_FOLDER (ASSET_FOLDER_ID, NAME) VALUES (-999999, 'blcRootFolder');
-- Update orphaned assets to have blcRootFolder as parent
INSERT INTO BLC_ASSET_FOLDER_ITEM_XREF (ASSET_FOLDER_ITEM_XREF_ID, ASSET_ID, FOLDER_ID)
SELECT -1 * (asset.static_asset_id + 100), asset.static_asset_id, -999999
FROM BLC_STATIC_ASSET asset
WHERE asset.static_asset_id not in (-999999)
AND asset.static_asset_id not in (select xref.asset_id from BLC_ASSET_FOLDER_ITEM_XREF xref);
