USE [Surgere1]
GO
/****** Object:  UserDefinedFunction [dbo].[fnAssetTranactionList]    Script Date: 04/27/2012 12:09:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[fnAssetTranactionList](@container_id bigint,@label_nbr varchar(10))
Returns  @tblAssetTransactionList Table
(

 transaction_id bigint,
 asset_id bigint,
 container_id bigint,
 client_id bigint,
 from_id bigint,
 to_id bigint,
 supplier_id bigint,
 Transaction_Dt datetime,
 transaction_by varchar(50),
 From_type varchar(50),
 from_name varchar(50),
 to_type varchar(50),
 to_name varchar(50),
 transaction_status varchar(50),
 is_active_yn varchar(10),
 inactive_Dt datetime

)
As
Begin
Declare @tblTemp table
(id bigint,supplier_locationname varchar(50))

if exists(select * from transactions
 where from_type='Manu' and to_type='Supplier' and
 asset_id in (select asset_id from asset where container_id=@container_id and
 label_nbr=@label_nbr)  )
 Begin
  insert into @tblAssetTransactionList
  select b.transaction_id,a.asset_id,b.container_id,b.client_id,b.from_id,
  b.to_id,b.supplier_id,
   b.created_dt,transaction_by,from_type,
  c.manufacturer_name,to_type,d.Supplier_name,transaction_status,a.is_active_yn,
  a.inactive_Dt
   from asset  a
    inner join transactions b on a.asset_id=b.asset_id
    inner join manufacturer c on b.from_id=c.manufacturer_id
    inner join Supplier d on d.Supplier_id=b.supplier_id    
    where a.container_id=@container_id and  a.label_nbr=@label_nbr
    and from_type='Manu' and to_type='Supplier'
 END
 if exists(select * from transactions
      where from_type='Supplier' and to_type='Facility' and
      asset_id in (select asset_id from asset where container_id=@container_id and
     label_nbr=@label_nbr)  )
 Begin
  insert into @tblAssetTransactionList
   select  b.transaction_id,a.asset_id,b.container_id,b.client_id,b.from_id,
   b.to_id,b.supplier_id,
   b.created_dt,transaction_by,from_type,
   cc.Supplier_name+' - '+ c.Location_name,to_type,d.facility_name,transaction_status,a.is_active_yn,
   a.inactive_Dt
   from asset  a
   inner join transactions b on a.asset_id=b.asset_id
   inner join Supplier_location c on b.from_id=c.Supplier_location_id
   inner join Supplier cc on b.supplier_id=cc.Supplier_id
   inner join Facility d on d.facility_id=b.to_id    
   where a.container_id=@container_id and  a.label_nbr=@label_nbr
   and from_type='Supplier' and to_type='Facility'
 End
  if exists(select * from transactions
      where from_type='Supplier' and to_type='Facility' and
      asset_id in (select asset_id from asset where container_id=@container_id and
     label_nbr=@label_nbr)  )
 Begin
  insert into @tblAssetTransactionList
   select  b.transaction_id,a.asset_id,b.container_id,b.client_id,b.from_id,
   b.to_id,b.supplier_id,
   b.created_dt,transaction_by,from_type,
   d.facility_name,to_type,cc.Supplier_name+' - '+ c.Location_name,transaction_status,a.is_active_yn,
   a.inactive_Dt
   from asset  a
   inner join transactions b on a.asset_id=b.asset_id
   inner join Supplier_location c on b.to_id=c.Supplier_location_id
   inner join Supplier cc on b.supplier_id=cc.Supplier_id
   inner join Facility d on d.facility_id=b.from_id    
   where a.container_id=@container_id and  a.label_nbr=@label_nbr
   and from_type='Facility' and to_type='Supplier'
 End
   if exists(select * from transactions
      where from_type='Client' and to_type='Repair' and
       asset_id in (select asset_id from asset where container_id=@container_id and
      label_nbr=@label_nbr)  )
 Begin
  insert into @tblAssetTransactionList
   select  b.transaction_id,a.asset_id,b.container_id,b.client_id,b.from_id,
   b.to_id,b.supplier_id,
   b.created_dt,transaction_by,from_type,
   '',to_type,'',transaction_status,a.is_active_yn,
   a.inactive_Dt
   from asset  a
   inner join transactions b on a.asset_id=b.asset_id
   --inner join Supplier_location c on b.to_id=c.Supplier_location_id
   --inner join Supplier cc on b.supplier_id=cc.Supplier_id
   --inner join Facility d on d.facility_id=b.from_id    
   where a.container_id=@container_id and  a.label_nbr=@label_nbr
   and from_type='Client' and to_type='Repair'
 End
  if exists(select * from transactions
      where from_type='Client' and to_type='Retire' and
       asset_id in (select asset_id from asset where container_id=@container_id and
      label_nbr=@label_nbr)  )
 Begin
  insert into @tblAssetTransactionList
   select  b.transaction_id,a.asset_id,b.container_id,b.client_id,b.from_id,
   b.to_id,b.supplier_id,
   b.created_dt,transaction_by,from_type,
   '',to_type,'',transaction_status,a.is_active_yn,
   a.inactive_Dt
   from asset  a
   inner join transactions b on a.asset_id=b.asset_id
   --inner join Supplier_location c on b.to_id=c.Supplier_location_id
   --inner join Supplier cc on b.supplier_id=cc.Supplier_id
   --inner join Facility d on d.facility_id=b.from_id    
   where a.container_id=@container_id and  a.label_nbr=@label_nbr
   and from_type='Client' and to_type='Retire'
 End
 Return
 End