USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_CreateListBenefit034]    Script Date: 10/29/2014 19:47:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- last modified by Pribadi Pamungkas, 2014-10-29
ALTER Procedure [dbo].[proc_CreateListBenefit034]
@NoProposal varchar(9),
@IdProduk varchar(10) 
AS
  EXEC proc_RepairKelasProduk @NoProposal 
  SET NOCOUNT ON
  DECLARE @Count Int
  DECLARE @CountBenefit Int
  DECLARE @Baris Int
  DECLARE @Kolom Int
  DECLARE @Urut Int
  DECLARE @Urut2 Int
  DECLARE @ModelPremi Int
  DECLARE @SQLText Nvarchar(4000)
  DECLARE @SQLTotal Nvarchar(1000)

  DECLARE @Kode varchar(10)
  DECLARE @ColField varchar(30)
  DECLARE @Deskripsi varchar(200)
  DECLARE @Benefit Numeric(30)
  DECLARE @PrPria Numeric(30)
  DECLARE @PrWanita Numeric(30)
  DECLARE @PrAnak Numeric(30)
  DECLARE @PrTK Numeric(30) 
  DECLARE @PrK0 Numeric(30) 
  DECLARE @PrK1 Numeric(30) 
  DECLARE @PrK2 Numeric(30) 
  DECLARE @PrK3 Numeric(30) 
  DECLARE @PrK4 Numeric(30) 
  DECLARE @PrK5 Numeric(30) 
  DECLARE @PrW0 Numeric(30) 
  DECLARE @PrW1 Numeric(30) 
  DECLARE @PrW2 Numeric(30) 
  DECLARE @PrW3 Numeric(30) 
  DECLARE @PrW4 Numeric(30) 
  DECLARE @PrW5 Numeric(30)
  DECLARE @As_Cash bit
  DECLARE @Keluarga bit
  SELECT  @Keluarga=Keluarga, @ModelPremi= Model_Premi FROM Produk_Polis WHERE No_Proposal=@NoProposal AND Id_Produk=@IdProduk
  --CREATE TABLE TEMPORARY----------------------------------------
  EXEC (N'DECLARE @Ada bit EXEC TmpInsurance..gproc_CekAdaTabel TmpBenefit034, @Ada OUTPUT '+
  N' IF @ada=1 DROP TABLE TmpInsurance..TmpBenefit034')
  DECLARE TmpCursor CURSOR FOR
  SELECT  dbo.Premi_Polis.Kelas FROM dbo.Produk_Polis INNER JOIN
          dbo.Premi_Polis ON dbo.Produk_Polis.Id_Produk = dbo.Premi_Polis.Id_Produk
  WHERE   (dbo.Produk_Polis.No_Proposal=isnull(@NoProposal,'')) AND 
          (dbo.Produk_Polis.Id_Produk=isnull(@IdProduk,'')) AND 
          (dbo.Produk_Polis.Status='I')
  ORDER BY dbo.Premi_Polis.Urut

  SET @SQLTotal=N''
  SET @SQLText=  'CREATE TABLE [TmpInsurance].[dbo].[TmpBenefit034] ('+
                 'Kode varchar(2) NOT NULL, '+
                 'Urut int NOT NULL, '+
                 'Kode_Santunan varchar(10) NULL, '+
                 'Kode_Produk varchar(10) NULL, '+
                 'Deskripsi varchar(100) NULL, '+
                 'Umur Int Null, '+
                 'Ganti_Produk int Null, '+
                 'Ganti_Item Int Null, '+
                 'As_Cash bit Default(0), '+
                 'Kode_Santunan_Asal varchar(10) NULL, '
  OPEN TmpCursor
  FETCH NEXT FROM TmpCursor INTO @ColField
  WHILE @@FETCH_STATUS = 0
  BEGIN
       SET @SQLText = @SQLText + N'['+@ColField+N'] Numeric(30) Null Default (0), ' 
       SET @SQLTotal= @SQLTotal+N'['+@ColField+N']  +'
       FETCH NEXT FROM TmpCursor INTO @ColField
  END
  CLOSE TmpCursor
  DEALLOCATE TmpCursor
  SET @SQLTotal= @SQLTotal+N'0'
  SET @SQLText = @SQLText + N' CONSTRAINT pk_TmpBenefit034 PRIMARY KEY (Kode, Urut)) ON [PRIMARY]'
  EXEC(@SQLText)

  --ISI BENEFIT----------------------------------------
  DECLARE @Tingkat int
  DECLARE @Margin int
  DECLARE @OldKode varchar(10)
  DECLARE @Ganti_Produk Numeric(30)
  DECLARE @Ganti_Item Numeric(30)
  DECLARE TmpCursor CURSOR FOR
  SELECT dbo.Item_Santunan_Polis.Kode_Santunan, dbo.Item_Santunan_Polis.Deskripsi, dbo.Benefit_Polis.Kelas, dbo.Benefit_Polis.Benefit, isnull(dbo.Item_Santunan_Polis.Tingkat,0),
         dbo.Produk_Polis.Penggantian, dbo.Item_Santunan_Polis.Prosen_Benefit
  FROM   dbo.Produk_Polis 
         INNER JOIN dbo.Benefit_Polis ON dbo.Produk_Polis.Id_Produk = dbo.Benefit_Polis.Id_Produk 
         INNER JOIN dbo.Item_Santunan_Polis ON dbo.Produk_Polis.Id_Produk = dbo.Item_Santunan_Polis.Id_Produk AND 
                    dbo.Benefit_Polis.Kode_Santunan = dbo.Item_Santunan_Polis.Kode_Santunan
  WHERE (dbo.Produk_Polis.No_Proposal = isnull(@NoProposal,'')) AND 
        (dbo.Produk_Polis.Id_Produk=isnull(@IdProduk,'')) AND 
        (dbo.Produk_Polis.Status='I')
  ORDER BY dbo.Item_Santunan_Polis.Urutan
  SET @Urut  =1
  SET @Urut2 =0
                 
  OPEN TmpCursor
  FETCH NEXT FROM TmpCursor INTO @Kode, @Deskripsi, @ColField, @Benefit, @Tingkat, @Ganti_Produk, @Ganti_Item 
  SET @Margin =@Tingkat
  WHILE @@FETCH_STATUS = 0
  BEGIN
        SET @Deskripsi = REPLACE(@Deskripsi,'''','''''')
        IF NOT EXISTS(SELECT * FROM TmpInsurance..TmpBenefit034 WHERE LTRIM(Deskripsi)=LTRIM(@Deskripsi) AND Kode_Santunan_Asal=@Kode)  -- modified by Pribadi Pamungkas, 2014-10-29
        BEGIN
            SELECT @As_Cash=Bts_Maks FROM Item_Santunan_Polis WHERE Id_Produk=@IdProduk AND Kode_Santunan=@Kode
             IF isnull(@Tingkat,0)>isnull(@Margin,0)
             BEGIN
                 SET @Deskripsi=' '+@Deskripsi
                 
                 SET @SQLText = N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi, Ganti_Produk, Ganti_Item, As_Cash, Kode_Santunan_Asal) VALUES(''01'', '+CAST(@Urut as varchar)+N', '+''''''+N','''+@Deskripsi+N''','+CAST(isnull(@Ganti_Produk,0) as varchar)+N','+CAST(isnull(@Ganti_Item,0) as varchar)+N','+CAST(isnull(@As_Cash,0) as varchar)+N','''+CAST(@Kode as varchar)+N''')'  -- modified by Pribadi Pamungkas, 2014-10-29
             END
             ELSE 
             BEGIN
                  SET @Urut2 = @Urut2 + 1
                  SET @SQLText = N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi, Ganti_Produk, Ganti_Item, As_Cash, Kode_Santunan_Asal) VALUES(''01'', '+CAST(@Urut as varchar)+N', '+CAST(@Urut2 as varchar)+N','''+@Deskripsi+N''','+CAST(@Ganti_Produk as varchar)+N','+CAST(@Ganti_Item as varchar)+N','+CAST(isnull(@As_Cash,0) as varchar)+N','''+CAST(@Kode as varchar)+N''')'  -- modified by Pribadi Pamungkas, 2014-10-29
             END
             EXEC(@SQLText)
        END
        SET @Benefit=isnull(@Benefit,0)
        SET @OldKode = LEFT(@Kode,4)

        EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@Benefit+' WHERE LTRIM(Deskripsi)=LTRIM('''+@Deskripsi+N''')')
        SET @Urut = @Urut + 1
        FETCH NEXT FROM TmpCursor INTO  @Kode, @Deskripsi, @ColField, @Benefit, @Tingkat, @Ganti_Produk, @Ganti_Item 
  END
  CLOSE TmpCursor
  DEALLOCATE TmpCursor
  --------------------
  DECLARE @Awal int 
  DECLARE @Akhir int
  DECLARE @Santunan Numeric(30)
  DECLARE @Premi Numeric(30)

  IF @ModelPremi=3
  BEGIN
    --PREMI MODEL 3--------------------------------
    SELECT @Count=Count(*) FROM TmpInsurance..TmpBenefit034  WHERE Kode='01'
    IF isnull(@Keluarga,0)=1 SET @SQLText = N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''01'', '+CAST(@Urut as varchar)+N','''+''+N''',''Santunan Maksimum per Masa Berlaku Asuransi, Per Keluarga'')'
    ELSE SET @SQLText = N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''01'', '+CAST(@Urut as varchar)+N','''+''+N''',''Santunan Maksimum per Masa Berlaku Asuransi, Per Tertanggung'')'
    EXEC(@SQLText)

    SELECT  @Akhir = Max(dbo.Produk_Polis.Batas_Usia) FROM dbo.Produk_Polis 
    WHERE (dbo.Produk_Polis.No_Proposal =isnull(@NoProposal,'')) AND (dbo.Produk_Polis.Id_Produk=isnull(@IdProduk,''))AND (dbo.Produk_Polis.Status='I')

    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(1 as varchar)+N',''*'''+N',''Umur s.d.'+CAST(@Akhir as varchar)+''')'
    EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(2 as varchar)+N','''',''Pria'')'
    EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(3 as varchar)+N','''',''Wanita'')'
    EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(4 as varchar)+N','''',''Anak'')'
    EXEC(@SQLText)

    DECLARE Employee_Cursor CURSOR FOR
    SELECT   dbo.Ekstra_Premi_Polis.Awal, dbo.Ekstra_Premi_Polis.Akhir
    FROM   dbo.Produk_Polis INNER JOIN
             dbo.Ekstra_Premi_Polis ON dbo.Produk_Polis.Id_Produk = dbo.Ekstra_Premi_Polis.Id_Produk
    WHERE   (dbo.Produk_Polis.No_Proposal = isnull(@NoProposal,'')) AND (dbo.Produk_Polis.Id_Produk=isnull(@IdProduk,''))
    GROUP BY dbo.Ekstra_Premi_Polis.Awal, dbo.Ekstra_Premi_Polis.Akhir
    ORDER BY dbo.Ekstra_Premi_Polis.Awal
    SET @Urut =5
    OPEN Employee_Cursor
    FETCH NEXT FROM Employee_Cursor INTO @Awal, @Akhir

    WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi ) VALUES(''02'', '+CAST(@Urut as varchar)+N',''*'''+N',''Umur '+CAST(@Awal as varchar(2))+' s.d. '+CAST(@Akhir as varchar(2))+N''')'
      EXEC(@SQLText)
      SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi, Umur ) VALUES(''02'', '+CAST(@Urut+1 as varchar)+N','''','''+N' Pria'','+CAST(@Awal as varchar(3))+')'
      EXEC(@SQLText)
      SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi, Umur ) VALUES(''02'', '+CAST(@Urut+2 as varchar)+N','''','''+N' Wanita'','+CAST(@Awal as varchar(3))+')'
      EXEC(@SQLText)
      SET @Urut = @Urut + 3
      FETCH NEXT FROM Employee_Cursor  INTO @Awal, @Akhir
    END
    CLOSE Employee_Cursor
    DEALLOCATE Employee_Cursor
    
    DECLARE TmpCursor CURSOR FOR
    SELECT  dbo.Premi_Polis.Kelas, dbo.Premi_Polis.Premi_Pria, dbo.Premi_Polis.Premi_Wanita, dbo.Premi_Polis.Premi_Anak,  dbo.Premi_Polis.Benefit
            FROM dbo.Produk_Polis INNER JOIN dbo.Premi_Polis ON dbo.Produk_Polis.Id_Produk = dbo.Premi_Polis.Id_Produk
            WHERE (dbo.Produk_Polis.No_Proposal =isnull(@NoProposal,'')) AND (dbo.Produk_Polis.Id_Produk=isnull(@IdProduk,''))AND (dbo.Produk_Polis.Status='I')
    OPEN TmpCursor
    FETCH NEXT FROM TmpCursor INTO @ColField, @PrPria, @PrWanita, @PrAnak, @Benefit
    WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @Santunan=@Benefit
      SET @PrPria =isnull(@PrPria,0)
      SET @PrWanita =isnull(@PrWanita,0)
      SET @PrAnak =isnull(@PrAnak,0)
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrPria+' WHERE Deskripsi=''Pria''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrWanita+' WHERE Deskripsi=''Wanita''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrAnak+' WHERE Deskripsi=''Anak''')
      
      IF @Benefit>=1000000000.00  AND @IdProduk not in ('2014.00043','2014.00022', '2014.00037','2014.00049') SET @Benefit=0.00
      
      IF isnull(@Keluarga,0)=1   EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@Benefit+' WHERE Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Per Keluarga''')
      ELSE
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@Benefit+' WHERE Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Per Tertanggung''')
      FETCH NEXT FROM TmpCursor INTO @ColField, @PrPria, @PrWanita, @PrAnak, @Benefit
    END
    CLOSE TmpCursor
    DEALLOCATE TmpCursor
    ---------------------------
    DECLARE TmpCursor CURSOR FOR
    SELECT dbo.Ekstra_Premi_Polis.Kelas, dbo.Ekstra_Premi_Polis.Premi_Pria, dbo.Ekstra_Premi_Polis.Premi_Wanita, dbo.Ekstra_Premi_Polis.Awal,  dbo.Ekstra_Premi_Polis.Akhir 
    FROM   dbo.Produk_Polis INNER JOIN
           dbo.Ekstra_Premi_Polis ON dbo.Produk_Polis.Id_Produk = dbo.Ekstra_Premi_Polis.Id_Produk INNER JOIN
           dbo.Premi_Polis ON dbo.Ekstra_Premi_Polis.Id_Produk = dbo.Premi_Polis.Id_Produk AND 
           dbo.Ekstra_Premi_Polis.Kelas = dbo.Premi_Polis.Kelas
    WHERE  (dbo.Produk_Polis.No_Proposal = @NoProposal) AND (dbo.Produk_Polis.Id_Produk=@IdProduk)
    OPEN TmpCursor
    FETCH NEXT FROM TmpCursor INTO @ColField, @PrPria, @PrWanita, @Awal, @Akhir
    WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrPria+  ' WHERE Deskripsi='' Pria'' AND Umur='+@Awal)
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrWanita+' WHERE Deskripsi='' Wanita''AND Umur='+@Awal)
      FETCH NEXT FROM TmpCursor INTO @ColField, @PrPria, @PrWanita, @Awal, @Akhir
    END
    CLOSE TmpCursor
    DEALLOCATE TmpCursor


    If @Benefit=0 
    BEGIN
      IF isnull(@Keluarga,0)=1   
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Tidak Terbatas'''+N'WHERE Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Per Keluarga''')
      ELSE EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Tidak Terbatas'''+N'WHERE Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Per Tertanggung''')
    END

  END
  ELSE IF @ModelPremi=4
  BEGIN

    --PREMI MODEL 4--------------------------------
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(1 as varchar)+ N',''1'+N''',''TK'')'   EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(2 as varchar)+ N',''2'+N''',''K0'')'   EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(3 as varchar)+ N',''3'+N''',''K1'')'   EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(4 as varchar)+ N',''4'+N''',''K2'')'   EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(5 as varchar)+ N',''5'+N''',''K3'')'   EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(6 as varchar)+ N',''6'+N''',''K4'')'   EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(7 as varchar)+ N',''7'+N''',''K5'')'   EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(8 as varchar)+ N',''8'+N''',''W0'')'   EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(9 as varchar)+ N',''9'+N''',''W1'')'   EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(10 as varchar)+N',''10'+N''',''W2'')'   EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(11 as varchar)+N',''11'+N'''''1'''+N',''W3'')'   EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(12 as varchar)+N',''12'+N''',''W4'')'   EXEC(@SQLText)
    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(13 as varchar)+N',''13'+N''',''W5'')'   EXEC(@SQLText)
