SELECT
	*
FROM
	dbo.Peserta_Produk
WHERE
  No_Proposal = '2015.0552'
  AND No_Peserta = '1991026581-0'
  AND Sub_Kelompok = '0301'

SELECT DISTINCT group_name FROM grouping_polis
SELECT * FROM grouping_polis WHERE no_polis = '1991.0189.0'
SELECT * FROM mapping_item_santunan_polis WHERE kd_tarip = '0804'


INSERT INTO dbo.mapping_item_santunan_polis
        (group_name,
         grade_awal,
         grade_akhir,
         kd_tarip,
         kd_mapping)
SELECT
  'listGT4',
  grade_awal,
  grade_akhir,
  kd_tarip,
  kd_mapping
FROM
	dbo.mapping_item_santunan_polis
WHERE 
  kd_tarip = '0804'


--SELECT dbo.func_get_mapping_item_santunan_polis('1991.0203.1', NULL, '01100')

-- Last Modified: Budi Candra, 2014-06-20
--CREATE FUNCTION [dbo].[func_get_mapping_item_santunan_polis] (
  @pi_no_polis varchar(12), 
  @pi_grade_peserta int, 
  @pi_kd_tarip varchar(10))
RETURNS varchar(10)
AS
BEGIN
  DECLARE 
    @p_hasil varchar(10),
    @p_grade_peserta varchar(5)
    
  IF @pi_grade_peserta <> 0
  BEGIN    
    SET @p_grade_peserta = dbo.func_get_aligned_string('R', CONVERT(varchar(5), @pi_grade_peserta), 2, '0') 
  END
  
  SET @p_grade_peserta = COALESCE(@p_grade_peserta, '*')
  
  -- Jika polis tidak terdapat di dalam grouping
  -- maka gunakan wildcard yang ada di mapping_item_santunan_polis
  IF NOT EXISTS (SELECT 1 FROM grouping_polis WHERE no_polis = @pi_no_polis)
  BEGIN
    IF EXISTS (  
      SELECT TOP 1 *
      FROM mapping_item_santunan_polis
      WHERE
        (group_name = '*') AND
        (kd_tarip = @pi_kd_tarip) AND
        (@p_grade_peserta >= dbo.func_get_aligned_string('R', grade_awal, 2, '0')) AND 
        (@p_grade_peserta <= dbo.func_get_aligned_string('R', grade_akhir, 2, '0'))
    )
    BEGIN
      SELECT 
        @p_hasil = kd_mapping
      FROM mapping_item_santunan_polis
      WHERE
        (group_name = '*') AND
        (kd_tarip = @pi_kd_tarip) AND
        (@p_grade_peserta >= dbo.func_get_aligned_string('R', grade_awal, 2, '0')) AND 
        (@p_grade_peserta <= dbo.func_get_aligned_string('R', grade_akhir, 2, '0'))
    END
    ELSE
    IF EXISTS (  
      SELECT TOP 1 *
      FROM mapping_item_santunan_polis
      WHERE
        (group_name = '*') AND
        (kd_tarip = @pi_kd_tarip) AND
        (grade_awal = '*') AND 
        (grade_akhir = '*')
    )
    BEGIN
      SELECT 
        @p_hasil = kd_mapping
      FROM mapping_item_santunan_polis
      WHERE
        (group_name = '*') AND
        (kd_tarip = @pi_kd_tarip) AND
        (grade_awal = '*') AND 
        (grade_akhir = '*')
    END
  END
  ELSE
  BEGIN
    SELECT
      @p_hasil = mapping_item_santunan_polis.kd_mapping
	  FROM 
	    mapping_item_santunan_polis 
	    INNER JOIN
      grouping_polis 
      ON mapping_item_santunan_polis.group_name = grouping_polis.group_name
    WHERE 
      (grouping_polis.no_polis = @pi_no_polis) AND
      (mapping_item_santunan_polis.kd_tarip = @pi_kd_tarip) AND
      (
        (
          (@p_grade_peserta >= dbo.func_get_aligned_string('R', mapping_item_santunan_polis.grade_awal, 2, '0')) AND
          (@p_grade_peserta <= dbo.func_get_aligned_string('R', mapping_item_santunan_polis.grade_akhir, 2, '0'))
        ) OR
        (@p_grade_peserta = '*')
      )
  END

  RETURN @p_hasil
END


GO
