This SQL marks all active artwork groups as Produced. It will also create the history entries as well. SQL Bridge is required for this query to work correctly.

HIGH. This query modifies data; therefore, you should make a backup of the database prior to running this query.

DECLARE @GroupsToChange TABLE(ArtworkGroupID INT PRIMARY KEY, ArtworkGroupClassTypeID INT, SeqID INT, OldStatusID INT, PlayerID INT);
DECLARE @NewHistory TABLE(ID INT PRIMARY KEY, ClassTypeID INT, SeqID INT, ArtworkGroupID INT, FromStatusID INT, ToStatusID INT, StatusDT DATETIME, PlayerID INT);
INSERT INTO @GroupsToChange
SELECT ID, ClassTypeID, SeqID+1, StatusID, 
       (SELECT TOP 1 ID
        FROM ArtworkPlayer
        WHERE TransHeaderID = ArtworkGroup.TransHeaderID
        ORDER BY CASE WHEN EmployeeID IS NULL THEN 1 ELSE 0 END,
                 EmployeeID, ContactID) 
FROM ArtworkGroup
WHERE StatusID < 7
;
DECLARE @GroupCount INT;
DECLARE @FirstHistoryID INT;
SET @GroupCount = (SELECT COUNT(1) FROM @GroupsToChange WHERE PlayerID IS NOT NULL);
SET @FirstHistoryID = (SELECT dbo.csf_chapi_nextid(30302, @GroupCount));
DECLARE @ModifiedDate DATETIME;
SET @ModifiedDate = GETDATE();
UPDATE AG
SET SeqID = T.SeqID,
    StatusID = 7,
    StatusDT = @ModifiedDate
FROM ArtworkGroup AG
     JOIN @GroupsToChange T ON T.ArtworkGroupID = AG.ID
WHERE ID IN (SELECT ArtworkGroupID FROM @GroupsToChange)
;
INSERT INTO @NewHistory
(ID, ClassTypeID, SeqID, ArtworkGroupID, FromStatusID, ToStatusID, StatusDT, PlayerID)
SELECT @FirstHistoryID + ROW_NUMBER() OVER (ORDER BY ArtworkGroupID)-1, 30302, 0, ArtworkGroupID, OldStatusID, 7, @ModifiedDate, PlayerID
FROM @GroupsToChange
WHERE PlayerID IS NOT NULL
;
INSERT INTO ArtworkGroupStatusHistory
(ID, ClassTypeID, SeqID, ArtworkGroupID, FromStatusID, ToStatusID, StatusDT, PlayerID)
SELECT *
FROM @NewHistory
;
DECLARE @MyCursor CURSOR;
DECLARE @ID INT;
DECLARE @ClassTypeID INT;
DECLARE @SeqID INT;
SET @MyCursor = CURSOR FOR
SELECT ArtworkGroupID, ArtworkGroupClassTypeID, SeqID
FROM @GroupsToChange
OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @ID, @ClassTypeID, @SeqID
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC dbo.csf_chapi_refresh @ID, @ClassTypeID, @SeqID;
    FETCH NEXT FROM @MyCursor 
    INTO @ID, @ClassTypeID, @SeqID
END; 
CLOSE @MyCursor ;
SET @MyCursor = CURSOR FOR
SELECT ID, ClassTypeID, SeqID
FROM @NewHistory
OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @ID, @ClassTypeID, @SeqID
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC dbo.csf_chapi_refresh @ID, @ClassTypeID, @SeqID;
    FETCH NEXT FROM @MyCursor 
    INTO @ID, @ClassTypeID, @SeqID
END; 
CLOSE @MyCursor ;
DEALLOCATE @MyCursor;

• Entered : 12/02/2016
• Version : 6.0+