USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_App10316]    Script Date: 03/20/2017 11:22:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** Object:  Stored Procedure dbo.proc_App10316    Script Date: 5/26/2006 2:06:07 AM ******/
ALTER Procedure [dbo].[proc_App10316]
@NoLama varchar(9), 
@TglOtomatis bit, 
@TglMulai DateTime, 
@TglSelesai DateTime,
@CopyProdukYa bit,
@CopyPesertaYa bit,
@CreateBy varchar(4),
@TglMulaiYa DateTime, 
@TglSelesaiYa DateTime, 
@Msg varchar(100) OUTPUT
AS 
  -- POLIS RENEWAL
  SET @Msg=''
  DECLARE @Id_Produk varchar(10),
          @No_Polis varchar(11),
          @No_Baru varchar(9),  
          @Status varchar(1), 
          @Tahun int, 
          @Tgl_Selesai_Old DateTime,
          @Create_Date DateTime

  SET @Create_Date = GetDate()
  SET @Tahun = Year(GetDate())
  IF NOT EXISTS(SELECT * FROM Polis_Asuransi WHERE No_Proposal=@NoLama) 
  BEGIN
       SET @Msg='Polis belum terdaftar.'
       RETURN 1
  END

  SELECT @No_Polis=No_Polis, @Status=Status FROM Polis_Asuransi WHERE No_Proposal=@NoLama
  IF @No_Polis='' 
  BEGIN
       SET @Msg='Polis belum mempunyai Nomor.'
       RETURN 1
  END

  IF isnull(@TglOtomatis,0)=1 
  BEGIN
       SELECT @Tgl_Selesai_Old=Tgl_Selesai FROM Polis_Asuransi WHERE No_Proposal=@NoLama  AND Status<>'C'
       SET @TglMulai=@Tgl_Selesai_Old+1
       SET @TglSelesai = DATEADD(year, 1, @TglMulai)-1
  END

  IF EXISTS(SELECT * FROM Polis_Asuransi WHERE No_Polis=@No_Polis AND Tgl_Mulai<=@TglMulai AND Tgl_Selesai>=@TglMulai) 
  BEGIN
       SET @Msg='Mungkin polis sudah renewal. Coba periksa kembali!'
       RETURN 1
  END

  -- pengecekan saldo. added by Pribadi Pamungkas. 2017-03-20. ticket RQ2017030229
  DECLARE @saldo NUMERIC(20,2) = 0
  SELECT
	  @saldo = CAST(SUM(COALESCE(saldo,0)) AS NUMERIC(20,2))
  FROM
	  custPort_View_Tagihan_Kewajiban
  WHERE
    no_Polis = @No_Polis
    AND jenis_Note IN (1,2,3,4,6,9,18)  -- DN Premi
  IF COALESCE(@saldo,0)>0
  BEGIN
       SET @Msg='Mungkin polis sudah renewal. Coba periksa kembali!'
       RETURN 1
  END
  --


  EXEC dbo.gproc_SetNo 1, 100, @Tahun, 2
  EXEC gproc_GetNo 1, @Create_Date, 'No_Proposal', @No_Baru OUTPUT
  IF EXISTS(SELECT * FROM Polis_Asuransi WHERE No_Proposal=@No_Baru) 
  BEGIN
       SET @Msg='Ada kesalahan Nomor Proposal. Coba Ulangi!'
       RETURN 1
  END
--COPY POLIS
  EXEC proc_CopyPolis @NoLama, @No_Baru, @TglMulai, @TglSelesai, @CreateBy, @Create_Date
  IF isnull(@CopyProdukYa,0)=1
  BEGIN
--COPY PRODUK POLIS
  DECLARE Employee_Cursor CURSOR FOR
  SELECT Id_Produk FROM Produk_Polis WHERE No_Proposal=@NoLama
  OPEN Employee_Cursor
  FETCH NEXT FROM Employee_Cursor INTO @Id_Produk
  WHILE @@FETCH_STATUS = 0
  BEGIN
     EXEC proc_CopyProdukPolis @No_Baru, @Id_Produk, @Msg OUTPUT
     FETCH NEXT FROM Employee_Cursor INTO @Id_Produk
  END
  CLOSE Employee_Cursor
  DEALLOCATE Employee_Cursor
  EXEC proc_SamakanIdProduk @No_Baru
  END
  IF isnull(@CopyPesertaYa,0)=1
  BEGIN
--COPY PESERTA
  EXEC proc_CopyPeserta @NoLama, @No_Baru, @TglMulai, @TglSelesai, @CreateBy, @Create_Date
--COPY PESERTA PRODUK
  EXEC proc_CopyPesertaProduk @NoLama, @No_Baru, @TglMulai, @TglSelesai, @CreateBy, @Create_Date 
  END
  EXEC proc_SetPolisLast @No_Baru
  UPDATE Polis_Asuransi SET Status='M'  WHERE isnull(Active_Ya,0)<>1 AND Status='O' AND No_Proposal=@NoLama
