USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep10397]    Script Date: 03/22/2018 09:41:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- last modified by Pribadi Pamungkas. 2015-05-13
--ALTER PROCEDURE [dbo].[proc_Rep10397]
DECLARE
      @NoProposal VARCHAR(9) = '2018.0012',
      @TglKlaim1 DATETIME = NULL,
      @TglKlaim2 DATETIME = NULL,
      @AsoYa BIT = 1,
      @AssYa BIT = 1,
      @NoPeserta1 VARCHAR(12) = '',
      @NoPeserta2 VARCHAR(12) = '',
      @TglKlaim1Ya BIT = 0,
      @TglKlaim2Ya BIT = 0
--AS 
--EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10397'
DECLARE @tmp_Rep10397 TABLE (
--CREATE TABLE TmpInsurance.dbo.Rep10397 (
				id_row INT IDENTITY(1,1) NOT NULL,
        [No_Klaim] [varchar](11) NOT NULL,
        [Kode_Santunan] [varchar](10) NOT NULL,
        [Nama] [varchar](40) NULL,
        [No_peserta] [varchar](12) NULL,
        [Nama_Karyawan] [varchar](40) NULL,
        [No_peserta_Karyawan] [varchar](12) NULL,
        [Produk] [varchar](15) NULL,
        [Kelas] [varchar](10) NULL,
        [Nik] [varchar](30) NULL,
        [Cabang] [varchar](50) NULL,
        [Ket_1] [varchar](150) NULL,
        [Tgl_Klaim] [Datetime] NULL,
        [Tgl_Kwitansi] [Datetime] NULL,
        [Item_Santunan] [varchar](200) 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](100) NULL,
        [Keterangan] [varchar](200) NULL--,
        --CONSTRAINT [PK_RepRep10397] PRIMARY KEY CLUSTERED ([No_Klaim], [Kode_Santunan]) ON [PRIMARY]
       )
--ON     [PRIMARY]

DECLARE @No_Klaim VARCHAR(11)
DECLARE Employee_Cursor CURSOR
FOR
SELECT
  dbo.Klaim_Peserta.No_Klaim
FROM
  dbo.Klaim_Peserta INNER JOIN 
  dbo.Produk_Polis ON dbo.Klaim_Peserta.Id_Produk = dbo.Produk_Polis.Id_Produk
WHERE
  (dbo.Klaim_Peserta.No_Proposal = @NoProposal)
  AND (dbo.Klaim_Peserta.No_Peserta >= @NoPeserta1 OR ISNULL(@NoPeserta1, '') = '')
  AND (dbo.Klaim_Peserta.No_Peserta <= @NoPeserta2 OR ISNULL(@NoPeserta2, '') = '')
  --AND (dbo.Klaim_Peserta.Tgl_Klaim >= '2018-03-16')
  --AND (dbo.Klaim_Peserta.Tgl_Klaim < '2018-04-16')
  AND (dbo.Klaim_Peserta.Status = 'I')
  --AND dbo.Klaim_Peserta.Jenis_Pembayaran = 'R'
  AND dbo.Klaim_Peserta.Sub_kelompok = '0302'
  --AND dbo.Klaim_Peserta.Id_Produk = '2018.00051'
  AND ((ISNULL(dbo.Produk_Polis.Aso_Ya, 0) = 1 AND @AsoYa = 1)
        OR (ISNULL(dbo.Produk_Polis.Aso_Ya, 0) = 0 AND @AssYa = 1))
