DECLARE @list_polis TABLE ( no_polis VARCHAR(12))

DECLARE
  @pi_tgl_mulai DATETIME = '2016-01-01',
  @pi_tgl_selesai DATETIME = '2016-12-31'


DECLARE 
  @p_no_polis VARCHAR(11),
  @p_no_proposal VARCHAR(9),
  @p_nama_perusahaan VARCHAR(50)

DECLARE list_cursor CURSOR FAST_FORWARD FOR  
SELECT 
	pa.No_Polis,
	pa.No_Proposal,
	prs.Nama_Perusahaan
FROM
	dbo.Polis_Asuransi AS pa INNER JOIN
	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
WHERE
  --pa.No_Polis IN ('2011.0018.0',
  --                '2012.0008.0',
  --                '2014.0069.0',
  --                '2008.0247.0',
  --                '2012.0009.0',
  --                '2007.0196.0',
  --                '2007.0226.0',
  --                '2007.0195.0',
  --                '2007.0205.0',
  --                '2007.0197.0',
  --                '2007.0210.0',
  --                '2007.0203.0',
  --                '2007.0201.0',
  --                '2007.0206.0',
  --                '2012.0010.0',
  --                '2011.0017.0',
  --                '2008.0248.0',
  --                '2007.0198.0',
  --                '2007.0202.0',
  --                '2007.0207.0',
  --                '2007.0211.0',
  --                '2007.0200.0',
  --                '2014.0070.0',
  --                '2011.0016.0')
  pa.No_Polis IN ('2011.0018.0',
                  '2012.0008.0',
                  '2014.0069.0',
                  '2008.0247.0',
                  '2016.0005.0',
                  '2016.0004.0',
                  '2007.0196.0',
                  '2007.0226.0',
                  '2007.0195.0',
                  '2007.0205.0',
                  '2007.0197.0',
                  '2007.0210.0',
                  '2007.0201.0',
                  '2007.0206.0',
                  '2011.0017.0',
                  '2008.0248.0',
                  '2007.0198.0',
                  '2007.0202.0',
                  '2007.0207.0',
                  '2007.0211.0',
                  '2007.0200.0',
                  '2014.0070.0',
                  '2011.0016.0')
  AND pa.Tgl_Mulai >= @pi_tgl_mulai
  AND pa.Tgl_Selesai <= @pi_tgl_selesai
ORDER BY
  prs.Nama_Perusahaan

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @p_no_polis, @p_no_proposal, @p_nama_perusahaan

