Wednesday, August 16, 2017

Stored proc for errorhandling understanding

/*---------------------------------------------------------------------------
stored Procedure name  :  USP_Load_PL_Tool
created by             :  Mattel  babji
craeted date           :  06/21/2017
Description            :  Loading ToolData Staging data(ToolData_Agile) to persistant table(PL_INTG_BOMData)
                           as per TDSL rules
parameters             :   @batchdate: we should pass datime as ssis package start time

----------------------------------------------------------------------------*/
--USE DMAX
--GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'USP_Load_PL_Tool')
DROP PROCEDURE USP_Load_PL_Tool
GO

CREATE PROCEDURE dbo.USP_Load_PL_Tool
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(4000)=''
DECLARE @mincnt int
DECLARE @maxcnt int
DECLARE @ToolNumber VARCHAR(50)=''

IF OBJECT_ID('tempdb..#Tooldata') IS NOT NULL
  DROP TABLE #Tooldata

CREATE TABLE #Tooldata
(   id INT IDENTITY(1,1),
[ToolNumber] varchar(50) NOT NULL,
[ToolDescription] varchar(120) NULL,
[ToolType] varchar(50) NULL,
[ToolClass] varchar(5) NULL,
[ToolLocation] varchar(100) NULL,
[ToolCavity] varchar(10) NULL,
[MfrName] varchar(255) NULL,
[MfrItemType] varchar(255) NULL,
[ToolStatus] varchar(10) NULL,
[ToolCapacity] decimal(9, 2) NULL,
[LatestCost] decimal(11, 2) NULL,
[LatestCostLastUsedDate] date NULL,
[TotalPartsPerTool] int NULL,
[CumulativeShot] bigint NULL,
[DataSource] varchar(10) NULL,
[isProcessed] varchar(1) NULL,
[MAXUpdateTime] datetime NULL
)

/*This stored proc will update the what the item's is using only one product then update PL_Tool table column isprocessed to 'D'
UI team will those item values isprocessed values to 'R',Then every run we should delete the isprocessed value 'R' from PL_Tool */

DELETE
FROM dbo.PL_Tool
WHERE isProcessed='R'

/*Check all Agile(PL_INTG_ToolAgile) ItemNumber exist in  the PL_Item table
,If Exist,leave
,If Does not exist,Insert ItemNumber data into PL_Item from PL_INTG_ToolAgile */

EXEC dbo.USP_Load_PL_Item_From_PersistentTbl @ItemType='ToolAgile'

INSERT INTO #Tooldata
(
ToolNumber,ToolDescription,ToolType,ToolClass,ToolLocation,ToolCavity,MfrName,MfrItemType,
ToolStatus,ToolCapacity,LatestCost,TotalPartsPerTool,CumulativeShot
)
SELECT
ToolNumber
,ISNULL(ToolDescription,'') AS ToolDescription
,ISNULL(ToolType,'') AS ToolType
,ISNULL(ToolClass,'') AS ToolClass
,ISNULL(ToolLocation,'') AS ToolLocation
,ISNULL(ToolCavity,'') AS ToolCavity
,ISNULL(MfrName,'') AS MfrName
,ISNULL(MfrItemType,'') AS MfrItemType
,'' AS ToolStatus
,0.00  AS ToolCapacity
,0.00 AS LatestCost
,0 AS TotalPartsPerTool
,0 AS CumulativeShot
FROM
(SELECT DISTINCT
 ToolNumber
,ToolDescription
,ToolType
,MfrName
,MfrItemType
,ToolCavity
,ToolClass
,ToolLocation
,ROW_NUMBER() OVER(PARTITION BY ToolNumber ORDER BY  t1.updatedtime DESC) As rownum
 FROM PL_INTG_ToolAgile t1 WITH(NOLOCK)
 INNER JOIN ProductData t2 WITH(NOLOCK)
 ON t1.ProductNumber=t2.ProductNumber
 INNER JOIN dbo.Pl_ProductDash t3
 ON t2.ProductNumber=t3.ProductNumber
 INNER JOIN PL_Item t4 WITH(NOLOCK)
 ON t1.ItemNumber=t4.AutoGenNbr
 --AND t1.ItemNumber=t4.ItemNumber
 WHERE t1.ToProcessToolAgile='Y'
 and t1.ToolNumber<>''
 --AND ToolNumber='00315-A501-01'
 ) AS ToolAgile WHERE rownum=1


