USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep90111]    Script Date: 10/16/2015 14:05:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- EXEC dbo.proc_Rep90111 '2011.0730', 1, 1, 1, 1, 1, ''


-- last modified by Pribadi Pamungkas. 2015-10-16.

ALTER PROCEDURE [dbo].[proc_Rep90111]
      @NoProposal VARCHAR(9),
      @KomulatifYa BIT,
      @KaryawanYa BIT,
      @KeluargaYa BIT,
      @PriaYa BIT,
      @WanitaYa BIT,
      @Cabang VARCHAR(60)
AS 
SET NOCOUNT OFF
DECLARE @Sebagai VARCHAR(30)
DECLARE @Umur INT
DECLARE @Jenis INT
DECLARE @No INT
DECLARE @Produk VARCHAR(20)
DECLARE @SQLText NVARCHAR(1000)
DECLARE @SQLTotal NVARCHAR(1000)
DECLARE @ColField VARCHAR(50)
DECLARE @No_Peserta VARCHAR(12) 
DECLARE @No_Proposal VARCHAR(12) 
DECLARE @Nama VARCHAR(30) 
DECLARE @Nik VARCHAR(40) 
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 @Grd INT
DECLARE @Pln INT

DECLARE @No_Polis VARCHAR(100)
DECLARE @GT_OG VARCHAR(2)
DECLARE @Masa_ASuransi VARCHAR(50)
DECLARE @Periode_Bayar VARCHAR(30)
DECLARE @sTglTerbit VARCHAR(100)
DECLARE @Periode VARCHAR(100)
DECLARE @Up FLOAT
DECLARE @sUP VARCHAR(20)
SELECT
    @No_Polis = dbo.Polis_Asuransi.No_Polis + '/' + dbo.Perusahaan.GT_OG,
    @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
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 = @NoProposal
  
EXEC gproc_CreateRepHeader '10358'
EXEC gproc_AddRepHeader '10358', 'No. Polis', @No_Polis
EXEC gproc_AddRepHeader '10358', 'Masa Asuransi', @Masa_Asuransi
EXEC gproc_AddRepHeader '10358', 'Periode Bayar', @Periode_Bayar
  --PESERTA BARU DAN SUSULAN
  
EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10358'
SET @SQLTotal = N''
SET @SQLText = 'CREATE TABLE [TmpInsurance].[dbo].[Rep10358] (' + '[No_Proposal] varchar(9) NOT NULL, ' + '[No_Peserta] varchar(12) NOT NULL, ' + '[Nama] varchar(30) NULL, ' + '[Nik] varchar(30) NULL, ' + '[Tgl_Lahir] Datetime NULL, ' + '[Sex] varchar(1) NULL, ' + '[Umur] int NULL, ' + '[Status] varchar(2) NULL, ' + '[Sebagai] varchar(30) NULL, ' + '[Grd] int NULL, ' + '[Pln] int NULL, ' + '[Tgl_Mulai] Datetime NULL, '

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 = @NoProposal)
    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 = 10
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('10358',@No,@ColField+'_Premi',@Produk, 75,'Premi',55,'#,##0',3,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('10358',@No+1,@ColField+'_Kelas','',0,'Kelas',30,'',1,1,0,0,'SUM('+@ColField+'_Premi)')

            SET @SQLText = @SQLText + N'[' + @ColField + N'_Kelas] [varchar] (10), ' 
            SET @SQLText = @SQLText + N'[' + @ColField + N'_Premi] float Default (0), '
            IF @ColField = 'ACC'
               OR @ColField = 'AJB' 
               SET @SQLText = @SQLText + N'[' + @ColField + N'_Up] 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

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

IF @KomulatifYa = 1 
   BEGIN
   
----------- start: prib debug -------------
--DECLARE @tabel_hasil TABLE ( 
--                        no_peserta VARCHAR(12),
--                        ColField VARCHAR(50),
--                        urutan SMALLINT,
--                        premi FLOAT)
----------- end: prib debug -------------

         DECLARE TmpCursor CURSOR
         FOR
          SELECT
              dbo.Peserta.No_Proposal,
              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.Peserta.Tgl_Mulai,
              dbo.Peserta_Produk_Detail.Kelas,
              dbo.Sub_Kelompok.Field_Name,
              
              ABS(SUM(dbo.Peserta_Produk_Detail.Jumlah)) * dbo.Note_Terbit_Master.Tipe AS Premi,
              
              dbo.Sub_Kelompok.Urutan,
              dbo.Sebagai.Status_Peserta,
              dbo.func_GetUmur(dbo.Peserta.Tgl_Lahir, dbo.Peserta.Tgl_Mulai) AS Umur,
              SUM(dbo.Peserta_Produk_Detail.Benefit) AS Benefit
          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 INNER JOIN 
              dbo.Note_Terbit_Master ON dbo.Note_Terbit_Master.No_Ref = dbo.Peserta_Produk_Detail.No_Ref INNER JOIN 
              dbo.Sebagai ON dbo.Peserta.Sebagai = dbo.Sebagai.IDI
          WHERE
              (dbo.Peserta_Produk_Detail.Status <> 'C')
              AND (dbo.Note_Terbit_Master.Status <> 'C')
              --AND (dbo.Note_Terbit_Master.Jenis <= 19)
              AND ((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 (dbo.Peserta.Status IN ('I', 'O'))
              AND (dbo.Peserta_Produk_Detail.No_Proposal = @NoProposal)
              AND (dbo.Peserta.No_Proposal = dbo.Peserta_Produk_Detail.No_Proposal)
              AND ((dbo.Peserta.Sebagai = 0 AND @KaryawanYa = 1)
                    OR (dbo.Peserta.Sebagai <> 0 AND @KeluargaYa = 1)
                    OR @KeluargaYa = 0 OR @KaryawanYa = 0)
              AND ((dbo.Peserta.Kelamin = 'P' AND @PriaYa = 1)
                    OR (dbo.Peserta.Kelamin = 'W' AND @WanitaYa = 1)
                    OR @PriaYa = 0 OR @WanitaYa = 0)
          GROUP BY
              dbo.Peserta.No_Proposal,
              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.Sebagai.Status_Peserta,
              dbo.Note_Terbit_Master.Tipe
          ORDER BY
              dbo.Peserta.No_Proposal,
              dbo.Peserta.No_Peserta,
              dbo.Sub_Kelompok.Urutan 

         OPEN TmpCursor
         FETCH NEXT FROM TmpCursor INTO @No_Proposal, @No_Peserta, @Nama, @Nik, @Tgl_Lahir, @Kelamin, @Status_Kawin, @Grd, @Pln, @Tgl_Mulai, @Kelas, @ColField, @Premi, @Urut, @Sebagai, @Umur, @Up
         WHILE @@FETCH_STATUS = 0 
               BEGIN
----------- start: prib debug -------------
  --INSERT INTO @tabel_hasil
  --        (no_peserta,
  --         ColField,
  --         urutan,
  --         premi)
  --VALUES
  --        (@No_Peserta,
  --         @ColField,
  --         @Urut,
  --         @Premi)
----------- end: prib debug -------------

                     BEGIN TRAN
                     IF NOT EXISTS ( SELECT
                                        *
                                     FROM
                                        TmpInsurance.dbo.Rep10358
                                     WHERE
                                        [No_Proposal] = @No_Proposal
                                        AND [No_Peserta] = @No_Peserta ) 
                        INSERT  INTO TmpInsurance.dbo.Rep10358
                                ([No_Proposal],
                                 [No_Peserta],
                                 [Nama],
                                 [Nik],
                                 [Tgl_Lahir],
                                 [Sex],
                                 [Status],
                                 Grd,
                                 Pln,
                                 [Tgl_Mulai],
                                 [Sebagai],
                                 Umur)
                        VALUES
                                (@No_Proposal,
                                 @No_Peserta,
                                 @Nama,
                                 @Nik,
                                 @Tgl_Lahir,
                                 @Kelamin,
                                 @Status_Kawin,
                                 @Grd,
                                 @Pln,
                                 @Tgl_Mulai,
                                 @Sebagai,
                                 @Umur)
  -- odified by Pribadi Pamungkas, 2014-12-12
                     
                     --DECLARE @premiygada FLOAT 
                     --SELECT @premiygada=COALESCE(['+@ColField+'_Premi],0) FROM TmpInsurance.dbo.Rep10358 WHERE [No_Proposal]= @No_Proposal AND [No_Peserta]=@No_Peserta
                     
                     --EXEC ('UPDATE TmpInsurance.dbo.Rep10358 SET ['+@ColField+'_Premi]='+@Premi+', ['+@ColField+'_Kelas]='''+@Kelas+''' WHERE [No_Proposal]= '''+@No_Proposal+''' AND [No_Peserta]= '''+@No_Peserta+'''')
                     EXEC ('DECLARE @premiygada FLOAT SELECT @premiygada=COALESCE(['+@ColField+'_Premi],0) FROM TmpInsurance.dbo.Rep10358 WHERE [No_Proposal]= '''+@No_Proposal+''' AND [No_Peserta]= '''+@No_Peserta+''' UPDATE TmpInsurance.dbo.Rep10358 SET ['+@ColField+'_Premi]=(@premiygada + '+@Premi+'), ['+@ColField+'_Kelas]='''+@Kelas+''' WHERE [No_Proposal]= '''+@No_Proposal+''' AND [No_Peserta]= '''+@No_Peserta+'''')
                     --PRINT 'UPDATE TmpInsurance.dbo.Rep10358 SET ['+@ColField+'_Premi]='+CAST((@premiygada+@Premi) AS VARCHAR(12))+', ['+@ColField+'_Kelas]='''+@Kelas+''' WHERE [No_Proposal]= '''+@No_Proposal+''' AND [No_Peserta]= '''+@No_Peserta+''''
                     IF (@ColField = 'ACC') OR (@ColField = 'AJB') 
                        BEGIN
                              SET @sUP = STR(ISNULL(@up, 0), 15)

                              EXEC ('UPDATE TmpInsurance.dbo.Rep10358 SET ['+@ColField+'_UP]='+@sUP+' WHERE [No_Proposal]= '''+@No_Proposal+''' AND [No_Peserta]= '''+@No_Peserta+'''')
                              --PRINT 'UPDATE TmpInsurance.dbo.Rep10358 SET ['+@ColField+'_UP]='+@sUP+' WHERE [No_Proposal]= '''+@No_Proposal+''' AND [No_Peserta]= '''+@No_Peserta+''''
                        END
                     IF @@Error > 0 
                        ROLLBACK TRAN
                     COMMIT TRAN

                     FETCH NEXT FROM TmpCursor INTO @No_Proposal, @No_Peserta, @Nama, @Nik, @Tgl_Lahir, @Kelamin, @Status_Kawin, @Grd, @Pln, @Tgl_Mulai, @Kelas, @ColField, @Premi, @Urut, @Sebagai, @Umur, @Up
               END
         CLOSE TmpCursor
         DEALLOCATE TmpCursor
         EXEC ('UPDATE TmpInsurance.dbo.Rep10358 SET [Total]='+@SQLTotal)

   END
ELSE 
   BEGIN
         DECLARE TmpCursor CURSOR
         FOR
         SELECT
            dbo.Peserta.No_Proposal,
            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.Peserta.Tgl_Mulai,
            dbo.Peserta_Produk.Kelas,
            dbo.Sub_Kelompok.Field_Name,
            SUM(dbo.Peserta_Produk.Curr_Premi),
            dbo.Sub_Kelompok.Urutan,
            dbo.sebagai.Status_Peserta,
            dbo.func_GetUmur(dbo.Peserta.Tgl_Lahir, dbo.Peserta.Tgl_Mulai),
            SUM(dbo.Peserta_Produk.Benefit)
         FROM
            dbo.Peserta
            INNER JOIN dbo.Peserta_Produk ON dbo.Peserta.No_Proposal = dbo.Peserta_Produk.No_Proposal
                                             AND dbo.Peserta.No_Peserta = dbo.Peserta_Produk.No_Peserta
            INNER JOIN dbo.Sub_Kelompok ON dbo.Peserta_Produk.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok
            INNER JOIN dbo.Sebagai ON dbo.Peserta.Sebagai = dbo.Sebagai.IDI
         WHERE
            (dbo.Peserta_Produk.Status <> 'C')
            AND (dbo.Peserta.Status IN ('I', 'O'))
            AND (dbo.Peserta_Produk.No_Proposal = @NoProposal)
            AND (dbo.Peserta.No_Proposal = dbo.Peserta_Produk.No_Proposal)
            AND ((dbo.Peserta.Sebagai = 0
            AND @KaryawanYa = 1)
            OR (dbo.Peserta.Sebagai <> 0
            AND @KeluargaYa = 1)
            OR @KeluargaYa = 0
            OR @KaryawanYa = 0)
            AND ((dbo.Peserta.Kelamin = 'P'
            AND @PriaYa = 1)
            OR (dbo.Peserta.Kelamin = 'W'
            AND @WanitaYa = 1)
            OR @PriaYa = 0
            OR @WanitaYa = 0)
         GROUP BY
            dbo.Peserta.No_Proposal,
            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.Kelas,
            dbo.Sub_Kelompok.Field_Name,
            dbo.Sub_Kelompok.Urutan,
            dbo.Peserta.Grade,
            dbo.Peserta.Pln,
            dbo.Sebagai.Status_Peserta
         ORDER BY
            dbo.Peserta.No_Proposal,
            dbo.Peserta.No_Peserta,
            dbo.Sub_Kelompok.Urutan 

         OPEN TmpCursor
         FETCH NEXT FROM TmpCursor INTO @No_Proposal, @No_Peserta, @Nama, @Nik, @Tgl_Lahir, @Kelamin, @Status_Kawin, @Grd, @Pln, @Tgl_Mulai, @Kelas, @ColField, @Premi, @Urut, @Sebagai, @Umur, @Up
         WHILE @@FETCH_STATUS = 0 
               BEGIN
                     BEGIN TRAN
                     IF NOT EXISTS ( SELECT
                                        *
                                     FROM
                                        TmpInsurance.dbo.Rep10358
                                     WHERE
                                        [No_Proposal] = @No_Proposal
                                        AND [No_Peserta] = @No_Peserta ) 
                        INSERT  INTO TmpInsurance.dbo.Rep10358
                                ([No_Proposal],
                                 [No_Peserta],
                                 [Nama],
                                 [Nik],
                                 [Tgl_Lahir],
                                 [Sex],
                                 [Status],
                                 Grd,
                                 Pln,
                                 [Tgl_Mulai],
                                 Sebagai,
                                 Umur)
                        VALUES
                                (@No_Proposal,
                                 @No_Peserta,
                                 @Nama,
                                 @Nik,
                                 @Tgl_Lahir,
                                 @Kelamin,
                                 @Status_Kawin,
                                 @Grd,
                                 @Pln,
                                 @Tgl_Mulai,
                                 @Sebagai,
                                 @Umur)
                     EXEC ('UPDATE TmpInsurance.dbo.Rep10358 SET ['+@ColField+'_Premi]='+@Premi+', ['+@ColField+'_Kelas]='''+@Kelas+''' WHERE [No_Proposal]= '''+@No_Proposal+''' AND [No_Peserta]= '''+@No_Peserta+'''')
                     IF @ColField = 'ACC'
                        OR @ColField = 'AJB' 
                        BEGIN
                              SET @sUP = STR(ISNULL(@up, 0), 15)
                              EXEC ('UPDATE TmpInsurance.dbo.Rep10358 SET ['+@ColField+'_UP]='+@sUp+' WHERE [No_Proposal]= '''+@No_Proposal+''' AND [No_Peserta]= '''+@No_Peserta+'''')
                        END
                     IF @@Error > 0 
                        ROLLBACK TRAN
                     COMMIT TRAN

                     FETCH NEXT FROM TmpCursor INTO @No_Proposal, @No_Peserta, @Nama, @Nik, @Tgl_Lahir, @Kelamin, @Status_Kawin, @Grd, @Pln, @Tgl_Mulai, @Kelas, @ColField, @Premi, @Urut, @Sebagai, @Umur, @Up
               END
         CLOSE TmpCursor
         DEALLOCATE TmpCursor
         EXEC ('UPDATE TmpInsurance.dbo.Rep10358 SET [Total]='+@SQLTotal)
   END
   
------------- sebelumnya: -------
--SELECT TOP 100 PERCENT
--    TmpInsurance.dbo.Rep10358.*,
--    dbo.Cabang.Nama AS Cabang,
--    dbo.Kartu_Peserta.Card_No
--FROM
--    TmpInsurance.dbo.Rep10358
--    INNER JOIN dbo.Peserta ON TmpInsurance.dbo.Rep10358.No_Peserta = dbo.Peserta.No_Peserta
--                              AND TmpInsurance.dbo.Rep10358.No_Proposal = dbo.Peserta.No_Proposal
--    INNER JOIN dbo.Sebagai ON dbo.Peserta.Sebagai = dbo.Sebagai.IDI
--    LEFT OUTER JOIN dbo.Kartu_Peserta ON TmpInsurance.dbo.Rep10358.No_Proposal = dbo.Kartu_Peserta.No_Proposal
--                                         AND dbo.Peserta.No_Peserta = dbo.Kartu_Peserta.No_Peserta
--    LEFT OUTER JOIN dbo.Cabang ON dbo.Peserta.No_Prs = dbo.Cabang.No_Prs
--                                  AND dbo.Peserta.Cabang = dbo.Cabang.Kode
----WHERE (dbo.Cabang.Nama LIKE '%'+@Cabang+'%' OR isnull(@Cabang,'')='')
--ORDER BY
--    TmpInsurance.dbo.Rep10358.No_Peserta
----------------------------------


------------------- modified by Pribadi Pamungkas. 2015-10-16. Request dari bu Yis

-- list nama kolom
DECLARE @satu VARCHAR(1000) = ''
SELECT @satu=@satu + 'TmpInsurance.dbo.Rep10358.' + name + ', '
FROM TmpInsurance.sys.columns WHERE object_id = OBJECT_ID('TmpInsurance.dbo.Rep10358') AND name<>'No_Proposal'
--SELECT LEFT(@satu,LEN(@satu)-1)

    
DECLARE @QueryAkhir VARCHAR(4000)
SET @QueryAkhir = 'SELECT TOP 100 PERCENT ' + LEFT(@satu,LEN(@satu)-1) + ', dbo.Cabang.Nama AS Cabang '
SET @QueryAkhir = @QueryAkhir + 'FROM TmpInsurance.dbo.Rep10358
    INNER JOIN dbo.Peserta ON TmpInsurance.dbo.Rep10358.No_Peserta = dbo.Peserta.No_Peserta
                              AND TmpInsurance.dbo.Rep10358.No_Proposal = dbo.Peserta.No_Proposal
    INNER JOIN dbo.Sebagai ON dbo.Peserta.Sebagai = dbo.Sebagai.IDI
    LEFT OUTER JOIN dbo.Kartu_Peserta ON TmpInsurance.dbo.Rep10358.No_Proposal = dbo.Kartu_Peserta.No_Proposal
                                         AND dbo.Peserta.No_Peserta = dbo.Kartu_Peserta.No_Peserta
    LEFT OUTER JOIN dbo.Cabang ON dbo.Peserta.No_Prs = dbo.Cabang.No_Prs
                                  AND dbo.Peserta.Cabang = dbo.Cabang.Kode
ORDER BY
    TmpInsurance.dbo.Rep10358.No_Peserta'

EXEC(@QueryAkhir)

--SELECT
--  *
--FROM
--  TmpInsurance.dbo.Rep10358
--ORDER BY
--    No_Peserta

--SELECT
--  *
--FROM
--  @tabel_hasil
--ORDER BY
--  no_peserta

