CREATE PROCEDURE  sp_ProjectTransitionToFtrToM  AS

--- Create Temporary Table

CREATE TABLE #ProjectTransToFtrToM (                      

            [MarID]  [int] NOT NULL ,

            [ModID]   [int] NOT NULL ,

            [FtrID]     [int] NOT NULL ,

            [ProjID_S]  [int] NOT NULL ,

            [RPO]  [varchar] (50) NULL ,

            [Adv]  [varchar] (50) NULL ,

            [SOA]  [varchar] (21) NULL ,

            [RevDate]  [datetime] NULL ,

            [Notes]        [text] NULL ,

            [DTQualified]  [bit] NULL ,

            [Flag] [varchar] (37) NULL ,

            [LastUser] [varchar] (37) NULL  )

ON [PRIMARY]

ALTER TABLE #ProjectTransToFtrToM WITH NOCHECK ADD

            CONSTRAINT [PK_ProjectTransToFtrToM] PRIMARY KEY  NONCLUSTERED (

                        [MarID],

                        [ModID],

                        [FtrID],

                        [ProjID_S]  )

 CREATE INDEX [FtrID]    ON #ProjectTransToFtrToM(FtrID)

 CREATE INDEX [MarID] ON #ProjectTransToFtrToM(MarID)

 CREATE INDEX [ModID]  ON #ProjectTransToFtrToM(ModID)

 CREATE INDEX [ProjID_S] ON #ProjectTransToFtrToM(ProjID_S)

-- Append All Features from the Project Criteria

INSERT #ProjectTransToFtrToM

     ( MarID, ModID, FtrID, ProjID_S, RPO, Adv, SOA, RevDate, Notes, DTQualified, Flag, LastUser )

SELECT tblFtrToM.MarID, tblFtrToM.ModID, tblFtrToM.FtrID,

       9999 AS ProjID_S, tblFtrToM.RPO, tblFtrToM.Adv, tblFtrToM.SOA,

       tblProjectTransitionCriteria.RevDate, tblFtrToM.Notes, tblFtrToM.DTQualified,

       'ProjID' + convert(varchar(5),tblProjectTransitionCriteria.ProjID) AS Flag, tblProjectTransitionCriteria.LastUser

FROM tblFtrToM INNER JOIN

     tblProjectTransitionCriteria ON

     tblFtrToM.ProjID_S = tblProjectTransitionCriteria.ProjID

ORDER BY tblFtrToM.MarID, tblFtrToM.ModID, tblFtrToM.FtrID

-- Update Existing Records in FtrToM . . . Avail & Adv with Project Info

UPDATE tblFtrToM

SET tblFtrToM.Adv = #ProjectTransToFtrToM.Adv,

    tblFtrToM.SOA = #ProjectTransToFtrToM.SOA,

    tblFtrToM.RevDate = #ProjectTransToFtrToM.RevDate,

    tblFtrToM.Flag = #ProjectTransToFtrToM.Flag,

    tblFtrToM.LastUser = #ProjectTransToFtrToM.LastUser

FROM tblFtrToM INNER JOIN

   #ProjectTransToFtrToM ON

   #ProjectTransToFtrToM.ProjID_S = tblFtrToM.ProjID_S AND

   #ProjectTransToFtrToM.FtrID = tblFtrToM.FtrID             AND

   #ProjectTransToFtrToM.ModID = tblFtrToM.ModID  AND

   #ProjectTransToFtrToM.MarID = tblFtrToM.MarID

-- LEFT JOIN will Check for Existing Records before Appending

INSERT INTO tblFtrToM

     ( MarID, ModID, FtrID, ProjID_S, RPO, Adv, SOA, RevDate, Notes, DTQualified, Flag, LastUser )

SELECT #ProjectTransToFtrToM.MarID, #ProjectTransToFtrToM.ModID,

       #ProjectTransToFtrToM.FtrID, #ProjectTransToFtrToM.ProjID_S,

       #ProjectTransToFtrToM.RPO, #ProjectTransToFtrToM.Adv,

       #ProjectTransToFtrToM.SOA, #ProjectTransToFtrToM.RevDate,

       #ProjectTransToFtrToM.Notes, #ProjectTransToFtrToM.DTQualified,

       #ProjectTransToFtrToM.Flag, #ProjectTransToFtrToM.LastUser

FROM #ProjectTransToFtrToM LEFT JOIN

     tblFtrToM ON

     #ProjectTransToFtrToM.MarID = tblFtrToM.MarID AND

     #ProjectTransToFtrToM.ModID = tblFtrToM.ModID AND

     #ProjectTransToFtrToM.FtrID = tblFtrToM.FtrID AND

     #ProjectTransToFtrToM.ProjID_S = tblFtrToM.ProjID_S

WHERE -- #ProjectTransToFtrToM.FtrID < '400' AND

      tblFtrToM.MarID Is Null AND

      tblFtrToM.ModID Is Null AND

      tblFtrToM.FtrID Is Null AND

      tblFtrToM.ProjID_S Is Null

ORDER BY #ProjectTransToFtrToM.MarID, #ProjectTransToFtrToM.ModID,

         #ProjectTransToFtrToM.FtrID