SELECT @maxcnt=ISNULL(MAX(id),0) FROM #Tooldata
SET @mincnt=1
/* inser and upadte the PL_Tool table from PL_INTG_ToolAgile  with ToProcessToolAgile as "Y"  data*/
WHILE(@mincnt<=@maxcnt)
BEGIN
BEGIN TRY
SET @ToolNumber=''
SET @ErrorMessage=''
SELECT @ToolNumber=ToolNumber FROM #Tooldata where id=@mincnt

;MERGE dbo.[PL_Tool] AS T
USING
(
SELECT
 [ToolNumber]
,[ToolDescription]
,[ToolType]
,[MfrName]
,[MfrItemType]
,[ToolCavity]
,[ToolClass]
,[ToolLocation]
,[ToolStatus]
,[ToolCapacity]
,[LatestCost]
,[TotalPartsPerTool]
,[CumulativeShot]
,'AGILE' AS [DataSource]
,'N' AS [isProcessed]
,CURRENT_TIMESTAMP AS [MAXUpdateTime]
FROM #Tooldata
WHERE ToolNumber=@ToolNumber
) AS S
ON T.ToolNumber=S.ToolNumber

WHEN MATCHED THEN --AND T.DataSource='AGILE'
UPDATE
SET   T.[ToolType]=CASE WHEN T.DataSource='AGILE' THEN  S.[ToolType] ELSE T.[ToolType] END,
      T.[ToolClass]=CASE WHEN T.DataSource='AGILE' THEN  S.[ToolClass] ELSE  T.[ToolClass] END,
 T.[ToolDescription]=CASE WHEN T.DataSource='AGILE' THEN S.[ToolDescription] ELSE T.[ToolDescription] END,
 T.[MfrName]=CASE WHEN T.DataSource='AGILE' THEN S.[MfrName] ELSE T.[MfrName] END,
 T.[MfrItemType]=CASE WHEN T.DataSource='AGILE' THEN S.[MfrItemType] ELSE T.[MfrItemType] END ,

 T.[ToolLocation]= CASE WHEN T.[ToolLocation]='' AND T.DataSource='MIMICS' AND T.[CumulativeShot]=0 THEN S.[ToolLocation]
WHEN T.[ToolLocation]='' AND T.DataSource='AGILE'  THEN S.[ToolLocation]
                   ELSE T.[ToolLocation] END,

 T.[ToolCavity]= CASE WHEN T.[ToolCavity]='' AND T.[DataSource]='MIMICS' AND T.[CumulativeShot]=0 THEN S.[ToolCavity]
  WHEN T.[ToolCavity]='' AND T.[DataSource]='AGILE' THEN S.[ToolCavity]
                 ELSE  T.[ToolCavity] END,

      T.[ToolStatus]=ISNULL(T.[ToolStatus],S.[ToolStatus]),
 T.[ToolCapacity]=ISNULL(T.[ToolCapacity],0.00),
 T.LatestCost=ISNULL(T.[LatestCost],0.00),
 T.TotalPartsPerTool=ISNULL(T.TotalPartsPerTool,0),
 T.[CumulativeShot]=ISNULL(T.[CumulativeShot],0),
      T.[isProcessed]=S.[isProcessed],
 T.[MAXUpdateTime]=S.[MAXUpdateTime]
WHEN NOT MATCHED THEN
INSERT
(
 [ToolNumber]
,[ToolDescription]
,[ToolType]
,[ToolClass]
,[ToolLocation]
,[ToolCavity]
,[MfrName]
,[MfrItemType]
,[DataSource]
,[ToolStatus]
,[ToolCapacity]
,[LatestCost]
,[TotalPartsPerTool]
,[CumulativeShot]
,[isProcessed]
,[MAXUpdateTime]
)
VALUES
(
 S.[ToolNumber]
,S.[ToolDescription]
,S.[ToolType]
,S.[ToolClass]
,S.[ToolLocation]
,S.[ToolCavity]
,S.[MfrName]
,S.[MfrItemType]
,S.[DataSource]
,S.[ToolStatus]
,S.[ToolCapacity]
,S.[LatestCost]
,S.[TotalPartsPerTool]
,S.[CumulativeShot]
,S.[isProcessed]
,S.[MAXUpdateTime]
);
END TRY
BEGIN CATCH
SET @ErrorMessage =@ErrorMessage+'Sp_Name:USP_Load_PL_Tool getting Error ToolNumber is:'+ISNULL(CAST(@ToolNumber AS NVARCHAR(100)),'')+ERROR_MESSAGE();
INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT
'USP_Load_PL_Tool' AS [SPName]
,'' As [ItemNumber]
,'' AS [AutoGenNbr]
,'' AS [ProductNumber]
,'' As [dashCode]
,@ErrorMessage AS [Comments]
,GETDATE() As [Todays_date]          

