DECLARE 
  @kode_item_santunan_maksimum VARCHAR(10) = 'RI2313',  -- ini daftar santunan maksimum (RI2313, RJ40, LH14, MK08, RG37)
  @no_proposal VARCHAR(9),
  @nama_perusahaan VARCHAR(50),
  @sub_kelompok VARCHAR(6)

--DECLARE ##list_sukses TABLE ( no_proposal VARCHAR(9),
--                             nama_perusahaan VARCHAR(50),
--                             no_klaim VARCHAR(11))

--DECLARE ##list_gagal TABLE ( no_proposal VARCHAR(9),
--                             nama_perusahaan VARCHAR(50),
--                             no_klaim VARCHAR(11))
--DROP TABLE ##list_sukses
--DROP TABLE ##list_gagal
CREATE TABLE ##list_sukses ( no_proposal VARCHAR(9),
                             nama_perusahaan VARCHAR(50),
                             no_klaim VARCHAR(11))

CREATE TABLE ##list_gagal ( no_proposal VARCHAR(9),
                             nama_perusahaan VARCHAR(50),
                             no_klaim VARCHAR(11))

DECLARE list_cursor CURSOR FAST_FORWARD FOR 
SELECT DISTINCT 
  pa.No_Proposal,
	prs.Nama_Perusahaan,
	pp.Sub_Kelompok
FROM
	dbo.Item_Santunan_Polis AS isp INNER JOIN
	dbo.Produk_Polis AS pp ON isp.Id_Produk = pp.Id_Produk INNER JOIN
	dbo.Polis_Asuransi AS pa ON pp.No_Proposal = pa.No_Proposal INNER JOIN
	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
WHERE
  isp.Kode_Santunan = @kode_item_santunan_maksimum
  AND pa.Status = 'I'
  AND pp.Status = 'I'

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_proposal, @nama_perusahaan, @sub_kelompok

