SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
-- DELETE FROM enrollment WHERE no_proposal='2007.0776' and md_001='5'
-- SELECT no_peserta, md_001, md_045 FROM enrollment WHERE no_proposal='2007.0776' and md_001='5'
-- EXEC proc_CreateEnrollment '12/26/2007', '12/12/2007', '12/12/2007', 'SYS', '2007.0776'
--select top 100 * from Enrollment




-- last modified by Pribadi Pamungkas. 2016-02-18
--CREATE PROCEDURE [dbo].[proc_create_enrollment_peserta_admedika]
DECLARE
      @pi_tgl_proses DATETIME = '2016-08-25',
      @pi_tgl_buat_awal DATETIME = '2016-08-24',
      @pi_tgl_buat_akhir DATETIME = '2016-08-24',
      @pi_id_user VARCHAR(5) = 'SUP',
      @pi_no_proposal VARCHAR(9) = '2016.0312',
      @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

GO