DECLARE
  @no_proposal VARCHAR(9),
  @no_prs VARCHAR(9),
  @no_peserta VARCHAR(12)

DECLARE list_cursor CURSOR FOR  
SELECT
  pa.No_Proposal,
	prs.No_Prs,
	pst.No_Peserta
FROM
	dbo.Polis_Asuransi AS pa INNER JOIN
	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
	dbo.Peserta AS pst ON pa.No_Proposal = pst.No_Proposal
WHERE
  pa.Status = 'I' -- polis aktif saja
  AND prs.Group_Perusahaan = 23017	-- MITRA ADIPERKASA GROUP
  AND pst.Status = 'I'  -- peserta yg aktif saja
  --AND pst.Cabang IS NOT NULL -- peserta yang ada cabangnya
  AND COALESCE(pst.Cabang,'') <> '' -- peserta yang ada cabangnya

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_proposal, @no_prs, @no_peserta

WHILE @@FETCH_STATUS = 0  
BEGIN  

  -- jika cabang peserta tidak ditemukan di cabang perusahaan, 
  IF NOT EXISTS(
                SELECT
  	              *
                FROM
  	              dbo.Cabang
                WHERE
                  No_Prs = @no_prs
                  AND Kode = (SELECT Cabang FROM dbo.Peserta WHERE No_Proposal = @no_proposal AND No_Peserta = @no_peserta)
               )
      -- maka hapus cabang peserta tsb
      UPDATE
        dbo.Peserta
      SET
        Cabang = NULL
      WHERE
        No_Proposal = @no_proposal 
        AND No_Peserta = @no_peserta
    
  FETCH NEXT FROM list_cursor INTO @no_proposal, @no_prs, @no_peserta  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 