USE [GrpInsurance]
GO

  
-- For Debug --
--------------------------------------------
DECLARE
  @IdBroker varchar(5) = '',
  @GroupPrs varchar(5) = '',
  @GTOG varchar(2) = '',
  @NoProposal varchar(9) = '2014.0771',
  @TglMulai1 DATETIME = NULL,
  @TglMulai2 DATETIME = NULL,
  @TglSelesai1 DATETIME = NULL,
  @TglSelesai2 DATETIME = NULL,
  @TglPremi1 DATETIME = NULL,
  @TglPremi2 DATETIME = NULL,
  @TglKlaim1 DATETIME = NULL,
  @TglKlaim2 DATETIME = NULL,
  @Pilihan INT = 5,
  @PilihBroker SMALLINT = 0,
  @BidangUsaha varchar(10)= '',
  @TglMulai1Ya BIT = NULL,
  @TglMulai2Ya BIT = NULL,
  @TglSelesai1Ya BIT = NULL,
  @TglSelesai2Ya BIT = NULL,
  @TglPremi1Ya BIT = NULL,
  @TglPremi2Ya BIT = NULL,
  @TglKlaim1Ya BIT = NULL,
  @TglKlaim2Ya BIT = NULL
--------------------------------------------  
  


  EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10381_5_tmp'

    CREATE TABLE TmpInsurance.dbo.Rep10381_5_tmp (
    [No_Ref] [varchar] (11) NOT NULL ,
    [No_Klaim] [varchar] (11) NOT NULL ,
    [Kode_Santunan] [varchar] (10) NOT NULL ,
    [Nama] [varchar] (40) NULL,
    [No_peserta] [varchar] (12) NULL,
    [Produk] [varchar] (25) NULL,
    [Kelas] [varchar] (10) NULL,
    [Nik] [varchar] (30) NULL,
    [Cabang] [varchar] (50) NULL,
    [Departemen] [varchar] (30) NULL,
    [Ket_1] [varchar] (150) NULL,
    [Tgl_Klaim] [Datetime] NULL ,
    [Tgl_Kwitansi] [Datetime] NULL ,
    [Item_Santunan] [varchar] (100) NULL,
    [Diagnosa] [varchar] (100) NULL,
    [Jml_Diajukan] [float] NULL ,
    [Jml_Dibayar] [float] NULL ,
    [Jml_Deductible] [float] NULL ,
    [Jml_Ditolak] [float] NULL ,
    [Uang_Muka] [float] NULL ,
    [Rumah_Sakit] [varchar] (60) NULL,
    [Alasan_Penolakan] [varchar] (150) NULL,
    [Keterangan] [varchar] (150) NULL,
    [Tgl_Lunas] [Datetime] Null
    CONSTRAINT [PK_Rep10381_5_tmp] PRIMARY KEY  CLUSTERED 
    ([No_Ref], [No_Klaim], [Kode_Santunan]) ON [PRIMARY]) ON [PRIMARY]

    DECLARE @No_Ref varchar(11)
    DECLARE @No_Klaim varchar(11)
    DECLARE @Kode_Santunan varchar(10)

    DECLARE Employee_Cursor CURSOR FOR
    SELECT 
      dbo.Klaim_Ref_Detail.No_Ref, 
      dbo.Klaim_Ref_Detail.No_Klaim, 
      dbo.Klaim_Ref_Detail.Kode_Santunan 
    FROM 
      dbo.Note_Terbit_Master 
      INNER JOIN
      dbo.Klaim_Ref_Detail 
      ON dbo.Note_Terbit_Master.No_Ref = dbo.Klaim_Ref_Detail.No_Ref
      INNER JOIN  -- add zai 20140528
      (
         SELECT * FROM dbo.Klaim_Peserta
         WHERE Status = 'I'               
      ) AS kp
      ON dbo.Klaim_Ref_Detail.No_Proposal = kp.No_Proposal 
         AND dbo.Klaim_Ref_Detail.No_Peserta = kp.No_Peserta 
         AND dbo.Klaim_Ref_Detail.No_Klaim = kp.No_Klaim                  
      INNER JOIN
      dbo.Peserta 
      ON dbo.Note_Terbit_Master.No_Proposal = dbo.Peserta.No_Proposal 
         AND dbo.Klaim_Ref_Detail.No_Peserta = dbo.Peserta.No_Peserta
      INNER JOIN
      dbo.Polis_Asuransi 
      ON dbo.Note_Terbit_Master.No_Proposal = dbo.Polis_Asuransi.No_Proposal 
    WHERE 
      (dbo.Note_Terbit_Master.Jenis IN (20, 21, 22, 23, 24, 25, 29, 40, 41, 42, 43, 44, 45))  -- 29 jenis note baru, by Pribadi Pamungkas 2015-04-07
      AND (dbo.Note_Terbit_Master.Status<>'C') 
      AND (dbo.Note_Terbit_Master.No_Proposal=@NoProposal) 
      AND (dbo.Note_Terbit_Master.Tgl_Buat>=@TglKlaim1 OR isnull(@TglKlaim1Ya,0)=0)
      AND (dbo.Note_Terbit_Master.Tgl_Buat<=@TglKlaim2 OR isnull(@TglKLaim2Ya,0)=0)
    GROUP BY 
      dbo.Klaim_Ref_Detail.No_Ref, 
      dbo.Klaim_Ref_Detail.No_Klaim, 
      dbo.Klaim_Ref_Detail.Kode_Santunan

    OPEN Employee_Cursor
    FETCH NEXT FROM Employee_Cursor INTO @No_Ref, @No_Klaim, @Kode_Santunan
    

  BEGIN TRAN  
    
  WHILE @@FETCH_STATUS = 0
  BEGIN
      IF NOT EXISTS(SELECT No_Klaim FROM TmpInsurance.dbo.Rep10381_5_tmp WHERE No_Klaim=@No_Klaim AND No_Ref=@No_Ref AND Kode_Santunan=@Kode_Santunan)
      BEGIN
        --PRINT @No_Klaim+@No_Ref+@Kode_Santunan
        INSERT INTO TmpInsurance.dbo.Rep10381_5_tmp
        (
           No_Ref, 
           No_Klaim, 
           Kode_Santunan, 
           Nama, 
           No_peserta, 
           Produk, 
           Kelas, 
           NIK, 
           Cabang, 
           Departemen, 
           Ket_1, 
           Tgl_Klaim, 
           Tgl_Kwitansi,
           Item_Santunan, 
           Diagnosa, 
           Jml_Diajukan, 
           Jml_Dibayar, 
           Jml_Deductible, 
           Jml_Ditolak, 
           Uang_Muka, 
           Rumah_Sakit, 
           Alasan_Penolakan, 
           Keterangan, 
           Tgl_Lunas
        )
        SELECT 
          dbo.Klaim_Ref_Detail.No_Ref, 
          dbo.Klaim_Ref_Detail.No_Klaim, 
          dbo.Klaim_Ref_Detail.Kode_Santunan, 
          dbo.Peserta.Nama, 
          dbo.Klaim_Ref_Detail.No_Peserta, 
          dbo.Sub_Kelompok.Nama_Sub AS Produk, 
          dbo.Peserta_Produk.Kelas,
          dbo.Peserta.Nik,
          dbo.Cabang.Nama,
          dbo.Peserta.Departemen,
          '' as Ket_1, --dbo.Klaim_Ref_Detail.Notes AS Ket_1, 
          dbo.Klaim_Ref_Detail.Tgl_Klaim, 
          dbo.Klaim_Ref_Detail.Tgl_Kwitansi, 
          dbo.Item_Santunan_Polis.Deskripsi AS Item_Santunan, 
          dbo.Diagnosa.Diagnosis1 AS Diagnosa, 
          Abs(dbo.Klaim_Ref_Detail.Jml_Diajukan), 
          Abs(dbo.Klaim_Ref_Detail.Jml_Dibayar)-Abs(isnull(dbo.Klaim_Ref_Detail.Jml_Dibayar_Old,0)) , 
          dbo.Klaim_Ref_Detail.Jml_Deductible, 
          dbo.Klaim_Ref_Detail.Jml_Ditolak, 
          dbo.Klaim_Ref_Detail.Uang_Muka, 
          dbo.Provider.Nama AS Rumah_Sakit, 
          dbo.Tolakan.Nama AS Alasan_Penolakan, 
          dbo.Klaim_Ref_Detail.Keterangan, 
          dbo.func_GetTglLunasKlaim(dbo.Klaim_Ref_Detail.No_Klaim)  
        FROM  
          dbo.Klaim_Ref_Detail 
          INNER JOIN
          dbo.Item_Santunan_Polis 
          ON dbo.Klaim_Ref_Detail.Id_Produk = dbo.Item_Santunan_Polis.Id_Produk 
             AND dbo.Klaim_Ref_Detail.Kode_Santunan = dbo.Item_Santunan_Polis.Kode_Santunan 
          INNER JOIN
          dbo.Peserta 
          ON dbo.Klaim_Ref_Detail.No_Proposal = dbo.Peserta.No_Proposal 
             AND dbo.Klaim_Ref_Detail.No_Peserta = dbo.Peserta.No_Peserta 
          INNER JOIN
          dbo.Sub_Kelompok 
          ON dbo.Klaim_Ref_Detail.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok 
          LEFT OUTER JOIN
          dbo.Tolakan 
          ON dbo.Klaim_Ref_Detail.Kode_Tolakan = dbo.Tolakan.Kode 
          LEFT OUTER JOIN
          dbo.Provider 
          ON dbo.Klaim_Ref_Detail.Kode_Provider = dbo.Provider.IDI 
          LEFT OUTER JOIN
          dbo.Diagnosa 
          ON dbo.Klaim_Ref_Detail.Kode_Diagnosa = dbo.Diagnosa.Kode 
          LEFT OUTER JOIN 
          dbo.Cabang 
          ON dbo.Peserta.No_Prs=dbo.Cabang.No_Prs 
             AND dbo.Peserta.Cabang=dbo.Cabang.Kode
          LEFT OUTER JOIN 
          dbo.Peserta_Produk
          ON dbo.Peserta_Produk.No_Proposal=dbo.Klaim_Ref_Detail.No_Proposal 
             AND dbo.Peserta_Produk.No_Peserta=dbo.Klaim_Ref_Detail.No_Peserta 
             AND dbo.Peserta_Produk.Id_Produk=dbo.Klaim_Ref_Detail.Id_Produk     
        WHERE 
          (isnull(dbo.Klaim_Ref_Detail.No_Klaim,'')=@No_Klaim) AND (isnull(dbo.Klaim_Ref_Detail.No_Ref,'')=@No_Ref) AND (isnull(dbo.Klaim_Ref_Detail.Kode_Santunan,'')=@Kode_Santunan)
     END
     
      
     IF @@Error>0 ROLLBACK TRAN 
     ELSE
        FETCH NEXT FROM Employee_Cursor INTO @No_Ref, @No_Klaim, @Kode_Santunan
        
  END
    
  CLOSE Employee_Cursor
  DEALLOCATE Employee_Cursor
    
  EXEC gproc_RepColumnLoad '10381',5
    
  SELECT * FROM TmpInsurance.dbo.Rep10381_5_tmp

    
  COMMIT TRAN
    
  --EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10381_5_tmp'
  
  
