ESP - Delete Product Design in Imported Status

/************************************************
This script is to delete PD that do not have 
Orders on them and are in import status

20140207 Steven F Ling created

20230401 Steven F Ling Modified

Changed for the new Database structure

*************************************************/

declare @dnid	int
declare @rid	int
declare @dn		varchar(30)
declare @slid	int
declare @pdpid	int
declare @udid	int

declare look cursor for
SELECT isnull(pd.ID,0), isnull(pd.designnumber,''), isnull(rt.ID,0), isnull(sl.id,0), isnull(pdp.id,0), isnull(ud.id,0)
FROM ebxproductDesign pd with (NOLOCK)
LEFT JOIN ebxroute rt
ON pd.ID = rt.productDesignID
LEFT JOIN ebxProductDesignPlant pdp
ON pdp.productDesignID = pd.id
LEFT JOIN esporder o
ON o.productDesignID = pd.id
LEFT JOIN fgsstockline sl
ON sl.productdesignid = pd.id
LEFT JOIN ebxunitizingData ud
ON pdp.unitizingDataID = ud.id
inner join orgcompany c
on c.id = pd.companyID 
	where productdesignstatus = 'imported'
and o.productDesignID is null
--and designnumber in ('')
and c.companynumber ='1555'


open look
fetch next from look into @dnid, @dn ,@rid, @slid, @pdpid, @udid
while @@fetch_status=0
begin

IF @dnid > 0
Begin

	print 'preping to delete '+@dn +' with ID of '+convert(nvarchar(30),@dnid)
	print '	deleting ebxPriceListProductDesign'
	delete from ebxPriceListProductDesign where productdesignid=@dnid
	print '	deleting ebxproductprice'
	delete from ebxproductprice where productdesignid=@dnid
	print '	deleting cstcostestimateline'
	delete from cstcostestimateline where productdesignid=@dnid
	print '	deleting cstcostestimate'
	delete from cstcostestimate where productdesignid=@dnid
	print '	deleting ebxalternateRoute'
	delete from ebxalternateRoute where productdesignid=@dnid
	print '	deleting ebxmachinestep'
	delete from ebxmachinestep where routeid in (@rid)
	print '	deleting cstpurchaseCost PD'
	delete from cstpurchaseCost where productDesignID = @dnid
	print '	deleting cstpurchaseCost Route'
	delete from cstpurchaseCost where routeID = @rid
	print '	deleting ebxroute'
	delete from ebxroute where productdesignid=@dnid
	print '	deleting ebxProductDesignColourCoating'
	delete from ebxProductDesignColourCoating where productDesignID = @dnid
	print '	deleting ebxproductDesignInstruction'
	delete from ebxproductDesignInstruction where productDesignID =  @dnid
	print '	Updating ebxunitizingData'
	update ebxunitizingData set ownerProductDesignPlantID=null where id=@udid
	print '	deleting ebxProductDesignPlant'
	delete from ebxProductDesignPlant where productDesignID = @dnid
	print '	deleting ebxunitizingData'
	delete from ebxunitizingData where id=@udid
	print '	deleting fgsstocklinePurchaser'
	delete from fgsstocklinePurchaser where stockLineID = @slid
	print '	deleting fgsstockLine'
	delete from fgsstockLine where productDesignID = @dnid
	print '	updating ebxproductDesign'
	Update ebxproductDesign set previousProductDesignID=null, nextProductDesignID=null where ID = @dnid
	print '	updating ebxproductDesign'
	Update ebxproductDesign set mainttime=getdate(), userid='Removed PD Link', previousProductDesignID=null where previousProductDesignID  = @dnid
	print '	updating ebxproductDesign'
	Update ebxproductDesign set mainttime=getdate(), userid='Removed PD Link', nextProductDesignID=null where nextProductDesignID = @dnid
	print '	deleting ebxproductDesign'
	delete from ebxproductdesign where designnumber = @dn

End
Else
Begin
	print 'does not exist...'
end

fetch next from look into @dnid, @dn ,@rid, @slid, @pdpid, @udid
end
close look
deallocate look