--    IF @Count>1 
    BEGIN
         IF isnull(@Keluarga,0)=1    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''01'', '+CAST(@Urut as varchar)+N','''+N''',''Santunan Maksimum per Masa Berlaku Asuransi, Per Keluarga'')'         
         ELSE SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''01'', '+CAST(@Urut as varchar)+N','''+N''',''Santunan Maksimum per Masa Berlaku Asuransi, Per Tertanggung'')'
         EXEC(@SQLText)
    END

    DECLARE TmpCursor CURSOR FOR
    SELECT dbo.Premi_Polis.Kelas, 
isnull(dbo.Premi_Polis.Premi_TK,0),
isnull(dbo.Premi_Polis.Premi_K0, 0),
isnull(dbo.Premi_Polis.Premi_K1, 0),
isnull(dbo.Premi_Polis.Premi_K2, 0),
isnull(dbo.Premi_Polis.Premi_K3, 0),
isnull(dbo.Premi_Polis.Premi_K4, 0),
isnull(dbo.Premi_Polis.Premi_K5, 0),
isnull(dbo.Premi_Polis.Premi_W0, 0),
isnull(dbo.Premi_Polis.Premi_W1, 0),
isnull(dbo.Premi_Polis.Premi_W2, 0),
isnull(dbo.Premi_Polis.Premi_W3, 0),
isnull(dbo.Premi_Polis.Premi_W4,0), 
isnull(dbo.Premi_Polis.Premi_W5,0),
isnull(dbo.Premi_Polis.Benefit,0)
      FROM dbo.Produk_Polis INNER JOIN dbo.Premi_Polis ON dbo.Produk_Polis.Id_Produk = dbo.Premi_Polis.Id_Produk
    WHERE (dbo.Produk_Polis.No_Proposal =@NoProposal) AND 
          (dbo.Produk_Polis.Id_Produk=@IdProduk) AND 
          (dbo.Produk_Polis.Status='I')
    OPEN TmpCursor
    FETCH NEXT FROM TmpCursor INTO @ColField, @PrTK, @PrK0, @PrK1, @PrK2, @PrK3, @PrK4, @PrK5, @PrW0, @PrW1, @PrW2, @PrW3, @PrW4, @PrW5, @Benefit
    WHILE @@FETCH_STATUS = 0
    BEGIN

      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrTK+' WHERE Deskripsi=''TK''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrK0+' WHERE Deskripsi=''K0''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrK1+' WHERE Deskripsi=''K1''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrK2+' WHERE Deskripsi=''K2''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrK3+' WHERE Deskripsi=''K3''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrK4+' WHERE Deskripsi=''K4''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrK5+' WHERE Deskripsi=''K5''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrW0+' WHERE Deskripsi=''W0''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrW1+' WHERE Deskripsi=''W1''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrW2+' WHERE Deskripsi=''W2''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrW3+' WHERE Deskripsi=''W3''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrW4+' WHERE Deskripsi=''W4''')
      EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrW5+' WHERE Deskripsi=''W5''')
