USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_add_enrollment_peserta_admedika]    Script Date: 08/05/2015 18:11:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- last modified by Pribadi Pamungkas, 2015-08-05 (tambah MD_008 dan MD_009, MD_027, MD_031, ubah MD_004)
ALTER Procedure [dbo].[proc_add_enrollment_peserta_admedika]
@pi_tgl_proses Datetime,
@pi_no_proposal varchar(9),
@pi_no_peserta varchar(12),
@pi_jenis smallint,
@pi_mode varchar(2),
@pi_tipe5 varchar(2),
@pi_id_user varchar(4),
@po_message varchar(50) OUTPUT
AS
  DECLARE @Status varchar(1)
  SET @po_message=''	
  IF isnull(@pi_no_proposal,'')='' OR isnull(@pi_no_peserta,'')='' 
  BEGIN
       SET @po_message='No Proposal dan atau No Peserta kosong' 
       IF @pi_jenis IN (3,62) RETURN 1
       ELSE RETURN 0
  END
  SET NOCOUNT ON
  DECLARE @Mode_Update smallint
  DECLARE @No_Proposal_Last varchar(9)
  DECLARE @No_Polis varchar(11)
  DECLARE @Kelamin varchar(1)
  DECLARE @Kelas_RI varchar(10)
  DECLARE @Sebagai int
  DECLARE @Sub_Init varchar(1)
  DECLARE @No_Prs varchar(9)
  DECLARE @Foto_File varchar(50)
  DECLARE @Foto_Ada bit
  DECLARE @Plan varchar(20)
  DECLARE @Tot_Produk varchar(50)
  DECLARE @Produk varchar(5)
  DECLARE @No_Santunan varchar(12)
  DECLARE @Cabang varchar(30) 
  DECLARE @Kawin varchar(5) 
  DECLARE @NIK varchar(20) 
  DECLARE @Create_Date Datetime
  DECLARE @MaxTgl Datetime
  DECLARE @Nama_Old varchar(30)
  DECLARE @Kelas_Old varchar(10)
  DECLARE @Nik_Old varchar(30)
  DECLARE @Cabang_Old varchar(30)
  DECLARE @Kawin_Old varchar(30)
  DECLARE @Cabang_New varchar(30)
  DECLARE @Nama varchar(30)
  DECLARE @Id_Payor varchar(2)
--DECLARE @Nama_Payor varchar(20)
  DECLARE @Nik_Ya Bit
  DECLARE @Foto_Ya Bit
  DECLARE @Unit_Ya Bit
  DECLARE @Kawin_Ya Bit

  DECLARE @MD_001_Last varchar(2)
  DECLARE @MD_002_Last varchar(1)
  DECLARE @MD_003_Last varchar(7)
  DECLARE @MD_004_Last varchar(12)
  DECLARE @MD_005_Last varchar(11)
  DECLARE @MD_007_Last varchar(8)
  DECLARE @MD_015_Last varchar(9)
  DECLARE @MD_016_Last varchar(1)
  DECLARE @MD_017_Last varchar(1)
  DECLARE @MD_018_Last varchar(8)
  DECLARE @MD_019_Last varchar(8)
  DECLARE @MD_026_Last varchar(30)
  DECLARE @MD_042_Last varchar(8)
  DECLARE @MD_043_Last varchar(1)
  DECLARE @MD_045_Last varchar(255)
  DECLARE @MD_050_Last varchar(8)
  DECLARE @MD_056_Last varchar(8)
  DECLARE @MD_057_Last varchar(9)
  DECLARE @MD_058_Last varchar(1)
  DECLARE @MD_059_Last varchar(1)
  DECLARE @MD_064_Last varchar(255)

