declare @Site varchar(50), @PartNumber varchar(20),
    @MaterialName varchar(200), @ComponentMaterialName varchar(200),
    @ComponentLotNumber varchar(20), @ComponentSerialNumber varchar(20),
    @UsageDate datetime, @ExpirationDate datetime, @SubAssemblyExpirationDate datetime, @SubSerialNumber varchar(20)

declare @refid uniqueidentifier
set @refid=null
declare getRef cursor for select top 1000 refid from tempSourcerefId
where processed=0
open getRef
fetch next from getRef into @refid
while @@fetch_status = 0 begin
    set @Site=''
    set @PartNumber =''
    set @MaterialName =''
    set @SubAssemblyExpirationDate = null
    set @SubSerialNumber=''
    --get subassembly info
    select @Site=s.Name, @PartNumber=m.PartNumber, @materialName=m.MaterialName, @SubSerialNumber =sl.SerialNumber,
        @SubAssemblyExpirationDate= coalesce(l.ExpirationDate, sl.ExpirationDate)
        from material m join lot l on m.materialid=l.materialid
        join sublot sl on l.lotid=sl.lotid
        join site s on s.siteid=sl.siteid
        where sl.sublotid=@refid

    --get subassembly usage dates
    declare @subUsageTbl table (UsageDate datetime)
    insert into @subUsageTbl select consumedate from itemconsumption where sublotid=@refid

    --get component
    declare @componentTbl table (Componentid uniqueidentifier)
    insert into @componentTbl select sublotid from itemconsumption where referenceid=@refid

    declare @sublotid uniqueidentifier
    set @sublotid=null
    declare getCom cursor for select distinct componentid from @componentTbl
    open getCom
    fetch next from getCom into @sublotid
    while @@fetch_status=0 begin
        set @ExpirationDate = null
        select @ExpirationDate = coalesce(l.ExpirationDate, sl.ExpirationDate)
            from lot l join sublot sl on l.lotid =sl.lotid
            where sl.sublotid =@sublotid
        if @ExpirationDate is not null begin
            if exists(select * from @subUsageTbl where
                dateadd(dd, datediff(dd, 0, UsageDate)+0, 0) > @ExpirationDate)
            begin
                set @ComponentMaterialName =''
                set @ComponentLotNumber =''
                set @ComponentSerialNumber =''

                select @ComponentMaterialName = m.MaterialName,
                    @ComponentLotNumber =l.LotNumber, @ComponentSerialNumber =sl.SerialNumber
                    from Material m join lot l on m.materialid=l.materialid
                    join sublot sl on l.lotid =sl.lotid
                    where sl.sublotid =@sublotid

                set @UsageDate = null
                declare getUsageDates cursor for select UsageDate from @subUsageTbl
                    where dateadd(dd, datediff(dd, 0, UsageDate)+0, 0) > @ExpirationDate
                open getUsageDates
                fetch next from getUsageDates into @UsageDate
                while @@fetch_status=0 begin
                    insert into tempOutputSubs values (@Site, @PartNumber,
                        @MaterialName, @SubSerialNumber, @ComponentMaterialName,
                        @ComponentLotNumber, @ComponentSerialNumber,
                        @UsageDate, @SubAssemblyExpirationDate, @ExpirationDate)

                    set @UsageDate = null
                    fetch next from getUsageDates into @UsageDate
                end
                close getUsageDates
                deallocate getUsageDates
            end
        end
        set @sublotid=null
        fetch next from getCom into @sublotid
    end
    close getCom
    deallocate getCom

    delete @subUsageTbl
    delete @componentTbl

    update tempSourceRefid set processed=1 where refid=@refid
    set @refid=null
    fetch next from getRef into @refid
end
close getRef
deallocate getRef