--SELECT
--	pa.No_Polis,
--	pa.No_Proposal,
--	prs.Nama_Perusahaan,
--	ntm.No_Ref,
--	ntm.Jenis
--FROM
--	dbo.Polis_Asuransi AS pa INNER JOIN
--	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
--	dbo.Note_Terbit_Master AS ntm ON pa.No_Proposal = ntm.No_Proposal
--WHERE
--  pa.No_Polis IN ('2002.0049.1',
--                  '2002.0072.1',
--                  '2006.0172.1',
--                  '2007.0131.1',
--                  '2008.0147.1',
--                  '2009.0149.1',
--                  '2011.0146.1',
--                  '2011.0175.1',
--                  '2012.0155.1',
--                  '2012.0189.1',
--                  '2013.0115.1',
--                  '2013.0154.1',
--                  '2014.0116.1',
--                  '2014.0119.1',
--                  '2015.0099.1',
--                  '2006.0036.1',
--                  '2008.0073.1',
--                  '2009.0128.1',
--                  '2014.0077.1',
--                  '1993.0003.1',
--                  '2008.0199.1',
--                  '2013.0185.1',
--                  '2016.0070.1',
--                  '2001.0048.1',
--                  '2006.0124.1',
--                  '2009.0151.1',
--                  '2012.0119.1',
--                  '2013.0111.1',
--                  '2013.0124.1',
--                  '2013.0133.1',
--                  '2014.0080.1',
--                  '2014.0100.1',
--                  '2015.0073.1',
--                  '1998.0038.1',
--                  '2007.0117.1',
--                  '2011.0126.1',
--                  '2011.0135.1',
--                  '2014.0107.1',
--                  '2015.0078.1',
--                  '2015.0079.1',
--                  '2016.0044.1',
--                  '2016.0051.1',
--                  '2016.0063.1',
--                  '2005.0246.1',
--                  '2011.0145.1',
--                  '2012.0102.1',
--                  '2013.0137.1',
--                  '2015.0088.1',
--                  '2015.0098.1',
--                  '2016.0072.1',
--                  '2016.0073.1',
--                  '1997.0057.1',
--                  '1998.0059.1',
--                  '2005.0158.1',
--                  '2007.0087.1',
--                  '2010.0154.1',
--                  '2013.0107.1',
--                  '2015.0062.1',
--                  '2015.0063.1',
--                  '2015.0066.1',
--                  '2016.0046.1',
--                  '2011.0082.1',
--                  '2011.0115.1',
--                  '2012.0073.1',
--                  '2012.0092.1',
--                  '2012.0101.1',
--                  '2013.0113.1',
--                  '2013.0128.1',
--                  '2014.0112.1',
--                  '2015.0077.1',
--                  '1998.0019.1',
--                  '1998.0028.1',
--                  '1998.0032.1',
--                  '2003.0062.1',
--                  '2005.0086.1',
--                  '2009.0086.1',
--                  '2012.0075.1',
--                  '2013.0083.1',
--                  '2014.0076.1',
--                  '2015.0044.1',
--                  '2003.0008.1',
--                  '2005.0135.1',
--                  '2006.0099.0',
--                  '2007.0033.1',
--                  '2007.0083.1',
--                  '2009.0102.1',
--                  '2010.0120.1',
--                  '2010.0130.1',
--                  '2011.0085.1',
--                  '2013.0092.1',
--                  '1995.0020.1',
--                  '2003.0066.1',
--                  '2006.0098.1',
--                  '2011.0109.1',
--                  '1996.0073.1',
--                  '1997.0082.1',
--                  '2005.0104.1',
--                  '2010.0112.1',
--                  '2011.0038.1',
--                  '2012.0065.1',
--                  '2013.0053.1',
--                  '2014.0041.1',
--                  '2014.0042.1',
--                  '2014.0054.1',
--                  '2015.0042.1',
--                  '2015.0049.1',
--                  '2001.0034.1',
--                  '2012.0074.1',
--                  '2013.0068.1',
--                  '2015.0033.1',
--                  '2015.0034.1',
--                  '2015.0037.1',
--                  '2015.0038.1',
--                  '2015.0040.1',
--                  '2016.0019.1',
--                  '2016.0020.1',
--                  '2013.0087.1',
--                  '2015.0158.1',
--                  '2016.0111.1',
--                  '2002.0048.1',
--                  '2003.0009.1',
--                  '2004.0036.1',
--                  '2005.0043.1',
--                  '2005.0044.1',
--                  '2011.0032.1',
--                  '2011.0088.1',
--                  '2012.0032.1',
--                  '2012.0036.1',
--                  '2012.0050.1')
--  AND pa.Status = 'I'
--  AND ntm.Status = 'I'
--  AND ntm.Jenis IN (1, 2, 3, 4, 6, 9, 11, 12, 19)
--ORDER BY
--  pa.No_Polis