--sp_columns 'enrollment'  
  DECLARE @MD_001	varchar(2)
  DECLARE @MD_002	varchar(1)
  DECLARE @MD_003	varchar(7)
  DECLARE @MD_004	varchar(12)
  DECLARE @MD_005	varchar(11)
  DECLARE @MD_007	varchar(8)
  DECLARE @MD_008	varchar(30)  -- add by Pribadi Pamungkas, 2015-07-29. Diisi NIK, request by email tgl 2015-07-29 oleh pak Elfrido dgn subject "ENROLLMENT ADMEDIKA FIELD CABANG DAN NIK"
  DECLARE @MD_009	varchar(30)  -- add by Pribadi Pamungkas, 2015-07-29. Diisi Nama Cabang, request by email tgl 2015-07-29 oleh pak Elfrido dgn subject "ENROLLMENT ADMEDIKA FIELD CABANG DAN NIK"
  DECLARE @MD_015	varchar(9)
  DECLARE @MD_016	varchar(1)
  DECLARE @MD_017	varchar(1)
  DECLARE @MD_018	varchar(8)
  DECLARE @MD_019	varchar(8)
  DECLARE @MD_026	varchar(30)
  DECLARE @MD_027	varchar(30) -- add by Pribadi Pamungkas, 2015-08-04. Diisi NIK, request by email tgl 2015-08-03 oleh pak Elfrido dgn subject "DATA ENROLLMENT FIELD 27  (KELAS)"
  DECLARE @MD_031	varchar(30) -- add by Pribadi Pamungkas, 2015-08-05. Tambah masa/periode asuransi, request by email tgl 2015-08-05 oleh pak Elfrido dgn subject "Re: FIELD 31 TAMPIL MASA / PERIODE ASURANSI"
  DECLARE @MD_042	varchar(8)
  DECLARE @MD_043	varchar(1)
  DECLARE @MD_045	varchar(255)
  DECLARE @MD_050 varchar(8)
  DECLARE @MD_056	varchar(8)
  DECLARE @MD_057	varchar(9)
  DECLARE @MD_058	varchar(1)
  DECLARE @MD_059	varchar(1)
  DECLARE @MD_060	varchar(8)
  DECLARE @MD_064 varchar(255)

  DECLARE @Tgl_Awal	Datetime
  DECLARE @Tgl_Akhir	Datetime
  DECLARE @Tgl_Mulai Datetime
  DECLARE @Tgl_Selesai Datetime
  DECLARE @Enrollment_Value int
  DECLARE @Card_Value varchar(4000)

  DECLARE @Card_Value_Last varchar(4000)
  DECLARE @Tgl_Proses_Last datetime
  DECLARE @Tgl_Selesai_Last datetime
  DECLARE @pi_jenis_Last int
  DECLARE @Cetak_Ya Bit
  DECLARE @AdMedika_Ya bit
  DECLARE @Lokasi_Cetak Smallint
  DECLARE @Status_Peserta varchar(1)
  SET @Create_Date=dbo.func_FixedDate(GetDate())

  --Get Variable Polis
  SET @Nik_Ya=0
  SET @Unit_Ya=0
  SET @Lokasi_Cetak=0
  SET @AdMedika_Ya=0
  IF EXISTS(SELECT No_Proposal FROM Polis_Asuransi WHERE No_Proposal=@pi_no_proposal)
  BEGIN
      SELECT @AdMedika_Ya=AdMedika_Ya, @No_Polis=No_Polis, @Id_Payor=Id_Payor, @Sub_Init=Sub_Init, @No_Prs=No_Prs FROM Polis_Asuransi WHERE No_Proposal=@pi_no_proposal
      SELECT @MD_003=Nama_Payor FROM Payor_Header WHERE Id_Payor=@Id_Payor
      IF isnull(@MD_003,'')='' SET @MD_003='EQUITY'
      SELECT @Lokasi_Cetak=isnull(Lokasi_Cetak,0), @Nik_Ya=isnull(Nik_Ya,0), @Unit_Ya=isnull(Unit_Ya,0), @Foto_Ya=isnull(Foto_Ya,0) FROM  Polis_Kartu WHERE No_Polis=@No_Polis
  END
  ELSE RETURN 1
  --GET NEW VALUE
  SELECT @Status=Peserta.Status, @Nama=Peserta.Card_Name, @Kelamin=Peserta.Kelamin, @Sebagai=Peserta.Sebagai,  @Cabang=isnull(Cabang.Nama,''), @Kawin=Status_Kawin, @NIK=isnull(Peserta.NIK,'')FROM Peserta LEFT OUTER JOIN Cabang ON Peserta.No_Prs=Cabang.No_Prs AND Peserta.Cabang=Cabang.Kode WHERE (No_Proposal=@pi_no_proposal) AND (No_Peserta=@pi_no_peserta) 
  
  SET @MD_008 = @NIK  -- add by Pribadi Pamungkas, 2015-07-29. Diisi NIK, request by email tgl 2015-07-29 oleh pak Elfrido dgn subject "ENROLLMENT ADMEDIKA FIELD CABANG DAN NIK"
  SELECT
    @MD_009 = c.Nama  -- add by Pribadi Pamungkas, 2015-07-29. Diisi NIK, request by email tgl 2015-07-29 oleh pak Elfrido dgn subject "ENROLLMENT ADMEDIKA FIELD CABANG DAN NIK"
  FROM
	  dbo.Peserta AS p INNER JOIN
	  dbo.Cabang AS c ON p.Cabang = c.Kode
  WHERE
    c.No_Prs = @No_Prs
    AND p.No_Peserta = @pi_no_peserta
    AND p.No_Proposal = @pi_no_proposal
  
  SELECT @Kelas_RI=Kelas FROM Peserta_Produk WHERE (No_Proposal=@pi_no_proposal) AND (No_Peserta=@pi_no_peserta) AND (Sub_Kelompok='0302') 
  
  -- start: kolom 27 --
  -- add by Pribadi Pamungkas, 2015-08-04. Diisi NIK, request by email tgl 2015-08-03 oleh pak Elfrido dgn subject "DATA ENROLLMENT FIELD 27  (KELAS)"
  DECLARE @inisial_produk VARCHAR(100)
  SET @inisial_produk = ''
  SELECT
	  @inisial_produk = @inisial_produk + '-' + sk.Field_Name
  FROM
	  dbo.Peserta_Produk AS pp INNER JOIN
	  dbo.Sub_Kelompok AS sk ON pp.Sub_Kelompok = sk.Sub_Kelompok
  WHERE
    pp.No_Proposal = @pi_no_proposal
    AND pp.No_Peserta = @pi_no_peserta
    AND pp.Sub_Kelompok IN ('0301', '0302', '0303', '0304', '0305', '0306', '0307', '0311')
  SET @inisial_produk = RIGHT(@inisial_produk, LEN(@inisial_produk) - 1)
  --SELECT @inisial_produk
  SET @MD_027 = @Kelas_RI + '/' + @inisial_produk
  -- end: kolom 27 --

--KHUSUS CMNP GROUP
IF @pi_no_proposal IN ('2006.0671','2007.0776', '2007.0837', '2007.0836', '2008.0913', '2009.0933', '2009.0936',
'2011.0851', '2011.0845') --Proposal='2006.0671'
BEGIN
  DECLARE AddEnrollment_Cursor CURSOR FOR
  SELECT LEFT(Id_Produk,4)+RIGHT(Id_Produk,5)+Kelas, Sub_Kelompok.Field_Name FROM Peserta_Produk INNER JOIN Sub_Kelompok ON Peserta_Produk.Sub_Kelompok = Sub_Kelompok.Sub_Kelompok
  WHERE (No_Proposal=@pi_no_proposal) AND (No_Peserta=@pi_no_peserta) AND 
        (LEFT(dbo.Peserta_Produk.Sub_Kelompok,2)='03') AND 
        (dbo.Peserta_Produk.Status<>'C' AND  @Status<>'O')
         ORDER BY Sub_Kelompok.Urutan   
  OPEN AddEnrollment_Cursor
  FETCH NEXT FROM AddEnrollment_Cursor INTO @Plan, @Produk
  SET @MD_045 =''
  SET @Tot_Produk =''
  WHILE @@FETCH_STATUS = 0
  BEGIN
     IF @MD_045='' SET @MD_045= @MD_045 + @Plan
     ELSE SET @MD_045= @MD_045 + ', '+@Plan
     IF @Tot_Produk='' SET @Tot_Produk= @Tot_Produk + isnull(@Produk,'')
     ELSE SET @Tot_Produk= @Tot_Produk + '-'+isnull(@Produk,'')
     FETCH NEXT FROM AddEnrollment_Cursor  INTO @Plan, @Produk
  END
  CLOSE AddEnrollment_Cursor
  DEALLOCATE AddEnrollment_Cursor