WHILE @@FETCH_STATUS = 0  
BEGIN  

  DECLARE 
    @no_klaim VARCHAR(11),
    @no_peserta VARCHAR(12),
    @id_produk VARCHAR(10)
  
  DECLARE klaim_cursor CURSOR FAST_FORWARD FOR 
  SELECT DISTINCT
  	kp.No_Klaim,
  	kp.No_Peserta,
  	kp.Id_Produk
  FROM
  	dbo.Klaim_Peserta AS kp INNER JOIN
  	dbo.Klaim_Detail AS kd ON kp.No_Klaim = kd.No_Klaim
  WHERE
    kp.No_Proposal = @no_proposal
    AND kp.Status = 'G'
    AND kp.Sub_Kelompok = @sub_kelompok
    AND kd.Kode_Santunan <> @kode_item_santunan_maksimum
    AND CAST(kd.Jml_Diajukan AS NUMERIC(18,2)) <> 0
  
  OPEN klaim_cursor  
  FETCH NEXT FROM klaim_cursor INTO @no_klaim, @no_peserta, @id_produk
  WHILE @@FETCH_STATUS = 0  -- start loop no klaim
  BEGIN  

    DECLARE
      @jml_diajukan FLOAT,
      @jml_klaim FLOAT,
      @jml_deductible FLOAT,
      @jml_dibayar FLOAT,
      @jml_ditolak FLOAT,
      @temp_jml_dibayar FLOAT,
      @temp_jml_ditolak FLOAT
      
    SELECT
      @jml_diajukan = SUM(x.Jml_Diajukan),
      @jml_klaim = SUM(x.Jml_Klaim),
      @jml_deductible = SUM(x.Jml_Deductible),
      @jml_dibayar = SUM(x.Jml_Dibayar),
      @jml_ditolak = SUM(x.Jml_Ditolak)
    FROM
	    (
	      -- non 211 / bukan santunan melebihi limit
        SELECT
  	      COALESCE(SUM(kd.Jml_Diajukan),0) AS Jml_Diajukan,
  	      COALESCE(SUM(kd.Jml_Klaim),0) AS Jml_Klaim,
  	      COALESCE(SUM(kd.Jml_Deductible),0) AS Jml_Deductible,
  	      COALESCE(SUM(kd.Jml_Dibayar),0) AS Jml_Dibayar,
  	      COALESCE(SUM(kd.Jml_Ditolak),0) AS Jml_Ditolak
        FROM
  	      dbo.Klaim_Peserta AS kp INNER JOIN
  	      dbo.Klaim_Detail AS kd ON kp.No_Klaim = kd.No_Klaim
        WHERE
          kp.No_Proposal = @no_proposal
          AND kp.No_Klaim = @no_klaim
          AND kp.Status = 'G'
          AND kp.Sub_Kelompok = @sub_kelompok
          AND kd.Kode_Santunan <> @kode_item_santunan_maksimum
          AND CAST(kd.Jml_Diajukan AS NUMERIC(18,2)) <> 0
          AND (COALESCE(kd.Kode_Tolakan,'') <> '211' -- tolakan standar (bukan santunan melebihi limit) tetap dimasukan ke tolakan
                AND COALESCE(kd.Kode_Tolakan,'') <> '')

        UNION ALL
        
        -- 211 / santunan melebihi limit
        SELECT
  	      COALESCE(SUM(kd.Jml_Diajukan),0) AS Jml_Diajukan,
  	      COALESCE(SUM(kd.Jml_Klaim),0) AS Jml_Klaim,
  	      COALESCE(SUM(kd.Jml_Deductible),0) AS Jml_Deductible,
  	      COALESCE(SUM(kd.Jml_Dibayar) + SUM(kd.Jml_Ditolak),0) AS Jml_Dibayar,
  	      0 AS Jml_Ditolak
        FROM
  	      dbo.Klaim_Peserta AS kp INNER JOIN
  	      dbo.Klaim_Detail AS kd ON kp.No_Klaim = kd.No_Klaim
        WHERE
          kp.No_Proposal = @no_proposal
          AND kp.No_Klaim = @no_klaim
          AND kp.Status = 'G'
          AND kp.Sub_Kelompok = @sub_kelompok
          AND kd.Kode_Santunan <> @kode_item_santunan_maksimum
          AND CAST(kd.Jml_Diajukan AS NUMERIC(18,2)) <> 0
          AND (COALESCE(kd.Kode_Tolakan,'') = '211' -- tolakan santunan melebihi limit jangan dimasukan ke tolakan, tapi tambahkan ke jml_dibayar
               OR COALESCE(kd.Kode_Tolakan,'') = '')
          
      ) AS x
    IF (@@RowCount > 0)
      BEGIN

        BEGIN TRAN
        
        DECLARE @benefit_limit FLOAT = 0
        SELECT @benefit_limit = dbo.func_get_benefit_limit(@no_peserta,@id_produk,@kode_item_santunan_maksimum)
        
        IF EXISTS(  SELECT
      	              *
                    FROM
  	                  dbo.Klaim_Peserta AS kp INNER JOIN
  	                  dbo.Klaim_Detail AS kd ON kp.No_Klaim = kd.No_Klaim
                    WHERE
                      kp.No_Proposal = @no_proposal
                      AND kp.No_Klaim = @no_klaim
                      AND kp.Status = 'G'
                      AND kp.Sub_Kelompok = @sub_kelompok
                      AND kd.Kode_Santunan = @kode_item_santunan_maksimum
                  ) -- jika sudah ada, maka update
          BEGIN --update

            DECLARE
              @update_jml_diajukan FLOAT,
              @update_jml_klaim FLOAT,
              @update_jml_deductible FLOAT,
              @update_jml_dibayar FLOAT,
              @update_jml_ditolak FLOAT

            SELECT
              @update_jml_diajukan = Jml_Diajukan + @jml_diajukan,
              @update_jml_klaim = Jml_Klaim + @jml_klaim,
              @update_jml_deductible = Jml_Deductible + @jml_deductible,
              @update_jml_dibayar = CASE
                              WHEN (Jml_Dibayar + @jml_dibayar) > @benefit_limit THEN @benefit_limit
                              ELSE Jml_Dibayar + @jml_dibayar
                            END,
              @update_jml_ditolak = CASE
                              WHEN (Jml_Dibayar + @jml_dibayar) > @benefit_limit THEN (Jml_Dibayar + @jml_dibayar) - @benefit_limit
                              ELSE Jml_Ditolak + @jml_ditolak
                            END
            FROM
            	dbo.Klaim_Detail
            WHERE
              No_Klaim = @no_klaim
              AND Kode_Santunan = @kode_item_santunan_maksimum
              
            UPDATE
              dbo.Klaim_Detail
            SET
              Jml_Diajukan = @update_jml_diajukan,
              Jml_Klaim = @update_jml_klaim,
              Jml_Deductible = @update_jml_deductible,
              Jml_Dibayar = @update_jml_dibayar,
              Jml_Ditolak = @update_jml_ditolak,
              Change_By = 'SYS',
              Change_Date = GETDATE()
            WHERE
              No_Klaim = @no_klaim
              AND Kode_Santunan = @kode_item_santunan_maksimum
            IF (@@Error <> 0)-- Gagal
              BEGIN
	              ROLLBACK TRAN
                INSERT INTO ##list_gagal
                        (no_proposal,
                         nama_perusahaan,
                         no_klaim)
                VALUES
                        (@no_proposal, -- no_proposal - varchar(9)
                         @nama_perusahaan, -- nama_perusahaan - varchar(50)
                         @no_klaim  -- no_klaim - varchar(11)
                         )
              END
            ELSE
              BEGIN
              
                DELETE FROM dbo.Klaim_Detail WHERE No_Klaim = @no_klaim AND Kode_Santunan <> @kode_item_santunan_maksimum
                --UPDATE dbo.Klaim_Detail SET Jml_Diajukan=0, Jml_Klaim=0, Jml_Deductible=0, Jml_Dibayar=0, Jml_Ditolak=0 WHERE No_Klaim = @no_klaim AND Kode_Santunan <> @kode_item_santunan_maksimum
                
                UPDATE 
                  dbo.Klaim_Peserta 
                SET 
                  Jml_Dibayar = @update_jml_dibayar, 
                  Jml_Ditolak = @update_jml_ditolak 
                WHERE No_Klaim = @no_klaim
                IF (@@Error <> 0)-- Gagal
                  BEGIN
	                  ROLLBACK TRAN
                    INSERT INTO ##list_gagal
                            (no_proposal,
                             nama_perusahaan,
                             no_klaim)
                    VALUES
                            (@no_proposal, -- no_proposal - varchar(9)
                             @nama_perusahaan, -- nama_perusahaan - varchar(50)
                             @no_klaim  -- no_klaim - varchar(11)
                             )
	                END
                ELSE
                  BEGIN
                    COMMIT TRAN
                    INSERT INTO ##list_sukses
                            (no_proposal,
                             nama_perusahaan,
                             no_klaim)
                    VALUES
                            (@no_proposal, -- no_proposal - varchar(9)
                             @nama_perusahaan, -- nama_perusahaan - varchar(50)
                             @no_klaim  -- no_klaim - varchar(11)
                             )
                  END
                  
              END

          END
        ELSE
          BEGIN -- insert
            IF @jml_dibayar>@benefit_limit
              BEGIN
                SET @temp_jml_dibayar = @benefit_limit
                SET @temp_jml_ditolak = @jml_dibayar - @benefit_limit
              END
            ELSE
              BEGIN
                SET @temp_jml_dibayar = @jml_dibayar
                SET @temp_jml_ditolak = @jml_ditolak
              END
          
            INSERT INTO dbo.Klaim_Detail
                    (No_Klaim,
                     Kode_Santunan,
                     Tabel_Prosen,
                     Amount,
                     Jml_Diajukan,
                     Jml_Klaim,
                     Jml_Deductible,
                     Jml_Benefit,
                     Jml_Dibayar,
                     Jml_Ditolak,
                     Kode_Tolakan,
                     Jml_Adm,
                     Jml_Dispensasi,
                     Jml_Dibayar_Old,
                     Status,
                     Change_By,
                     Change_Date,
                     Create_By,
                     Create_Date)
            VALUES
                    (@no_klaim, -- No_Klaim - varchar(11)
                     @kode_item_santunan_maksimum, -- Kode_Santunan - varchar(10)
                     0, -- Tabel_Prosen - float
                     0, -- Amount - float
                     @jml_diajukan, -- Jml_Diajukan - float
                     @jml_klaim, -- Jml_Klaim - float
                     @jml_deductible, -- Jml_Deductible - float
                     @benefit_limit, -- Jml_Benefit - float
                     @temp_jml_dibayar, -- Jml_Dibayar - float
                     @temp_jml_ditolak, -- Jml_Ditolak - float
                     '', -- Kode_Tolakan - varchar(5)
                     0, -- Jml_Adm - float
                     0, -- Jml_Dispensasi - float
                     0, -- Jml_Dibayar_Old - float
                     'I', -- Status - varchar(1)
                     'SYS', -- Change_By - varchar(4)
                     GETDATE(), -- Change_Date - datetime
                     'SYS', -- Create_By - varchar(4)
                     GETDATE()  -- Create_Date - datetime
                     )
            		IF (@@Error <> 0)-- Gagal
		              BEGIN
			              ROLLBACK TRAN
                    INSERT INTO ##list_gagal
                            (no_proposal,
                             nama_perusahaan,
                             no_klaim)
                    VALUES
                            (@no_proposal, -- no_proposal - varchar(9)
                             @nama_perusahaan, -- nama_perusahaan - varchar(50)
                             @no_klaim  -- no_klaim - varchar(11)
                             )
		              END
		            ELSE
		              BEGIN
		              
		                DELETE FROM dbo.Klaim_Detail WHERE No_Klaim = @no_klaim AND Kode_Santunan <> @kode_item_santunan_maksimum
		                --UPDATE dbo.Klaim_Detail SET Jml_Diajukan=0, Jml_Klaim=0, Jml_Deductible=0, Jml_Dibayar=0, Jml_Ditolak=0 WHERE No_Klaim = @no_klaim AND Kode_Santunan <> @kode_item_santunan_maksimum
		                
		                UPDATE dbo.Klaim_Peserta SET Jml_Dibayar = @temp_jml_dibayar, Jml_Ditolak = @temp_jml_ditolak WHERE No_Klaim = @no_klaim
		                IF (@@Error <> 0)-- Gagal
		                  BEGIN
			                  ROLLBACK TRAN
                        INSERT INTO ##list_gagal
                                (no_proposal,
                                 nama_perusahaan,
                                 no_klaim)
                        VALUES
                                (@no_proposal, -- no_proposal - varchar(9)
                                 @nama_perusahaan, -- nama_perusahaan - varchar(50)
                                 @no_klaim  -- no_klaim - varchar(11)
                                 )
			                END
		                ELSE
		                  BEGIN
		                    COMMIT TRAN
                        INSERT INTO ##list_sukses
                                (no_proposal,
                                 nama_perusahaan,
                                 no_klaim)
                        VALUES
                                (@no_proposal, -- no_proposal - varchar(9)
                                 @nama_perusahaan, -- nama_perusahaan - varchar(50)
                                 @no_klaim  -- no_klaim - varchar(11)
                                 )
		                  END
		                  
		              END
		
          END
      
      END

    FETCH NEXT FROM klaim_cursor INTO @no_klaim, @no_peserta, @id_produk
  END  -- end loop no klaim  

  CLOSE klaim_cursor  
  DEALLOCATE klaim_cursor


  FETCH NEXT FROM list_cursor INTO @no_proposal, @nama_perusahaan, @sub_kelompok
END  

CLOSE list_cursor  
DEALLOCATE list_cursor

SELECT * FROM ##list_sukses
SELECT * FROM ##list_gagal

