USE GrpInsurance

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas. 2019-02-18
CREATE PROCEDURE dbo.proc_rep_userportalmember
	@pi_no_polis varchar(12),
	@pi_status_aktivasi INT
AS
BEGIN

	SET NOCOUNT ON;	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	SELECT
		cpu.no_polis,
		pp.Nama_Perusahaan,
		cpu.username,
		cpu.no_peserta,
		ps.Nama,
		CASE cpu.status
			WHEN 'I' THEN 'Sudah Aktivasi'
			WHEN 'G' THEN 'Belum Aktivasi'
			ELSE 'Unknown'
		END AS status
	FROM
		dbo.custport_user_login_client AS cpu INNER JOIN
		(
			SELECT DISTINCT
				pa.No_Polis,
				prs.Nama_Perusahaan
			FROM
				dbo.Polis_Asuransi AS pa INNER JOIN
				dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
			WHERE
				pa.Active_Ya = 1
		) AS pp ON cpu.no_polis = pp.No_Polis INNER JOIN
		(
			SELECT DISTINCT
				pst.No_Peserta,
				pst.Nama,
				pa.No_Polis
			FROM
				dbo.Peserta AS pst INNER JOIN
				dbo.Polis_Asuransi AS pa ON pst.No_Proposal = pa.No_Proposal
			WHERE
				pa.Active_Ya = 1
				AND pa.Status = 'I'
		) AS ps ON pp.No_Polis = ps.No_Polis
					AND cpu.no_peserta = ps.No_Peserta
	WHERE
		(cpu.no_polis = @pi_no_polis OR isnull(@pi_no_polis,'') = '')
		AND (cpu.status = 'I' OR @pi_status_aktivasi<>1)
		AND (cpu.status = 'G' OR @pi_status_aktivasi<>2)
	ORDER BY
		pp.Nama_Perusahaan,
		ps.Nama

END
GO
