DECLARE @hasil TABLE ( 
                  No_Proposal VARCHAR(9),
                  Nama_Perusahaan_tmp VARCHAR(50),
                  No_Polis_tmp VARCHAR(11),
                  Nama_Perusahaan VARCHAR(50),
                  No_Polis VARCHAR(11),
                  GT_OG VARCHAR(2),
                  Sub_Init VARCHAR(1),
                  Tgl_Mulai DATETIME,
                  Tgl_Selesai DATETIME,
                  AdMedika VARCHAR(6),
                  Bidang_Usaha VARCHAR(50),
                  [Broker] VARCHAR(50),
                  Jml_Peserta INT,
                  Jml_Premi FLOAT,
                  Diskon_Pr FLOAT,
                  Diskon_Cp FLOAT,
                  Jml_Diajukan FLOAT,
                  Jml_Dibayar FLOAT,
                  Rasio1 FLOAT,
                  Rasio2 FLOAT,
                  Rasio3 FLOAT
               )

DECLARE 
  @no_proposal VARCHAR(9),
  @nama_perusahaan VARCHAR(50),
  @no_polis VARCHAR(11)

DECLARE list_cursor CURSOR FAST_FORWARD FOR  
SELECT 
	pa.No_Proposal,
	p.Nama_Perusahaan,
	pa.No_Polis
FROM
	dbo.Polis_Asuransi AS pa INNER JOIN
	dbo.Perusahaan AS p ON pa.No_Prs = p.No_Prs
WHERE
  pa.Status = 'I'
  AND p.GT_OG = 'GT'
  --AND pa.No_Proposal = '2014.0692'
ORDER BY
  p.Nama_Perusahaan


OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_proposal, @nama_perusahaan, @no_polis