------------------------------------------------------------------

DECLARE
  @generate_no_polis VARCHAR(11),
  @generate_no_proposal VARCHAR(9),
  @generate_nama_perusahaan VARCHAR(50),
  @generate_tgl_buat DATETIME

DECLARE list_cursor CURSOR FAST_FORWARD FOR 

SELECT
	a.No_Polis,
	a.No_Proposal,
	a.Nama_Perusahaan,
	ntm.Tgl_Buat
FROM
	(
    SELECT
      x.No_Polis,
      x.No_Proposal,
      x.Nama_Perusahaan,
	    COUNT(x.No_Proposal) AS jml
    FROM
	    (
        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 INNER JOIN
	        dbo.Note_Terbit_Master AS ntm ON pa.No_Proposal = ntm.No_Proposal
        WHERE
          pa.No_Polis IN ('2002.0049.1',
                          '2002.0072.1',
                          '2006.0172.1',
                          '2007.0131.1',
                          '2008.0147.1',
                          '2009.0149.1',
                          '2011.0146.1',
                          '2011.0175.1',
                          '2012.0155.1',
                          '2012.0189.1',
                          '2013.0115.1',
                          '2013.0154.1',
                          '2014.0116.1',
                          '2014.0119.1',
                          '2015.0099.1',
                          '2006.0036.1',
                          '2008.0073.1',
                          '2009.0128.1',
                          '2014.0077.1',
                          '1993.0003.1',
                          '2008.0199.1',
                          '2013.0185.1',
                          '2016.0070.1',
                          '2001.0048.1',
                          '2006.0124.1',
                          '2009.0151.1',
                          '2012.0119.1',
                          '2013.0111.1',
                          '2013.0124.1',
                          '2013.0133.1',
                          '2014.0080.1',
                          '2014.0100.1',
                          '2015.0073.1',
                          '1998.0038.1',
                          '2007.0117.1',
                          '2011.0126.1',
                          '2011.0135.1',
                          '2014.0107.1',
                          '2015.0078.1',
                          '2015.0079.1',
                          '2016.0044.1',
                          '2016.0051.1',
                          '2016.0063.1',
                          '2005.0246.1',
                          '2011.0145.1',
                          '2012.0102.1',
                          '2013.0137.1',
                          '2015.0088.1',
                          '2015.0098.1',
                          '2016.0072.1',
                          '2016.0073.1',
                          '1997.0057.1',
                          '1998.0059.1',
                          '2005.0158.1',
                          '2007.0087.1',
                          '2010.0154.1',
                          '2013.0107.1',
                          '2015.0062.1',
                          '2015.0063.1',
                          '2015.0066.1',
                          '2016.0046.1',
                          '2011.0082.1',
                          '2011.0115.1',
                          '2012.0073.1',
                          '2012.0092.1',
                          '2012.0101.1',
                          '2013.0113.1',
                          '2013.0128.1',
                          '2014.0112.1',
                          '2015.0077.1',
                          '1998.0019.1',
                          '1998.0028.1',
                          '1998.0032.1',
                          '2003.0062.1',
                          '2005.0086.1',
                          '2009.0086.1',
                          '2012.0075.1',
                          '2013.0083.1',
                          '2014.0076.1',
                          '2015.0044.1',
                          '2003.0008.1',
                          '2005.0135.1',
                          '2006.0099.0',
                          '2007.0033.1',
                          '2007.0083.1',
                          '2009.0102.1',
                          '2010.0120.1',
                          '2010.0130.1',
                          '2011.0085.1',
                          '2013.0092.1',
                          '1995.0020.1',
                          '2003.0066.1',
                          '2006.0098.1',
                          '2011.0109.1',
                          '1996.0073.1',
                          '1997.0082.1',
                          '2005.0104.1',
                          '2010.0112.1',
                          '2011.0038.1',
                          '2012.0065.1',
                          '2013.0053.1',
                          '2014.0041.1',
                          '2014.0042.1',
                          '2014.0054.1',
                          '2015.0042.1',
                          '2015.0049.1',
                          '2001.0034.1',
                          '2012.0074.1',
                          '2013.0068.1',
                          '2015.0033.1',
                          '2015.0034.1',
                          '2015.0037.1',
                          '2015.0038.1',
                          '2015.0040.1',
                          '2016.0019.1',
                          '2016.0020.1',
                          '2013.0087.1',
                          '2015.0158.1',
                          '2016.0111.1',
                          '2002.0048.1',
                          '2003.0009.1',
                          '2004.0036.1',
                          '2005.0043.1',
                          '2005.0044.1',
                          '2011.0032.1',
                          '2011.0088.1',
                          '2012.0032.1',
                          '2012.0036.1',
                          '2012.0050.1')
          AND pa.Status = 'I'
          AND ntm.Status = 'I'
          AND ntm.Jenis IN (1, 2, 3, 4, 6, 9, 11, 12, 19)
      ) AS x
    GROUP BY
      x.No_Polis,
      x.No_Proposal,
      x.Nama_Perusahaan
  ) AS a INNER JOIN
	dbo.Note_Terbit_Master AS ntm ON a.No_Proposal = ntm.No_Proposal