END CATCH
SET @mincnt=@mincnt+1
END

/* After insert/update the   PL_Tool Agile data then check PL_Tool combination data (ProductNumber,ToolNumber,AutoGenNbr) exist or not exist in PL_ProdItemToolRel
if exist leave as it as,if does not exist insert into PL_ProdItemToolRel table */
MERGE PL_ProdItemToolRel AS T
USING (
SELECT
[ProductNumber],
[ToolNumber],
[AutoGenNbr]
FROM
(
SELECT DISTINCT
 t1.[ProductNumber],
 t1.[ToolNumber],
 t4.[AutoGenNbr],
 ROW_NUMBER() OVER(PARTITION BY t1.[ProductNumber],t1.[ToolNumber], t3.[AutoGenNbr] order by  t3.[AutoGenNbr]) as rownum
FROM dbo.PL_INTG_ToolAgile AS t1 WITH(NOLOCK)
INNER HASH JOIN (SELECT DISTINCT ProductNumber FROM dbo.ProductData) AS t2
ON t1.ProductNumber=t2.ProductNumber
INNER JOIN PL_ProductDash t3 WITH(NOLOCK)
ON  t3.ProductNumber=t2.ProductNumber
INNER JOIN dbo.PL_ITEM AS t4 WITH(NOLOCK)
ON t1.ItemNumber=t4.AutoGenNbr
--AND t3.ItemNumber=t4.ItemNumber
--AND t3.AutoGenNbr=t4.AutoGenNbr
INNER JOIN PL_Tool as t5 WITH(NOLOCK)
ON t1.ToolNumber=t5.ToolNumber
WHERE t1.ToProcessToolAgile='Y'
AND t5.isProcessed='N'
--t4.[isProcessed]='N'


) As tool where rownum=1
) AS S
ON T.[ProductNumber]=S.[ProductNumber]
AND T.[ToolNumber]=S.[ToolNumber]
AND T.[ItemAutoGenNbr]=S.[AutoGenNbr]
WHEN NOT MATCHED THEN
INSERT
(
[ProductNumber],
[ToolNumber],
[ItemAutoGenNbr]
)
VALUES
(
S.[ProductNumber],
S.[ToolNumber],
S.[AutoGenNbr]
);


 /* Update the processed ToolNUmber's in agile persistent table (PL_INTG_ToolAgile) column ToProcessToolAgile to 'N'*/
 UPDATE t1
 SET ToProcessToolAgile='N'
 FROM dbo.PL_INTG_ToolAgile t1 WITH(ROWLOCK)
 INNER JOIN ProductData t2
 ON t1.ProductNumber=t2.ProductNumber
 INNER JOIN PL_ProductDash t3
 ON t2.ProductNumber=t3.ProductNumber
 AND t1.ToProcessToolAgile='Y'
 INNER JOIN dbo.[PL_Tool] t4 WITH(NOLOCK)
 ON t1.ToolNumber=t4.ToolNumber
 --AND t4.isProcessed='N'

 /* Agile ProductNumber ErrorLog */
 INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT DISTINCT
'USP_Load_PL_Tool' AS [SPName]
,'' AS [ItemNumber]
,'' AS [AutoGenNbr]
,[ProductNumber]
,''  AS [dashCode]
,'AGILE ProductData Check: ProductNumber ('+[ProductNumber]+') Does Not Exist In ProductData Lookup Table' AS [Comments]
,GETDATE() As [Todays_date]
FROM PL_INTG_ToolAgile WITH(NOLOCK)
WHERE  ProductNumber NOT IN ( SELECT DISTINCT ProductNumber FROM ProductData)
AND  ToProcessToolAgile='Y'