WHILE @@FETCH_STATUS = 0  
BEGIN  

  --No.	Nama Perusahaan	No. Polis	GT/OG	S/I	Tgl.Mulai	Tgl.Selesai	Admedika	Bidang_Usaha	Broker	Jml.Premi	Disk.Prs	Disk.Cp	Klaim Diajukan	Klaim Dibayar	Rasio1	Rasio2	Rasio3
  
  -- start: klaim rasio polis --
  DECLARE
    @IdBroker varchar(5) = '',
    @GroupPrs varchar(5) = '',
    @GTOG varchar(2) = '',
    @NoProposal varchar(9) = @no_proposal,
    @TglMulai1 DATETIME = NULL,
    @TglMulai2 DATETIME = NULL,
    @TglSelesai1 DATETIME = NULL,
    @TglSelesai2 DATETIME = NULL,
    @TglPremi1 DATETIME = NULL,
    @TglPremi2 DATETIME = '2015-09-30',
    @TglKlaim1 DATETIME = NULL,
    @TglKlaim2 DATETIME = '2015-09-30',
    @Pilihan INT = 3,
    @PilihBroker SMALLINT = 0,
    @BidangUsaha varchar(10)= '',
    @TglMulai1Ya BIT = 0,
    @TglMulai2Ya BIT = 0,
    @TglSelesai1Ya BIT = 0,
    @TglSelesai2Ya BIT = 0,
    @TglPremi1Ya BIT = 0,
    @TglPremi2Ya BIT = 1,
    @TglKlaim1Ya BIT = 0,
    @TglKlaim2Ya BIT = 1,
    @Marketing VARCHAR(4) = '',
    @StatusNasabah  INT = 0

  DECLARE 
    @StatusYa varchar(1)  --add zai 20140526
    
	IF @StatusNasabah = 0 
	  SET @StatusYa = ''
	ELSE IF @StatusNasabah = 1 
	  SET @StatusYa = 'I'  --add zai 20140526

  --EXEC gproc_RepColumnLoad '10381', 3

  INSERT INTO @hasil
        (No_Proposal,         -- No_Proposal - varchar(9)
         Nama_Perusahaan_tmp, -- Nama_Perusahaan_tmp - varchar(50)
         No_Polis_tmp,
         Nama_Perusahaan,     -- Nama_Perusahaan - varchar(50)
         No_Polis,            -- No_Polis - varchar(11)
         GT_OG,               -- GT_OG - varchar(2)
         Sub_Init,            -- Sub_Init - varchar(1)
         Tgl_Mulai,           -- Tgl_Mulai - datetime
         Tgl_Selesai,         -- Tgl_Selesai - datetime
         AdMedika,            -- AdMedika - varchar(6)
         Bidang_Usaha,        -- Bidang_Usaha - varchar(50)
         [Broker],            -- Broker - varchar(50)
         Jml_Peserta,         -- Jml_Peserta - int
         Jml_Premi,           -- Jml_Premi - float
         Diskon_Pr,           -- Diskon_Pr - float
         Diskon_Cp,           -- Diskon_Cp - float
         Jml_Diajukan,        -- Jml_Diajukan - float
         Jml_Dibayar,         -- Jml_Dibayar - float
         Rasio1,              -- Rasio1 - float
         Rasio2,              -- Rasio2 - float
         Rasio3)              -- Rasio3 - float
  SELECT
    a.no_proposal,
    a.nama_perusahaan,
    a.no_polis,
    b.Nama_Perusahaan, 
    b.No_Polis, 
    b.GT_OG, 
    b.Sub_Init, 
    b.Tgl_Mulai, 
    b.Tgl_Selesai, 
    b.AdMedika, 
    b.Bidang_Usaha, 
    b.[Broker],
    COALESCE(b.Jml_Peserta,0) AS Jml_Peserta,
    b.Jml_Premi, 
    b.Diskon_Pr,  
    b.Diskon_Cp,
    b.Jml_Diajukan, 
    b.Jml_Dibayar, 
    b.Rasio1,
    b.Rasio2,
    b.Rasio3
  FROM
    (
      SELECT
        @no_proposal AS no_proposal,
        @nama_perusahaan AS nama_perusahaan,
        @no_polis AS no_polis
    ) AS a LEFT JOIN
    (
      SELECT 
        T1.No_Proposal,
        T1.Nama_Perusahaan, 
        T1.No_Polis, 
        T1.GT_OG, 
        T1.Sub_Init, 
        T1.Tgl_Mulai, 
        T1.Tgl_Selesai, 
        T1.AdMedika, 
        T1.Bidang_Usaha, 
        T1.[Broker],
        jp.Jml_Peserta,
        Sum(T2.Jml_Premi) as Jml_Premi, 
        Sum(0.01*T1.Diskon_Pr*T2.Jml_Premi+T1.Diskon_PrRp) as Diskon_Pr,  
        Sum(0.01*T1.Diskon_Cp*T2.Jml_Premi+T1.Diskon_CpRp) as Diskon_Cp,
        Sum(isnull(T3.Jml_Diajukan,0)) as Jml_Diajukan, 
        Sum(isnull(T3.Jml_Dibayar,0)) as Jml_Dibayar, 
        Rasio1 = Round(100.0*dbo.func_Bagi(Sum(T3.Jml_Dibayar), Sum(T2.Jml_Premi)),2),
        Rasio2 = Round(100.0*dbo.func_Bagi(Sum(T3.Jml_Dibayar), Sum(T2.Jml_Premi - 0.01*T1.Diskon_Pr*T2.Jml_Premi+T1.Diskon_PrRp)),2),
        Rasio3 = Round(100.0*dbo.func_Bagi(Sum(T3.Jml_Dibayar), Sum(T2.Jml_Premi - 0.01*T1.Diskon_Pr*T2.Jml_Premi+T1.Diskon_PrRp - 0.01*T1.Diskon_Cp*T2.Jml_Premi+T1.Diskon_CpRp)),2)
      FROM
        (
          SELECT  
            dbo.Bidang_Usaha.Nama as Bidang_Usaha, 
            dbo.Broker.Broker_Nama as [Broker], 
            dbo.Polis_Asuransi.No_Proposal, 
            dbo.Polis_Asuransi.No_Polis, 
            dbo.Perusahaan.GT_OG, 
            dbo.Polis_Asuransi.Sub_Init, 
            dbo.Polis_Asuransi.Tgl_Mulai, 
            dbo.Polis_Asuransi.Tgl_Selesai, 
            AdMedika=dbo.func_BooleanToStr(dbo.Polis_Asuransi.AdMedika_Ya,'Ya','Tidak'), 
            dbo.Produk_Polis.Id_Produk, 
            dbo.Perusahaan.Nama_Perusahaan,  
            dbo.Peserta.No_Peserta, 
            dbo.Peserta.Nama, 
            '[' + dbo.Peserta.Cabang + ']' + dbo.Cabang.Nama AS Nama_Unit, 
            dbo.Produk_Polis.Nama_Paket as Nama_Sub, 
            isnull(dbo.Produk_Polis.Diskon_Pr,0) as Diskon_Pr,
            isnull(dbo.Produk_Polis.Diskon_PrRp,0) as Diskon_PrRp, 
            isnull(dbo.Produk_Polis.Diskon_Cp,0) as Diskon_Cp, 
            isnull(dbo.Produk_Polis.Diskon_CpRp,0) as Diskon_CpRp
          FROM     
            dbo.Peserta INNER JOIN
            dbo.Polis_Asuransi ON dbo.Peserta.No_Proposal = dbo.Polis_Asuransi.No_Proposal INNER JOIN
            dbo.Produk_Polis ON dbo.Polis_Asuransi.No_Proposal = dbo.Produk_Polis.No_Proposal INNER JOIN
            dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs LEFT OUTER JOIN
            dbo.Cabang ON dbo.Polis_Asuransi.No_Prs = dbo.Cabang.No_Prs AND dbo.Peserta.Cabang = dbo.Cabang.Kode LEFT OUTER JOIN
            dbo.Bidang_Usaha ON dbo.Perusahaan.Bidang_Usaha=dbo.Bidang_Usaha.IDI LEFT OUTER JOIN
            dbo.Broker ON dbo.Polis_Asuransi.Broker_Id=dbo.Broker.Broker_Id
          WHERE 
            (dbo.Peserta.Status<>'C') 
            AND (COALESCE(dbo.Peserta.Keterangan,'') <> 'LAPSE AUTO 2015-07-01')
            AND (dbo.Perusahaan.Group_Perusahaan=@GroupPrs OR isnull(@GroupPrs,'')='')
            AND (dbo.Perusahaan.GT_OG=@GTOG OR isnull(@GTOG,'')='')
            AND (dbo.Perusahaan.Bidang_Usaha=@BidangUsaha OR isnull(@BidangUsaha,'')='')
            AND (dbo.Polis_Asuransi.Broker_Id=@IdBroker OR isnull(@IdBroker,'')='')
            AND ((isnull(dbo.Polis_Asuransi.Broker_Id,'')<>'' AND @PilihBroker=1) OR (isnull(dbo.Polis_Asuransi.Broker_Id,'')='' AND @PilihBroker=2) OR isnull(@PilihBroker,0)=0)
            AND (dbo.Polis_Asuransi.No_Proposal=@NoProposal OR isnull(@NoProposal,'')='') 
            AND (dbo.Polis_Asuransi.Tgl_Mulai>=@TglMulai1 OR isnull(@TglMulai1Ya,0)=0)
            AND (dbo.Polis_Asuransi.Tgl_Mulai<=@TglMulai2 OR isnull(@TglMulai2Ya,0)=0)
            AND (dbo.Polis_Asuransi.Tgl_Selesai>=@TglSelesai1 OR isnull(@TglSelesai1Ya,0)=0)
            AND (dbo.Polis_Asuransi.Tgl_Selesai<=@TglSelesai2 OR isnull(@TglSelesai2Ya,0)=0) AND (dbo.Peserta.No_Proposal = @NoProposal OR isnull(@NoProposal,'')='') 
            AND (dbo.Polis_Asuransi.Marketing = @Marketing OR ISNULL(@Marketing,'')='')    --add zai 20140528
            AND (dbo.Polis_Asuransi.Status = @StatusYa OR ISNULL(@StatusYa,'')='')    --add zai 20140526
         ) 
         as T1 
         INNER JOIN
         (
           SELECT 
             dbo.Note_Terbit_Master.No_Proposal, 
             dbo.Peserta_Produk_Detail.No_Peserta, 
             dbo.Peserta_Produk_Detail.Id_Produk, 
             SUM(dbo.Note_Terbit_Master.Tipe* ABS(isnull(dbo.Peserta_Produk_Detail.Jumlah,0))) AS Jml_Premi
           FROM 
             dbo.Note_Terbit_Master INNER JOIN
             dbo.Peserta_Produk_Detail ON dbo.Note_Terbit_Master.No_Ref = dbo.Peserta_Produk_Detail.No_Ref INNER JOIN
             dbo.Peserta ON dbo.Note_Terbit_Master.No_Proposal = dbo.Peserta.No_Proposal AND dbo.Peserta_Produk_Detail.No_Peserta = dbo.Peserta.No_Peserta 
             
           WHERE   
             (dbo.Note_Terbit_Master.Jenis <= 19) 
             AND (dbo.Note_Terbit_Master.Status<>'C') 
             AND (dbo.Note_Terbit_Master.No_Proposal = @NoProposal OR isnull(@NoProposal,'')='')
             AND (dbo.Note_Terbit_Master.Tgl_Buat>=@TglPremi1 OR isnull(@TglPremi1Ya,0)=0)
             AND (dbo.Note_Terbit_Master.Tgl_Buat<=@TglPremi2 OR isnull(@TglPremi2Ya,0)=0)
           GROUP BY dbo.Note_Terbit_Master.No_Proposal, dbo.Peserta_Produk_Detail.No_Peserta, dbo.Peserta_Produk_Detail.Id_Produk
         ) 
         as T2 
         ON T1.No_Proposal=T2.No_Proposal AND T1.No_Peserta=T2.No_Peserta AND T1.Id_Produk=T2.Id_Produk
         LEFT OUTER JOIN
         (
           SELECT  
             dbo.Klaim_Ref_Detail.No_Proposal, 
             dbo.Klaim_Ref_Detail.No_Santunan as No_Peserta, 
             dbo.Klaim_Ref_Detail.Id_Produk, 
             SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Diajukan,0))) as Jml_Diajukan,
             SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar,0)) - Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old,0))) as Jml_Dibayar
           FROM 
             dbo.Note_Terbit_Master INNER JOIN
             dbo.Klaim_Ref_Detail ON dbo.Note_Terbit_Master.No_Ref = dbo.Klaim_Ref_Detail.No_Ref
             INNER JOIN
             (
               SELECT * FROM dbo.Klaim_Peserta
               WHERE Status = 'I'               
             ) AS kp
             ON dbo.Klaim_Ref_Detail.No_Proposal = kp.No_Proposal 
                AND dbo.Klaim_Ref_Detail.No_Peserta = kp.No_Peserta    
                AND dbo.Klaim_Ref_Detail.No_Klaim = kp.No_Klaim      
           WHERE (dbo.Note_Terbit_Master.Jenis IN (20, 21, 22, 23, 24, 25, 29))  -- 29 jenis note baru, by Pribadi Pamungkas 2015-04-07
             AND (dbo.Note_Terbit_Master.Status<>'C') 
             AND (dbo.Note_Terbit_Master.No_Proposal = @NoProposal OR isnull(@NoProposal,'')='')
             AND (dbo.Note_Terbit_Master.Tgl_Buat>=@TglKlaim1 OR isnull(@TglKlaim1Ya,0)=0)
             AND (dbo.Note_Terbit_Master.Tgl_Buat<=@TglKlaim2 OR isnull(@TglKLaim2Ya,0)=0)
          GROUP BY 
            dbo.Klaim_Ref_Detail.No_Proposal, dbo.Klaim_Ref_Detail.No_Santunan, dbo.Klaim_Ref_Detail.Id_Produk
         ) 
         as T3 
         ON T1.No_Proposal=T3.No_Proposal AND T1.No_Peserta=T3.No_Peserta AND T1.Id_Produk=T3.Id_Produk
         
         INNER JOIN
         (
            SELECT
              No_Proposal,
        	    COUNT(*) AS Jml_Peserta
            FROM
        	    dbo.Peserta
            WHERE 
              (Status<>'C') 
              AND (COALESCE(Keterangan,'') <> 'LAPSE AUTO 2015-07-01')
            GROUP BY
              No_Proposal
         ) AS jp ON T1.No_Proposal = jp.No_Proposal

      GROUP BY T1.Nama_Perusahaan, T1.No_Polis, T1.GT_OG, T1.Sub_Init, T1.Tgl_Mulai, T1.Tgl_Selesai, T1.AdMedika, T1.Bidang_Usaha, T1.BROKER, T1.No_Proposal, jp.Jml_Peserta
      --ORDER BY T1.Nama_Perusahaan, T1.No_Polis, T1.GT_OG, T1.Sub_Init
    ) AS b ON a.no_proposal = b.No_Proposal
  -- end: klaim rasio polis --

  FETCH NEXT FROM list_cursor INTO @no_proposal, @nama_perusahaan, @no_polis
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 


SELECT
	*
FROM
	@hasil