GROUP BY
  dbo.Klaim_Peserta.No_Klaim
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor INTO @No_Klaim
WHILE @@FETCH_STATUS = 0 
      BEGIN
            INSERT  INTO @tmp_Rep10397--TmpInsurance.dbo.Rep10397
                    (No_Klaim,
                     Kode_Santunan,
                     Nama,
                     No_peserta,
                     Nama_Karyawan,
                     No_peserta_Karyawan,
                     Produk,
                     Kelas,
                     NIK,
                     Cabang,
                     Ket_1,
                     Tgl_Klaim,
                     Tgl_Kwitansi,
                     Item_Santunan,
                     Diagnosa,
                     Jml_Diajukan,
                     Jml_Dibayar,
                     Jml_Deductible,
                     Jml_Ditolak,
                     Uang_Muka,
                     Rumah_Sakit,
                     Alasan_Penolakan,
                     Keterangan)
                    SELECT
											CASE ROW_NUMBER() OVER (ORDER BY (SELECT 100))
												WHEN 1 THEN dbo.Klaim_Peserta.No_Klaim
												ELSE ''
											END AS No_Klaim,
                      dbo.Klaim_Detail.Kode_Santunan,
                      dbo.Peserta.Nama,
                      dbo.Klaim_Peserta.No_Peserta,
                      kry.Nama,
                      kry.No_Peserta,
                      dbo.Sub_Kelompok.Nama_Sub AS Produk,
                      dbo.Peserta_Produk.Kelas,
                      dbo.Peserta.Nik,
                      dbo.Cabang.Nama,
                      dbo.Klaim_Peserta.Notes AS Ket_1,
                      dbo.Klaim_Peserta.Tgl_Klaim,
                      dbo.Klaim_Peserta.Tgl_Kwitansi,
                      dbo.Item_Santunan_Polis.Deskripsi AS Item_Santunan,
                      dbo.Diagnosa.Diagnosis1 AS Diagnosa,
                      dbo.Klaim_Detail.Jml_Diajukan,
                      dbo.Klaim_Detail.Jml_Dibayar,
                      dbo.Klaim_Detail.Jml_Deductible,
                      dbo.Klaim_Detail.Jml_Ditolak,
                      dbo.Klaim_Peserta.Uang_Muka,
                      dbo.Provider.Nama AS Rumah_Sakit,
                      dbo.Tolakan.Nama AS Alasan_Penolakan,
                      --dbo.Klaim_Detail.Keterangan -- remark by Pribadi Pamungkas. 2015-05-13
                      CASE dbo.Klaim_Peserta.Status -- add by Pribadi Pamungkas. 2015-05-13
                        WHEN 'G' THEN dbo.Klaim_Detail.Keterangan + '. Klaim masih dalam proses.'
                        ELSE dbo.Klaim_Detail.Keterangan
                      END AS Keterangan
                    FROM
                      dbo.Klaim_Peserta INNER JOIN 
                      dbo.Klaim_Detail ON dbo.Klaim_Peserta.No_Klaim = dbo.Klaim_Detail.No_Klaim INNER JOIN 
                      dbo.Item_Santunan_Polis ON dbo.Klaim_Peserta.Id_Produk = dbo.Item_Santunan_Polis.Id_Produk
                                                 AND dbo.Klaim_Detail.Kode_Santunan = dbo.Item_Santunan_Polis.Kode_Santunan INNER JOIN 
                      dbo.Peserta ON dbo.Klaim_Peserta.No_Proposal = dbo.Peserta.No_Proposal
                                     AND dbo.Klaim_Peserta.No_Peserta = dbo.Peserta.No_Peserta INNER JOIN 
                      dbo.Sub_Kelompok ON dbo.Klaim_Peserta.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok LEFT OUTER JOIN 
                      dbo.Tolakan ON dbo.Klaim_Detail.Kode_Tolakan = dbo.Tolakan.Kode LEFT OUTER JOIN 
                      dbo.Provider ON dbo.Klaim_Peserta.Kode_Provider = dbo.Provider.IDI LEFT OUTER JOIN 
                      dbo.Diagnosa ON dbo.Klaim_Peserta.Kode_Diagnosa = dbo.Diagnosa.Kode INNER JOIN 
                      dbo.Peserta_Produk ON dbo.Peserta_Produk.No_Proposal = dbo.Klaim_Peserta.No_Proposal
                                            AND dbo.Peserta_Produk.No_Peserta = dbo.Klaim_Peserta.No_Peserta
                                            AND dbo.Peserta_Produk.Id_Produk = dbo.Klaim_Peserta.Id_Produk LEFT OUTER JOIN 
                      dbo.Cabang ON dbo.Peserta.No_Prs = dbo.Cabang.No_Prs
                                    AND dbo.Peserta.Cabang = dbo.Cabang.Kode INNER JOIN 
                      dbo.Peserta AS kry ON dbo.Klaim_Peserta.No_Proposal = kry.No_Proposal
                                     AND LEFT(dbo.Klaim_Peserta.No_Peserta,11)+'0' = kry.No_Peserta
                    WHERE
                      (dbo.Klaim_Peserta.No_Klaim = @No_Klaim) 
					  AND CAST(dbo.Klaim_Detail.Jml_Ditolak AS NUMERIC(18,2)) > 0
					  AND (
							COALESCE(dbo.Klaim_Detail.Keterangan,'')=''
							OR COALESCE(dbo.Diagnosa.Diagnosis1,'')=''
							OR COALESCE(dbo.Klaim_Peserta.Notes,'')=''
						)
                    
                    PRINT SCOPE_IDENTITY();

            FETCH NEXT FROM Employee_Cursor INTO @No_Klaim
      END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

SELECT
  *
FROM
  @tmp_Rep10397--TmpInsurance.dbo.Rep10397
-- ORDER BY dbo.Klaim_Peserta.No_Klaim, dbo.Item_Santunan_Polis.Urutan
