USE [WebBranches]
GO
/****** Object:  StoredProcedure [dbo].[proc_list_polis_web]    Script Date: 01/07/2013 15:56:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[proc_list_polis_web] 
  @pi_no_polis     varchar(100), 
  @pi_id_user  varchar(4)
AS
BEGIN
  DECLARE @p_id_unit_pelayanan varchar(4)
  
	SELECT @p_id_unit_pelayanan = id_unit_pelayanan 
	FROM user_unit_pelayanan
	WHERE id_user = @pi_id_user
  
	IF (@p_id_unit_pelayanan = '0000')
	  BEGIN
			select 
				s.no_spaj,    
				p.no_polis, 
				y.nama_lengkap nama_pemegang, 
				y.id_jenis_kelamin jenis_kelamin_pemegang, 
				y.tgl_lahir tgl_lahir_pemegang, 
				x.nama_lengkap nama_tertanggung, 
				x.id_jenis_kelamin jenis_kelamin_tertanggung, 
				x.tgl_lahir tgl_lahir_tertanggung 
				--t.nama_ahli_waris nama_termaslahat,
				--t.tgl_lahir tgl_lahir_termaslahat
			from IndInsurance.dbo.polis p
				--inner join polis_termaslahat t on p.id_polis = t.id_polis 
				inner join IndInsurance.dbo.polis_tertanggung x on p.id_polis = x.id_polis 
				inner join IndInsurance.dbo.polis_pemegang y on p.id_polis = y.id_polis 
				inner join IndInsurance.dbo.spaj s on p.no_spaj = s.no_spaj
			where p.no_polis=@pi_no_polis
			and p.id_polis = IndInsurance.dbo.func_get_id_polis_per_tanggal(p.no_polis, IndInsurance.dbo.func_fixed_date(getdate()), 0)
	  END
	ELSE
	  BEGIN
			select 
				s.no_spaj,    
				p.no_polis, 
				y.nama_lengkap nama_pemegang, 
				y.id_jenis_kelamin jenis_kelamin_pemegang, 
				y.tgl_lahir tgl_lahir_pemegang, 
				x.nama_lengkap nama_tertanggung, 
				x.id_jenis_kelamin jenis_kelamin_tertanggung, 
				x.tgl_lahir tgl_lahir_tertanggung 
				--t.nama_ahli_waris nama_termaslahat,
				--t.tgl_lahir tgl_lahir_termaslahat
			from IndInsurance.dbo.polis p
				--inner join polis_termaslahat t on p.id_polis = t.id_polis 
				inner join IndInsurance.dbo.polis_tertanggung x on p.id_polis = x.id_polis 
				inner join IndInsurance.dbo.polis_pemegang y on p.id_polis = y.id_polis 
				inner join IndInsurance.dbo.spaj s on p.no_spaj = s.no_spaj
			where p.no_polis=@pi_no_polis
			and p.id_polis = IndInsurance.dbo.func_get_id_polis_per_tanggal(p.no_polis, IndInsurance.dbo.func_fixed_date(getdate()), 0)
			and
				(
					(coalesce(p.id_unit_pelayanan_produksi,'') IN (
					SELECT id_unit_pelayanan FROM IndInsurance.dbo.func_get_hirarki_unit_pelayanan(@p_id_unit_pelayanan)))
					or 	(coalesce(p.id_unit_pelayanan_korespondensi,'') IN (
					SELECT id_unit_pelayanan from IndInsurance.dbo.func_get_hirarki_unit_pelayanan(@p_id_unit_pelayanan)))
				) 
	END
END