--- Product Dash Error Log
 INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT DISTINCT
'USP_Load_PL_Tool' AS [SPName]
,'' AS [ItemNumber]
,'' AS [AutoGenNbr]
,[ProductNumber]
,''  AS [dashCode]
,'AGILE ProductDash Check: ProductNumber ('+[ProductNumber]+' ) Exist In ProductData And Does Not exist in PL_ProductDash Lookup Table' AS [Comments]
,GETDATE() As [Todays_date]
FROM PL_INTG_ToolAgile WITH(NOLOCK)
WHERE  ProductNumber IN ( SELECT DISTINCT ProductNumber FROM ProductData)
AND ProductNumber NOT IN( SELECT DISTINCT ProductNumber FROM PL_ProductDash)
AND  ToProcessToolAgile='Y'



------------------------------------------------------------------------------------------------------------------------------------

/*Check all MIMICS ItemNumber exist in  the PL_Item table
,If Exist,leave
,If Does not exist,Insert ItemNumber data into PL_Item from PL_INTG_ToolMIMCS */

EXEC USP_Load_PL_Item_From_PersistentTbl @ItemType='ToolMIMICS'

-->cleanup the agile data from temp table
TRUNCATE TABLE #Tooldata

INSERT INTO #Tooldata
(
ToolNumber,ToolDescription,ToolType,ToolClass,ToolLocation,ToolCavity,MfrName,MfrItemType,
ToolStatus,ToolCapacity,LatestCost,[LatestCostLastUsedDate],TotalPartsPerTool,CumulativeShot
)
SELECT
[ToolNumber]
,'' AS ToolDescription
,'' As ToolType
,'' As ToolClass
,ISNULL([ToolLocation],'') AS [ToolLocation]
,ISNULL([ToolCavity],'') AS [ToolCavity]
,'' AS MfrName
,'' AS MfrItemType
,ISNULL([ToolStatus],'') AS [ToolStatus]
,isnull([ToolCapacity],0.00) as [ToolCapacity]
,isnull([LatestCost],0.00) as [LatestCost]
,[LatestCostLastUsedDate]
,isnull([TotalPartsPerTool],0) as [TotalPartsPerTool]
,isnull([CumulativeShot],0) as [CumulativeShot]
FROM (
SELECT DISTINCT
 [ToolNumber]
,[ToolStatus]
,[ToolLocation]
,[ToolCavity]
,[TotalPartsPerTool]
,[ToolCapacity]
,[CumulativeShot]
,[LatestCost]
,[LatestCostLastUsedDate]
,T1.UpdatedTime
,ROW_NUMBER() OVER(PARTITION BY ToolNumber ORDER BY  t1.updatedtime desc) As rownum
 FROM PL_INTG_ToolMIMICS t1 WITH(NOLOCK)
 INNER HASH JOIN Productdata AS t2
 ON t1.ProductNumber=t2.ProductNumber
 AND t1.ToProcessToolMIMICS='Y'
 INNER JOIN dbo.Pl_ProductDash t3 WITH(NOLOCK)
 ON t2.ProductNumber=t3.ProductNumber
 INNER JOIN PL_Item t4 WITH(NOLOCK)
 ON t1.ItemNumber=t4.AutoGenNbr
 WHERE t1.ToolNumber<>''
) AS ToolMIMICS WHERE RowNum=1
--AND ToolNumber='00315-B511-01'


