Create PROCEDURE [dbo].[sp_CreateParByReferDay] @tmpIdHavaleh  Numeric(18,0)
As 
DECLARE @Par_Id NUMERIC(9)
if Not Exists(Select Par_No From tbl_HParvande Where Par_No = (Select Par_No From TmpDrugH Where Id_Havaleh = @tmpIdHavaleh) )
AND 
( (Select SUM(ISNULL(Refer_Day,0)) From TmpDrugHavaleh Where Id_Havaleh = @tmpIdHavaleh and ISNULL(Refer_Day,0) > 0 ) > 0  )  
Begin

    INSERT INTO tbl_HParvande 
	( Par_No, PT_Code, Sazman_Code, Note_Code,Family,Name ,Nezam_No , Par_Status)
	SELECT Par_No ,ISNULL(PT_Code ,'0') , Sazman_Code , Note_Code, Resive_Name , ' '  ,Nezam_No , '0'
	FROM     TmpDrugH
	WHERE Id_Havaleh = @tmpIdHavaleh

	SELECT  @Par_Id = @@IDENTITY

	INSERT INTO tbl_DParvande
	(Par_Id, Row_No, K_Code, K_Qty, Price_Forosh , Refer_Day ,Refer_Step, Tahvil_Date, Tahvil )
	SELECT @Par_Id , Radif, TmpDrugHavaleh.K_Code, K_Qty1, Price_Forosh ,Refer_Day ,  1, GETDATE(), K_Qty1
	FROM         TmpDrugHavaleh
	Inner Join KalaId On KalaId.K_Code = TmpDrugHavaleh.K_Code And KalaId.Parvande_Flag = 1
	WHERE Id_Havaleh = @tmpIdHavaleh

End
else
Begin
	Select @Par_Id  = Par_id From tbl_HParvande
	Where Par_No = (Select Par_No From TmpDrugH Where Id_Havaleh = @tmpIdHavaleh)
	INSERT INTO tbl_DParvande
	(Par_Id, Row_No, K_Code, K_Qty, Price_Forosh , Refer_Day ,Refer_Step, Tahvil_Date, Tahvil )
	SELECT @Par_Id , Radif, TmpDrugHavaleh.K_Code, K_Qty1, Price_Forosh ,Refer_Day ,
	 ( 	   Select MAX(Refer_Step)+1 from tbl_DParvande where Par_Id = @Par_Id) , GETDATE(), K_Qty1
	FROM  TmpDrugHavaleh
	Inner Join KalaId On KalaId.K_Code = TmpDrugHavaleh.K_Code AND KalaId.Parvande_Flag = 1
	WHERE Id_Havaleh = @tmpIdHavaleh
End