--      IF @Count>1  
      If @Benefit>=1000000000 AND @IdProduk not in ('2014.00043','2014.00022', '2014.00037','2014.00049') SET @Benefit=0
      
      IF isnull(@Keluarga,0)=1    EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@Benefit+' WHERE Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Per Keluarga''')
      ELSE EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@Benefit+' WHERE Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Per Tertanggung''')
      FETCH NEXT FROM TmpCursor INTO @ColField, @PrTK, @PrK0, @PrK1, @PrK2, @PrK3, @PrK4, @PrK5, @PrW0, @PrW1, @PrW2, @PrW3, @PrW4, @PrW5, @Benefit
   END
   CLOSE TmpCursor
   DEALLOCATE TmpCursor

If @Benefit=0 
    BEGIN
         IF isnull(@Keluarga,0)=1    EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Tidak Terbatas'''+N'WHERE Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Per Keluarga''')
         ELSE EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Tidak Terbatas'''+N'WHERE Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Per Tertanggung''')
    END
   END

   ELSE IF @ModelPremi=5
   BEGIN
    SELECT @Count=Count(*) FROM TmpInsurance..TmpBenefit034  WHERE Kode='01'
--    IF @Count>1 
    BEGIN
         IF isnull(@Keluarga,0)=1    SET @SQLText = N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''01'', '+CAST(@Urut as varchar)+N','''+''+N''',''Santunan Maksimum per Masa Berlaku Asuransi, Per Keluarga'')'
         ELSE SET @SQLText = N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''01'', '+CAST(@Urut as varchar)+N','''+''+N''',''Santunan Maksimum per Masa Berlaku Asuransi, Per Tertanggung'')'
         EXEC(@SQLText)
    END

    SELECT  @Akhir = Max(dbo.Produk_Polis.Batas_Usia) FROM dbo.Produk_Polis 
    WHERE (dbo.Produk_Polis.No_Proposal =@NoProposal) AND (dbo.Produk_Polis.Id_Produk=@IdProduk)AND (dbo.Produk_Polis.Status='I')

    SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi) VALUES(''02'', '+CAST(2 as varchar)+N','''','' Premi per Tertanggung'')'
    EXEC(@SQLText)


    DECLARE Employee_Cursor CURSOR FOR
    SELECT   dbo.Ekstra_Premi_Polis.Awal, dbo.Ekstra_Premi_Polis.Akhir
      FROM   dbo.Produk_Polis INNER JOIN
             dbo.Ekstra_Premi_Polis ON dbo.Produk_Polis.Id_Produk = dbo.Ekstra_Premi_Polis.Id_Produk
     WHERE   (dbo.Produk_Polis.No_Proposal = @NoProposal) AND (dbo.Produk_Polis.Id_Produk=@IdProduk)
    GROUP BY dbo.Ekstra_Premi_Polis.Awal, dbo.Ekstra_Premi_Polis.Akhir
    ORDER BY dbo.Ekstra_Premi_Polis.Awal
    SET @Urut =5
    OPEN Employee_Cursor
    FETCH NEXT FROM Employee_Cursor INTO @Awal, @Akhir
    WHILE @@FETCH_STATUS = 0
    BEGIN
         SET @SQLText=	N'INSERT INTO TmpInsurance..TmpBenefit034 (Kode, Urut, Kode_Santunan, Deskripsi, Umur ) VALUES(''02'', '+CAST(@Urut+2 as varchar)+N','''','''+N' Premi per Tertanggung'','+CAST(@Awal as varchar(3))+')'
         EXEC(@SQLText)
         SET @Urut = @Urut + 3
         FETCH NEXT FROM Employee_Cursor  INTO @Awal, @Akhir
    END
    CLOSE Employee_Cursor
    DEALLOCATE Employee_Cursor
    DECLARE TmpCursor CURSOR FOR
    SELECT  isnull(dbo.Premi_Polis.Kelas,''), dbo.Premi_Polis.Premi,  dbo.Premi_Polis.Santunan --dbo.Premi_Polis.Benefit
            FROM dbo.Produk_Polis INNER JOIN dbo.Premi_Polis ON dbo.Produk_Polis.Id_Produk = dbo.Premi_Polis.Id_Produk
            WHERE (dbo.Produk_Polis.No_Proposal =@NoProposal) AND (dbo.Produk_Polis.Id_Produk=@IdProduk)AND (dbo.Produk_Polis.Status='I')
    OPEN TmpCursor 
    FETCH NEXT FROM TmpCursor INTO @ColField, @Premi, @Benefit
    WHILE @@FETCH_STATUS = 0
    BEGIN
         SET @Santunan=@Benefit
	  SET @Premi =isnull(@Premi,0)
          EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@Premi+' WHERE Deskripsi='' Premi per Tertanggung''')