--select * from PL_INTG_ToolMIMICS where ToolNumber='00315-B511-01'
SELECT @maxcnt=ISNULL(MAX(id),0) FROM #Tooldata
SET @mincnt=1
/* INser and upadte the PL_Tool table from PL_INTG_ToolMIMCS with ToProcessToolAgile as "Y"  data*/
WHILE(@mincnt<=@maxcnt)
BEGIN
BEGIN TRY
SET @ToolNumber=''
SET @ErrorMessage=''
SELECT @ToolNumber=ToolNumber from #Tooldata where id=@mincnt
;MERGE dbo.[PL_Tool] As T
USING
(
SELECT DISTINCT
  [ToolNumber]
 ,[ToolStatus]
 ,[ToolLocation]
 ,[ToolCavity]
 ,[TotalPartsPerTool]
 ,[ToolCapacity]
 ,[CumulativeShot]
 ,[LatestCost]
 ,[LatestCostLastUsedDate]
 ,[MfrName]
 ,[MfrItemType]
 ,[ToolClass]
 ,[ToolType]
 ,[ToolDescription]
,'MIMICS' AS [DataSource]
,'N' AS [isProcessed]
,CURRENT_TIMESTAMP AS [MAXUpdateTime]
FROM  #Tooldata
WHERE ToolNumber=@ToolNumber
) AS S
ON T.ToolNumber=S.ToolNumber
WHEN MATCHED THEN
UPDATE SET T.[ToolStatus]=S.[ToolStatus],
           T.[ToolLocation]= CASE WHEN  S.[CumulativeShot]>0 THEN S.[ToolLocation]
 WHEN  S.[CumulativeShot]=0 and (S.[ToolLocation] IS NOT NULL or S.[ToolLocation] != '') THEN S.[ToolLocation]
                         ELSE ISNULL(T.[ToolLocation],'')  END,
           T.[ToolCavity]= CASE WHEN S.[CumulativeShot]>0 THEN S.[ToolCavity]
WHEN S.[CumulativeShot]=0 and (S.[ToolCavity]  IS NOT NULL or S.[ToolCavity]  != '') THEN S.[ToolCavity]
                       ELSE  ISNULL(T.[ToolCavity],'')  END,
           T.[TotalPartsPerTool]=S.[TotalPartsPerTool],
  T.[ToolCapacity]=S.[ToolCapacity],
  T.[CumulativeShot]=S.[CumulativeShot],
  T.[LatestCost]=S.[LatestCost],
  T.[LatestCostLastUsedDate]=S.[LatestCostLastUsedDate],
  T.[MfrName]=ISNULL(T.[MfrName],S.[MfrName]),
  T.[MfrItemType]=ISNULL(T.[MfrItemType],S.[MfrItemType]),
  T.[ToolClass]=ISNULL(T.[ToolClass],S.[ToolClass]),
  T.[ToolType]=ISNULL(T.[ToolType],S.[ToolType]),
  T.[ToolDescription]=ISNULL(T.[ToolDescription],S.[ToolDescription]),
  T.[DataSource]=S.[DataSource],
  T.[isProcessed]=S.[isProcessed],
  T.[MAXUpdateTime]=S.[MAXUpdateTime]

WHEN NOT MATCHED THEN
INSERT
(
  [ToolNumber]
 ,[ToolStatus]
 ,[ToolLocation]
 ,[ToolCavity]
 ,[TotalPartsPerTool]
 ,[ToolCapacity]
 ,[CumulativeShot]
 ,[LatestCost]
 ,[LatestCostLastUsedDate]
 ,[MfrName]
 ,[MfrItemType]
 ,[ToolClass]
 ,[ToolType]
 ,[ToolDescription]
 ,[DataSource]
 ,[isProcessed]
 ,[MAXUpdateTime]
)
VALUES
(
  S.[ToolNumber]
 ,S.[ToolStatus]
 ,S.[ToolLocation]
 ,S.[ToolCavity]
 ,S.[TotalPartsPerTool]
 ,S.[ToolCapacity]
 ,S.[CumulativeShot]
 ,S.[LatestCost]
 ,S.[LatestCostLastUsedDate]
 ,S.[MfrName]
 ,S.[MfrItemType]
 ,S.[ToolClass]
 ,S.[ToolType]
 ,S.[ToolDescription]
 ,S.[DataSource]
 ,S.[isProcessed]
 ,S.[MAXUpdateTime]
);
END TRY
BEGIN CATCH
SET @ErrorMessage =@ErrorMessage+'Sp_Name:USP_Load_PL_Tool getting Error on MIMICS Tool is:'+ISNULL(CAST(@ToolNumber AS NVARCHAR(100)),'')+ERROR_MESSAGE();
INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT
'USP_Load_PL_Tool' AS [SPName]
,@ToolNumber As [ItemNumber]
,'' AS [AutoGenNbr]
,'' AS [ProductNumber]
,'' As [dashCode]
,@ErrorMessage AS [Comments]
,GETDATE() As [Todays_date]          

