CREATE Function [dbo].[SearchKhordehForoshi] (@KCode Varchar(15), @SystemName Varchar(50)) 
RETURNS 
   @SearchKhordehForoshi TABLE
   (
    K_Code         varchar(40),
    Barcode        Varchar(40),
    Name1          nVarchar(50),
    A_Code         varchar(3),
    R_Unit1_2      Real,
    Hamrah_Flag    Bit,
    Exp_Flag       Bit,
    Serial_flag    Bit,
    Point_Flag     Bit,
    Make_Flag      Bit,
    CtrMojodi      Bit,
    Duty_Flag      Smallint,
    Price_Kharid   money,
    Price_Forosh   money,
    Price_Consumer Money,
    Mojodi         Money 
   )
As 
Begin
  Declare @ACode varchar(31)
  Select @ACode = Sender From Local_Option Where Computer_Name = @SystemName
  Insert Into @SearchKhordehForoshi
  Select Kalaid.K_Code, Barcode, Name1, Anbar.A_Code, R_Unit1_2,Hamrah_Flag, Exp_Flag, Serial_flag, Point_Flag,  
         Make_Flag, Kalaid.CtrMojodi, Cast(Duty_Flag as Smallint) Duty_Flag, Price_Kharid, Price_Forosh, Price_Consumer,Mojodi
         From Anbar Inner join KalaId on kalaid.K_Code = Anbar.K_Code
         Inner join  Anbarid 
         on anbarid.A_code = Anbar.A_Code 
         Where (Kalaid.K_Code = @KCode Or BarCode = @KCode)  
         And Anbar.A_Code in(Select splitdata From dbo.fnSplitString(@ACode,',')) 
         And Mojodi > (
                       Select Case   
                                When Anbarid.CtrMojodi = 0 then -9999999999   
                                When Anbarid.CtrMojodi = 1 then 0   
                                When Anbarid.CtrMojodi = 2 And Kalaid.CtrMojodi = 1 then 0   
                                When Anbarid.CtrMojodi = 2 And Kalaid.CtrMojodi = 0 then -9999999999 
                              end 
                      ) order By Anbar.A_Code Desc
   RETURN                     
End