WHERE
  a.jml=1
  AND ntm.Jenis IN (1, 2, 3, 4, 6, 9, 11, 12, 19)
  AND ntm.Status = 'I'
ORDER BY
  a.No_Proposal

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @generate_no_polis, @generate_no_proposal, @generate_nama_perusahaan, @generate_tgl_buat

WHILE @@FETCH_STATUS = 0  
BEGIN

  DECLARE
        @pi_tgl_proses DATETIME = '2016-09-23',
        @pi_tgl_buat_awal DATETIME = @generate_tgl_buat,
        @pi_tgl_buat_akhir DATETIME = @generate_tgl_buat,
        @pi_id_user VARCHAR(5) = 'SUP',
        @pi_no_proposal VARCHAR(9) = @generate_no_proposal,
        @pi_tipe5 VARCHAR(30) = 'NORMAL' 
  --AS 
  IF UPPER(COALESCE(@pi_tipe5, '')) = 'NORMAL' 
     BEGIN
           SET @pi_tipe5 = ''
    
           SET NOCOUNT ON
           DECLARE @gk_Kartu BIT
           DECLARE @No_Proposal_Old VARCHAR(9) 
           DECLARE @No_Proposal VARCHAR(9) 
           DECLARE @No_Peserta VARCHAR(12) 
           DECLARE @Foto VARCHAR(50) 
           DECLARE @MD_001 VARCHAR(1) 
           DECLARE @Jenis INT
           DECLARE @Sub_Init VARCHAR(1)
           DECLARE @Msg VARCHAR(100)
    
    -- Proses Enrollment I (Baru)
           DECLARE CreateEnrollment_Cursor CURSOR
           FOR
           SELECT
            dbo.Peserta_Produk_Detail.No_Proposal,
            dbo.Peserta_Produk_Detail.No_Peserta,
            dbo.Note_Terbit_Master.Jenis
           FROM
            dbo.Peserta_Produk_Detail
            INNER JOIN dbo.Note_Terbit_Master ON dbo.Peserta_Produk_Detail.No_Ref = dbo.Note_Terbit_Master.No_Ref
                                                 AND dbo.Peserta_Produk_Detail.No_Proposal = dbo.Note_Terbit_Master.No_Proposal
            INNER JOIN dbo.Polis_Asuransi ON dbo.Polis_Asuransi.No_Proposal = dbo.Note_Terbit_Master.No_Proposal
            INNER JOIN dbo.Peserta ON dbo.Polis_Asuransi.No_Proposal = dbo.Peserta.No_Proposal
            INNER JOIN produk_polis ON Peserta_Produk_Detail.No_Proposal = produk_polis.No_Proposal
                                       AND Peserta_Produk_Detail.id_produk = produk_polis.id_produk
                                       --AND Produk_Polis.AdMedika_Ya = 1
           WHERE
            (dbo.Note_Terbit_Master.No_Proposal = @pi_no_proposal
            OR ISNULL(@pi_no_proposal, '') = '')
            AND (dbo.Note_Terbit_Master.Jenis IN (1, 2, 3, 4, 6, 9, 11, 12, 19)
            OR (dbo.Note_Terbit_Master.Jenis BETWEEN 60 AND 69))
            AND (dbo.Note_Terbit_Master.Status = 'I')
            --AND (ISNULL(dbo.Polis_Asuransi.AdMedika_Ya, 0) = 1)
            AND (dbo.Polis_Asuransi.Status = 'I')
            AND (LEFT(dbo.Peserta_Produk_Detail.Sub_Kelompok, 2) = '03')
            AND (dbo.Note_Terbit_Master.Tgl_Buat >= @pi_tgl_buat_awal)
            AND (dbo.Note_Terbit_Master.Tgl_Buat <= @pi_tgl_buat_akhir)
           GROUP BY
            dbo.Note_Terbit_Master.Tgl_Buat,
            dbo.Peserta_Produk_Detail.No_Proposal,
            dbo.Peserta_Produk_Detail.No_Peserta,
            dbo.Note_Terbit_Master.Jenis
           ORDER BY
            dbo.Note_Terbit_Master.Tgl_Buat

           OPEN CreateEnrollment_Cursor
           FETCH NEXT FROM CreateEnrollment_Cursor INTO @No_Proposal, @No_Peserta, @Jenis
           WHILE @@FETCH_STATUS = 0 
                 BEGIN
                       EXEC proc_add_enrollment_peserta_admedika_test_prib @pi_tgl_proses, @No_Proposal, @No_Peserta, 1, '', @pi_tipe5, @pi_id_user, @Msg OUTPUT
                       FETCH NEXT FROM CreateEnrollment_Cursor  INTO @No_Proposal, @No_Peserta, @Jenis
                 END
           CLOSE CreateEnrollment_Cursor
           DEALLOCATE CreateEnrollment_Cursor

    -- Proses Enrollment I (Lapse)
           DECLARE CreateEnrollment_Cursor CURSOR
           FOR
           SELECT
            dbo.Peserta_Produk_Detail.No_Proposal,
            dbo.Peserta_Produk_Detail.No_Peserta,
            dbo.Note_Terbit_Master.Jenis
           FROM
            dbo.Peserta_Produk_Detail
            INNER JOIN dbo.Note_Terbit_Master ON dbo.Peserta_Produk_Detail.No_Ref = dbo.Note_Terbit_Master.No_Ref
                                                 AND dbo.Peserta_Produk_Detail.No_Proposal = dbo.Note_Terbit_Master.No_Proposal
            INNER JOIN dbo.Polis_Asuransi ON dbo.Polis_Asuransi.No_Proposal = dbo.Note_Terbit_Master.No_Proposal
            INNER JOIN dbo.Peserta ON dbo.Polis_Asuransi.No_Proposal = dbo.Peserta.No_Proposal
            INNER JOIN produk_polis ON Peserta_Produk_Detail.No_Proposal = produk_polis.No_Proposal
                                       AND Peserta_Produk_Detail.id_produk = produk_polis.id_produk
                                       --AND Produk_Polis.AdMedika_Ya = 1
           WHERE
            (dbo.Note_Terbit_Master.No_Proposal = @pi_no_proposal
            OR ISNULL(@pi_no_proposal, '') = '')
            AND (dbo.Note_Terbit_Master.Jenis IN (1, 2, 3, 4, 6, 9, 11, 12, 19, 60, 61, 62, 63))
            AND (dbo.Note_Terbit_Master.Status = 'C')
            --AND (ISNULL(dbo.Polis_Asuransi.AdMedika_Ya, 0) = 1)
            AND (dbo.Polis_Asuransi.Status = 'I')
            AND (LEFT(dbo.Peserta_Produk_Detail.Sub_Kelompok, 2) = '03')
            AND (dbo.Note_Terbit_Master.Tgl_Batal >= @pi_tgl_buat_awal)
            AND (dbo.Note_Terbit_Master.Tgl_Batal <= @pi_tgl_buat_akhir)
           GROUP BY
            dbo.Peserta_Produk_Detail.No_Proposal,
            dbo.Peserta_Produk_Detail.No_Peserta,
            dbo.Note_Terbit_Master.Jenis
           OPEN CreateEnrollment_Cursor
           FETCH NEXT FROM CreateEnrollment_Cursor INTO @No_Proposal, @No_Peserta, @Jenis
           WHILE @@FETCH_STATUS = 0 
                 BEGIN
                       EXEC proc_add_enrollment_peserta_admedika_test_prib @pi_tgl_proses, @No_Proposal, @No_Peserta, 1, '', @pi_tipe5, @pi_id_user, @Msg OUTPUT
                       FETCH NEXT FROM CreateEnrollment_Cursor  INTO @No_Proposal, @No_Peserta, @Jenis
                 END
           CLOSE CreateEnrollment_Cursor
           DEALLOCATE CreateEnrollment_Cursor

  -- Proses Enrollment I (Lapse 2)
           DECLARE CreateEnrollment_Cursor CURSOR
           FOR
           SELECT
            dbo.Peserta.No_Proposal,
            dbo.Peserta.No_Peserta,
            3
           FROM
            dbo.Peserta
            INNER JOIN dbo.Polis_Asuransi ON dbo.Polis_Asuransi.No_Proposal = dbo.Peserta.No_Proposal
           WHERE
            (dbo.Polis_Asuransi.No_Proposal = @pi_no_proposal
            OR ISNULL(@pi_no_proposal, '') = '')
            --AND (ISNULL(dbo.Polis_Asuransi.AdMedika_Ya, 0) = 1)
            AND (dbo.Polis_Asuransi.Status = 'I')
            AND (dbo.Peserta.Status <> 'I')
            AND (dbo.Peserta.Tgl_Lapse >= @pi_tgl_buat_awal)
            AND (dbo.Peserta.Tgl_Lapse <= @pi_tgl_buat_akhir)
           GROUP BY
            dbo.Peserta.No_Proposal,
            dbo.Peserta.No_Peserta 
           OPEN CreateEnrollment_Cursor
           FETCH NEXT FROM CreateEnrollment_Cursor INTO @No_Proposal, @No_Peserta, @Jenis
           WHILE @@FETCH_STATUS = 0 
                 BEGIN
                       EXEC proc_add_enrollment_peserta_admedika_test_prib @pi_tgl_proses, @No_Proposal, @No_Peserta, 1, '', @pi_tipe5, @pi_id_user, @Msg OUTPUT
                       FETCH NEXT FROM CreateEnrollment_Cursor  INTO @No_Proposal, @No_Peserta, @Jenis
                 END
           CLOSE CreateEnrollment_Cursor
           DEALLOCATE CreateEnrollment_Cursor
     END

  PRINT @generate_no_proposal
  
  FETCH NEXT FROM list_cursor INTO @generate_no_polis, @generate_no_proposal, @generate_nama_perusahaan, @generate_tgl_buat  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 