ALTER procedure [dbo].[uspDCLateBoundLotInfo](
	@intFileDefinitionID int = 2
	, @strAttributeSetFamilyName varchar(100)='Late Bound Product Lot Attribution'
)as

set nocount on

CREATE TABLE #tblTripleValue 
(	  strName varchar(255)
	, strValue varchar(255)
	, strTimeStamp varchar(255)
 );

DECLARE curX CURSOR READ_ONLY FOR
	select strValue00 as strPK, strValue01 as strFacility, strValue02 as strLine
		, dbo.ufnDateTimeFromNETDateString(strValue03)  as datLotStart, strValue04 as strLotID 
		from dbo.tblFileData
		where intFileDefinitionID = 2 and strValue05<>'x'
		order by strFacility, strLine, datLotStart 

declare @intChildCount		int
declare @datFrom			datetime
declare @datTo				datetime
declare @strFacilityCurrent varchar(255)
declare @strLineCurrent		varchar(255)
declare @strLotIDCurrent	varchar(255)
declare @strPKCurrent		varchar(255)
declare @strFacility		varchar(255)
declare @strLine			varchar(255)
declare @strLotID			varchar(255)
declare @strPK				varchar(255)
declare @guidNew			uniqueidentifier
declare @strNameValueList	varchar(255)
declare @strTripleValueList	varchar(8000) 

open curX
--get earliest row 
fetch next from curX into @strPKCurrent, @strFacilityCurrent, @strLineCurrent, @datFrom, @strLotIDCurrent
if @@fetch_status<>0 return

while(1=1)
	begin	--loop
		--get next row 
		fetch next from curX into @strPK, @strFacility, @strLine, @datTo, @strLotID
		if @@fetch_status<>0 break

		--check to see if there are any child containers for test below
			select @intChildCount=count(*)
			from dbo.tblReadStreamCases 
			where datCaseRead >= @datFrom				-- in time range
				and datCaseRead <  @datTo
				and intStationID in (				-- from a station of the current line
					SELECT     S.intStationID
						FROM  dbo.tblLines AS L INNER JOIN
							  dbo.tblStations AS S ON L.intLineID = S.intLineID INNER JOIN
							  dbo.tblFacilities F ON L.intFacilityID = F.intFacilityID
						WHERE     (L.strLineName = @strLineCurrent) 
AND (F.strFacility = @strFacilityCurrent)

			  ) and strCaseReading not in	(		--not a child of any other container
					select strItemReading			--think this is applied at end; 
--if not, could be peformance issue. <<<
						from tblReadStreamCases 
			  ) 


		if @strFacility = @strFacilityCurrent and @strLine = @strLineCurrent and @intChildCount>0

			begin	--if block

				--write attribution event (This is hard-wired for .)
				--Parameters are those defined for ' Late Bound Product Lot Attribution'
				set @strNameValueList='Pack Date|' + cast(@datFrom as varchar) + '|Lot|' 
+ @strLotIDCurrent
				set @guidNew=NewID()
--				print 'NameValueList= ' + @strNameValueList
				exec dbo.uspQueueInsertAttributionEvent 'FacilityTag', @guidNew
					, @strAttributeSetFamilyName, @strNameValueList

				--create a table to hold triples of parent and child containers 
--for the association event
				truncate table #tblTripleValue 
				insert into #tblTripleValue 
					select 'FacilityTag' as strType, cast(@guidNew as varchar(50))as strValue
						, cast(getdate() as varchar(50)) as strTimeStamp
					UNION
					select distinct 
(case when left(strCaseReading,2)='FT' then 'FacilityTag' 
else 'CodeRead' end) as strType
						, strCaseReading as strValue
, cast(datCaseRead as varchar(50)) as strTimeStamp
					from dbo.tblReadStreamCases 
					where datCaseRead >= @datFrom		-- in time range
						and datCaseRead <  @datTo
						and intStationID in (		-- from a station of the current line
							SELECT     S.intStationID
								FROM  dbo.tblLines AS L INNER JOIN
									  dbo.tblStations AS S ON L.intLineID = S.intLineID INNER JOIN
									  dbo.tblFacilities F ON L.intFacilityID = F.intFacilityID
								WHERE     (L.strLineName = @strLineCurrent) 
AND (F.strFacility = @strFacilityCurrent)

					  ) and strCaseReading not in	(			--not a child of any other container
							select strItemReading				--think this is applied at end; 
--if not, could be peformance issue. <<<
								from tblReadStreamCases 
					  ) 

				--convert triple value table to triple value string
				set @strTripleValueList=''
				select @strTripleValueList=@strTripleValueList 
					+ strName + '|' + strValue + '|' + strTimeStamp + '|' 
					from #tblTripleValue
				set @strTripleValueList=left(@strTripleValueList, len(@strTripleValueList)-1)

				--write the association event
--				print 'TripleValueList= ' + @strTripleValueList
				exec dbo.uspQueueInsertAssociationEvent @strTripleValueList

			end	--if block

		--mark row as processed, except for last; 
--Parameters are those defined for Late Bound Product Lots' file definition
		if @strFacility = @strFacilityCurrent and @strLine = @strLineCurrent
			update dbo.tblFileData
				set strValue05 = 'x'
				where strValue00=@strPKCurrent
					and intFileDefinitionID=@intFileDefinitionID

		--update current items
		set @strPKCurrent = @strPK
		set @strFacilityCurrent = @strFacility
		set @strLineCurrent = @strLine
		set @datFrom = @datTo
		set @strLotIDCurrent = @strLotID

	end	--loop

close curX
deallocate curX

drop table #tblTripleValue