END
ELSE
--BILA LAHIR PRIA DAN GT MAKA PRODUK DILETAKKAN DI WANITA
IF RIGHT(@No_Polis,1)='0'
BEGIN
  DECLARE AddEnrollment_Cursor CURSOR FOR
  SELECT LEFT(Id_Produk,4)+RIGHT(Id_Produk,5)+Kelas, Sub_Kelompok.Field_Name FROM Peserta_Produk INNER JOIN Sub_Kelompok ON Peserta_Produk.Sub_Kelompok = Sub_Kelompok.Sub_Kelompok
  WHERE (No_Proposal=@pi_no_proposal) AND (No_Peserta=@pi_no_peserta) AND 
        (LEFT(dbo.Peserta_Produk.Sub_Kelompok,2)='03') AND 
        (dbo.Peserta_Produk.Sub_Kelompok<>'0304' AND @Kelamin='P' OR @Kelamin='W') AND
        (dbo.Peserta_Produk.Status<>'C' OR @Status='O')
         ORDER BY Sub_Kelompok.Urutan   
  OPEN AddEnrollment_Cursor
  FETCH NEXT FROM AddEnrollment_Cursor INTO @Plan, @Produk
  SET @MD_045 =''
  SET @Tot_Produk =''
  WHILE @@FETCH_STATUS = 0
  BEGIN
     IF @MD_045='' SET @MD_045= @MD_045 + @Plan
     ELSE SET @MD_045= @MD_045 + ', '+@Plan
     IF @Tot_Produk='' SET @Tot_Produk= @Tot_Produk + isnull(@Produk,'')
     ELSE SET @Tot_Produk= @Tot_Produk + '-'+isnull(@Produk,'')
     FETCH NEXT FROM AddEnrollment_Cursor  INTO @Plan, @Produk
  END
  CLOSE AddEnrollment_Cursor
  DEALLOCATE AddEnrollment_Cursor
END
ELSE
BEGIN
  DECLARE AddEnrollment_Cursor CURSOR FOR
  SELECT LEFT(Id_Produk,4)+RIGHT(Id_Produk,5)+Kelas, Sub_Kelompok.Field_Name FROM Peserta_Produk INNER JOIN Sub_Kelompok ON Peserta_Produk.Sub_Kelompok = Sub_Kelompok.Sub_Kelompok
  WHERE (No_Proposal=@pi_no_proposal) AND (No_Peserta=@pi_no_peserta) AND 
        (LEFT(dbo.Peserta_Produk.Sub_Kelompok,2)='03') AND 
        --(dbo.Peserta_Produk.Sub_Kelompok<>'0304' AND @Kelamin='P' OR @Kelamin='W') AND
        (dbo.Peserta_Produk.Status<>'C' OR @Status='O')
         ORDER BY Sub_Kelompok.Urutan   
  OPEN AddEnrollment_Cursor
  FETCH NEXT FROM AddEnrollment_Cursor INTO @Plan, @Produk
  SET @MD_045 =''
  SET @Tot_Produk =''
  WHILE @@FETCH_STATUS = 0
  BEGIN
     IF @MD_045='' SET @MD_045= @MD_045 + @Plan
     ELSE SET @MD_045= @MD_045 + ', '+@Plan
     IF @Tot_Produk='' SET @Tot_Produk= @Tot_Produk + isnull(@Produk,'')
     ELSE SET @Tot_Produk= @Tot_Produk + '-'+isnull(@Produk,'')
     FETCH NEXT FROM AddEnrollment_Cursor  INTO @Plan, @Produk
  END
  CLOSE AddEnrollment_Cursor
  DEALLOCATE AddEnrollment_Cursor
END

IF (RIGHT(@pi_no_peserta,1)<>'0') AND (NOT @pi_no_proposal IN ('2006.0671','2007.0776', '2007.0837', '2007.0836', '2008.0913', '2009.0004', '2009.0933', '2009.0936',
'2011.0851', '2011.0845')) 
BEGIN
       SET @No_Santunan=LEFT(@pi_no_peserta,11)+'0'
       DECLARE AddEnrollment_Cursor CURSOR FOR
       SELECT    LEFT(dbo.Peserta_Produk.Id_Produk,4)+RIGHT(dbo.Peserta_Produk.Id_Produk,5)+dbo.Peserta_Produk.Kelas, Sub_Kelompok.Field_Name  FROM  dbo.Peserta_Produk INNER JOIN
                 dbo.Produk_Polis ON dbo.Peserta_Produk.Id_Produk = dbo.Produk_Polis.Id_Produk AND 
                 dbo.Peserta_Produk.No_Proposal = dbo.Produk_Polis.No_Proposal
                 INNER JOIN Sub_Kelompok ON Peserta_Produk.Sub_Kelompok = Sub_Kelompok.Sub_Kelompok
       WHERE (dbo.Peserta_Produk.No_Proposal=@pi_no_proposal) AND 
             (dbo.Peserta_Produk.No_Peserta=@No_Santunan) AND 
             (isnull(dbo.Produk_Polis.Keluarga,0)=1) AND 
             (LEFT(dbo.Peserta_Produk.Sub_Kelompok,2)='03') AND 
             (dbo.Peserta_Produk.Status<>'C'  OR @Status='O')
       ORDER BY Sub_Kelompok.Urutan
       OPEN AddEnrollment_Cursor
       FETCH NEXT FROM AddEnrollment_Cursor INTO @Plan, @Produk
       WHILE @@FETCH_STATUS = 0
       BEGIN
         IF CHARINDEX(@Plan,@MD_045)=0
         BEGIN
         IF @MD_045='' SET @MD_045= @MD_045 + @Plan
         ELSE SET @MD_045= @MD_045 + ', '+@Plan
         END 
         IF CHARINDEX(@Produk,@Tot_Produk)=0
         BEGIN
         IF @Tot_Produk='' SET @Tot_Produk= @Tot_Produk + isnull(@Produk,'')
         ELSE SET @Tot_Produk= @Tot_Produk + '-'+ isnull(@Produk,'') 
         END
         FETCH NEXT FROM AddEnrollment_Cursor INTO @Plan, @Produk
       END
       CLOSE AddEnrollment_Cursor
       DEALLOCATE AddEnrollment_Cursor
  END

  IF (@Sebagai=1) AND NOT ( @pi_no_proposal IN ('2006.0671','2007.0776', '2007.0837', '2007.0836', '2011.0851', '2011.0845') ) 
  BEGIN
       IF EXISTS(SELECT * FROM Peserta_Produk  WHERE (No_Proposal=@pi_no_proposal) AND (No_Peserta=@No_Santunan) AND (dbo.Peserta_Produk.Sub_Kelompok='0304') 
          AND (dbo.Peserta_Produk.Status<>'C'  OR @pi_mode='3'))
       BEGIN
          SELECT @Plan=LEFT(Id_Produk,4)+RIGHT(Id_Produk,5)+Kelas, @Produk=Sub_Kelompok.Field_Name FROM Peserta_Produk
          INNER JOIN Sub_Kelompok ON Peserta_Produk.Sub_Kelompok = Sub_Kelompok.Sub_Kelompok
          WHERE (No_Proposal=@pi_no_proposal) AND 
           (No_Peserta=@No_Santunan) AND 
          (dbo.Peserta_Produk.Sub_Kelompok='0304') AND
        (dbo.Peserta_Produk.Status<>'C' OR @pi_mode='3')
          IF @MD_045='' SET @MD_045= @MD_045 + @Plan
          ELSE SET @MD_045= @MD_045 + ', '+@Plan
          IF @Tot_Produk='' SET @Tot_Produk= @Tot_Produk + isnull(@Produk,'')
          ELSE SET @Tot_Produk= @Tot_Produk + '-'+ isnull(@Produk,'')
      END
 END
 SET @MD_045=RTRIM(@MD_045)

 IF CHARINDEX(LEFT(@Kelas_RI,2),'RI-RJ-LH-RG-KM')<>0 AND SUBSTRING(@Kelas_RI,3,1)<>'-'
 SET @Kelas_RI=LEFT(@Kelas_RI,2)+'-'+SUBSTRING(@Kelas_RI,3,10)

 SET @MD_058 = 'I'

 SET @MD_059 = 'N'
 SELECT @Status_Peserta=dbo.Peserta.Status,
        @MD_002= CASE 
                 WHEN RIGHT(dbo.Peserta.No_Peserta,1)='0' THEN  'P'
                 WHEN RIGHT(dbo.Peserta.No_Peserta,1)<>'0' THEN  'D'
                 ELSE ''
                 END,
        --@MD_004=LEFT(dbo.Peserta.No_Peserta,10)+RIGHT(dbo.Peserta.No_Peserta,1),
        @MD_004=LEFT(dbo.Peserta.No_Peserta,10)+'-'+RIGHT(dbo.Peserta.No_Peserta,1),  -- modified by Pribadi Pamungkas, 2015-08-05. Tambah strip, request by email tgl 2015-08-05 oleh pak Elfrido dgn subject "Re: FIELD 31 TAMPIL MASA / PERIODE ASURANSI"
        @MD_005 = CASE 
                  WHEN RIGHT(dbo.Peserta.No_Peserta,1)<>'0' THEN  LEFT(dbo.Peserta.No_Peserta,10)+'0'
                  ELSE ''
                  END,
        @MD_007 = LEFT(dbo.Peserta.No_Prs,4)+RIGHT(dbo.Peserta.No_Prs,4),
        @MD_015 = LEFT(dbo.Polis_Asuransi.No_Polis,4)+SUBSTRING(dbo.Polis_Asuransi.No_Polis,6,4)+RIGHT(dbo.Polis_Asuransi.No_Polis,1),
        @MD_016 = CASE WHEN Status_Kawin='TK' THEN  'S'
                  WHEN LEFT(Status_Kawin,1)='K' THEN  'M'
                  WHEN LEFT(Status_Kawin,1)='W'  THEN  'D'
                  ELSE ''
                  END,
        @MD_017 = CASE 
                  WHEN Sebagai=0 THEN  'D'
                  WHEN Sebagai=1 THEN  'W'
                  WHEN Sebagai=2 AND Kelamin='P' THEN  'S'
                  WHEN Sebagai=2 AND Kelamin='W' THEN  'D'
                  WHEN Sebagai=3 THEN  'H'
                  ELSE ''
                  END,
        @MD_018 = dbo.func_Tanggal(dbo.Peserta.Tgl_Mulai),
        @MD_019 = dbo.func_Tanggal(dbo.Polis_Asuransi.Tgl_Selesai),
        @MD_060 = dbo.func_Tanggal(dbo.Peserta.Tgl_Mulai),
        @Tgl_Selesai =dbo.Polis_Asuransi.Tgl_Selesai,
        @Tgl_Mulai   =dbo.Peserta.Tgl_Mulai,
        @MD_026 = dbo.Peserta.Card_Name,
        @MD_031 = CONVERT(VARCHAR(10), CAST(dbo.Peserta.Tgl_Mulai AS DATE), 103) + ' - ' + CONVERT(VARCHAR(10), CAST(dbo.Polis_Asuransi.Tgl_Selesai AS DATE), 103),  -- add by Pribadi Pamungkas, 2015-08-05. Tambah masa/periode asuransi, request by email tgl 2015-08-05 oleh pak Elfrido dgn subject "Re: FIELD 31 TAMPIL MASA / PERIODE ASURANSI"
        @MD_042 = dbo.func_Tanggal(dbo.Peserta.Tgl_Lahir),
        @MD_043 = CASE 
                  WHEN Kelamin='P' THEN  'M'
                  WHEN Kelamin='W' THEN  'F'
                  ELSE ''
                  END,
        @MD_050 = dbo.func_Tanggal(dbo.Peserta.Tgl_Lapse),
        @MD_056 = dbo.func_Tanggal(dbo.Peserta.Tgl_Masuk),
        @MD_057 = dbo.func_Tanggal(dbo.Polis_Asuransi.Tgl_Selesai),
        @MD_064 = LEFT(dbo.Peserta.No_Peserta,10)+RIGHT(dbo.Peserta.No_Peserta,1)+','+
                  LEFT(dbo.Polis_Asuransi.No_Polis,4)+SUBSTRING(dbo.Polis_Asuransi.No_Polis,6,4)+RIGHT(dbo.Polis_Asuransi.No_Polis,1)+','+
                  LEFT(dbo.Peserta.No_Prs,4)+RIGHT(dbo.Peserta.No_Prs,4),
        @Tgl_Awal = dbo.Polis_Asuransi.Tgl_Mulai,
        @Tgl_Akhir = dbo.Polis_Asuransi.Tgl_Selesai2
  FROM  dbo.Peserta INNER JOIN dbo.Polis_Asuransi ON dbo.Peserta.No_Proposal = dbo.Polis_Asuransi.No_Proposal 
  WHERE (dbo.Polis_Asuransi.No_Proposal=@pi_no_proposal) AND (dbo.Peserta.No_Peserta=@pi_no_peserta)

  SET @Card_Value=@MD_026+ @Kelas_RI+ @Cabang+ @Kawin+ @Produk+ @Nik+dbo.func_Tanggal(@Tgl_Awal)+dbo.func_Tanggal(@Tgl_Akhir)
-- SET @Enrollment_Value=BINARY_CHECKSUM(isnull(@MD_003,'')+ isnull(@MD_004,'')+ isnull(@MD_005,'')+ isnull(@MD_007,'')+ isnull(@MD_015,'')+ isnull(@MD_018,'')+ isnull(@MD_019,'')+ isnull(@MD_026,'')+ isnull(@MD_042,'')+ isnull(@MD_045,'')+ isnull(@MD_056,'')+ isnull(@MD_057,'')+ isnull(@MD_058,'')+ isnull(@MD_059,''))

  --GET LAST VALUE  
  SET @MD_001='-'
  SET @MD_001_Last=@MD_001
  SET @Foto_Ada=0
  SET @Cetak_Ya=0
  SET @Mode_Update=1
  
  SET @Foto_File=LEFT(@No_Prs,4)+RIGHT(@No_Prs,4)+'_'+LEFT(@pi_no_peserta,10)+RIGHT(@pi_no_peserta,1)+'.JPG'

  --Insert Foto Otomatis  
  IF @pi_jenis IN (4) SET @pi_mode='8' 
  IF isnull(@Foto_Ya,0)=1 AND (@Lokasi_Cetak=1)
  BEGIN
    IF (@pi_mode IN ('0','8','15') OR (@pi_jenis=4)) 
    BEGIN
      IF NOT EXISTS(SELECT * FROM Foto WHERE Foto_File=@Foto_File)
      INSERT INTO Foto (Foto_File, Tgl_Proses, Kirim_Ya, Status, Create_Date, Create_By, Change_By, Change_Date)
      VALUES(@Foto_File, @pi_tgl_proses, 1, 'I', @Create_Date, @pi_id_user, @Create_Date, @pi_id_user)
      ELSE UPDATE Foto SET Tgl_Proses=@pi_tgl_proses, Kirim_Ya=1, Change_By=@Create_Date, Change_Date=@pi_id_user WHERE Foto_File=@Foto_File  
      SET @Foto_Ada=1 
    END
    ELSE
    BEGIN
      IF EXISTS(SELECT * FROM Foto WHERE Upper(Foto_File)=Upper(@Foto_File) AND Create_Date<=@Create_Date) 
      SET @Foto_Ada=1 
    END
  END
  --Sudah Ada Enrollment


  IF EXISTS(SELECT Enrollment.No_Peserta FROM Enrollment INNER JOIN Polis_Asuransi ON Enrollment.No_Proposal=Polis_Asuransi.No_Proposal 
  WHERE Polis_Asuransi.No_Polis=@No_Polis AND Enrollment.No_Peserta=@pi_no_peserta)
  BEGIN       
       --Cek Tgl Proses sebelum
       SELECT @Tgl_Proses_Last=Max(Enrollment.Tgl_Proses) FROM Enrollment INNER JOIN Polis_Asuransi ON Enrollment.No_Proposal=Polis_Asuransi.No_Proposal 
       WHERE Polis_Asuransi.No_Polis=@No_Polis AND Enrollment.No_Peserta=@pi_no_peserta
       IF @pi_tgl_proses<=@Tgl_Proses_Last 

       BEGIN
            IF @pi_jenis in (3,62) SET @po_message='Tanggal harus > sebelumnya'
            RETURN 1 --proses batal
       END  
       --Cek Enrollment sebelum
       SELECT @MD_001_Last=MD_001,
@MD_002_Last=MD_002,
@MD_003_Last=MD_003,
@MD_004_Last=MD_004,
@MD_005_Last=MD_005,
@MD_007_Last=MD_007,
@MD_015_Last=MD_015,
@MD_016_Last=MD_016,
@MD_017_Last=MD_017,

@MD_018_Last=MD_018,
@MD_019_Last=MD_019,
@MD_026_Last=MD_026,
@MD_042_Last=MD_042,
@MD_043_Last=MD_043,
@MD_045_Last=MD_045,

@MD_050_Last=MD_050,
@MD_056_Last=MD_056,
@MD_057_Last=MD_057,
@MD_058_Last=MD_058,
@MD_059_Last=MD_059,
@MD_064_Last=MD_064,
@No_Proposal_Last=Enrollment.No_Proposal, 
@Tgl_Proses_Last=Tgl_Proses,
@Tgl_Selesai_Last=Polis_Asuransi.Tgl_Selesai,  
@pi_jenis_Last=Enrollment.Jenis,
@Card_Value_Last=MD_026+Kelas_RI+Cabang+Kawin+Enrollment.Produk+ Nik+dbo.func_Tanggal(Enrollment.Tgl_Awal)+dbo.func_Tanggal(Enrollment.Tgl_Akhir)
--z@Enrollment_Value_Last = BINARY_CHECKSUM(isnull(MD_003,'')+ isnull(MD_004,'')+ isnull(MD_005,'')+ isnull(MD_007,'')+ isnull(MD_015,'')+ isnull(MD_018,'')+ isnull(MD_019,'')+ isnull(MD_026,'')+ isnull(MD_042,'')+ isnull(MD_045,'')+ isnull(MD_056,'')+ isnull(MD_057,'')+ isnull(MD_058,'')+ isnull(MD_059,''))
       FROM Enrollment INNER JOIN Polis_Asuransi ON Enrollment.No_Proposal=Polis_Asuransi.No_Proposal 
       WHERE Enrollment.Tgl_Proses=@Tgl_Proses_Last AND Polis_Asuransi.No_Polis=@No_Polis AND Enrollment.No_Peserta=@pi_no_peserta
       
       IF (@Foto_Ada=1 OR isnull(@Foto_Ya,0)=0) AND (@Card_Value<>@Card_Value_Last) AND (@Lokasi_Cetak=1) SET @Cetak_Ya=1
SET @Enrollment_Value=0
IF @MD_001_Last = @MD_001 OR
@MD_002_Last = @MD_002 OR 
@MD_003_Last = @MD_003 OR 
@MD_004_Last = @MD_004 OR 
@MD_005_Last = @MD_005 OR 
@MD_007_Last = @MD_007 OR 
@MD_015_Last = @MD_015 OR 
@MD_016_Last = @MD_016 OR 
@MD_017_Last = @MD_017 OR 
@MD_018_Last = @MD_018 OR 
@MD_019_Last = @MD_019 OR 
@MD_026_Last = @MD_026 OR 
@MD_042_Last = @MD_042 OR 
@MD_043_Last = @MD_043 OR 
@MD_045_Last = @MD_045 OR 
@MD_056_Last = @MD_056 OR 
@MD_057_Last = @MD_057 OR 
@MD_058_Last = @MD_058 OR 
@MD_059_Last = @MD_059 OR
@MD_064_Last = @MD_064
BEGIN
     IF (@MD_018<>@MD_018_Last) OR (@MD_019<>@MD_019_Last) SET @Enrollment_Value=4
     ELSE IF (@MD_064<>@MD_064_Last) SET @Enrollment_Value=7 
     SET @Enrollment_Value=1
END
       --Enrollment Manual
       IF @pi_mode<>'' 
       BEGIN
            IF @pi_mode='1' 
            BEGIN
               SET @po_message='Mode '+@MD_001_Last+'=> '+@pi_mode+' tidak boleh.'
               SET @Mode_Update=0 
               RETURN 1
            END
            ELSE
            IF (@pi_mode='2N') AND (@MD_059_Last='Y') AND (@Enrollment_Value<>0) -- AND @MD_001_Last='2Y'
            BEGIN
                 SET @MD_059='N'
       		 SET @MD_001=LEFT(@pi_mode,1)
            END
            ELSE
            IF (@pi_mode='2Y') AND (@Enrollment_Value<>0)
            BEGIN
                 SET @MD_059='Y'
       		 SET @MD_001=LEFT(@pi_mode,1)
            END
            ELSE
            IF (@pi_mode='2' AND @MD_001_Last='2') 
            BEGIN
                 
       		 IF @Enrollment_Value=0 SET @Mode_Update=2 
                 SET @MD_001=@pi_mode
            END
            ELSE
            IF (@pi_mode='8' AND @MD_001_Last='8') 
            BEGIN
       		 IF @Card_Value_Last=@Card_Value SET @Mode_Update=2 
                 SET @MD_001=@pi_mode
            END
            ELSE
            BEGIN 
                IF @pi_mode='9' AND @MD_001_Last<>'9'
                BEGIN 
                  IF @MD_001_Last<>'3' SET @MD_001='3' ELSE SET @MD_001=@pi_mode
                END
                ELSE
                IF (@MD_001_Last<>@pi_mode AND CHARINDEX(RIGHT('00'+@pi_mode,2),'02,03,04,05,07,08,00,15,2Y')<>0)
                OR (@MD_001_Last=@pi_mode  AND CHARINDEX(RIGHT('00'+@pi_mode,2),'02,08,015')<>0)
                OR (@MD_001_Last='3' AND @pi_mode='9') 
