DECLARE --parameter input
  @pi_no_ref VARCHAR(11) = '2015.085986',
  @pi_no_proposal VARCHAR(9) = '2014.0784',
  @pi_cabang VARCHAR(8) = '0027'
  
----
DECLARE 
  @no_proposal VARCHAR(9),
  @no_peserta VARCHAR(12),
  @keterangan VARCHAR(30)
DECLARE list_cursor CURSOR FOR
SELECT
  No_Proposal,
	No_Peserta,
	Keterangan
FROM
	dbo.Peserta
WHERE
  No_Proposal = @pi_no_proposal
  AND Grade >= 10
  AND Grade <= 22
  AND Cabang = @pi_cabang
  AND (Status = 'I' OR Keterangan LIKE '%LAPSE AUTO 2015-07-01')
  --AND No_Peserta NOT IN ('1996006923-0','1996007933-0') -- khusus 2014.0783
ORDER BY
	No_Peserta

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

WHILE @@FETCH_STATUS = 0  
BEGIN

  --PRINT @keterangan
  DECLARE
    @keterangan_asal VARCHAR(30),
    @keterangan_new VARCHAR(30)
  
  SET @keterangan_asal = COALESCE(@keterangan,'')
  IF LEN(@keterangan_asal)>0
    SET @keterangan_new = @keterangan_asal + '. ' + 'LAPSE AUTO 2015-07-01'
  ELSE
    SET @keterangan_new = 'LAPSE AUTO 2015-07-01'
  
   
  UPDATE
    dbo.Peserta
  SET
    Status = 'O',
    Keterangan = @keterangan_new
  WHERE
    No_Proposal = @no_proposal
    AND No_Peserta = @no_peserta
  
  UPDATE
    pp
  SET
    Tgl_Ubah = '2015-07-01',
    Status = 'G'
  FROM
	  dbo.Peserta AS p INNER JOIN
	  dbo.Peserta_Produk AS pp ON p.No_Proposal = pp.No_Proposal
	                              AND p.No_Peserta = pp.No_Peserta
  WHERE
    p.No_Proposal = @pi_no_proposal
    AND p.No_Peserta =  @no_peserta

  
  PRINT @no_proposal + ' ' + @no_peserta

  FETCH NEXT FROM list_cursor INTO @no_proposal, @no_peserta, @keterangan
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 


--------------------------------------------------------------------------------------------

--SELECT
--	*
--FROM
--	dbo.Peserta
--WHERE
--  No_Proposal = @pi_no_proposal
--  AND Grade >= 10
--  AND Grade <= 22
--  AND Cabang = @pi_cabang
--  AND No_Peserta NOT IN ('1996006923-0','1996007933-0')

--SELECT
--	*
--FROM
--	dbo.Peserta_Produk
--WHERE
--  No_Proposal = '2014.0783'
--  AND No_Peserta = '1996000553-0'

--UPDATE
--  dbo.Peserta
--SET
--  Keterangan = 'LAPSE AUTO 2015-07-01'
--WHERE
--  No_Proposal = '2014.0782'
--  AND No_Peserta = '1991004247-0'

--------------------

--UPDATE
--  dbo.Note_Terbit_Master
--SET
--  Status = 'I'
--WHERE
--  No_Ref IN ('2015.085985', '2015.085984', '2015.085986')

SELECT
	*
FROM
	dbo.Note_Terbit_Master
WHERE
  No_Ref IN ('2015.085985', '2015.085984', '2015.085986')

--UPDATE
--  dbo.Note_Terbit_Master
--SET
--  No_Note = '085986/CN/GT/XII/2015'
--WHERE
--  No_Ref = '2015.085986'