Result Set 3 - Product Changes

The following table lists the parameters used in a Product Change Result Set.

Order	Field Name	Req	Values/Table Reference
0	Result Set Type	X	3
1	Start Id	X	Production_Starts (Start_Id)
2	Production Unit Id	X	Production_Starts (PU_Id) Prod_Units (PU_Id)
3	Product Id	X	Production_Starts (Prod_Id) Products (Prod_Id)
4	Start Time	X	Production_Starts (Start_Time)
5	Update Type	X	0 – Pre Update. Database Manager writes to database and sends to client. 1 – Post Update. Data already in database and sent to clients only.
6	User Id		Production_Starts (User_Id) Users (User_Id) Allows a specific User_Id to be used and tracked. If not using a specific User_Id account then set it to 0 and the appropriate Service User_Id will be used.
7	Second User Id
8	Transaction Type
9	ESignature		Signature ID from the ESignature table

--Declare the SQL variables used

Declare

@CurrentProd_Id int,

@NewProd_Id int,

@NewPCTime datetime,

@PCStart_Id int,

@PCPU_Id int,

@PCProd_Id int,

@PCStart_Time datetime,

@PCPostUpdate int

-------------Product Change Logic----------------------

--Retrieve Current Product being run on a Unit

Select @CurrentProd_Id = Prod_Id From Production_Starts

Where (PU_Id = @PU_Id) And

(Start_Time < @EventTimestamp) And

((End_Time >= @EventTimestamp) Or (End_Time Is Null))

--Check to see if the Product is new

If @NewProd_Id <> @CurrentProd_Id

Begin

Select @PrevEventTimeStamp = Timestamp

From Events Where (PU_Id = @PU_Id) And (Timestamp = (Select Max(Timestamp) From Events

Where (PU_Id = @PU_Id) and (Timestamp < @EventTimestamp)))

--Set the Product Change Time to 1 Minute after the Previous Event Time

Select @NewPCTime = DateAdd(mi,1,@PrevEventTimeStamp)

Create Table #ProductChange(

PCStart_Id Int Null,

PCPU_Id Int,

PCProd_Id Int,

PCStart_Time DateTime,

PCPostUpdate Int Null)

Select @PCStart_Id = Null

Select @PCPU_Id = @PU_Id

Select @PCProd_Id = @NewProd_Id

Select @PCStart_Time = @NewPCTime

Select @PCPostUpdate = 0

Insert into #ProductChange (PCStart_Id,PCPU_Id,PCProd_Id,PCStart_Time,PCPostUpdate)

Values (@PCStart_Id,@PCPU_Id,@PCProd_Id,@PCStart_Time,@PCPostUpdate)

Select 3,* From #ProductChange

Drop Table #ProductChange

End