In Control 04.40.1003.2501, the Description field was added to the TransPart table. But the field was not initialized to the looked up values previously used. This query can be used to update the data, until the April 2010 release, which will include this SQL.

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

DECLARE @TransPartUpdate TABLE(ID INT PRIMARY KEY, TransDetailID INT, PartID INT,
                               PartLinkID INT, PartLinkClassTypeID INT,
                               AttachedBillDetailID INT, AttachedPODetailID INT, AttachedRDDetailID INT,
                               Description VARCHAR(255));
INSERT INTO @TransPartUpdate (ID, TransDetailID, PartID, PartLinkID, PartLinkClassTypeID,
                              AttachedBillDetailID, AttachedPODetailID, AttachedRDDetailID)
SELECT ID, TransDetailID, PartID, PartLinkID, PartLinkClassTypeID,
       AttachedBillDetailID, AttachedPODetailID, AttachedRDDetailID
FROM TransPart
WHERE ID > 0
      AND Description IS NULL
      AND PartID IS NOT NULL
      AND TransDetailID IS NOT NULL
;
UPDATE @TransPartUpdate
SET Description = CAST( P.Description AS VARCHAR(255) )
FROM @TransPartUpdate T
     LEFT JOIN Part P ON (P.ID = T.PartID)
WHERE P.Description IS NOT NULL
;
UPDATE @TransPartUpdate
SET Description = CAST( PL.Description AS VARCHAR(255) )
FROM @TransPartUpdate T
     LEFT JOIN GoodsItemPartLink PL ON (PL.ID = T.PartLinkID AND PL.ClassTypeID = T.PartLinkClassTypeID)
WHERE PL.Description IS NOT NULL
;
UPDATE @TransPartUpdate
SET Description = CAST((TD.ItemName + ' ' + CAST(TD.Description AS VARCHAR(255))) AS VARCHAR(255))
FROM @TransPartUpdate T
     LEFT JOIN VendorTransDetail TD ON (TD.ID = T.AttachedRDDetailID)
WHERE TD.Description IS NOT NULL
;
UPDATE @TransPartUpdate
SET Description = CAST((TD.ItemName + ' ' + CAST(TD.Description AS VARCHAR(255))) AS VARCHAR(255))
FROM @TransPartUpdate T
     LEFT JOIN VendorTransDetail TD ON (TD.ID = T.AttachedPODetailID)
WHERE TD.Description IS NOT NULL
;
UPDATE @TransPartUpdate
SET Description = CAST((TD.ItemName + ' ' + CAST(TD.Description AS VARCHAR(255))) AS VARCHAR(255))
FROM @TransPartUpdate T
     LEFT JOIN VendorTransDetail TD ON (TD.ID = T.AttachedBillDetailID)
WHERE TD.Description IS NOT NULL
;
DELETE FROM @TransPartUpdate
WHERE Description IS NULL
;
UPDATE TransPart
SET Description = T.Description
FROM TransPart TP
     LEFT JOIN @TransPartUpdate T ON (T.ID = TP.ID)
WHERE TP.ID IN (SELECT ID FROM @TransPartUpdate)
;
UPDATE TransDetail
SET PartStr = NULL
WHERE ID IN (SELECT TransDetailID FROM @TransPartUpdate)

• Entered : 04/05/2010
• Version :