END CATCH
SET @mincnt=@mincnt+1
END

/* After insert/update the   PL_Tool MIMICS data then check PL_Tool combination data (ProductNumber,ToolNumber,AutoGenNbr) exist or not exist in PL_ProdItemToolRel
if exist leave as it as,if does not exist insert into PL_ProdItemToolRel table FROM PL_INTG_ToolMIMICS*/


MERGE dbo.PL_ProdItemToolRel AS T
USING (
SELECT
[ProductNumber],
[ToolNumber],
[AutoGenNbr]
FROM
(
SELECT DISTINCT
 t1.[ProductNumber],
 t1.[ToolNumber],
 t1.ItemNumber AS [AutoGenNbr],
 ROW_NUMBER() OVER(PARTITION BY t1.[ProductNumber],t1.[ToolNumber], t1.ItemNumber order by  t1.ItemNumber) as rownum
FROM dbo.PL_INTG_ToolMIMICS AS t1 WITH(NOLOCK)
INNER JOIN (SELECT DISTINCT ProductNumber FROM dbo.ProductData) AS t2
ON t1.ProductNumber=t2.ProductNumber
AND t1.ToProcessToolMIMICS='Y'
INNER JOIN PL_ProductDash t3 WITH(NOLOCK)
ON  t3.ProductNumber=t2.ProductNumber
INNER JOIN dbo.PL_ITEM AS t4 WITH(NOLOCK)
ON t1.ItemNumber=t4.AutoGenNbr
--AND t4.[isProcessed]='N'
INNER JOIN dbo.PL_Tool as t5 WITH(NOLOCK)
ON t1.ToolNumber=t5.ToolNumber
WHERE t5.isProcessed='N'
--AND t1.DeleteStatus<>'Y'
) As tool where rownum=1
) AS S
ON T.[ProductNumber]=S.[ProductNumber]
AND T.[ToolNumber]=S.[ToolNumber]
AND T.[ItemAutoGenNbr]=S.[AutoGenNbr]
WHEN NOT MATCHED THEN
INSERT
(
[ProductNumber],
[ToolNumber],
[ItemAutoGenNbr]
)
VALUES
(
S.[ProductNumber],
S.[ToolNumber],
S.[AutoGenNbr]
);


-------------------------------------------------------------------------------------------------------------------------------
-- • Select the records from PL_INTG_ToolMIMICS table where ToProcessToolMIMICS column is ‘Y’ AND DeleteStatus column is ‘Y’
--1. Lookup PL_ProdItemToolRel table for the combination, ProductNumber, AutoGenNbr and ToolNumber. Get the AutoGenNbr for the specific item from PL_Item table.
--If data exists delete the row.
--2. Check for the same Tool number exists for any other Product Item combination in PL_Tool table
-- If yes, leave as-is
-- If no,  mark the isProcessed column as ‘D’
--• Select the records from PL_Tool table where isProcessed column is ‘R’
--1. Delete the rows marked as ‘D’  in isProcessed column. This means MediaBeacon has removed the respective data, so we can delete the entry from PL_Tool table

;WITH CTE_Tooldata_Update As
(
SELECT t2.ToolNumber,count(*) As cnt
 FROM dbo.PL_INTG_ToolMIMICS t1 WITH(NOLOCK)
 INNER JOIN PL_ProdItemToolRel t2 WITH(NOLOCK)
 ON t1.ToolNUmber=t2.ToolNumber
 INNER JOIN PL_ITEM t3 WITH(NOLOCK)
 ON t2.ItemAutoGenNbr=t3.AutoGenNbr
 WHERE ToProcessToolMIMICS='Y'
 AND DeleteStatus='Y'
 GROUP BY t2.ToolNumber HAVING COUNT(*)=1
 )
 UPDATE t1
 SET isProcessed='D'
 FROM PL_Tool t1 WITH(ROWLOCK)
 JOIN  PL_ProdItemToolRel t2 WITH(NOLOCK)
 ON t1.ToolNumber=t2.ToolNumber
 INNER JOIN CTE_Tooldata_Update sub
 ON t2.ToolNumber=sub.ToolNumber


 --select * from PL_ProdItemToolRel where ToolNumber='FGG71-S509-01'

