USE [WebBranches]
GO
/****** Object:  StoredProcedure [dbo].[proc_text_info_curr_polis_plan]    Script Date: 01/08/2013 08:50:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--exec proc_text_info_curr_polis_plan '06.02286.3'
CREATE procedure [dbo].[proc_text_info_curr_polis_plan]
@pi_no_polis varchar(12)
AS
DECLARE @Image nVarchar(4000)
SET @Image=''
/**
SET @image=@image+'DETAIL PLAN'+CHAR(13)+CHAR(10)
SET @image=@image+'--'+
  IndInsurance.dbo.func_GetJustify('Deskripsi',1,30,'-')+
  IndInsurance.dbo.func_GetJustify('Mulai',3,12,'-')+
  IndInsurance.dbo.func_GetJustify('MK',3,5,'-')+
  IndInsurance.dbo.func_GetJustify('MP',3,5,'-')+
  IndInsurance.dbo.func_GetJustify('UP',3,15,'-')+
  IndInsurance.dbo.func_GetJustify('Premi',3,15,'-') +
  IndInsurance.dbo.func_GetJustify('Ekstra Premi',3,15,'-') +CHAR(13)+CHAR(10)
**/  
SELECT    
@image=@image+
upper(T4.deskripsi)+CASE WHEN IsNull(T2.kelas,'')<>'' and IsNull(T2.kelas,'')<>'0' THEN ' '+IsNull(T2.kelas,'') ELSE '' END+ CASE WHEN IsNull(T2.risiko,'')<>'' THEN '/'+IsNull(T2.risiko,'') ELSE '' END+
' ('+CASE WHEN T2.id_plan=t2.id_plan_dasar THEN 'Plan Dasar' ELSE 'Rider' END+')'+CHAR(13)+CHAR(10)+
'- Tgl. mulai            :'+IndInsurance.dbo.func_Datetostr(T2.tgl_mulai)+CHAR(13)+CHAR(10)+
'- Masa kontrak          :'+CAST(T2.masa_kontrak as varchar(2))+' tahun'+CHAR(13)+CHAR(10)+
'- Masa pembayaran premi :'+CAST(T2.masa_pembayaran_premi as varchar(2))+' tahun'+CHAR(13)+CHAR(10)+  
'- Uang Pertanggungan    :'+IndInsurance.dbo.func_GetJustify(IndInsurance.dbo.func_FormatCurr(T2.uang_pertanggungan,20,2,1),3,15,' ')+CHAR(13)+CHAR(10)+
case when IndInsurance.dbo.func_get_kelompok_produk(T1.id_plan_dasar) = '01' then
'- Premi                 :'+IndInsurance.dbo.func_GetJustify(IndInsurance.dbo.func_FormatCurr(T2.Premi,20,2,1),3,15,' ') else '' end
--'- Biaya Asuransi        :'+IndInsurance.dbo.func_GetJustify(IndInsurance.dbo.func_FormatCurr(T2.Premi,20,2,1),3,15,' ') end
 +CHAR(13)+CHAR(10)+
case when IndInsurance.dbo.func_get_kelompok_produk(T1.id_plan_dasar) = '01' then
'- Ekstra premi          :'+IndInsurance.dbo.func_GetJustify(IndInsurance.dbo.func_FormatCurr(T2.ekstra_premi,20,2,1),3,15,' ') else '' end +CHAR(13)+CHAR(10)
FROM         IndInsurance.dbo.polis_plan T2 INNER JOIN
                      --plan_detail T3 ON T2.id_plan_dasar = T3.id_plan_dasar AND T2.id_plan = T3.id_plan INNER JOIN
                      (select id_polis, id_plan_dasar, coalesce(premi_dasar,0) premi_dasar, coalesce(Premi_rider,0) premi_rider,
												coalesce(premi_berkala,0) premi_berkala, coalesce(top_up_berkala,0) top_up_berkala
											from IndInsurance.dbo.polis
											where id_polis = IndInsurance.dbo.func_get_id_polis_per_tanggal(no_polis, IndInsurance.dbo.func_fixed_date(getdate()), 0)
											and no_polis = @pi_no_polis) T1 ON T2.id_polis = T1.id_polis left join
                      IndInsurance.dbo.plan_asuransi T4 ON T2.id_plan = T4.id_plan
WHERE
--(T1.no_polis=@pi_no_polis) and 
T2.aktif_ya=1

SELECT Memo=CAST(@Image as Text)


