SELECT 
	tmp.[nip],
    tmp.[nama_karyawan],
    tmp.[status],
    tmp.[branch],
    LOWER(tmp.[email]) AS email,
    --tmp.[email_recon],
	hris.[IdNo],
	hris.[NoParticipants],
	hris.[Name],
	hris.[EmployeeName],
	hris.[Status],
	dapes.*
FROM 
	[GrpInsurance].[dbo].[tmp_email_karyawan_eli] AS tmp LEFT JOIN
	[GrpInsurance].[dbo].[tmp_data_hris] AS hris ON tmp.nip = hris.IdNo LEFT JOIN
	(
		SELECT
			pa.No_Polis,
			prs.Nama_Perusahaan,
			pst.No_Peserta,
			pst.Nama,
			pa.Status AS PA_Status,
			pst.Status AS PST_Status
		FROM
			[ELI-DB10\grpinsurance].GrpInsurance.dbo.Polis_Asuransi AS pa INNER JOIN
			[ELI-DB10\grpinsurance].GrpInsurance.dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
			[ELI-DB10\grpinsurance].GrpInsurance.dbo.Peserta AS pst ON pa.No_Proposal = pst.No_Proposal
		WHERE
			pa.Active_Ya = 1
			AND prs.Nama_Perusahaan LIKE '%equity%'
	) AS dapes ON hris.[NoParticipants] = dapes.No_Peserta
WHERE
	COALESCE(tmp.[email],'') <> ''
	AND COALESCE(hris.[NoParticipants],'') <> ''
	AND hris.[Status] = 'V'
	AND dapes.PST_Status = 'I'
--ORDER BY
--	hris.[Name]

-- 438 row

--No Polis
--1992.0011.0
--1999.0082.0
--2017.0052.0
--2018.0049.0


--SELECT 
--	[IdNo],
--	[NoParticipants],
--	[Name],
--	[EmployeeName],
--	[Status]
--FROM 
--	[GrpInsurance].[dbo].[tmp_data_hris]

SELECT
	*
FROM
	[ELI-DB10\grpinsurance].GrpInsurance.dbo.custport_user_login_client
WHERE
	username IN (SELECT username FROM tmp_custport_user_login_client)