;WITH CTE_ToolRelation_Delete As
(
SELECT t2.ItemAutoGenNbr,count(*) As cnt
 FROM dbo.PL_INTG_ToolMIMICS t1 WITH(NOLOCK)
 INNER JOIN PL_ProdItemToolRel t2 WITH(NOLOCK)
 ON t1.ProductNumber=t2.ProductNumber
 AND t1.ItemNumber=t2.ItemAutoGenNbr
 AND t1.ToolNUmber=t2.ToolNumber
 INNER JOIN PL_ITEM t3 WITH(NOLOCK)
 ON t2.ItemAutoGenNbr=t3.AutoGenNbr
 WHERE t1.ToProcessToolMIMICS='Y'
 AND DeleteStatus='Y'
 GROUP BY t2.ItemAutoGenNbr HAVING COUNT(*)=1
 )
 DELETE main
 FROM PL_ProdItemToolRel main WITH(ROWLOCK)
 INNER JOIN CTE_ToolRelation_Delete sub
 ON main.ItemAutoGenNbr=sub.ItemAutoGenNbr



 /* Update the processed ToolNUmber's in MIMICS persistent table (PL_INTG_ToolMIMICS) column ToProcessToolAgile to 'N'*/

 UPDATE t1
 SET ToProcessToolMIMICS='N'
 FROM dbo.PL_INTG_ToolMIMICS t1 WITH(ROWLOCK)
 INNER JOIN ProductData t2 WITH(NOLOCK)
 ON t1.ProductNumber=t2.ProductNumber
 INNER JOIN PL_ProductDash t3 WITH(NOLOCK)
 ON t2.ProductNumber=t3.ProductNumber
 AND t1.ToProcessToolMIMICS='Y'
 INNER JOIN dbo.[PL_Tool] t4 WITH(NOLOCK)
 ON t1.ToolNumber=t4.ToolNumber
 --AND t4.isProcessed='N'


 /* MIMICS ProductNumber Error Log */

 INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT DISTINCT
'USP_Load_PL_Tool' AS [SPName]
,'' AS [ItemNumber]
,'' AS [AutoGenNbr]
,[ProductNumber]
,''  AS [dashCode]
,'MIMICS ProductData Check: ProductNumber ('+[ProductNumber]+') Does Not Exist In ProductData Lookup Table' AS [Comments]
,GETDATE() As [Todays_date]
FROM PL_INTG_ToolMIMICS WITH(NOLOCK)
WHERE  ProductNumber NOT IN ( SELECT DISTINCT ProductNumber FROM ProductData)
AND  ToProcessToolMIMICS='Y'
--AND DeleteStatus<>'Y'

--- Product Dash Error Log
 INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT DISTINCT
'USP_Load_PL_Tool' AS [SPName]
,'' AS [ItemNumber]
,'' AS [AutoGenNbr]
,[ProductNumber]
,''  AS [dashCode]
,'MIMICS ProductDash Check: ProductNumber ('+[ProductNumber]+') Exist In ProductData And Does Not exist in PL_ProductDash Lookup Table' AS [Comments]
,GETDATE() As [Todays_date]
FROM PL_INTG_ToolMIMICS WITH(NOLOCK)
WHERE  ProductNumber IN ( SELECT DISTINCT ProductNumber FROM ProductData)
AND ProductNumber NOT IN( SELECT DISTINCT ProductNumber FROM PL_ProductDash)
AND  ToProcessToolMIMICS='Y'


IF OBJECT_ID('tempdb..#Tooldata') IS NOT NULL
  DROP TABLE #Tooldata

END TRY
BEGIN CATCH
SET @ErrorMessage =@ErrorMessage+'Sp_Name:USP_Load_PL_Tool getting Error on:'+ERROR_MESSAGE();
INSERT INTO dbo.[PL_ErrorLog]
(
[SPName]
,[ItemNumber]
,[AutoGenNbr]
,[ProductNumber]
,[dashCode]
,[Comments]
,[UpdatedTime]
)
SELECT
'USP_Load_PL_Tool' AS [SPName]
,'' As [ItemNumber]
,'' AS [AutoGenNbr]
,'' AS [ProductNumber]
,'' As [dashCode]
,@ErrorMessage AS [Comments]
,GETDATE() As [Todays_date]          

END CATCH
SET NOCOUNT OFF;
END