OR (@MD_001_Last='2Y' AND @pi_mode='2N') SET @MD_001=@pi_mode            
                ELSE
                BEGIN
                   SET @po_message='Mode '+@MD_001_Last+'=> '+@pi_mode+' tidak boleh.'
                   RETURN 1
                END
            END
            IF (@Foto_Ada=1 OR isnull(@Foto_Ya,0)=0) AND (@Card_Value<>@Card_Value_Last) AND (@Lokasi_Cetak=1) SET @Cetak_Ya=1
       END
       ELSE
       BEGIN
         IF @MD_001_Last='3' 
         BEGIN
            IF @Status_Peserta='I' SET @MD_001='9' --ReInforce
            ELSE RETURN 1
         END 
         ELSE
         BEGIN
           IF @Status_Peserta in ('C','O','D') SET @MD_001='3' --Lapse
           ELSE
           IF (@Enrollment_Value<>0)
           BEGIN
                IF @Tgl_Mulai+1>@Tgl_Selesai_Last 
                BEGIN
                     IF (@Cetak_Ya=1) SET @MD_001='6' --Renewal With card ( mode 5,8)
                     ELSE SET @MD_001='5' --Renewal Without card 
                END
                ELSE
                BEGIN
                     IF (@Cetak_Ya=1) SET @MD_001='8' --Perubahan cetak kartu
                     ELSE
                     BEGIN
                          IF (@Enrollment_Value=4) SET @MD_001='4'
                          ELSE IF (@Enrollment_Value=7) SET @MD_001='7'
                          ELSE SET @MD_001='2' --Perubahan tanpa cetak kartu
                     END
               END
            END
            ELSE
            BEGIN
              IF (@Cetak_Ya=1) SET @MD_001='0' --cetak kartu Tanpa Perubahan 
              ELSE RETURN 1
            END
         END
       END
   END
   ELSE 
   BEGIN
        SET @MD_001='1'
        IF (@Foto_Ada=1 OR isnull(@Foto_Ya,0)=0) AND (@Card_Value<>@Card_Value_Last) AND (@Lokasi_Cetak=1) SET @Cetak_Ya=1
   END
   IF @MD_001='-' RETURN 1 --Proses Gagal
   IF @Mode_Update=2
   BEGIN
     UPDATE Enrollment SET Status='G', Jenis=@pi_jenis, Tgl_Proses=@pi_tgl_proses, Cetak_Ya=@Cetak_Ya, Foto_Ada=@Foto_Ada
     WHERE Tgl_Proses=@Tgl_Proses_Last AND No_Proposal=@pi_no_proposal AND No_Peserta=@pi_no_peserta
   END
   ELSE
   BEGIN
     IF @MD_001='6'
     BEGIN
     SET @MD_001='5'
     

     INSERT INTO Enrollment (Tgl_Proses, No_Proposal, No_Peserta, Jenis,
     MD_001, MD_002, MD_003, MD_004, MD_005, MD_006, MD_007, MD_008, MD_009, MD_010, MD_011, MD_012, MD_013, MD_014, MD_015, 
     MD_016, MD_017, MD_018, MD_019, MD_020, MD_021, MD_022, MD_023, MD_024, MD_025, MD_026, MD_027, MD_028, MD_029, MD_030, 
     MD_031, MD_032, MD_033, MD_034, MD_035, MD_036, MD_037, MD_038, MD_039, MD_040, MD_041, MD_042, MD_043, MD_044, MD_045,  
     MD_046, MD_047, MD_048, MD_049, MD_050, MD_051, MD_052, MD_053, MD_054, MD_055, MD_056, MD_057, MD_058, MD_059, MD_060, 
     MD_061, MD_062, MD_063, MD_064,
     Kelas_RI, Cabang, Kawin, Produk, NIK, Foto_Ada, Cetak_Ya,
     Status, Tgl_Awal, Tgl_Akhir, Create_Date, Create_By)
     VALUES(@pi_tgl_proses, @pi_no_proposal, @pi_no_peserta, @pi_jenis,
     @MD_001, @MD_002, @MD_003, @MD_004, @MD_005, '', @MD_007, @MD_008, @MD_009, '', '', '', '', '', @MD_015, 
     @MD_016, @MD_017, @MD_018, @MD_019,  '', '', '', '', '', '', @MD_026, @MD_027, '', '', '', 
     @MD_031, '', '', '', '', '', '', '', '', '', '', @MD_042, @MD_043, '', @MD_045,  
     '', '', '', '', @MD_050, '', '', '', dbo.func_GetMemberLimitCMNP(@pi_no_proposal, @pi_no_peserta, @MD_045), '', @MD_056, @MD_057, @MD_058, @MD_059,  '', 
     '', '', '', @MD_064,   
     @Kelas_RI, @Cabang, @Kawin, dbo.func_GetProdukCMNP(@MD_045, @Tot_Produk), @NIK, @Foto_Ada, @Cetak_Ya,
     'G', @Tgl_Awal, @Tgl_Akhir, @Create_Date, @pi_id_user)
     SET @MD_001='8'
     SET @pi_tgl_proses=DATEADD(day,1,@pi_tgl_proses) --maju satu hari
     INSERT INTO Enrollment (Tgl_Proses, No_Proposal, No_Peserta, Jenis,
     MD_001, MD_002, MD_003, MD_004, MD_005, MD_006, MD_007, MD_008, MD_009, MD_010, MD_011, MD_012, MD_013, MD_014, MD_015, 
     MD_016, MD_017, MD_018, MD_019, MD_020, MD_021, MD_022, MD_023, MD_024, MD_025, MD_026, MD_027, MD_028, MD_029, MD_030, 
     MD_031, MD_032, MD_033, MD_034, MD_035, MD_036, MD_037, MD_038, MD_039, MD_040, MD_041, MD_042, MD_043, MD_044, MD_045,  
     MD_046, MD_047, MD_048, MD_049, MD_050, MD_051, MD_052, MD_053, MD_054, MD_055, MD_056, MD_057, MD_058, MD_059, MD_060, 
     MD_061, MD_062, MD_063, MD_064,
     Kelas_RI, Cabang, Kawin, Produk, NIK, Foto_Ada, Cetak_Ya,
     Status, Tgl_Awal, Tgl_Akhir, Create_Date, Create_By)
     VALUES(@pi_tgl_proses, @pi_no_proposal, @pi_no_peserta, @pi_jenis,
     @MD_001, @MD_002, @MD_003, @MD_004, @MD_005, '', @MD_007, @MD_008, @MD_009, '', '', '', '', '', @MD_015, 
     @MD_016, @MD_017, @MD_018, @MD_019,  '', '', '', '', '', '', @MD_026, @MD_027, '', '', '', 
     @MD_031, '', '', '', '', '', '', '', '', '', '', @MD_042, @MD_043, '', @MD_045,  
     '', '', '', '', @MD_050, '', '', '', '', '', @MD_056, @MD_057, @MD_058, @MD_059,  '', 
     '', '', '', '',   
     @Kelas_RI, @Cabang, @Kawin, dbo.func_GetProdukCMNP(@MD_045, @Tot_Produk), @NIK, @Foto_Ada, @Cetak_Ya,
     'G', @Tgl_Awal, @Tgl_Akhir, @Create_Date, @pi_id_user)

     END
     ELSE    
     BEGIN
     IF (coalesce(@pi_tipe5,'') in ('11','12','13') and @MD_001='5') SET @MD_001=@pi_tipe5
     
     INSERT INTO Enrollment (Tgl_Proses, No_Proposal, No_Peserta, Jenis,
     MD_001, MD_002, MD_003, MD_004, MD_005, MD_006, MD_007, MD_008, MD_009, MD_010, MD_011, MD_012, MD_013, MD_014, MD_015, 
     MD_016, MD_017, MD_018, MD_019, MD_020, MD_021, MD_022, MD_023, MD_024, MD_025, MD_026, MD_027, MD_028, MD_029, MD_030, 
     MD_031, MD_032, MD_033, MD_034, MD_035, MD_036, MD_037, MD_038, MD_039, MD_040, MD_041, MD_042, MD_043, MD_044, MD_045,  
     MD_046, MD_047, MD_048, MD_049, MD_050, MD_051, MD_052, MD_053, MD_054, MD_055, MD_056, MD_057, MD_058, MD_059, MD_060, 
     MD_061, MD_062, MD_063, MD_064,
     Kelas_RI, Cabang, Kawin, Produk, NIK, Foto_Ada, Cetak_Ya,
     Status, Tgl_Awal, Tgl_Akhir, Create_Date, Create_By)
     VALUES(@pi_tgl_proses, @pi_no_proposal, @pi_no_peserta, @pi_jenis,
     @MD_001, @MD_002, @MD_003, @MD_004, @MD_005, '', @MD_007, @MD_008, @MD_009, '', '', '', '', '', @MD_015, 
     @MD_016, @MD_017, @MD_018, @MD_019,  '', '', '', '', '', '', @MD_026, @MD_027, '', '', '', 
     @MD_031, '', '', '', '', '', '', '', '', '', '', @MD_042, @MD_043, '', @MD_045,  
     '', '', '', '', @MD_050, '', '', '', dbo.func_GetMemberLimitCMNP(@pi_no_proposal, @pi_no_peserta, @MD_045), '', @MD_056, @MD_057, @MD_058, @MD_059,  @MD_060, 
     '', '', '', '',   
     @Kelas_RI, @Cabang, @Kawin, dbo.func_GetProdukCMNP(@MD_045, @Tot_Produk), @NIK, @Foto_Ada, @Cetak_Ya,
     'G', @Tgl_Awal, @Tgl_Akhir, @Create_Date, @pi_id_user)
     END
     IF EXISTS(SELECT dbo.Enrollment.No_Peserta
             FROM         dbo.Enrollment INNER JOIN
             dbo.Polis_Asuransi ON dbo.Enrollment.No_Proposal = dbo.Polis_Asuransi.No_Proposal
             WHERE Polis_Asuransi.No_Polis=@No_Polis AND dbo.Enrollment.No_Peserta=@pi_no_peserta AND Tgl_Proses=@pi_tgl_proses)

     UPDATE Enrollment SET Enrollment.Active_Ya=1 FROM Polis_Asuransi WHERE Polis_Asuransi.No_Proposal=Enrollment.No_Proposal
     AND Polis_Asuransi.No_Polis=@No_Polis AND dbo.Enrollment.No_Peserta=@pi_no_peserta AND Tgl_Proses=@pi_tgl_proses
   
     IF EXISTS(SELECT     dbo.Enrollment.No_Peserta
             FROM         dbo.Enrollment INNER JOIN
             dbo.Polis_Asuransi ON dbo.Enrollment.No_Proposal = dbo.Polis_Asuransi.No_Proposal
             WHERE Polis_Asuransi.No_Polis=@No_Polis AND dbo.Enrollment.No_Peserta=@pi_no_peserta  AND Tgl_Proses<@pi_tgl_proses)
     UPDATE Enrollment SET Enrollment.Active_Ya=0 FROM Polis_Asuransi WHERE Polis_Asuransi.No_Proposal=Enrollment.No_Proposal
     AND Polis_Asuransi.No_Polis=@No_Polis AND dbo.Enrollment.No_Peserta=@pi_no_peserta  AND Tgl_Proses<@pi_tgl_proses
  END  

  IF @MD_001<>@pi_mode AND @pi_mode<>'' 
  BEGIN
       SET @po_message='Mode belum sesuai dengan yang dimaksud.'
       --RETURN 1
  END
  IF NOT EXISTS(SELECT * FROM Enrollment_Log WHERE No_Polis=@No_Polis AND No_Peserta=@pi_no_peserta)
  INSERT INTO Enrollment_Log (No_Polis, No_Peserta, Jenis, Tgl_Proses_Last, Tgl_Proses_New, MD_001, MD_001_Last, MD_001_New, Keterangan)
  VALUES(@No_Polis, @pi_no_peserta, @pi_jenis, @Tgl_Proses_Last, @pi_tgl_proses, @pi_mode, @MD_001_Last, @MD_001, @po_message)
  ELSE
  UPDATE Enrollment_Log SET Jenis=@pi_jenis, Tgl_Proses_Last=@Tgl_Proses_Last, Tgl_Proses_New=@pi_tgl_proses, 
    MD_001=@pi_mode, MD_001_Last=@MD_001_Last, MD_001_New=@MD_001, Keterangan=@po_message
  WHERE No_Polis=@No_Polis AND No_Peserta=@pi_no_peserta
