SQLize Online / PHPize Online  /  SQLtest Online

USE [APADWMS] GO /****** Object: StoredProcedure [dbo].[ContainerPlanningDtl_PushNew1] Script Date: 18-05-2023 16:27:53 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[ContainerPlanningDtl_PushNew1] AS BEGIN TRAN BEGIN TRY INSERT INTO [APA-DWMS].dbo.ContainerPlanningDtl (ContainerId,linenumber,Saleorderno ,ItemId ,Partcode,Partdesc ,rate,Quantity,APAPallet,APAEquivalent,noofpallet,totalamount,versionid, pickedqty,Rejectedqty,shortqty,remarks,packinghrs,SoRemarks,PlannedWorkingHr,NrmlWrkStrtTime,NrmlWrkEndTime,NrmWrkDuration,OTStartTime, OTEndTime,OT_Duration,Picking_start,PickingEnd,PickingDuration,WorkerName,salary,Person_cost,PerKitCost,CusLabourCost, NoPersons,Flag,PickingFlag,PickingPlanQty) SELECT C.ContainerId,C.linenumber,C.Saleorderno ,I.ItemId,C.Partcode ,C.Partdesc ,C.rate,C.Quantity,C.APAPallet,C.APAEquivalent,C.noofpallet,C.totalamount, C.Versionid,C.pickedqty,C.Rejectedqty,C.shortqty,C.remarks,C.packinghrs,C.SoRemarks,C.PlannedWorkingHr,C.NrmlWrkStrtTime,C.NrmlWrkEndTime, C.NrmWrkDuration,C.OTStartTime,C.OTEndTime,C.OT_Duration,C.Picking_start,C.PickingEnd,C.PickingDuration,C.WorkerName,C.salary,C.Person_cost, C.PerKitCost,C.CusLabourCost,C.NoPersons,1,1,0 -- select * FROM ContainerPlanningDtl C JOIN [APA-DWMS].dbo.ItemMaster I ON C.Partcode = I.ItemCode WHERE C.Flag = 0 AND C.versionid = (select MAX(B.versionid) from [APA-DWMS].dbo.ContainerPlanningHdr B WHERE B.newid = C.ContainerId) AND NOT EXISTS (SELECT * FROM [APA-DWMS].dbo.ContainerPlanningDtl B WHERE C.ContainerId= B.ContainerId AND C.Partcode = B.Partcode AND C.Saleorderno = B.Saleorderno AND C.linenumber = B.linenumber ) ORDER BY tblid DESC; update [APA-DWMS].DBO.ContainerPlanningDtl set ContainerId = C.ContainerId,linenumber = C.linenumber,Saleorderno = C.Saleorderno ,ItemId = I.ItemId, Partcode = C.Partcode ,Partdesc = C.Partdesc ,rate = C.rate,Quantity = C.Quantity,APAPallet = C.APAPallet,APAEquivalent = C.APAEquivalent, noofpallet = C.noofpallet,totalamount = C.totalamount,versionid = C.versionid,pickedqty = C.pickedqty ,Rejectedqty = C.Rejectedqty, shortqty = C.shortqty,remarks = C.remarks,packinghrs = C.packinghrs,SoRemarks = C.SoRemarks,PlannedWorkingHr = C.PlannedWorkingHr, NrmlWrkStrtTime = C.NrmlWrkStrtTime,NrmlWrkEndTime = C.NrmlWrkEndTime,NrmWrkDuration = C.NrmWrkDuration,OTStartTime = C.OTStartTime, OTEndTime = C.OTEndTime,OT_Duration = C.OT_Duration,Picking_start = C.Picking_start,PickingEnd = C.PickingEnd,PickingDuration = C.PickingDuration, WorkerName = C.WorkerName,salary = C.salary,Person_cost = C.Person_cost,PerKitCost = C.PerKitCost,CusLabourCost = C.CusLabourCost, NoPersons = C.NoPersons,Status = CASE WHEN C.ConStatus = 1 THEN 1 WHEN C.ConStatus = 2 THEN 2 ELSE 4 END -- select * FROM ContainerPlanningDtl C JOIN [APA-DWMS].dbo.ItemMaster I ON C.Partcode = I.ItemCode JOIN [APA-DWMS].dbo.ContainerPlanningDtl C1 ON C1.ContainerId= C.ContainerId AND C1.Partcode = C.Partcode AND C1.Saleorderno = C.Saleorderno AND C1.linenumber = C.linenumber WHERE C.Flag = 0 AND C.versionid = (select MAX(B.versionid) from [APA-DWMS].dbo.ContainerPlanningHdr B WHERE B.newid = C.ContainerId) AND NOT EXISTS (SELECT * FROM [APA-DWMS].dbo.ContainerPlanningDtl B WHERE C.ContainerId= B.ContainerId AND C.Partcode = B.Partcode AND C.Saleorderno = B.Saleorderno AND C.linenumber = B.linenumber ) UPDATE [APADWMS].DBO.ContainerPlanningDtl SET [APADWMS].DBO.ContainerPlanningDtl.Remark='Completed',Flag=1 --select * FROM ContainerPlanningDtl CH JOIN [APA-DWMS].dbo.ContainerPlanningHdr C on C.newid = CH.ContainerId JOIN [APA-DWMS].dbo.ItemMaster I on I.ItemCode = CH.Partcode where CH.Flag=0 UPDATE [APADWMS].DBO.ContainerPlanningDtl SET [APADWMS].DBO.ContainerPlanningDtl.Remark='Invalid Container Hdr' FROM ContainerPlanningDtl CH LEFT OUTER JOIN [APA-DWMS].dbo.ContainerPlanningHdr C on C.newid = CH.ContainerId WHERE C.newid IS NULL UPDATE [APADWMS].DBO.ContainerPlanningDtl SET [APADWMS].DBO.ContainerPlanningDtl.Remark='Invalid Item' FROM ContainerPlanningDtl CH LEFT OUTER JOIN [APA-DWMS].dbo.ItemMaster I on I.ItemCode = CH.Partcode WHERE I.ItemId IS NULL SELECT 'ok' as msg COMMIT TRAN END try BEGIN CATCH select ERROR_MESSAGE() as msg; ROLLBACK TRANSACTION END CATCH; -- EXEC [dbo].[ContainerPlanningDtl_PushNew1] -- update ContainerPlanningDtl set Flag = 0 , Remark = '' -- SELECT * FROM ContainerPlanningDtl -- SELECT * FROM [APA-DWMS].DBO.ContainerPlanningDtl -- delete [APA-DWMS].dbo.ContainerPlanningDtl DBCC IDENT (ContainerPlanningDtl,RESEED,0)