I haven't posted the View yet, Wait I will post that too
ALTER view [dbo].[XXACL_ERP_EMP_Confirmation_View] as
select * from (
select distinct comp.company_name Company_Name,case when e.comp_mkey!=e.on_Deput_comp_mkey
then Dcomp.company_name else '' end Deputed_Company_Name ,
'NA' [Company Code],e.emp_card_no [Emp_Card_No], 'NA' Title,
e.emp_name [Employee_Name],usr.first_name [First_Name],replace(d1.type_desc,'','') Designation, grade.type_desc Grade
,replace(isnull(b1.type_desc,'--NA--'),'','') as 'Department'
,isnull(b2.type_desc,'--NA--') as 'Sub Department'
,isnull(hub.type_desc,'--NA--') as Hub_Name
,'NA' Location
,'NA' City
,'NA' [State Name]
,case when e.emp_type='E' then 'HO-Employee' when e.emp_type='T' then 'Temporary'
when e.emp_type='C' then 'Consultant'
when e.emp_type='S' then 'Site-Employee' else 'Temporary-OutSource' end [Emp_Type]
,convert(datetime,e.date_of_joining,103) Date_of_Joining
,convert(datetime,e.dt_of_birth,103) DOB
,ctc.final_ctc_amt [Fixed CTC P.A]
,ctc.variance_amt [Variable CTC P.A]
,ctc.Anu_ctc_amt Total_CTC_PA
,dbo.fn_spellNumber(cast(cast(ctc.Anu_ctc_amt as numeric(18,0)) as varchar),'UK','0') [CTC_in_Word]
,convert(datetime,ctc.pay_slip_eff_date,103) [Last Increment Date]
,s1.emp_name as 'RA1 Name',
isnull(s2.emp_name,'--NA--') as 'RA2 Name'
,'NA' 'Functional Head Name'
,'NA' [Probation Period],
CASE WHEN (select count(*) from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no)>0 then
(select top 1 confirmation_date from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no)
else CONVERT(VARCHAR(25),dateadd(mm,1,DATEADD(dd,-(DAY(getdate())-1),getdate())),103) end AS Confirmation_Date
--case when convert(varchar,e.date_of_confirmation,103)='01/01/1900' then null else
--convert(datetime,e.date_of_confirmation,103) end Confirmation_Date
,'NA' 'Confirmation Remarks'
,case when e.status='A' then 'Active'
when e.status='S' then 'Active'
when e.status='F' then 'Full and Final'
when e.status='R' then 'Resign' else 'NA' end [Employee Status]
,'NA' [Notice Period Term]
,case when e.resig_date='1900-01-01 00:00:00.000' then null else convert(datetime,e.resig_date,103) end [Resigned Date]
,'NA' 'Resignation Received HR'
,case when e.dt_of_leave='1900-01-01 00:00:00.000' then null
else convert(datetime,e.dt_of_leave,103) end [Last Working Date]
,e.Reason_of_resignation [Reason for Leaving]
,'NA' [Sourced Through]
,'NA' 'Replacement For'
,case when e.marital_status='M' then 'Married' when e.marital_status='U' then 'UnMarried' else '--NA--'
end [Marital Status]
, 'NA' [Qualification Category]
,dbo.StringConcat((
isnull((select isnull(education,'') +'--'+ isnull(edu.remarks ,'')
from p_Emp_Education edu
join p_education_mst edumst on edu.education_mkey=edumst.mkey
where edu.mkey=e.mkey and Entry_Srno=1
order by entry_srno
for xml raw,elements),'NA')))
'Highest Qualification'
,'NA' Graduation
,'NA'[GradYear Passed]
,'NA' [Post Graduation]
,'NA' [Post Grad Year Passed]
,ad.add_1 Address_Line1
,ad.add_2 Address_Line2
,c.city_name as Address_City
,ad.pincode as 'PinCode'
,stmst.State_name Address_State
,Residence_No 'Telephone No.'
,e.mobile_no 'Mobile No.'
,e.email_id_official [EmailId Official]
,e.email_id_personal 'EmailId Personal'
,e.pan_no 'PAN CARD' ,isnull(e.pf_no,'--NA--') as 'PF_No'
,isnull(e.ESIC_No,'--NA--') as 'ESIC_No' ,isnull(e.passport_no,'--NA--') as 'Passport_No'
,e.Work_Exp as 'Previous Experience Year'
,fam.Member_Name as 'Family Details 1 Name'
,r.relation 'Family Details 1 Relation'
,isnull(convert(varchar,fam.DOB,103),'null') as 'Family Details 1 DOB'
,fam1.Member_Name as 'Family Details 2 Name'
,r1.relation 'Family Details 2 Relation'
,isnull(convert(varchar,fam1.DOB,103),'null') as 'Family Details 2 DOB'
,fam2.Member_Name as 'Family Details 3 Name'
,r2.relation 'Family Details 3 Relation'
,isnull(convert(varchar,fam2.DOB,103),'null') as 'Family Details 3 DOB'
,fam3.Member_Name as 'Family Details 4 Name'
,r3.relation 'Family Details 4 Relation'
,isnull(convert(varchar,fam3.DOB,103),'null') as 'Family Details 4 DOB'
,e.bld_grp 'Blood Group'
--,bld.type_desc blood_type
,'NA' 'Background Verif Doc'
--,case when Medical_Fit='Y' then 'Yes' else 'No' end Medical_Fitness
,'NA' 'Medical Fitness'
,'NA' 'Medical Fitness Remark'
,'NA' 'Graphology Test'
,'NA' 'Bank A/C status'
,bank.bank_name [Bank Name]
,e.citybank_acc_no 'Bank Account Number'
, case when e.on_Deput_comp_mkey>0 then e.on_Deput_comp_mkey else e.comp_mkey
end comp_mkey
,e.new_design_mkey
,e.New_Dept_mkey
,e.ctc_hdr_mkey
,e.status emp_status1
,e.mkey emp_mkey
,e.subdept_mkey
,e.department_mkey ,e.mkey,
CASE WHEN (select count() from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no)>0 then
(select top 1 confirmation_date as 'confirmation_Due_On' from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no) else
CONVERT(VARCHAR(25),dateadd(mm,1,DATEADD(dd,-(DAY(getdate())-1),getdate())),103) end as 'confirmation_Due_On' -- e.confirmation_due_on
,
CASE WHEN (select count() from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no)>0 then
(select top 1 PROBATION_PERIOD as 'PROBATION_PERIOD' from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no) else
(select datediff(d,e.dt_of_join,CONVERT(VARCHAR(25),dateadd(mm,1,DATEADD(dd,-(DAY(getdate())-1),getdate())),101))/30) END as Probation_Period
--,e.Probation_Period
,e.wages_type
,e.reporting_to
,e.reporting_to2
,
CASE WHEN (select count() from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no)>0 then
(select top 1 LEAVE_TAKEN_PROB_PER as 'Absent_Count' from p_emp_confirmation_hdr where emp_card_no=e.emp_card_no) else
(select dbo.AbsentCount_view(e.emp_card_no)) end as Absent_count
--,(select count() from xxacl_ERP_AB_PL_Count_V where emp_card_no = e.emp_card_no) Absent_count
,case
when (select count(*) from xxacl_ERP_AB_PL_Count_View where emp_card_no= e.emp_card_no)>7
then dateadd(mm,e.probation_period+1,e.Date_of_Joining)
when day(e.Date_of_Joining)>15
then dateadd(mm,e.probation_period+1,e.Date_of_Joining)
else e.date_of_confirmation
end
Confirmation_Date_New
,case
when (select isnull(sum(total_day),0) from xxacl_ERP_AB_PL_Count_View where emp_card_no= e.emp_card_no)>7
then 'DOC Exteded By 1 month. Reason:- Taken leave='+ Convert(varchar,(select SUM(TOTAL_DAY) from xxacl_ERP_AB_PL_Count_View where emp_card_no = e.emp_card_no))+' which is > 7. Actual DOC='+Convert(Varchar,dateadd(mm,e.probation_period,e.Date_of_Joining),103)+'
'
-- when day(e.Date_of_Joining)>15
-- then 'N/A'-- 'DOC Exteded By 1 month. Reason:- DOJ should be before 15th of month. Actual DOC='+Convert(varchar,dateadd(mm,e.probation_period,e.Date_of_Joining),103)+''
else 'N/A'
end
Remark
,case when e.sex='F' then 'Female'
when e.sex='M' then 'Male'
else 'NA' end Gender
,ctc.mkey CTC_MKEY
, case when e.on_Deput_comp_mkey>0 then Dcomp.add1+' '+Dcomp.add2+ '' +Dcomp.city else
comp.add1+' '+comp.add2+ '' +comp.city
end Company_Add
,convert( varchar,getdate(),103) curr_date
,case when b1.add_tinfo1='C' then 'Core' else 'Support' end Core_Support
,case when e.ctc_hdr_mkey in (213,214,215) then 30 else 90 end Prob_period_Letter,isnull(e.func_role_mkey,0) func_role_mkey
--,e.new_design_mkey
from emp_mst e
left join emp_shift_details b on e.emp_card_no=b.emp_card_no and b.mkey = (select max(mkey) from emp_shift_details c where b.emp_card_no=c.emp_card_no)
left join emp_mst1 e1 on e.emp_card_no=e1.emp_card_no --and e.emp_card_no=1216
--left join user_mst u on e.mkey=u.employee_mkey
left join shift_master s on b.shift_mkey=s.mkey
left join company_mst comp on e.comp_mkey=comp.mkey and comp.fa_year=2008
left join company_mst Dcomp on e.on_Deput_comp_mkey=Dcomp.mkey and Dcomp.fa_year=2008
left join type_mst_a grade on e.ctc_hdr_mkey=grade.add_iinfo1
left join type_mst_a d1 on e.new_design_mkey=d1.master_mkey
left join type_mst_a b1 on e.New_Dept_mkey=b1.master_mkey
left join type_mst_a b2 on e.New_subDept_mkey=b2.master_mkey
left join type_mst_a pw on e.paidweekly_off=pw.type_abbr
left join type_mst_a po on e.weekly_off=po.type_abbr
left join type_mst_a hub on e.Hub_Mkey=hub.master_mkey
left join state_mst st on e1.domicile_mkey=st.mkey and e1.country_of_birth=st.country_mkey and st.add_flag='N'
left join emp_mst s1 on s1.mkey=e.reporting_to
left join emp_mst s2 on s2.mkey=e.reporting_to2
left join ctc_recal_group_hdr ctc on ctc.emp_mkey=e.mkey
and ctc.mkey = (select max(mkey) from ctc_recal_group_hdr c where e.mkey=c.emp_mkey and delete_flag='N')
left join emp_address_trl ad on e.mkey=ad.mkey and ad.entry_sr_no=(select max(entry_sr_no) from emp_address_trl where mkey=e.mkey)
left join city_mst as c on ad.city_mkey = c.mkey
left join state_mst stmst on ad.state_mkey = stmst.mkey
left join p_Emp_Family fam on e.mkey=fam.mkey and fam.entry_srno=1
left join p_relation_mst r on r.mkey=fam.relation_mkey
left join p_Emp_Family fam1 on e.mkey=fam1.mkey and fam1.entry_srno=2
left join p_relation_mst r1 on r1.mkey=fam1.relation_mkey
left join p_Emp_Family fam2 on e.mkey=fam2.mkey and fam2.entry_srno=3
left join p_relation_mst r2 on r2.mkey=fam2.relation_mkey
left join p_Emp_Family fam3 on e.mkey=fam3.mkey and fam3.entry_srno=4
left join p_relation_mst r3 on r3.mkey=fam3.relation_mkey
left join type_mst_a bld on e1.blood_type=bld.master_mkey and bld.type_code='BG' and bld.delete_flag='N'
left join user_mst usr on e.mkey=usr.employee_mkey
left join p_bank_mst bank on e.bank_code=bank.mkey
--where type_code='PWP'
where 1=1 and e.emp_card_no!=9999
and e.emp_card_no in (select emp_card_no from P_Emp_Confirmation_Hdr)
) aa
--and e.emp_card_no=9999
--and e.mkey<=1932
-- b.mkey = (select max(mkey) from emp_shift_details c where b.emp_card_no=c.emp_card_no)
--and e.emp_card_no=2192
--and ctc.mkey = (select max(mkey) from ctc_recal_group_hdr c where e.mkey=c.emp_mkey and delete_flag='N')
GO
Do let me know if you need anything else