USE [GrpInsurance]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas. 2017-09-06
CREATE FUNCTION [dbo].[func_marketing_hirarki_polis](@pi_no_polis VARCHAR(11))
RETURNS VARCHAR(200)

AS
BEGIN 

  DECLARE 
    @v_id_marketing VARCHAR(12),
    @po_hasil VARCHAR(200)

  SELECT
  	--@v_id_marketing = COALESCE(Marketing_REN,Marketing)
  	@v_id_marketing = CASE
  	                    WHEN COALESCE(Marketing_REN,'')<>'' THEN Marketing_REN
  	                    ELSE Marketing
  	                  END
  FROM
  	dbo.Polis_Asuransi
  WHERE
    No_Polis = @pi_no_polis
    AND Active_Ya = 1
   
  SELECT @po_hasil = dbo.func_marketing_hirarki(@v_id_marketing)


  RETURN (@po_hasil)
END

