Transition A Proj To A Ser

CREATE PROCEDURE sp_ProjTransition_Build  AS

DELETE      tbl_tmpProjTransitionToSer

FROM         tbl_tmpProjTransitionToSer

INSERT INTO  tbl_tmpProjTransitionToSer ( SerID, ProjID, FtrID, FtrToProjComment, FtPValueStory,

                                                                           FtPValueRevDate, RevDate, LastUser )

SELECT     tblProjTransitionCriteria.SerID, tblProjTransitionCriteria.ProjID,

                   tblFtrToProj.FtrID, tblFtrToProj.FtrToProjComment,

                   tblFtrToProj.FtPValueStory, tblFtrToProj.FtPValueRevDate,

                   tblProjTransitionCriteria.RevDate, tblProjTransitionCriteria.LastUser

FROM         tblProjTransitionCriteria INNER JOIN

                  tblFtrToProj ON

                  tblProjTransitionCriteria.ProjID = tblFtrToProj.ProjID

ORDER BY tblProjTransitionCriteria.SerID, tblFtrToProj.FtrID;

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

CREATE PROCEDURE sp_ProjTransition_Overwrite  AS

UPDATE tblFtrToSer

SET    tblFtrToSer.SerComment = tbl_tmpProjTransitionToSer.FtrToProjComment,

          tblFtrToSer.SerValueStory = convert(varchar(8000),ISNULL(tbl_tmpProjTransitionToSer.FtPValueStory,' '))

                                                                            + '  ' + convert(varchar(8000),ISNULL(tblFtrToSer.SerValueStory,' ')),

          tblFtrToSer.SerValueRevDate = (SELECT FtPValueRevDate =

                                                                      CASE

                                                                           WHEN FtPValueRevDate > SerValueRevDate

                                                                           THEN FtPValueRevDate

                                                                           ELSE SerValueRevDate

                                                                       END

                                                                   ),

          tblFtrToSer.SerRevDate = tbl_tmpProjTransitionToSer.RevDate,

          tblFtrToSer.LastUser = tbl_tmpProjTransitionToSer.LastUser

FROM tblFtrToSer INNER JOIN

    tbl_tmpProjTransitionToSer ON

    tblFtrToSer.SerID = tbl_tmpProjTransitionToSer.SerID AND

    tblFtrToSer.FtrID = tbl_tmpProjTransitionToSer.FtrID

SELECT  @@ROWCOUNT AS Overwrite

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

CREATE PROCEDURE sp_ProjTransition_Append_New AS

INSERT INTO tblFtrToSer ( SerID, FtrID, SerComment, SerValueStory, SerValueRevDate, SerRevDate, LastUser )

SELECT tbl_tmpProjTransitionToSer.SerID, tbl_tmpProjTransitionToSer.FtrID,

       tbl_tmpProjTransitionToSer.FtrToProjComment, tbl_tmpProjTransitionToSer.FtPValueStory,

       tbl_tmpProjTransitionToSer.FtPValueRevDate, tbl_tmpProjTransitionToSer.RevDate,

       tbl_tmpProjTransitionToSer.LastUser

FROM tbl_tmpProjTransitionToSer LEFT JOIN

     tblFtrToSer ON

     tbl_tmpProjTransitionToSer.FtrID = tblFtrToSer.FtrID AND

     tbl_tmpProjTransitionToSer.SerID = tblFtrToSer.SerID

WHERE

    (tblFtrToSer.SerID Is Null) AND

    (tblFtrToSer.FtrID Is Null)

ORDER BY tbl_tmpProjTransitionToSer.SerID, tbl_tmpProjTransitionToSer.FtrID;

SELECT  @@ROWCOUNT AS Appended_New

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

CREATE PROCEDURE sp_ProjTransition_Append_VS  AS

UPDATE tblFtrToSer

SET    tblFtrToSer.SerValueStory = convert(varchar(8000),ISNULL(tbl_tmpProjTransitionToSer.FtPValueStory,' '))

                                                                            + '  ' + convert(varchar(8000),ISNULL(tblFtrToSer.SerValueStory,' ')),

          tblFtrToSer.SerValueRevDate = (SELECT FtPValueRevDate =

                                                                      CASE

                                                                           WHEN FtPValueRevDate > SerValueRevDate

                                                                           THEN FtPValueRevDate

                                                                           ELSE SerValueRevDate

                                                                       END

                                                                   ),

          tblFtrToSer.SerRevDate = tbl_tmpProjTransitionToSer.RevDate,

          tblFtrToSer.LastUser = tbl_tmpProjTransitionToSer.LastUser

FROM tblFtrToSer INNER JOIN

           tbl_tmpProjTransitionToSer ON

           tblFtrToSer.SerID = tbl_tmpProjTransitionToSer.SerID AND

           tblFtrToSer.FtrID = tbl_tmpProjTransitionToSer.FtrID

SELECT  @@ROWCOUNT AS Appended_VS

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _