SELECT * FROM TmpInsurance.dbo.Rep10528_prib_11	--> (6, 11) PESERTA KELUAR
SELECT * FROM TmpInsurance.dbo.Rep10528_prib_12	--> (4, 12) PESERTA PERUBAHAN
SELECT * FROM TmpInsurance.dbo.Rep10528_prib_3	--> (1, 2, 3, 60, 61, 62) PESERTA BARU DAN SUSULAN

DELETE FROM TmpInsurance.dbo.Rep10528_prib_11	--> (6, 11) PESERTA KELUAR
DELETE FROM TmpInsurance.dbo.Rep10528_prib_12	--> (4, 12) PESERTA PERUBAHAN
DELETE FROM TmpInsurance.dbo.Rep10528_prib_3	--> (1, 2, 3, 60, 61, 62) PESERTA BARU DAN SUSULAN

---proses------------------------------------------------------------------------------------------------------
DECLARE @no_ref VARCHAR(12)
DECLARE @jenisnote SMALLINT

DECLARE list_cursor CURSOR FOR  
SELECT --TOP 1
  No_Ref,
  Jenis
FROM
  dbo.Note_Terbit_Master
WHERE
  ((dbo.Note_Terbit_Master.Jenis >= 1) 
  AND (dbo.Note_Terbit_Master.Jenis <= 19) 
  OR (dbo.Note_Terbit_Master.Jenis IN (34, 33, 31, 90, 100)) 
  OR (dbo.Note_Terbit_Master.Jenis BETWEEN 60 AND 64))
  
  AND No_Proposal = '2014.0423'
  AND Status='G'
  
  --AND (Tgl_Buat='2014-06-23' OR Tgl_Buat='2014-08-27')
  --AND Tgl_Buat>='2014-12-17'
  --AND Tgl_Buat<='2014-12-31'
  
  --AND Jenis=12

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_ref, @jenisnote

WHILE @@FETCH_STATUS = 0  
BEGIN  
  --PRINT @no_ref
  --PRINT @jenisnote
  
    SET NOCOUNT ON

    DECLARE @Jenis INT
    DECLARE @No INT
    DECLARE @Produk VARCHAR(20)
    DECLARE @No_Proposal VARCHAR(9)
    DECLARE @SQLText NVARCHAR(1000)
    DECLARE @SQLTotal NVARCHAR(1000)
    DECLARE @ColField VARCHAR(50)
    DECLARE @No_Peserta VARCHAR(12) 
    DECLARE @Nama VARCHAR(30) 
    DECLARE @Nik VARCHAR(15) 
    DECLARE @Tgl_Lahir DATETIME 
    DECLARE @Tgl_Mulai DATETIME 
    DECLARE @Kelamin VARCHAR(1) 
    DECLARE @Status_Kawin VARCHAR(2) 
    DECLARE @Kelas VARCHAR(10) 
    DECLARE @Premi FLOAT
    DECLARE @Urut INT
    DECLARE @GT_OG VARCHAR(2)
    DECLARE @Grd INT
    DECLARE @Pln INT
    DECLARE @Ke INT
    DECLARE @Tgl_Buat VARCHAR(30)
    DECLARE @Tgl_Terbit DATETIME
    DECLARE @Jenis_Note VARCHAR(50)
    DECLARE @No_Note VARCHAR(50)
    DECLARE @Cabang VARCHAR(100)
    DECLARE @Cost_Center VARCHAR(50)

    SELECT
      @No_Note = No_Note,
      @Jenis = Jenis,
      @Jenis_Note = Note_Jenis.[Description],
      @No_Proposal = No_Proposal,
      @Tgl_Terbit = Tgl_Awal,
      @Tgl_Buat = dbo.func_Datetostr(Tgl_Buat)
    FROM
      Note_Terbit_Master
      INNER JOIN Note_Jenis
      ON Note_Terbit_Master.Jenis = Note_Jenis.Kode
    WHERE
      No_Ref = @no_ref
    --SET @Jenis=20
    DECLARE @Nama_Perusahaan VARCHAR(100)
    DECLARE @Masa_ASuransi VARCHAR(50)
    DECLARE @Periode_Bayar VARCHAR(30)
    DECLARE @sTglTerbit VARCHAR(100)
    DECLARE @Periode VARCHAR(100)
    SELECT
      @Nama_Perusahaan = dbo.Perusahaan.Nama_Perusahaan + ' [' + dbo.Polis_Asuransi.No_Polis + ']',
      @Masa_Asuransi = dbo.func_Datetostr(dbo.Polis_Asuransi.Tgl_Mulai) + ' - ' + dbo.func_Datetostr(dbo.Polis_Asuransi.Tgl_Selesai),
      @Periode_Bayar = dbo.Periode_Bayar.Deskripsi,
      @GT_OG = dbo.Perusahaan.GT_OG,
      @Ke = dbo.func_JTKe(dbo.Polis_Asuransi.Tgl_Mulai, dbo.Polis_Asuransi.Tgl_Selesai, @Tgl_Terbit, dbo.Periode_Bayar.Pengali)
    FROM
      dbo.Polis_Asuransi
      INNER JOIN dbo.Perusahaan
      ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs
      INNER JOIN dbo.Periode_Bayar
      ON dbo.Polis_Asuransi.Periode_Bayar = dbo.Periode_Bayar.Kode
    WHERE
      dbo.Polis_Asuransi.No_Proposal = @No_Proposal
    SET @Periode = dbo.func_Datetostr(@Tgl_Terbit) + ' - ' + @Periode_Bayar + ' [' + CAST(@Ke AS VARCHAR) + '] '
    SET @sTglTerbit = @Tgl_Buat + '  [' + @Jenis_Note + ']'
    EXEC gproc_CreateRepHeader '10528'
    IF ISNULL(@No_Note, '') <> '' 
       EXEC gproc_AddRepHeader '10528', 'Nomor', @No_Note
    ELSE 
       EXEC gproc_AddRepHeader '10528', 'Nomor', 'DRAFT'
    EXEC gproc_AddRepHeader '10528', 'Perusahaan', @Nama_Perusahaan
    EXEC gproc_AddRepHeader '10528', 'Masa Asuransi', @Masa_Asuransi
    EXEC gproc_AddRepHeader '10528', 'Periode', @Periode
    EXEC gproc_AddRepHeader '10528', 'Tgl. Terbit', @sTglTerbit
    --PESERTA BARU DAN SUSULAN
    
    IF @Jenis IN (1, 2, 3, 60, 61, 62) 
       BEGIN
             EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10528_tmp'
             SET @SQLTotal = N''
             SET @SQLText = 'CREATE TABLE [TmpInsurance].[dbo].[Rep10528_tmp] (' + '[No_Peserta] varchar(12) NOT NULL, ' + '[Nama] varchar(30) NULL, ' + '[Nik] varchar(15) NULL, ' + '[Tgl_Lahir] Datetime NULL, ' + '[Sex] varchar(1) NULL, ' + '[Status] varchar(2) NULL, ' + '[Grd] int NULL, ' + '[Pln] int NULL, ' + '[Cabang] varchar(100) NULL, ' + '[Cost_Center] varchar(50) NULL, ' + '[Tgl_Mulai] Datetime NULL, '

      -- gproc_RepColumnLikeAdd '10528'
             DELETE FROM
              gMenu_RepColumn
             WHERE
              Id_Menu = '10528'
             UPDATE
              gMenu_RepMaster
             SET
              Font_Size = 7
             WHERE
              Id_Menu = '10528'
             INSERT   INTO gMenu_RepColumn
                      (Id_Menu,
                       Col_No,
                       Col_Field,
                       Col_Caption1,
                       Col_Width1,
                       Col_Caption2,
                       Col_Width2,
                       Col_Mask,
                       Col_Align,
                       Grp_Level,
                       Grp_Count,
                       Grp_Reset,
                       Col_Total)
             VALUES
                      ('10528',
                       1,
                       'No_Peserta',
                       '',
                       0,
                       'No. Peserta',
                       60,
                       '',
                       1,
                       1,
                       1,
                       1,
                       '')
             INSERT   INTO gMenu_RepColumn
                      (Id_Menu,
                       Col_No,
                       Col_Field,
                       Col_Caption1,
                       Col_Width1,
                       Col_Caption2,
                       Col_Width2,
                       Col_Mask,
                       Col_Align,
                       Grp_Level,
                       Grp_Count,
                       Grp_Reset,
                       Col_Total)
             VALUES
                      ('10528',
                       2,
                       'Nama',
                       '',
                       0,
                       'Nama',
                       100,
                       '',
                       1,
                       1,
                       0,
                       0,
                       '')
             INSERT   INTO gMenu_RepColumn
                      (Id_Menu,
                       Col_No,
                       Col_Field,
                       Col_Caption1,
                       Col_Width1,
                       Col_Caption2,
                       Col_Width2,
                       Col_Mask,
                       Col_Align,
                       Grp_Level,
                       Grp_Count,
                       Grp_Reset,
                       Col_Total)
             VALUES
                      ('10528',
                       3,
                       'Nik',
                       '',
                       0,
                       'Nik',
                       60,
                       '',
                       1,
                       1,
                       0,
                       0,
                       '')
             INSERT   INTO gMenu_RepColumn
                      (Id_Menu,
                       Col_No,
                       Col_Field,
                       Col_Caption1,
                       Col_Width1,
                       Col_Caption2,
                       Col_Width2,
                       Col_Mask,
                       Col_Align,
                       Grp_Level,
                       Grp_Count,
                       Grp_Reset,
                       Col_Total)
             VALUES
                      ('10528',
                       4,
                       'Tgl_Lahir',
                       '',
                       0,
                       'Tgl. Lahir',
                       60,
                       'dd/mm/yyyy',
                       1,
                       1,
                       0,
                       0,
                       '')
             INSERT   INTO gMenu_RepColumn
                      (Id_Menu,
                       Col_No,
                       Col_Field,
                       Col_Caption1,
                       Col_Width1,
                       Col_Caption2,
                       Col_Width2,
                       Col_Mask,
                       Col_Align,
                       Grp_Level,
                       Grp_Count,
                       Grp_Reset,
                       Col_Total)
             VALUES
                      ('10528',
                       5,
                       'Sex',
                       '',
                       0,
                       'Sex',
                       12,
                       '',
                       1,
                       1,
                       0,
                       0,
                       '')
             INSERT   INTO gMenu_RepColumn
                      (Id_Menu,
                       Col_No,
                       Col_Field,
                       Col_Caption1,
                       Col_Width1,
                       Col_Caption2,
                       Col_Width2,
                       Col_Mask,
                       Col_Align,
                       Grp_Level,
                       Grp_Count,
                       Grp_Reset,
                       Col_Total)
             VALUES
                      ('10528',
                       6,
                       'Status',
                       '',
                       0,
                       'Stat',
                       15,
                       '',
                       1,
                       1,
                       0,
                       0,
                       '')
             IF @GT_OG = 'GT' 
                BEGIN
                      INSERT  INTO gMenu_RepColumn
                              (Id_Menu,
                               Col_No,
                               Col_Field,
                               Col_Caption1,
                               Col_Width1,
                               Col_Caption2,
                               Col_Width2,
                               Col_Mask,
                               Col_Align,
                               Grp_Level,
                               Grp_Count,
                               Grp_Reset,
                               Col_Total)
                      VALUES
                              ('10528',
                               7,
                               'Grd',
                               '',
                               0,
                               'Grd',
                               15,
                               '',
                               1,
                               1,
                               0,
                               0,
                               '')
                      INSERT  INTO gMenu_RepColumn
                              (Id_Menu,
                               Col_No,
                               Col_Field,
                               Col_Caption1,
                               Col_Width1,
                               Col_Caption2,
                               Col_Width2,
                               Col_Mask,
                               Col_Align,
                               Grp_Level,
                               Grp_Count,
                               Grp_Reset,
                               Col_Total)
                      VALUES
                              ('10528',
                               8,
                               'Pln',
                               '',
                               0,
                               'Pln',
                               15,
                               '',
                               1,
                               1,
                               0,
                               0,
                               '')
                END
             INSERT   INTO gMenu_RepColumn
                      (Id_Menu,
                       Col_No,
                       Col_Field,
                       Col_Caption1,
                       Col_Width1,
                       Col_Caption2,
                       Col_Width2,
                       Col_Mask,
                       Col_Align,
                       Grp_Level,
                       Grp_Count,
                       Grp_Reset,
                       Col_Total)
             VALUES
                      ('10528',
                       9,
                       'Cabang',
                       '',
                       0,
                       'Cabang',
                       60,
                       '',
                       1,
                       1,
                       0,
                       0,
                       '')
             INSERT   INTO gMenu_RepColumn
                      (Id_Menu,
                       Col_No,
                       Col_Field,
                       Col_Caption1,
                       Col_Width1,
                       Col_Caption2,
                       Col_Width2,
                       Col_Mask,
                       Col_Align,
                       Grp_Level,
                       Grp_Count,
                       Grp_Reset,
                       Col_Total)
             VALUES
                      ('10528',
                       10,
                       'Cost_Center',
                       '',
                       0,
                       'Cost Center',
                       60,
                       '',
                       1,
                       1,
                       0,
                       0,
                       '')
             INSERT   INTO gMenu_RepColumn
                      (Id_Menu,
                       Col_No,
                       Col_Field,
                       Col_Caption1,
                       Col_Width1,
                       Col_Caption2,
                       Col_Width2,
                       Col_Mask,
                       Col_Align,
                       Grp_Level,
                       Grp_Count,
                       Grp_Reset,
                       Col_Total)
             VALUES
                      ('10528',
                       11,
                       'Tgl_Mulai',
                       '',
                       0,
                       'Tgl. Mulai',
                       60,
                       'dd/mm/yyyy',
                       1,
                       1,
                       0,
                       0,
                       '')

             DECLARE TmpCursor CURSOR
             FOR
                     SELECT
                      dbo.Sub_Kelompok.Field_Name,
                      dbo.Sub_Kelompok.Nama_Pendek
                     FROM
                      dbo.Produk_Polis
                      INNER JOIN dbo.Sub_Kelompok
                      ON dbo.Produk_Polis.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok
                     WHERE
                      (dbo.Produk_Polis.No_Proposal = @No_Proposal) AND ISNULL(dbo.Sub_Kelompok.Field_Name, '') <> ''
                     GROUP BY
                      dbo.Sub_Kelompok.Urutan,
                      dbo.Sub_Kelompok.Field_Name,
                      dbo.Sub_Kelompok.Nama_Pendek
                     ORDER BY
                      dbo.Sub_Kelompok.Urutan

             OPEN TmpCursor
             FETCH NEXT FROM TmpCursor INTO @ColField, @Produk
             SET @No = 13
             WHILE @@FETCH_STATUS = 0 
                   BEGIN
                         INSERT   INTO gMenu_RepColumn
                                  (Id_Menu,
                                   Col_No,
                                   Col_Field,
                                   Col_Caption1,
                                   Col_Width1,
                                   Col_Caption2,
                                   Col_Width2,
                                   Col_Mask,
                                   Col_Align,
                                   Grp_Level,
                                   Grp_Count,
                                   Grp_Reset,
                                   Col_Total)
                         VALUES
                                  ('10528',
                                   @No,
                                   @ColField + '_Premi',
                                   @Produk,
                                   75,
                                   'Premi',
                                   55,
                                   '#,##0',
                                   3,
                                   1,
                                   0,
                                   0,
                                   'SUM(' + @ColField + '_Premi)')
                         INSERT   INTO gMenu_RepColumn
                                  (Id_Menu,
                                   Col_No,
                                   Col_Field,
                                   Col_Caption1,
                                   Col_Width1,
                                   Col_Caption2,
                                   Col_Width2,
                                   Col_Mask,
                                   Col_Align,
                                   Grp_Level,
                                   Grp_Count,
                                   Grp_Reset,
                                   Col_Total)
                         VALUES
                                  ('10528',
                                   @No + 1,
                                   @ColField + '_Kelas',
                                   '',
                                   0,
                                   'Kelas',
                                   10,
                                   '',
                                   1,
                                   1,
                                   0,
                                   0,
                                   '')

                         SET @SQLText = @SQLText + N'[' + @ColField + N'_Kelas] [varchar] (10), ' 
                         SET @SQLText = @SQLText + N'[' + @ColField + N'_Premi] float Default (0), '
                         SET @SQLTotal = @SQLTotal + N'[' + @ColField + N'_Premi]  +'
                         SET @No = @No + 2
                         FETCH NEXT FROM TmpCursor INTO @ColField, @Produk
                   END
             CLOSE TmpCursor
             DEALLOCATE TmpCursor

             INSERT   INTO gMenu_RepColumn
                      (Id_Menu,
                       Col_No,
                       Col_Field,
                       Col_Caption2,
                       Col_Width2,
                       Col_Mask,
                       Col_Align,
                       Grp_Level,
                       Grp_Count,
                       Grp_Reset,
                       Col_Total)
             VALUES
                      ('10528',
                       @No,
                       'Total',
                       'Total',
                       90,
                       '#,##0',
                       3,
                       1,
                       0,
                       0,
                       'SUM(Total)')

             SET @SQLText = @SQLText + '[Total] float default(0), '
             SET @SQLTotal = @SQLTotal + N'0'
             SET @SQLText = @SQLText + N' CONSTRAINT pk_Rep10528_tmp PRIMARY KEY ([No_Peserta])) ON [PRIMARY]'
             EXEC(@SQLText)

             DECLARE TmpCursor CURSOR
             FOR
                     SELECT
                      dbo.Peserta.No_Peserta,
                      dbo.Peserta.Nama,
                      dbo.Peserta.Nik,
                      dbo.Peserta.Tgl_Lahir,
                      dbo.Peserta.Kelamin,
                      dbo.Peserta.Status_Kawin,
                      dbo.Peserta.Grade,
                      dbo.Peserta.Pln,
                      dbo.Cabang.Nama,
                      dbo.Peserta.Departemen,
                      dbo.Peserta.Tgl_Mulai,
                      dbo.Peserta_Produk_Detail.Kelas,
                      dbo.Sub_Kelompok.Field_Name,
                      SUM(dbo.Peserta_Produk_Detail.Jumlah),
                      dbo.Sub_Kelompok.Urutan
                     FROM
                      dbo.Peserta
                      INNER JOIN dbo.Peserta_Produk_Detail
                      ON dbo.Peserta.No_Proposal = dbo.Peserta_Produk_Detail.No_Proposal AND dbo.Peserta.No_Peserta = dbo.Peserta_Produk_Detail.No_Peserta
                      INNER JOIN dbo.Sub_Kelompok
                      ON dbo.Peserta_Produk_Detail.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok
                      LEFT OUTER JOIN dbo.Cabang
                      ON dbo.Peserta.No_Prs = dbo.Cabang.No_Prs AND dbo.Peserta.Cabang = dbo.Cabang.Kode
                     WHERE
                      (dbo.Peserta_Produk_Detail.Status <> 'C') AND (dbo.Peserta.Status <> 'C') AND (dbo.Peserta_Produk_Detail.No_Ref = @no_ref) AND (dbo.Peserta.No_Proposal = dbo.Peserta_Produk_Detail.No_Proposal)
                     GROUP BY
                      dbo.Peserta.No_Peserta,
                      dbo.Peserta.Nama,
                      dbo.Peserta.Nik,
                      dbo.Peserta.Tgl_Lahir,
                      dbo.Peserta.Kelamin,
                      dbo.Peserta.Tgl_Mulai,
                      dbo.Peserta.Status_Kawin,
                      dbo.Peserta_Produk_Detail.Kelas,
                      dbo.Sub_Kelompok.Field_Name,
                      dbo.Sub_Kelompok.Urutan,
                      dbo.Peserta.Grade,
                      dbo.Peserta.Pln,
                      dbo.Cabang.Nama,
                      dbo.Peserta.Departemen
                     ORDER BY
                      dbo.Peserta.No_Peserta,
                      dbo.Sub_Kelompok.Urutan 

             OPEN TmpCursor
             FETCH NEXT FROM TmpCursor INTO @No_Peserta, @Nama, @Nik, @Tgl_Lahir, @Kelamin, @Status_Kawin, @Grd, @Pln, @Cabang, @Cost_Center, @Tgl_Mulai, @Kelas, @ColField, @Premi, @Urut
             WHILE @@FETCH_STATUS = 0 
                   BEGIN
                         IF NOT EXISTS ( SELECT
                                          *
                                         FROM
                                          TmpInsurance.dbo.Rep10528_tmp
                                         WHERE
                                          [No_Peserta] = @No_Peserta ) 
                            INSERT    INTO TmpInsurance.dbo.Rep10528_tmp
                                      ([No_Peserta],
                                       [Nama],
                                       [Nik],
                                       [Tgl_Lahir],
                                       [Sex],
                                       [Status],
                                       Grd,
                                       Pln,
                                       Cabang,
                                       Cost_Center,
                                       [Tgl_Mulai])
                            VALUES
                                      (@No_Peserta,
                                       @Nama,
                                       @Nik,
                                       @Tgl_Lahir,
                                       @Kelamin,
                                       @Status_Kawin,
                                       @Grd,
                                       @Pln,
                                       @Cabang,
                                       @Cost_Center,
                                       @Tgl_Mulai)
                         EXEC ('UPDATE TmpInsurance.dbo.Rep10528_tmp SET ['+@ColField+'_Premi]='+@Premi+', ['+@ColField+'_Kelas]='''+@Kelas+''' WHERE [No_Peserta]= '''+@No_Peserta+'''')
                         FETCH NEXT FROM TmpCursor INTO @No_Peserta, @Nama, @Nik, @Tgl_Lahir, @Kelamin, @Status_Kawin, @Grd, @Pln, @Cabang, @Cost_Center, @Tgl_Mulai, @Kelas, @ColField, @Premi, @Urut
                   END
             CLOSE TmpCursor
             DEALLOCATE TmpCursor
             EXEC ('UPDATE TmpInsurance.dbo.Rep10528_tmp SET [Total]='+@SQLTotal)
             IF @Jenis IN (60, 61, 62) 
                EXEC ('UPDATE TmpInsurance.dbo.Rep10528_tmp SET [Total]=isnull(B.Jumlah,0)+isnull(B.Biaya_Kartu,0) FROM Note_Peserta_Aso B WHERE B.No_Ref='''+@no_ref+''' AND TmpInsurance.dbo.Rep10528_tmp.No_Peserta=B.No_Peserta')
             
             INSERT INTO [TmpInsurance].[dbo].[Rep10528_prib_3]
                   ([No_Ref]
                   ,[No_Peserta]
                   ,[Nama]
                   ,[Nik]
                   ,[Tgl_Lahir]
                   ,[Sex]
                   ,[Status]
                   ,[Grd]
                   ,[Pln]
                   ,[Cabang]
                   ,[Cost_Center]
                   ,[Tgl_Mulai]
                   ,[RI_Kelas]
                   ,[RI_Premi]
                   ,[RJ_Kelas]
                   ,[RJ_Premi]
                   ,[Total])
             SELECT
                  @no_ref
                 ,[No_Peserta]
                 ,[Nama]
                 ,[Nik]
                 ,[Tgl_Lahir]
                 ,[Sex]
                 ,[Status]
                 ,[Grd]
                 ,[Pln]
                 ,[Cabang]
                 ,[Cost_Center]
                 ,[Tgl_Mulai]
                 ,[RI_Kelas]
                 ,[RI_Premi]
                 ,[RJ_Kelas]
                 ,[RJ_Premi]
                 ,[Total]
             FROM
              TmpInsurance.dbo.Rep10528_tmp
             ORDER BY
              [No_Peserta]
     
     --EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10528_tmp'
       END
    ELSE
  --PESERTA PERUBAHAN
       IF @Jenis IN (4, 12) -- PERUBAHAN 1
          BEGIN
                EXEC gproc_RepColumnLoad '10528', 11
                UPDATE
                  gMenu_RepMaster
                SET
                  Font_Size = 8
                WHERE
                  Id_Menu = '10528'
                
                INSERT INTO [TmpInsurance].[dbo].[Rep10528_prib_12]
                       ([No_Ref]
                       ,[No_Peserta]
                       ,[Nama]
                       ,[Nik]
                       ,[Cost_Center]
                       ,[Nama_Sub]
                       ,[Kelamin_Old]
                       ,[Status_Kawin_Old]
                       ,[Usia_Old]
                       ,[Kelas_Old]
                       ,[Benefit_Old]
                       ,[Curr_Premi_Old]
                       ,[Curr_Santunan_Old]
                       ,[Kelamin]
                       ,[Status_Kawin]
                       ,[Usia]
                       ,[Kelas]
                       ,[Benefit]
                       ,[Premi]
                       ,[Curr_Santunan]
                       ,[Jumlah])
                SELECT
                  @no_ref,
                  dbo.Peserta_Produk_Detail.No_Peserta,
                  --dbo.Peserta.Nama + dbo.func_GetKaryawan(' (', dbo.Peserta_Produk_Detail.No_Proposal, dbo.Peserta_Produk_Detail.No_Peserta, ') ') + dbo.func_AddPrefiks('/Nik:', dbo.Peserta.Nik) AS Nama, --+
                  dbo.Peserta.Nama + dbo.func_GetKaryawan(' (', dbo.Peserta_Produk_Detail.No_Proposal, dbo.Peserta_Produk_Detail.No_Peserta, ') ') AS Nama, --+
                  dbo.Peserta.Nik,
                  dbo.Peserta.Departemen AS Cost_Center,
                  dbo.Sub_Kelompok.Nama_Sub,
                  dbo.Peserta_Produk_Detail.Kelamin_Old,
                  dbo.Peserta_Produk_Detail.Status_Kawin_Old,
                  dbo.Peserta_Produk_Detail.Usia_Old,
                  dbo.Peserta_Produk_Detail.Kelas_Old + dbo.func_AddPrefiks('/', dbo.Peserta_Produk_Detail.Resiko_Old) AS Kelas_Old,
                  dbo.Peserta_Produk_Detail.Benefit_Old,
                  dbo.Peserta_Produk_Detail.Curr_Premi_Old,
                  dbo.Peserta_Produk_Detail.Curr_Santunan_Old,
                  dbo.Peserta_Produk_Detail.Kelamin,
                  dbo.Peserta_Produk_Detail.Status_Kawin,
                  dbo.Peserta_Produk_Detail.Usia,
                  dbo.Peserta_Produk_Detail.Kelas + dbo.func_AddPrefiks('/', dbo.Peserta_Produk_Detail.Resiko) AS Kelas,
                  dbo.Peserta_Produk_Detail.Benefit,
                  dbo.Peserta_Produk_Detail.Curr_Premi AS Premi,
                  dbo.Peserta_Produk_Detail.Curr_Santunan,
                  dbo.Peserta_Produk_Detail.Jumlah
                FROM
                  dbo.Peserta_Produk_Detail
                  INNER JOIN dbo.Peserta
                  ON dbo.Peserta_Produk_Detail.No_Peserta = dbo.Peserta.No_Peserta AND dbo.Peserta_Produk_Detail.No_Proposal = dbo.Peserta.No_Proposal
                  INNER JOIN dbo.Note_Terbit_Master
                  ON dbo.Peserta_Produk_Detail.No_Ref = dbo.Note_Terbit_Master.No_Ref
                  INNER JOIN dbo.Sub_Kelompok
                  ON dbo.Peserta_Produk_Detail.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok
                WHERE
                  (dbo.Peserta_Produk_Detail.No_Ref = @no_ref)
                ORDER BY
                  dbo.Peserta_Produk_Detail.No_Peserta
          END
       ELSE --PESERTA KELUAR
          IF @Jenis IN (6, 11) 
             BEGIN
                   EXEC gproc_RepColumnLoad '10528', 12
                   UPDATE
                      gMenu_RepMaster
                   SET
                      Font_Size = 8
                   WHERE
                      Id_Menu = '10528'
                   
                   INSERT INTO [TmpInsurance].[dbo].[Rep10528_prib_11]
                         ([No_Ref]
                         ,[No_Peserta]
                         ,[Nama]
                         ,[Nik]
                         ,[Cost_Center]
                         ,[Tgl_Mulai]
                         ,[Urutan]
                         ,[Nama_Sub]
                         ,[Kelas]
                         ,[Up]
                         ,[Tgl_Keluar]
                         ,[Tgl_Lapse]
                         ,[Premi]
                         ,[Adm]
                         ,[Klaim]
                         ,[Jumlah])
                   SELECT
                      @no_ref,
                      dbo.Peserta_Produk_Detail.No_Peserta,
                      --dbo.Peserta.Nama + dbo.func_GetKaryawan(' (', dbo.Peserta_Produk_Detail.No_Proposal, dbo.Peserta_Produk_Detail.No_Peserta, ') ') + dbo.func_AddPrefiks('/Nik:', dbo.Peserta.Nik) AS Nama,
                      dbo.Peserta.Nama + dbo.func_GetKaryawan(' (', dbo.Peserta_Produk_Detail.No_Proposal, dbo.Peserta_Produk_Detail.No_Peserta, ') ') AS Nama,
                      dbo.Peserta.Nik,
                      dbo.Peserta.Departemen AS Cost_Center,
                      dbo.Peserta_Produk_Detail.Tgl_Mulai,
                      dbo.Sub_Kelompok.Urutan,
                      dbo.Sub_Kelompok.Nama_Sub,
                      dbo.Peserta_Produk_Detail.Kelas + dbo.func_AddPrefiks('/', dbo.Peserta_Produk_Detail.Resiko) AS Kelas,
                      dbo.Peserta_Produk_Detail.Benefit_Old AS Up,
                      dbo.Peserta_Produk_Detail.Tgl_Ubah AS [Tgl_Keluar],
                      dbo.Peserta.Tgl_Lapse,
                      dbo.Peserta_Produk_Detail.Curr_Premi_Old AS Premi,
                      0.01 * Polis_Asuransi.Adm_Keluar * dbo.Peserta_Produk_Detail.Curr_Premi_Old AS Adm,
                      dbo.Peserta_Produk_Detail.Jml_Klaim AS Klaim,
                      dbo.Peserta_Produk_Detail.Jumlah
                   FROM
                      dbo.Peserta_Produk_Detail
                      INNER JOIN dbo.Peserta
                      ON dbo.Peserta_Produk_Detail.No_Peserta = dbo.Peserta.No_Peserta AND dbo.Peserta_Produk_Detail.No_Proposal = dbo.Peserta.No_Proposal
                      INNER JOIN dbo.Note_Terbit_Master
                      ON dbo.Peserta_Produk_Detail.No_Ref = dbo.Note_Terbit_Master.No_Ref
                      INNER JOIN dbo.Sub_Kelompok
                      ON dbo.Peserta_Produk_Detail.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok
                      INNER JOIN Polis_Asuransi
                      ON dbo.Note_Terbit_Master.No_Proposal = Polis_Asuransi.No_Proposal
                   WHERE
                      (dbo.Peserta_Produk_Detail.No_Ref = @no_ref)
                   ORDER BY
                      dbo.Peserta_Produk_Detail.No_Peserta,
                      dbo.Sub_Kelompok.Urutan
             END
          ELSE 
             IF @Jenis IN (21, 20, 41, 40, 42) 
                BEGIN
                      EXEC gproc_RepColumnLoad '10528', 20
                      SELECT TOP 100 PERCENT
                          Peserta_Produk.Kelas,
                          T1.No_Klaim,
                          dbo.func_DateToStr(T1.Tgl_Kwitansi) + ' sd ' AS Tgl_Kuitansi,
                          T1.No_Peserta,
                          dbo.Peserta.Nama,
                          dbo.Peserta.Nik,
                          dbo.func_GetKaryawan2('', dbo.Peserta.No_Proposal, dbo.Peserta.No_Peserta, '') AS Karyawan,
                          Tgl_masuk = dbo.func_DateToStr(CASE WHEN YEAR(dbo.Klaim_Peserta.Tgl_masuk) >= 1990 THEN dbo.Klaim_Peserta.Tgl_masuk
                                                              ELSE T1.Tgl_Kwitansi
                                                         END) + ' sd ',
                          dbo.Cabang.Nama AS Cabang,
                          dbo.Peserta.Departemen,
                          dbo.Klaim_Peserta.Id_RegKlaim AS No_Batch,
                          dbo.func_DateToStr(Klaim_Register.Tgl_Terima) + ' sd ' AS Tgl_Terima,
                          dbo.func_AddPrefiks('RS:', dbo.Provider.Nama) AS Rumah_Sakit,
                          T1.Kode_Diagnosa,
                          dbo.Diagnosa.Diagnosis1 AS Diagnosa,
                          dbo.Item_Santunan_Polis.Deskripsi,
                          T1.Jml_Diajukan,
                          T1.Jml_Dibayar,
                          T1.Jml_Ditolak
                      FROM
                          (
                           SELECT
                              SUM(Jml_Diajukan) AS Jml_Diajukan,
                              SUM(Jml_Dibayar) AS Jml_Dibayar,
                              SUM(Jml_Ditolak) AS Jml_Ditolak,
                              No_Klaim,
                              No_Ref,
                              Tgl_Kwitansi,
                              No_Peserta,
                              Kode_Diagnosa,
                              Id_Produk,
                              Kode_Provider,
                              Kode_Santunan,
                              No_Proposal
                           FROM
                              dbo.Klaim_Ref_Detail
                           GROUP BY
                              No_Klaim,
                              Tgl_Kwitansi,
                              No_Peserta,
                              Kode_Diagnosa,
                              Id_Produk,
                              Kode_Provider,
                              Kode_Santunan,
                              No_Proposal,
                              No_Ref
                          ) AS T1
                          INNER JOIN dbo.Item_Santunan_Polis
                          ON T1.Id_Produk = dbo.Item_Santunan_Polis.Id_Produk AND T1.Kode_Santunan = dbo.Item_Santunan_Polis.Kode_Santunan
                          INNER JOIN dbo.Peserta
                          ON T1.No_Proposal = dbo.Peserta.No_Proposal AND T1.No_Peserta = dbo.Peserta.No_Peserta
                          LEFT OUTER JOIN dbo.Cabang
                          ON dbo.Peserta.No_Prs = dbo.Cabang.No_Prs AND dbo.Peserta.Cabang = dbo.Cabang.Kode
                          LEFT OUTER JOIN dbo.Provider
                          ON T1.Kode_Provider = dbo.Provider.IDI
                          LEFT OUTER JOIN dbo.Diagnosa
                          ON T1.Kode_Diagnosa = dbo.Diagnosa.Kode
                          INNER JOIN Klaim_Peserta
                          ON T1.No_Klaim = Klaim_Peserta.No_Klaim
                          LEFT OUTER JOIN Klaim_Register
                          ON Klaim_Peserta.Id_RegKlaim = Klaim_Register.Id_RegKlaim
                          INNER JOIN Peserta_Produk
                          ON Klaim_Peserta.No_Proposal = Peserta_Produk.No_Proposal AND Klaim_Peserta.No_Peserta = Peserta_Produk.No_Peserta AND Klaim_Peserta.Id_Produk = Peserta_Produk.Id_Produk
                      WHERE
                          (T1.No_Ref = @no_ref)
                      ORDER BY
                          T1.No_Klaim,
                          dbo.Item_Santunan_Polis.Urutan
                END
  
  
  
  FETCH NEXT FROM list_cursor INTO @no_ref, @jenisnote
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 
