USE [AJK]
GO
/****** Object:  StoredProcedure [dbo].[proc_custport_lookup_customer]    Script Date: 31/07/2018 15:08:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- last modified by Pribadi Pamungkas. 2018-07-31. => tambah kolom no_hp & email
-- created by Pribadi Pamungkas. 2018-06-29.
ALTER PROCEDURE [dbo].[proc_custport_lookup_customer]
	@pi_no_polis VARCHAR(12),
	@pi_contract_no VARCHAR(16),
	@pi_customer_name VARCHAR(50),
	@pi_ktp_id VARCHAR(16),
	@po_message VARCHAR(100) OUTPUT
AS
BEGIN

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

	IF COALESCE(@pi_no_polis, '') = ''
		SET @po_message = 'No Polis tidak boleh kosong'
	ELSE
		BEGIN
			IF COALESCE(@pi_contract_no, '') = ''
			   AND COALESCE(@pi_customer_name, '') = ''
			   AND COALESCE(@pi_ktp_id, '') = ''
				SET @po_message = 'Contract No atau Customer Name dan KTP tidak boleh kosong'
			ELSE

				BEGIN
	
					SELECT
						nama_peserta_1 AS nama_peserta,
						dbo.func_date_format('dd-mmm-YYYY',tgl_lahir_1) AS tgl_lahir,
						no_kontrak_external AS contract_no,
						no_identitas,
						no_peserta AS certificate_no,
						--tgl_mulai,
						--tgl_akhir,
						dbo.func_date_format('dd-mmm-YYYY',tgl_mulai) + ' - ' + dbo.func_date_format('dd-mmm-YYYY',tgl_akhir) AS insurance_periode,
						masa,
						uang_pertanggungan,
						CASE status
							WHEN 'I' THEN 'Active'
							WHEN 'C' THEN 'Cancel'
							WHEN 'D' THEN 'Death'
							WHEN 'E' THEN 'Verifikasi'
							WHEN 'G' THEN 'Draft'
							WHEN 'M' THEN 'Maturity'
							WHEN 'O' THEN 'Keluar'
							WHEN 'R' THEN 'Reject'
							WHEN 'L' THEN 'Terminate'	-- ?
							ELSE 'Unknown'
						END AS [status],
						'IDR' AS currency,
						id_peserta,
						no_polis,
						no_hp, 
						email
					FROM
						peserta
					WHERE
						no_polis = @pi_no_polis
						AND
							(
								((COALESCE(@pi_contract_no, '') <> '') AND (no_kontrak_external = @pi_contract_no)) OR
								(COALESCE(@pi_contract_no, '') = '') 
							)  
						AND
							(
								((COALESCE(@pi_customer_name, '') <> '') AND (nama_peserta_1 = @pi_customer_name)) OR
								(COALESCE(@pi_customer_name, '') = '') 
							)  
						AND
							(
								((COALESCE(@pi_ktp_id, '') <> '') AND (no_identitas = @pi_ktp_id)) OR
								(COALESCE(@pi_ktp_id, '') = '') 
							)
		
				END
		END


END
