control_sql_-_move_all_open_artwork_groups_to_produced [Support]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======  ======



===== Explanation of SQL =====



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.



===== Risk of Data Corruption if Run Improperly =====

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



===== SQL =====



<code sql>
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;
</code>



===== Version Information =====
  * Entered : 12/02/2016
  * Version : 6.0+