USE [GrpInsurance]
GO

/****** Object:  StoredProcedure [dbo].[proc_list_polis_block_jaminan]    Script Date: 02/06/2018 14:59:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[proc_list_polis_block_jaminan]
AS
   DECLARE 
    @Msg varchar(100)
    ,@hasil int
   ,@pi_curr_date datetime =getdate()
   ,@Tipe int=-1
   ,@No_Proposal varchar(9)
   ,@no_polis varchar(11)
   ,@nama_perusahaan varchar(50)
   ,@tgl_mulai datetime
   ,@tgl_selesai datetime
   ,@gt_og varchar(2)
   ,@mata_uang varchar(3)
   ,@jenis_tpa varchar(20)

   DECLARE @temp_table as Table
   (
   [ROW_ID] [bigint] IDENTITY(1, 1)
                           NOT NULL,
	  [no_proposal] [varchar](9) NOT NULL,
	  [no_polis] [varchar](11) NULL,
	  [Nama_Perusahaan] [varchar](50) NULL,
	  [GT_OG] [varchar](2) NULL,
	  [mata_uang] [varchar](3) NULL,
	  [tgl_mulai] [datetime] NULL,
	  [Tgl_Selesai] [datetime] NULL,
    [jenis_tpa] varchar(20) null,
    Keterangan varchar(100) null,
    tgl_proses datetime null
    )

  DECLARE CreateEnrollment_Cursor CURSOR FOR
  SELECT 
  pol.No_Proposal, pol.no_polis, prs.nama_perusahaan, prs.GT_OG, CASE WHEN pol.mata_uang='01' THEN 'IDR' ELSE 'USD' END, pol.tgl_mulai, pol.tgl_selesai, jenis_tpa=CASE 
  WHEN coalesce(pol.admedika_ya,0)=1 AND coalesce(show_card_ya,0)<>1 THEN 'Swap Card' 
  WHEN coalesce(show_card_ya,0)=1 THEN 'Show Card' 
  END
   
  from polis_asuransi pol inner join perusahaan prs on pol.no_prs=prs.no_prs
  where pol.status='I' and (coalesce(pol.admedika_ya,0)=1 or coalesce(show_card_ya,0)=1)
  and pol.Tgl_Mulai<=@pi_curr_date and  pol.Tgl_Selesai>=@pi_curr_date
  
  OPEN CreateEnrollment_Cursor
  FETCH NEXT FROM CreateEnrollment_Cursor INTO @No_Proposal, @no_polis, @nama_perusahaan, @gt_og, @mata_uang, @tgl_mulai, @tgl_selesai, @jenis_tpa
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC @hasil =proc_GetKlaimPending @No_Proposal, @pi_curr_date, @Tipe, 1, @Msg OUTPUT  
    IF @hasil<>0
    BEGIN
      PRINT @No_Proposal+' '+CAST(@hasil as varchar(3))+' '+@Msg
      INSERT INTO @temp_table (no_proposal,no_polis,Nama_Perusahaan,GT_OG, mata_uang, tgl_mulai,Tgl_Selesai, Jenis_tpa, Keterangan, tgl_proses)
      VALUES(@No_Proposal, @no_polis, @nama_perusahaan, @gt_og, @mata_uang, @tgl_mulai, @tgl_selesai, @jenis_tpa, @Msg, @pi_curr_date)
    END
    FETCH NEXT FROM CreateEnrollment_Cursor  INTO @No_Proposal, @no_polis, @nama_perusahaan, @gt_og, @mata_uang, @tgl_mulai, @tgl_selesai, @jenis_tpa
  END
  CLOSE CreateEnrollment_Cursor
  DEALLOCATE CreateEnrollment_Cursor





  SELECT * FROM @temp_table ORDER BY [Nama_Perusahaan]

GO


