USE GrpInsurance

DECLARE @hasil TABLE ( no_proposal VARCHAR(9),
                       id_produk VARCHAR(12),
                       [status] VARCHAR(1),
                       [message] VARCHAR(50),
                       cek BIT)

DECLARE @no_proposal VARCHAR(9)

DECLARE list_cursor CURSOR FOR  
SELECT No_Proposal
FROM [db_no_replicate_space].[dbo].[temp_prib_equity_ajb]

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_proposal  

WHILE @@FETCH_STATUS = 0  
BEGIN
  
  IF EXISTS(SELECT * FROM dbo.Produk_Polis WHERE Sub_Kelompok = '0100' AND No_Proposal = @no_proposal)
    BEGIN
    
      -- start: sub cursor
      DECLARE @id_produk VARCHAR(10)

      DECLARE produk_cursor CURSOR FAST_FORWARD FOR  
      SELECT Id_Produk
      FROM dbo.Produk_Polis
      WHERE No_Proposal = @no_proposal AND Sub_Kelompok = '0100' AND Status = 'I'

      OPEN produk_cursor  
      FETCH NEXT FROM produk_cursor INTO @id_produk  

      WHILE @@FETCH_STATUS = 0  
      BEGIN
        UPDATE
          dbo.Premi_Polis
        SET
          Kelas = '*'
        WHERE
          Id_Produk = @id_produk
        
        IF EXISTS(SELECT * FROM dbo.Benefit_Polis WHERE Id_Produk = @id_produk)
          BEGIN
            PRINT @id_produk
            UPDATE
              dbo.Benefit_Polis 
            SET
              Kelas = '*'
            WHERE
              Id_Produk = @id_produk
          END
        ELSE
          BEGIN
            INSERT INTO dbo.Benefit_Polis
                    (Id_Produk,
                     Kelas,
                     Kode_Santunan,
                     Urutan,
                     Benefit)
            SELECT TOP 1
              @id_produk,
              '*',
              Kode_Santunan,
              0, -- Urutan - smallint
              0.0 -- Benefit - float
            FROM 
              dbo.Item_Santunan_Polis 
            WHERE 
              Id_Produk = @id_produk
          END

        UPDATE 
          dbo.Item_Santunan_Polis
        SET
          F_Prosen = 100
        WHERE
          Id_Produk = @id_produk

        INSERT INTO @hasil
                (no_proposal, id_produk, [status], [message], cek)
        VALUES
                (@no_proposal, -- no_proposal - varchar(9)
                 @id_produk, -- id_produk - varchar(12)
                 NULL, -- STATUS - varchar(1)
                 'ok',
                 0  -- cek - bit
                 )
        
        FETCH NEXT FROM produk_cursor INTO @id_produk  
      END  

      CLOSE produk_cursor  
      DEALLOCATE produk_cursor 
      -- end: sub cursor
    END
  ELSE
    BEGIN
      INSERT INTO @hasil
              (no_proposal, id_produk, [status], [message], cek)
      VALUES
              (@no_proposal, -- no_proposal - varchar(9)
               NULL, -- id_produk - varchar(12)
               NULL, -- STATUS - varchar(1)
               'Tidak ada AJB utk No Proposal '+@no_proposal,
               0  -- cek - bit
               )
    END
  	
  FETCH NEXT FROM list_cursor INTO @no_proposal  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

SELECT * FROM @hasil 