--          IF @Count>1  
          BEGIN
	       IF @Benefit>=1000000000.00 AND @IdProduk not in ('2014.00043','2014.00022', '2014.00037','2014.00049') SET @Benefit=0.00
               IF isnull(@Keluarga,0)=1    EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@Benefit+' WHERE Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Per Keluarga''')
               ELSE EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@Benefit+' WHERE Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Per Tertanggung''')
          END
          FETCH NEXT FROM TmpCursor INTO @ColField, @Premi, @Benefit
     END
    CLOSE TmpCursor
    DEALLOCATE TmpCursor
-------------
    DECLARE TmpCursor CURSOR FOR
    SELECT dbo.Ekstra_Premi_Polis.Kelas, dbo.Ekstra_Premi_Polis.Premi_Pria, dbo.Ekstra_Premi_Polis.Premi_Wanita, dbo.Ekstra_Premi_Polis.Awal,  dbo.Ekstra_Premi_Polis.Akhir 
    FROM   dbo.Produk_Polis INNER JOIN
           dbo.Ekstra_Premi_Polis ON dbo.Produk_Polis.Id_Produk = dbo.Ekstra_Premi_Polis.Id_Produk INNER JOIN
           dbo.Premi_Polis ON dbo.Ekstra_Premi_Polis.Id_Produk = dbo.Premi_Polis.Id_Produk AND 
           dbo.Ekstra_Premi_Polis.Kelas = dbo.Premi_Polis.Kelas
    WHERE  (dbo.Produk_Polis.No_Proposal = @NoProposal) AND (dbo.Produk_Polis.Id_Produk=@IdProduk)
   OPEN TmpCursor
    FETCH NEXT FROM TmpCursor INTO @ColField, @PrPria, @PrWanita, @Awal, @Akhir
    WHILE @@FETCH_STATUS = 0
    BEGIN
