-- ============================================= -- Author: Abrar Khaled Samara -- Create date: 25/03/2019 -- Description: Salary Calc Report Generator -- ============================================= Create VIEW [dbo].[Employee_Salary_Calc] as ((select PEI.Branch_Id ,PEI.Employee_Id ,PEI.Employee_Code ,PEI.Employee_Full_Name_Main ,PEI.Employee_Full_Name_Foreign ,Convert(nvarchar,PEI.Hiring_Date,103) as Hiring_Date ,Convert(date,PEI.Hiring_Date) as HiringDate ,isnull(PEI.Site_Code,0) as Site_Code ,isnull(PEI.Department_Code,0) as Department_Code ,isnull(PEI.Division_Code,0) as Division_Code ,isnull(PEI.Section_Code,0) as Section_Code ,isnull(PEI.Unit_Code,0) as Unit_Code ,isnull(PEI.Contract_Type,0) as Contract_Type ,isnull(PEI.Team,0) as Team_Id ,isnull(PEI.Project,0) as Project ,isnull(PEI.Position,0) as Position_code ,isnull(PEI.Degree,0) as Degree_Id ,isnull(PEI.Step,0) as Step ,isnull(PEI.Nationality,0) as Nationality_Code ,isnull(PEI.job_family,0) as job_family_Code ,isnull(PEI.Shift_Work_Id,0) as Shift_Work_Id ,isnull(PEI.levelId,0) as levelId ,isnull(PEI.Work_Type,0) as Work_Type ,isnull(PEI.CostCategory,0) as CostCategory ,isnull(PEI.Contract_Details,0) as Contract_Details ,isnull(PEI.Religion,0) as Religion ,isnull(PEI.Social_Status,0) as Social_Status ,isnull(PEI.Gender,0) as Gender ,Convert(nvarchar,PEI.Birth_Date,103) as Birth_Date ,Convert(date,PEI.Birth_Date) as BirthDate ,isnull(PEI.CostCenter,0) as Cost_Center ,isnull(PEI.Class,0) as Class_Id ,dbo.udf_get_main_Desc(PEI.Branch_id,1,PEI.Site_Code ,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Site ,dbo.udf_get_main_Desc(PEI.Branch_id,2,PEI.Department_Code ,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Department ,dbo.udf_get_main_Desc(PEI.Branch_id,3, PEI.Section_Code,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Section_Code1 ,dbo.udf_get_main_Desc(PEI.Branch_id,4,PEI.Division_code ,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Division_code1 ,dbo.udf_get_main_Desc(PEI.Branch_id,5,PEI.Unit_code ,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Unitcode ,dbo.udf_get_main_Desc(PEI.Branch_id,151,PEI.Position,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) as Position ,dbo.udf_get_main_Desc(PEI.Branch_id,250,PEI.Nationality ,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Nationality ,dbo.udf_get_main_Desc(PEI.Branch_id,150,PEI.job_family,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS job_family ,dbo.udf_get_main_Desc(PEI.Branch_id,158,PEI.Contract_Type,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS ContractType ,dbo.udf_get_main_Desc(PEI.Branch_id,951,PEI.CostCenter,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS CostCenter ,dbo.udf_Att_Get_ShiftName(PEI.Branch_id,PEI.Shift_Work_Id,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Shift ,dbo.udf_get_main_Desc(PEI.Branch_id,152,PEI.Class,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Class ,dbo.udf_get_main_Desc(PEI.Branch_id,153,PEI.Degree,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Degree ,dbo.udf_get_main_Desc(PEI.Branch_id,1530,PEI.levelId,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Level ,dbo.udf_get_main_Desc(PEI.Branch_id,6,PEI.Team,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Team ,dbo.udf_get_main_Desc(PEI.Branch_id,1010,PEI.CostCategory,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS CostCategory_Name ,dbo.udf_get_main_Desc(PEI.Branch_id,7,PEI.Project,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Project_Name ,dbo.udf_getSetupDecs(PEI.Branch_id,154,PEI.First_Classification , dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS First_Classification_Name ,dbo.udf_getSetupDecs(PEI.Branch_id,155,PEI.Second_Classification , dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Second_Classification_Name ,dbo.udf_getSetupDecs(PEI.Branch_id,156,PEI.Third_Classification , dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Third_Classification_Name ,dbo.udf_getSetupDecs(PEI.Branch_id,157,PEI.Fourth_Classification , dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Fourth_Classification_Name ,dbo.udf_getSetupDecs(PEI.Branch_id,1581,PEI.Fifth_Classification ,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Fifth_Classification_Name ,dbo.udf_getSetupDecs(PEI.Branch_id,251,PEI.Religion ,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Religion_Name ,(case when PEI.Work_Type=0 then '' when PEI.Work_Type=1 then 'مياومة' when PEI.Work_Type=2 then 'شهري' when PEI.Work_Type=3 then 'جزئي' else 'مياومة مؤقت' End) as JopType ,(case when PEI.Contract_Details=0 then '' when PEI.Contract_Details=1 then 'عقد سنة' when PEI.Contract_Details=2 then 'عقد سنتين' when PEI.Contract_Details=3 then 'عقد ثلاث سنوات' when PEI.Contract_Details=4 then 'عقد أربع سنوات' when PEI.Contract_Details=5 then 'عقد خمس سنوات' when PEI.Contract_Details=6 then 'عقد ست سنوات' else 'عقد مفتوح' end ) as ContractDetails ,(case when PEI.Social_Status=0 then '' when PEI.Social_Status=1 then 'أعزب' when PEI.Social_Status=2 then 'متزوج' when PEI.Social_Status=3 then 'مطلق' when PEI.Social_Status=4 then 'أرمل' else 'مطلق و لديه أولاد ' end) as SocialStatus ,(case when PEI.Gender=1 then 'ذكر' else 'أنثى' end) as Gender_Name ,PEI.Employee_Code_Second ,PEI.First_Classification ,PEI.Second_Classification ,PEI.Third_Classification ,PEI.Fourth_Classification ,PEI.Fifth_Classification ----------------------------------------------------------------- ,PEFI.Employee_situation ,dbo.udf_get_main_Desc(PEI.Branch_id,503,PEFI.Employee_situation ,dbo.udf_get_BaseLang(PEI.Branch_Id,2)) AS Employee_situation_Name ,PEFI.Basic_Salary --,(Case when PTM.Transaction_Id=12 then PTM.Transaction_Id else null end) as Transaction_Id_Vacation --,(Case when PTM.Transaction_Id=12 then PTM.Transaction_Type_Id else null end) as Transaction_Type_Id_Vacation --,(Case when PTM.Transaction_Id=12 then dbo.udf_get_main_Desc(PEI.Branch_id,300,PTM.Transaction_Type_Id ,dbo.udf_get_BaseLang(PEI.Branch_Id,2))else null end) as Vacation_Type_Name --,(Case when PTM.Transaction_Id=12 then Convert(Nvarchar,PTM.Trans_Date,103) else null end) as From_Date_Vacation --,(Case when PTM.Transaction_Id=12 then Convert(Date,PTM.Trans_Date) else null end) as FromDate_Vacation --,(Case when PTM.Transaction_Id=12 then Convert(Nvarchar,PTM.To_Date,103) else null end) as To_Date_Vacation --,(Case when PTM.Transaction_Id=12 then Convert(Date,PTM.To_Date) else null end) as ToDate_Vacation --,(Case when PTM.Transaction_Id=12 then Convert(Nvarchar,PTM.Due_Date,103) else null end) as Due_Date_Vacation --,(Case when PTM.Transaction_Id=12 then Convert(Date,PTM.Due_Date) else null end) as DueDate_Vacation --,(Case when PTM.Transaction_Id=12 then PTM.Transaction_Value else null end) as Transaction_Day_Vacation --,(Case when PTM.Transaction_Id=12 then PTM.Transaction_Amount else null end) as Transaction_Amount_Vacation --,(Case when PTM.Transaction_Id=12 then PTM.Yearr else null end) as Yearr_Vacation --,(Case when PTM.Transaction_Id=12 then PTM.Transaction_Note else null end) as Transaction_Note_Vacation -- ,PTM.transaction_id ---------------------------- Adm_Branch -------------------------- ,dbo.udf_GetBranchName(dbo.udf_get_BaseLang(PEI.Branch_Id,2),PEI.Branch_Id) as BranchName ---------------------------------------------------------------------- From payroll_employee_Info as PEI Inner Join payroll_Employee_Financial_Info as PEFI on(PEI.Employee_Id =PEFI.employee_Id and PEFI.Branch_Id=PEI.Branch_Id) left outer join Payroll_trans_master as PTM on (PEI.Employee_Id=PTM.employee_id and PEI.Branch_Id=PTM.Branch_Id) left outer join Payroll_Employee_Vacations_Open_Balances as PEVOB on(PEI.Employee_Id =PEVOB.employee_Id and PEVOB.Branch_Id=PEI.Branch_Id ) Left Outer Join Adm_Branch as AB on (Ab.Branch_id=PEI.Branch_id) inner join payroll_option as PO on(PO.Branch_Id =PEI.Branch_Id ) where (transaction_id=12 or (PEI.Employee_Id in (Select Employee_Id From Payroll_Employee_Info )))-- and --PEI.Employee_Id in (Select Employee_Id From payroll_Employee_Financial_Info ) and --PEI.Employee_Id Not in (Select Employee_Id From Payroll_Trans_Master where Transaction_Id=12 ))) GO