USE [ShopMgt]

DECLARE @CTE_LineItem TABLE (LineItemId INT)

INSERT INTO @CTE_LineItem
SELECT LineItemId FROM SM.LineItem li WHERE 
    li.LineItemId NOT IN (SELECT LineItemId FROM SM.LaborItem 
    UNION SELECT LineItemId FROM SM.CouponItem
    UNION SELECT LineItemId FROM SM.PartItem
    UNION SELECT LineItemId FROM SM.SubletItem
    UNION SELECT LineItemId FROM SM.RepairArticleLineItem
    UNION SELECT LineItemId FROM SM.PackageItem
    UNION SELECT LineItemId FROM SM.NoteItem
    )

DELETE FROM SM.CannedJobLineItemSequence 
  WHERE LineItem IN (SELECT LineItemId FROM @CTE_LineItem)
DELETE FROM SM.PackageLineSequence 
  WHERE LineItemId IN (SELECT LineItemId FROM @CTE_LineItem)
DELETE FROM SM.SpecialPackageLineItemSequence 
  WHERE LineItemId IN (SELECT LineItemId FROM @CTE_LineItem)
DELETE FROM  SM.LineItemTaxes
  WHERE LineItemId IN (SELECT LineItemId FROM @CTE_LineItem)
DELETE FROM  SM.LineItemTechs
  WHERE LineItemId IN (SELECT LineItemId FROM @CTE_LineItem)
DELETE FROM SM.PartFee
  WHERE LineItemId IN (SELECT LineItemId FROM @CTE_LineItem)
DELETE FROM SM.RevisionLineItemSequence
  WHERE LineItemId IN (SELECT LineItemId FROM @CTE_LineItem)

SELECT ro.RepairOrderId, rn.RecordNumberId
   from SM.RepairOrderLineItemSequence ro
       LEFT OUTER JOIN SM.RecordNumber rn 
       ON ro.RepairOrderId = rn.RepairOrderId
  WHERE ro.LineItemId IN (SELECT LineItemId FROM @CTE_LineItem)


DELETE FROM SM.RepairOrderLineItemSequence
  WHERE LineItemId IN (SELECT LineItemId FROM @CTE_LineItem)
DELETE FROM SM.LineItem
  WHERE LineItemId IN (SELECT LineItemId FROM @CTE_LineItem)