CLIENT SUPPORT » Asuransi Bintang - Existing

USE [Bintang_Prod]

GO

/****** Object:  StoredProcedure [dbo].[empnewhiring]    Script Date: 11/18/2021 2:26:43 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[empnewhiring] as

begin

DECLARE @now VARCHAR(8) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')

--set @now='20211122'

DECLARE @mail VARCHAR(MAX)=(SELECT val1 FROM base_cust_parameter WHERE param ='BASE_CUST_PUSH_MAIL_HIRING' )

SELECT DISTINCT md01.full_name,md01.nick_name,objo.[description] + ' - ' + obcs.[description] as [description] , obj.[description] + ' - ' + obcs.[description] as [description]  ,(dbo.fn_formatdatetime(GETDATE(), 'dd mmmm yyyy')) as tanggal ,

--md12.comm_description,

'employee@asuransibintang.com' as comm_description,

dbo.fn_formatdatetime(md01.birth_date,'dd/mmmm/yyyy'),md12a.comm_description FROM hr_md_emp_md0001 md01

LEFT JOIN hr_md_emp_md0002 md02 on md01.emp_id = md02.emp_id and md02.end_date='99991231'

and md02.start_date = @now

and md02.movement_type='01'

LEFT JOIN hr_md_emp_md0002 md022 on  md022.end_date='99991231' and md022.employee_status='01'

 LEFT JOIN hr_md_emp_md0012 md12 ON md022.emp_id = md12.emp_id

and md12.comm_type='04'

LEFT JOIN hr_md_emp_md0012 md12a ON md02.emp_id = md12a.emp_id

and md12a.comm_type='04'

LEFT JOIN base_cust_ref_emp_office offs on md02.employee_office = offs.emp_subarea

LEFT JOIN hr_md_orm_object obj on md02.position = obj.object and obj.class='P'

and obj.end_date='99991231'

LEFT JOIN hr_md_orm_object objo on md02.organization = objo.object and objo.class='O'

and objo.end_date='99991231'

LEFT JOIN hr_md_orm_object obcs on md02.costcenter = obcs.object and obcs.class='CC'

and obcs.end_date='99991231'

WHERE md02.start_date = @now

and md02.movement_type='01'

and md12.comm_description IS NOT NULL

end