USE [WebBranches]
GO
/****** Object:  StoredProcedure [dbo].[proc_text_info_curr_polis_manfaat]    Script Date: 01/08/2013 08:47:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Last Modified: Tommy, 2012-06-28
CREATE procedure [dbo].[proc_text_info_curr_polis_manfaat]
  @pi_no_polis varchar(12)
AS
BEGIN
  DECLARE @Image nVarchar(4000)
  SET @Image=''
  
  IF EXISTS(
    SELECT * 
    FROM 
      IndInsurance.dbo.manfaat_jt T1 
      INNER JOIN
			(
				select * 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
			) T2
      ON T1.no_Polis = T2.no_Polis 
      INNER JOIN
      IndInsurance.dbo.jenis_manfaat T3 
      ON T1.id_jenis_manfaat = T3.id_Jenis_manfaat 
      INNER JOIN
      IndInsurance.dbo.plan_asuransi T4 
      ON T1.Id_Plan_dasar = T4.id_plan INNER JOIN
      IndInsurance.dbo.Valuta T5 
      ON T1.Id_Valuta=T5.Id_Valuta 
/*
  WHERE 
    (T2.no_polis=@pi_no_polis) AND
    (t2.aktif_ya=1)
*/
  )
  BEGIN
    SET @image = 'TAHAPAN'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
    SET @image = @image+
          IndInsurance.dbo.func_GetJustify('Th.Ke',3,5,'-')+ '  '+
          IndInsurance.dbo.func_GetJustify('J.Tempo',2,10,'-')+ '  ' +
          IndInsurance.dbo.func_GetJustify('Jumlah',2,17,'-') + '  ' +
          IndInsurance.dbo.func_GetJustify('Jenis Manfaat',2,15,'-') + '  '+
          IndInsurance.dbo.func_GetJustify('Tgl Lunas',2,15,'-') + '  '+
          IndInsurance.dbo.func_GetJustify('STATUS',2,14,'-') +CHAR(13)+CHAR(10)

    SELECT     
      @image = @image+
        IndInsurance.dbo.func_GetJustify(CAST(T1.jt_Ke as varchar(3)), 3,5,' ')+ '  ' +
        IndInsurance.dbo.func_GetJustify(IndInsurance.dbo.func_Datetostr(T1.Tgl_jt),3, 10, ' ')+'  ' +
        IndInsurance.dbo.func_GetJustify(IndInsurance.dbo.func_FormatCurr(T1.jml_manfaat,20,2,1),3,17,' ')+ '  '+
        IndInsurance.dbo.func_GetJustify(T3.deskripsi, 1, 15, ' ') +'  ' +
        IndInsurance.dbo.func_GetJustify(IndInsurance.dbo.func_date_format('dd/MM/yyyy',TL.tgl_valuta), 1, 15, ' ') +'  ' +
        IndInsurance.dbo.func_GetJustify(CASE IsNull(T1.Status,'') WHEN 'G' THEN 'belum diambil' else 'sudah diambil' END, 3,14,' ')+CHAR(13)+CHAR(10)
    FROM 
      IndInsurance.dbo.manfaat_jt T1 
      INNER JOIN
			(
				select * 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
			) T2
      ON T1.no_Polis = T2.no_Polis 
      INNER JOIN
      IndInsurance.dbo.jenis_manfaat T3 
      ON T1.id_jenis_manfaat = T3.id_Jenis_manfaat 
      INNER JOIN
      IndInsurance.dbo.plan_asuransi T4 
      ON T1.Id_Plan_dasar = T4.id_plan 
      INNER JOIN
      IndInsurance.dbo.Valuta  T5 
      ON T1.Id_Valuta=T5.Id_Valuta
      left join
      IndInsurance.dbo.transaksi T
      on t1.id_manfaat = t.no_referensi
      left join
      IndInsurance.dbo.transaksi_lunas TL
      on T.id_transaksi = TL.id_transaksi  and (TL.status = 'I')
/*
    WHERE 
      (T2.no_polis = @pi_no_polis) AND 
      (t2.aktif_ya = 1) --and
*/
  END
  SELECT Memo=CAST(@Image as Text)
END