WHILE @@FETCH_STATUS = 0  
BEGIN  
  DECLARE
    @jml_karyawan_1 INT,
    @jml_karyawan_2 INT,
    @jml_peserta_1 INT,
    @jml_peserta_2 INT,
    @premi_AJB_1 FLOAT,
    @premi_AJB_2 FLOAT,
    @premi_RJ_1 FLOAT,
    @premi_RJ_2 FLOAT,
    @premi_RI_1 FLOAT,
    @premi_RI_2 FLOAT,
    @klaim_diajukan_AJB_1 FLOAT,
    @klaim_diajukan_AJB_2 FLOAT,
    @klaim_diajukan_RJ_1 FLOAT,
    @klaim_diajukan_RJ_2 FLOAT,
    @klaim_diajukan_RI_1 FLOAT,
    @klaim_diajukan_RI_2 FLOAT,
    @klaim_dibayar_AJB_1 FLOAT,
    @klaim_dibayar_AJB_2 FLOAT,
    @klaim_dibayar_RJ_1 FLOAT,
    @klaim_dibayar_RJ_2 FLOAT,
    @klaim_dibayar_RI_1 FLOAT,
    @klaim_dibayar_RI_2 FLOAT
    

  ---- karyawan 1-9
  SELECT
  	@jml_karyawan_1 = COUNT(*)
  FROM
  	dbo.Peserta
  WHERE
    No_Proposal = @p_no_proposal
    AND Status NOT IN ('G','C')
    AND Grade >= 1
    AND Grade <= 9
    AND Sebagai = 0

  -- karyawan 10-22
  SELECT
  	@jml_karyawan_2 = COUNT(*)
  FROM
  	dbo.Peserta
  WHERE
    No_Proposal = @p_no_proposal
    AND Status NOT IN ('G','C')
    AND Grade >= 10
    AND Grade <= 22
    AND Sebagai = 0
    
  ---- peserta 1-9
  SELECT
  	@jml_peserta_1 = COUNT(*)
  FROM
  	dbo.Peserta
  WHERE
    No_Proposal = @p_no_proposal
    AND Status NOT IN ('G','C')
    AND Grade >= 1
    AND Grade <= 9

  ---- peserta 10-22
  SELECT
  	@jml_peserta_2 = COUNT(*)
  FROM
  	dbo.Peserta
  WHERE
    No_Proposal = @p_no_proposal
    AND Status NOT IN ('G','C')
    AND Grade >= 10
    AND Grade <= 22
  
  -------------
  
  -- premi AJB 1-9
  --SELECT
	 -- SUM(pp.Premi * 1.0 * DATEDIFF(DAY, pp.Tgl_Mulai, p.Tgl_Selesai + 1)/DATEDIFF(DAY, pa.Tgl_Mulai, pa.Tgl_Selesai + 1)),
	 -- SUM(pp.premi),
	 -- SUM(pp.Curr_Premi)
  --FROM
	 -- dbo.Polis_Asuransi AS pa INNER JOIN
	 -- dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal INNER JOIN
	 -- dbo.Peserta_Produk AS pp ON p.No_Proposal = pp.No_Proposal AND p.No_Peserta = pp.No_Peserta
  --WHERE
  --  pa.No_Proposal = @p_no_proposal
  --  AND p.Grade >= 1
  --  AND p.Grade <= 9
  --  AND p.Status NOT IN ('G','C')
  --  AND pp.Sub_Kelompok = '0100'  -- AJB

  SELECT 
    @premi_AJB_1 = SUM(dbo.Note_Terbit_Master.Tipe * isnull(dbo.Peserta_Produk_Detail.Jumlah,0))
  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 = @p_no_proposal)
    AND dbo.Peserta.Grade >= 1
    AND dbo.Peserta.Grade <= 9
    AND dbo.Peserta_Produk_Detail.Sub_Kelompok = '0100'  -- AJB

  -- premi RJ 1-9
  --SELECT
	 -- SUM(pp.Premi * 1.0 * DATEDIFF(DAY, pp.Tgl_Mulai, p.Tgl_Selesai + 1)/DATEDIFF(DAY, pa.Tgl_Mulai, pa.Tgl_Selesai + 1)),
	 -- SUM(pp.premi),
	 -- SUM(pp.Curr_Premi)
  --FROM
	 -- dbo.Polis_Asuransi AS pa INNER JOIN
	 -- dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal INNER JOIN
	 -- dbo.Peserta_Produk AS pp ON p.No_Proposal = pp.No_Proposal AND p.No_Peserta = pp.No_Peserta
  --WHERE
  --  pa.No_Proposal = @p_no_proposal
  --  AND p.Grade >= 1
  --  AND p.Grade <= 9
  --  AND p.Status NOT IN ('G','C')
  --  AND pp.Sub_Kelompok = '0301'  -- RJ

  SELECT 
    @premi_RJ_1 = SUM(dbo.Note_Terbit_Master.Tipe * isnull(dbo.Peserta_Produk_Detail.Jumlah,0))
  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 = @p_no_proposal)
    AND dbo.Peserta.Grade >= 1
    AND dbo.Peserta.Grade <= 9
    AND dbo.Peserta_Produk_Detail.Sub_Kelompok = '0301'  -- RJ

  -- premi RI 1-9
  --SELECT
	 -- SUM(pp.Premi * 1.0 * DATEDIFF(DAY, pp.Tgl_Mulai, p.Tgl_Selesai + 1)/DATEDIFF(DAY, pa.Tgl_Mulai, pa.Tgl_Selesai + 1)),
	 -- SUM(pp.premi),
	 -- SUM(pp.Curr_Premi)
  --FROM
	 -- dbo.Polis_Asuransi AS pa INNER JOIN
	 -- dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal INNER JOIN
	 -- dbo.Peserta_Produk AS pp ON p.No_Proposal = pp.No_Proposal AND p.No_Peserta = pp.No_Peserta
  --WHERE
  --  pa.No_Proposal = @p_no_proposal
  --  AND p.Grade >= 1
  --  AND p.Grade <= 9
  --  AND p.Status NOT IN ('G','C')
  --  AND pp.Sub_Kelompok = '0302'  -- RI
    
  SELECT 
    @premi_RI_1 = SUM(dbo.Note_Terbit_Master.Tipe * isnull(dbo.Peserta_Produk_Detail.Jumlah,0))
  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 = @p_no_proposal)
    AND dbo.Peserta.Grade >= 1
    AND dbo.Peserta.Grade <= 9
    AND dbo.Peserta_Produk_Detail.Sub_Kelompok = '0302'  -- RI

  -- premi AJB 10-22
  --SELECT
	 -- SUM(pp.Premi * 1.0 * DATEDIFF(DAY, pp.Tgl_Mulai, p.Tgl_Selesai + 1)/DATEDIFF(DAY, pa.Tgl_Mulai, pa.Tgl_Selesai + 1)),
	 -- SUM(pp.premi),
	 -- SUM(pp.Curr_Premi)
  --FROM
	 -- dbo.Polis_Asuransi AS pa INNER JOIN
	 -- dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal INNER JOIN
	 -- dbo.Peserta_Produk AS pp ON p.No_Proposal = pp.No_Proposal AND p.No_Peserta = pp.No_Peserta
  --WHERE
  --  pa.No_Proposal = @p_no_proposal
  --  AND p.Grade >= 10
  --  AND p.Grade <= 22
  --  AND p.Status NOT IN ('G','C')
  --  AND pp.Sub_Kelompok = '0100'  -- AJB

  SELECT 
    @premi_AJB_2 = SUM(dbo.Note_Terbit_Master.Tipe * isnull(dbo.Peserta_Produk_Detail.Jumlah,0))
  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 = @p_no_proposal)
    AND dbo.Peserta.Grade >= 10
    AND dbo.Peserta.Grade <= 22
    AND dbo.Peserta_Produk_Detail.Sub_Kelompok = '0100'  -- AJB

  -- premi RJ 10-22
  --SELECT
	 -- SUM(pp.Premi * 1.0 * DATEDIFF(DAY, pp.Tgl_Mulai, p.Tgl_Selesai + 1)/DATEDIFF(DAY, pa.Tgl_Mulai, pa.Tgl_Selesai + 1)),
	 -- SUM(pp.premi),
	 -- SUM(pp.Curr_Premi)
  --FROM
	 -- dbo.Polis_Asuransi AS pa INNER JOIN
	 -- dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal INNER JOIN
	 -- dbo.Peserta_Produk AS pp ON p.No_Proposal = pp.No_Proposal AND p.No_Peserta = pp.No_Peserta
  --WHERE
  --  pa.No_Proposal = @p_no_proposal
  --  AND p.Grade >= 10
  --  AND p.Grade <= 22
  --  AND p.Status NOT IN ('G','C')
  --  AND pp.Sub_Kelompok = '0301'  -- RJ

  SELECT 
    @premi_RJ_2 = SUM(dbo.Note_Terbit_Master.Tipe * isnull(dbo.Peserta_Produk_Detail.Jumlah,0))
  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 = @p_no_proposal)
    AND dbo.Peserta.Grade >= 10
    AND dbo.Peserta.Grade <= 22
    AND dbo.Peserta_Produk_Detail.Sub_Kelompok = '0301'  -- RJ

  -- premi RI 10-22
  --SELECT
	 -- SUM(pp.Premi * 1.0 * DATEDIFF(DAY, pp.Tgl_Mulai, p.Tgl_Selesai + 1)/DATEDIFF(DAY, pa.Tgl_Mulai, pa.Tgl_Selesai + 1)),
	 -- SUM(pp.premi),
	 -- SUM(pp.Curr_Premi)
  --FROM
	 -- dbo.Polis_Asuransi AS pa INNER JOIN
	 -- dbo.Peserta AS p ON pa.No_Proposal = p.No_Proposal INNER JOIN
	 -- dbo.Peserta_Produk AS pp ON p.No_Proposal = pp.No_Proposal AND p.No_Peserta = pp.No_Peserta
  --WHERE
  --  pa.No_Proposal = @p_no_proposal
  --  AND p.Grade >= 10
  --  AND p.Grade <= 22
  --  AND p.Status NOT IN ('G','C')
  --  AND pp.Sub_Kelompok = '0302'  -- RI

  SELECT 
    @premi_RI_2 = SUM(dbo.Note_Terbit_Master.Tipe * isnull(dbo.Peserta_Produk_Detail.Jumlah,0))
  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 = @p_no_proposal)
    AND dbo.Peserta.Grade >= 10
    AND dbo.Peserta.Grade <= 22
    AND dbo.Peserta_Produk_Detail.Sub_Kelompok = '0302'  -- RI

  ---
  --klaim AJB 1-9
  SELECT  
    @klaim_diajukan_AJB_1 = SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Diajukan,0))),
    @klaim_dibayar_AJB_1 = SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar,0)) - Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old,0)))
  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  INNER JOIN
    dbo.Peserta AS pst ON dbo.Klaim_Ref_Detail.No_Proposal = pst.No_Proposal
                          AND dbo.Klaim_Ref_Detail.No_Santunan = pst.No_Peserta
  WHERE 
    (dbo.Note_Terbit_Master.Jenis IN (20, 21, 22, 23, 24, 25, 29))
    AND (dbo.Note_Terbit_Master.Status<>'C') 
    AND (dbo.Note_Terbit_Master.No_Proposal = @p_no_proposal)
    AND pst.Grade >= 1
    AND pst.Grade <= 9
    AND kp.Sub_Kelompok = '0100'  -- AJB

  --klaim RJ 1-9
  SELECT  
    @klaim_diajukan_RJ_1 = SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Diajukan,0))),
    @klaim_dibayar_RJ_1 = SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar,0)) - Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old,0)))
  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  INNER JOIN
    dbo.Peserta AS pst ON dbo.Klaim_Ref_Detail.No_Proposal = pst.No_Proposal
                          AND dbo.Klaim_Ref_Detail.No_Santunan = pst.No_Peserta
  WHERE 
    (dbo.Note_Terbit_Master.Jenis IN (20, 21, 22, 23, 24, 25, 29))
    AND (dbo.Note_Terbit_Master.Status<>'C') 
    AND (dbo.Note_Terbit_Master.No_Proposal = @p_no_proposal)
    AND pst.Grade >= 1
    AND pst.Grade <= 9
    AND kp.Sub_Kelompok = '0301'  -- RJ

  -- klaim RI 1-9
  SELECT  
    @klaim_diajukan_RI_1 = SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Diajukan,0))),
    @klaim_dibayar_RI_1 = SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar,0)) - Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old,0)))
  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  INNER JOIN
    dbo.Peserta AS pst ON dbo.Klaim_Ref_Detail.No_Proposal = pst.No_Proposal
                          AND dbo.Klaim_Ref_Detail.No_Santunan = pst.No_Peserta
  WHERE 
    (dbo.Note_Terbit_Master.Jenis IN (20, 21, 22, 23, 24, 25, 29))
    AND (dbo.Note_Terbit_Master.Status<>'C') 
    AND (dbo.Note_Terbit_Master.No_Proposal = @p_no_proposal)
    AND pst.Grade >= 1
    AND pst.Grade <= 9
    AND kp.Sub_Kelompok = '0302'  -- RI

  -- klaim AJB 10-22
  SELECT  
    @klaim_diajukan_AJB_2 = SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Diajukan,0))),
    @klaim_dibayar_AJB_2 = SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar,0)) - Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old,0)))
  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  INNER JOIN
    dbo.Peserta AS pst ON dbo.Klaim_Ref_Detail.No_Proposal = pst.No_Proposal
                          AND dbo.Klaim_Ref_Detail.No_Santunan = pst.No_Peserta
  WHERE 
    (dbo.Note_Terbit_Master.Jenis IN (20, 21, 22, 23, 24, 25, 29))
    AND (dbo.Note_Terbit_Master.Status<>'C') 
    AND (dbo.Note_Terbit_Master.No_Proposal = @p_no_proposal)
    AND pst.Grade >= 10
    AND pst.Grade <= 22
    AND kp.Sub_Kelompok = '0100'  -- AJB

  -- klaim RJ 10-22
  SELECT  
    @klaim_diajukan_RJ_2 = SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Diajukan,0))),
    @klaim_dibayar_RJ_2 = SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar,0)) - Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old,0)))
  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  INNER JOIN
    dbo.Peserta AS pst ON dbo.Klaim_Ref_Detail.No_Proposal = pst.No_Proposal
                          AND dbo.Klaim_Ref_Detail.No_Santunan = pst.No_Peserta
  WHERE 
    (dbo.Note_Terbit_Master.Jenis IN (20, 21, 22, 23, 24, 25, 29))
    AND (dbo.Note_Terbit_Master.Status<>'C') 
    AND (dbo.Note_Terbit_Master.No_Proposal = @p_no_proposal)
    AND pst.Grade >= 10
    AND pst.Grade <= 22
    AND kp.Sub_Kelompok = '0301'  -- RJ

  -- klaim RI 10-22
  SELECT  
    @klaim_diajukan_RI_2 = SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Diajukan,0))),
    @klaim_dibayar_RI_2 = SUM(Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar,0)) - Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old,0)))
  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  INNER JOIN
    dbo.Peserta AS pst ON dbo.Klaim_Ref_Detail.No_Proposal = pst.No_Proposal
                          AND dbo.Klaim_Ref_Detail.No_Santunan = pst.No_Peserta
  WHERE 
    (dbo.Note_Terbit_Master.Jenis IN (20, 21, 22, 23, 24, 25, 29))
    AND (dbo.Note_Terbit_Master.Status<>'C') 
    AND (dbo.Note_Terbit_Master.No_Proposal = @p_no_proposal)
    AND pst.Grade >= 10
    AND pst.Grade <= 22
    AND kp.Sub_Kelompok = '0302'  -- RI

  SELECT
    @p_nama_perusahaan, @p_no_polis, @p_no_proposal, 
  	@jml_karyawan_1,
    @jml_karyawan_2,
    @jml_peserta_1,
    @jml_peserta_2,
    @premi_AJB_1,
    @premi_AJB_2,
    @premi_RI_1,
    @premi_RI_2,
    @premi_RJ_1,
    @premi_RJ_2,
    '',
    @klaim_diajukan_AJB_1,
    @klaim_diajukan_AJB_2,
    @klaim_diajukan_RI_1,
    @klaim_diajukan_RI_2,
    @klaim_diajukan_RJ_1,
    @klaim_diajukan_RJ_2,
    @klaim_dibayar_AJB_1,
    @klaim_dibayar_AJB_2,
    @klaim_dibayar_RI_1,
    @klaim_dibayar_RI_2,
    @klaim_dibayar_RJ_1,
    @klaim_dibayar_RJ_2
  
  FETCH NEXT FROM list_cursor INTO @p_no_polis, @p_no_proposal, @p_nama_perusahaan
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

--0100  AJB
--0301  Rawat Jalan
--0302  Rawat Inap