--          SET @WHERE = CAST(@Awal as varchar(2))+'-'+CAST(@Akhir as varchar(2))+' Pria'
--          EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrPria+' WHERE Deskripsi='''+@WHERE+'''')
--          SET @WHERE = CAST(@Awal as varchar(2))+'-'+CAST(@Akhir as varchar(2))+' Wanita'
--          EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrWanita+' WHERE Deskripsi='''+@WHERE+'''')
          EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@Premi+  ' WHERE Deskripsi='' Premi per Tertanggung'' AND Umur='+@Awal)
--          EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET ['+@ColField+N']= '+@PrWanita+' WHERE Deskripsi='' Wanita''AND Umur='+@Awal)
          FETCH NEXT FROM TmpCursor INTO @ColField, @PrPria, @PrWanita, @Awal, @Akhir
     END
    CLOSE TmpCursor
    DEALLOCATE TmpCursor

    If @Benefit=0 
    BEGIN
         IF isnull(@Keluarga,0)=1 EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Tidak Terbatas'''+N'WHERE Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Per Keluarga''')
         ELSE EXEC(N'UPDATE TmpInsurance..TmpBenefit034 SET Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Tidak Terbatas'''+N'WHERE Deskripsi=''Santunan Maksimum per Masa Berlaku Asuransi, Per Tertanggung''')
    END

  END
  UPDATE TmpInsurance..TmpBenefit034 SET Deskripsi=Deskripsi+' ('+CAST(Ganti_Item as varchar(5))+'%)' 
  WHERE isnull(Ganti_Produk,0)<>isnull(Ganti_Item, 0) AND isnull(Ganti_Item, 0)<>0 --AND LEFT(Deskripsi, 1)<>' '

--   EXEC('DELETE FROM TmpInsurance..TmpBenefit034 WHERE Kode=''02'' AND Kode_Santunan<>'''' AND '+@SQLTotal+'=0')
   SET @SQLText=N'SELECT * FROM TmpInsurance..TmpBenefit034' 
   EXEC(@SQLText)
--  EXEC TmpInsurance..gProc_DelTable 'TmpBenefit034'
