USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[admedika_plan_import]    Script Date: 14/05/2019 09.48.22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
-- ==============================================
-- Author:		Pribadi Pamungkas
-- Create date: 2015-02-24
-- Last modified by Pribadi Pamungkas, 2019-05-14
-- Description:	Plan Import for AdMedika
-- ==============================================
ALTER PROCEDURE [dbo].[admedika_plan_import]
--DECLARE
   @NoProposal VARCHAR(9) --= '2019.0127'
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
 
  -- Buat tabel untuk output/hasil
  DECLARE @hasil TABLE (
                    a VARCHAR(max) NULL,
                    b VARCHAR(max) NULL,
                    c VARCHAR(max) NULL,
                    d VARCHAR(max) NULL,
                    e VARCHAR(max) NULL,
                    f VARCHAR(max) NULL,
                    g VARCHAR(max) NULL,
                    h VARCHAR(max) NULL,
                    i VARCHAR(max) NULL,
                    j VARCHAR(max) NULL,
                    k VARCHAR(max) NULL,
                    l VARCHAR(max) NULL,
                    m VARCHAR(max) NULL,
                    n VARCHAR(max) NULL,
                    o VARCHAR(max) NULL,
                    p VARCHAR(max) NULL,
                    q VARCHAR(max) NULL,
                    r VARCHAR(max) NULL,
                    s VARCHAR(max) NULL,
                    t VARCHAR(max) NULL,
                    u VARCHAR(max) NULL,
                    v VARCHAR(max) NULL,
                    w VARCHAR(max) NULL,
                    x VARCHAR(max) NULL,
                    y VARCHAR(max) NULL,
                    z VARCHAR(max) NULL,
                    proposal varchar (MAX) NULL,
                    produk varchar (MAX) NULL)
 
  -- Buat tabel temporary untuk list plan
  DECLARE @list_plan TABLE ( 
                        id_produk VARCHAR(10),  -- untuk key
                        kelas VARCHAR(10),      -- untuk key
                        rectype VARCHAR(1) NULL,
                        payor_code VARCHAR(7) NULL,
                        plan_id VARCHAR(20) NULL,
                        revision_number VARCHAR(10) NULL,
                        corp_code VARCHAR(10) NULL,
                        effective_date VARCHAR(8) NULL,
                        terminate_date VARCHAR(8) NULL,
                        active_flag VARCHAR(1) NULL,
                        short_name VARCHAR(20) NULL,
                        long_name VARCHAR(50) NULL,
                        reserve_amount VARCHAR(13) NULL,
                        remarks VARCHAR(1000) NULL,
                        policy_no VARCHAR(30) NULL,
                        prorate VARCHAR(1) NULL,
                        frequency_code VARCHAR(2) NULL,
                        limit_code VARCHAR(2) NULL,
                        min_value VARCHAR(9) NULL,
                        max_value VARCHAR(13) NULL,
                        attach_code VARCHAR(15) NULL,
                        family_max_value VARCHAR(13) NULL,
                        client_plan VARCHAR(15) NULL,
                        print_text VARCHAR(30) NULL,
                        divert_coverage_list VARCHAR(50) NULL,
                        benefit_sharing_flag VARCHAR(1) NULL,
                        tmp_sub_kelompok VARCHAR(6) NULL, -- kolom pembantu
                        proposal VARCHAR(9)) -- kolom pembantu
 
  -- Isi tabel list plan
  INSERT INTO @list_plan
          (id_produk,
           kelas,
           rectype,
           payor_code,
           plan_id,
           revision_number,
           corp_code,
           effective_date,
           terminate_date,
           active_flag,
           short_name,
           long_name,
           reserve_amount,
           remarks,
           policy_no,
           prorate,
           frequency_code,
           limit_code,
           min_value,
           max_value,
           attach_code,
           family_max_value,
           client_plan,
           print_text,
           divert_coverage_list,
           benefit_sharing_flag,
           tmp_sub_kelompok,
           proposal)
      SELECT
        pp.Id_Produk,                                             -- id_produk - varchar(10)  -- untuk key
        premp.Kelas,                                              -- kelas - varchar(10)      -- untuk key
        '1' AS RecType,                                           -- rectype - varchar(1)
        --'EQUITY' AS Payor_Code,                                   -- payor_code - varchar(7)
        CASE
          WHEN pa.AdMedika_Ya=1 THEN 'EQUITY'
          ELSE 'ELI_TRD'
        END AS Payor_Code,
        REPLACE(pp.Id_Produk,'.','') + premp.Kelas AS Plan_ID,    -- plan_id - varchar(15)
        '' AS Revision_Number,                                    -- revision_number - varchar(10)
        LEFT(pa.No_Prs, 4) + RIGHT(pa.No_Prs, 4) AS Corp_Code,    -- corp_code - varchar(10)
        CONVERT(VARCHAR(12),pa.Tgl_Mulai,112) AS Effective_Date,  -- effective_date - varchar(8)  -- yyyymmdd
        CONVERT(VARCHAR(12),pa.Tgl_Selesai,112) AS Terminate_Date,-- terminate_date - varchar(8)  -- yyyymmdd
        --CASE                                                    
        --  WHEN (pp.Status='I' AND pp.AdMedika_Ya=1) THEN 'Y'    
        --  ELSE ''                                               
        --END AS Active_Flag,
        'Y',                                                      -- active_flag - varchar(1)
        REPLACE(pp.Id_Produk,'.','') + premp.Kelas AS Short_Name, -- short_name - varchar(20) -- pp.Nama_Pendek
        REPLACE(pp.Id_Produk,'.','') + premp.Kelas AS Long_Name,  -- long_name - varchar(50)  -- pp.Nama_Paket
        '0' AS Reserve_Amount,                                    -- reserve_amount - varchar(13)
        '' AS Remarks,                                            -- remarks - varchar(1000)
        pa.No_Polis AS Policy_No,                                 -- policy_no - varchar(30)
        CASE pp.Santunan_Prop
          WHEN 1 THEN 'Y'
          ELSE 'N'
        END AS Prorate,                                           -- prorate - varchar(1) 
        '8' AS Frequency_Code,                                    -- frequency_code - varchar(2)
        --'1' AS Limit_Code,                                       -- limit_code - varchar(2)                  [ ? --> Masih harus dipastikan ttg kondisinya ] -- modified by Pribadi Pamungklas. 2016-10-10. Tadinya 29
        CASE
          WHEN pp.Hadiah=1 THEN '29'  -- Family and Plan Sharing
          ELSE '1'
        END AS Limit_Code,                                       -- limit_code - varchar(2)
        '0' AS Min_Value,                                         -- min_value - varchar(9)
        CAST(CAST(premp.Benefit AS DECIMAL(13,0)) AS VARCHAR(13)) AS Max_Value,  -- max_value - varchar(13)
        --'' AS Attach_Code,                                        -- attach_code - varchar(15)
        CASE
          WHEN COALESCE(pp.show_card_ya,0)=1 AND prs.GT_OG<>'GT' THEN
            CASE pp.Sub_Kelompok
              WHEN '0301' THEN 'REIMBURSEMENT'  -- Rawat Jalan
              WHEN '0303' THEN 'REIMBURSEMENT'  -- Gigi
              ELSE ''
            END
          ELSE ''
        END AS Attach_Code,                                        -- attach_code - varchar(15) -- modified by Pribadi Pamungklas. 2016-10-10. Tadinya blank
        '' AS Family_Max_Value,                                   -- family_max_value - varchar(13)
        '' AS Client_Plan,                                        -- client_plan - varchar(15)
        '' AS Print_Text,                                         -- print_text - varchar(30)
        --CASE pp.Sub_Kelompok
        --  WHEN '0302' THEN '01'  -- Rawat Inap
        --  WHEN '0301' THEN '02,03'  -- Rawat Jalan
        --  WHEN '0305' THEN '04'  -- Kacamata
        --  WHEN '0303' THEN '05'  -- Gigi
        --  WHEN '0304' THEN '06'  -- Lahir
        --END AS Divert_Coverage_List,                               -- divert_coverage_list - varchar(50)
        
        --CASE
          --WHEN pa.AdMedika_Ya=1 THEN -- untuk Full TPA / cashless
            CASE pp.Sub_Kelompok
              --WHEN '0302' THEN '01'  -- Rawat Inap
              WHEN '0302' THEN  -- Rawat Inap
                CASE
                  WHEN prs.Group_Perusahaan = 23017 THEN '01,06' -- MAP group
                  ELSE
                    CASE
                      WHEN COALESCE(divert_ril.jml,0)>0 THEN '01,06'  -- jika ada divert lahir
                      ELSE '01'
                    END
                END
              WHEN '0301' THEN  -- Rawat Jalan
                CASE 
                  --WHEN COALESCE(divert_rj.jml,0)>0 THEN '02,03'  -- jika ada divert
                  WHEN COALESCE(divert_rjg.jml,0)>0 AND COALESCE(divert_rjl.jml,0)>0 THEN '02,05,03'  -- jika ada divert gigi & lahir
                  WHEN COALESCE(divert_rjg.jml,0)>0 AND COALESCE(divert_rjl.jml,0)=0 THEN '02,05'  -- jika ada divert gigi
                  WHEN COALESCE(divert_rjg.jml,0)=0 AND COALESCE(divert_rjl.jml,0)>0 THEN '02,03'  -- jika ada divert lahir
                  ELSE '02' -- jika tidak ada divert
                END
              WHEN '0305' THEN '04'  -- Kacamata
              WHEN '0306' THEN '07'  -- KB
              WHEN '0303' THEN '05'  -- Gigi
              WHEN '0304' THEN --'06'  -- Lahir
                CASE 
                  WHEN COALESCE(divert_lh.jml,0)>0 THEN '06,03'  -- jika ada divert
                  ELSE '06' -- jika tidak ada divert
                END
              WHEN '0311' THEN '07'  -- Manfaat Khusus
            --END
          --ELSE -- untuk show card
          --  CASE pp.Sub_Kelompok
          --    WHEN '0302' THEN '01'  -- Rawat Inap
          --    WHEN '0301' THEN '02,03'  -- Rawat Jalan
          --    WHEN '0305' THEN '04'  -- Kacamata
          --    WHEN '0303' THEN '05'  -- Gigi
          --    WHEN '0304' THEN '06'  -- Lahir
          --    WHEN '0306' THEN '07'  -- KB
          --    WHEN '0311' THEN '07'  -- Manfaat Khusus
          --  END
        END AS Divert_Coverage_List,                               -- divert_coverage_list - varchar(50)
        CASE  -- If benefit is shared amongst family member then put “Y” else put “N”
          WHEN pp.Hadiah=1 THEN 'Y'  -- Paket Santunan per Keluarga
          ELSE 'N'
        END AS Benefit_Sharing_Flag, 
        pp.Sub_Kelompok,
        @NoProposal -- proposal no
      FROM
        dbo.Polis_Asuransi AS pa INNER JOIN 
        dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN 
        dbo.Produk_Polis AS pp ON pa.No_Proposal = pp.No_Proposal INNER JOIN
        dbo.Premi_Polis AS premp ON pp.Id_Produk = premp.Id_Produk INNER JOIN 
        dbo.Periode_Bayar AS pb ON pa.Periode_Bayar = pb.Kode INNER JOIN 
        dbo.Sub_Kelompok AS sub ON pp.Sub_Kelompok = sub.Sub_Kelompok --INNER JOIN 
        --dbo.Mata_Uang AS valas ON pa.Mata_Uang = valas.Kode
        LEFT JOIN
        (
          SELECT
            COUNT(*) AS jml,
            isp.Id_Produk
          FROM 
            Item_Santunan_Polis AS isp LEFT JOIN
            dbo.Benefit_Polis AS bp ON isp.Id_Produk = bp.Id_Produk AND isp.Kode_Santunan = bp.Kode_Santunan INNER JOIN
            dbo.Item_Santunan AS ism ON isp.Kode_Santunan = ism.Kode_Item INNER JOIN
            dbo.Produk_Polis AS pp ON isp.Id_Produk = pp.Id_Produk
          WHERE
            pp.No_Proposal = @NoProposal
            AND isp.Klaim = 1
            --AND isp.Deskripsi LIKE '%gigi%'
            --AND isp.Kode_Santunan IN ('RJN1','RJG17') -- list apa saja yg masuk, contoh rawat gigi yg masuk di rawat jalan
            AND (
                  isp.Kode_Santunan LIKE 'RJG%'
                  OR isp.Kode_Santunan = 'RJN1'
                  OR isp.Kode_Santunan = 'RJ09'
                )
            AND pp.Sub_Kelompok = '0301'  -- Rawat Jalan
          GROUP BY
            isp.Id_Produk
        ) AS divert_rjg ON pp.Id_Produk = divert_rjg.Id_Produk
        LEFT JOIN
        (
          SELECT
            COUNT(*) AS jml,
            isp.Id_Produk
          FROM 
            Item_Santunan_Polis AS isp LEFT JOIN
            dbo.Benefit_Polis AS bp ON isp.Id_Produk = bp.Id_Produk AND isp.Kode_Santunan = bp.Kode_Santunan INNER JOIN
            dbo.Item_Santunan AS ism ON isp.Kode_Santunan = ism.Kode_Item INNER JOIN
            dbo.Produk_Polis AS pp ON isp.Id_Produk = pp.Id_Produk
          WHERE
            pp.No_Proposal = @NoProposal
            AND isp.Klaim = 1
            --AND isp.Deskripsi LIKE '%pasca melahirkan%'
            AND isp.Kode_Santunan IN ('RJ34','RJ41') -- list apa saja yg masuk, contoh pra & paca melahirkan yg masuk di rawat jalan
            AND pp.Sub_Kelompok = '0301'  -- Rawat Jalan
          GROUP BY
            isp.Id_Produk
        ) AS divert_rjl ON pp.Id_Produk = divert_rjl.Id_Produk

        LEFT JOIN
        (
          SELECT
            COUNT(*) AS jml,
            isp.Id_Produk
          FROM 
            Item_Santunan_Polis AS isp LEFT JOIN
            dbo.Benefit_Polis AS bp ON isp.Id_Produk = bp.Id_Produk AND isp.Kode_Santunan = bp.Kode_Santunan INNER JOIN
            dbo.Item_Santunan AS ism ON isp.Kode_Santunan = ism.Kode_Item INNER JOIN
            dbo.Produk_Polis AS pp ON isp.Id_Produk = pp.Id_Produk
          WHERE
            pp.No_Proposal = @NoProposal
            AND isp.Klaim = 1
            AND isp.Kode_Santunan = 'LH12'
            AND pp.Sub_Kelompok = '0304'-- lahir
          GROUP BY
            isp.Id_Produk
        ) AS divert_lh ON pp.Id_Produk = divert_lh.Id_Produk

        LEFT JOIN
        (
          SELECT
            COUNT(*) AS jml,
            isp.Id_Produk
          FROM 
            dbo.Item_Santunan_Polis AS isp LEFT JOIN
            dbo.Benefit_Polis AS bp ON isp.Id_Produk = bp.Id_Produk AND isp.Kode_Santunan = bp.Kode_Santunan INNER JOIN
            dbo.Item_Santunan AS ism ON isp.Kode_Santunan = ism.Kode_Item INNER JOIN
            dbo.Produk_Polis AS pp ON isp.Id_Produk = pp.Id_Produk
          WHERE
            pp.No_Proposal = @NoProposal
            AND isp.Klaim = 1
            AND isp.Kode_Santunan IN ('RI6216', 'RI6306', 'RI6307', 'RI6308') -- list apa saja yg masuk, contoh melahirkan yg masuk di rawat inap
            AND pp.Sub_Kelompok = '0302'  -- Rawat Inap
          GROUP BY
            isp.Id_Produk
        ) AS divert_ril ON pp.Id_Produk = divert_ril.Id_Produk

      WHERE
        --(pa.Status = 'I')     -- hanya untuk No Proposal yang aktif saja
        --AND (pp.Status = 'I') -- hanya untuk Produk yang aktif saja
        --AND 
        (pa.No_Proposal = @NoProposal)
        --AND pa.Active_Ya = 1 -- hanya polis yg aktif
        AND pp.Status='I' -- hanya produk yg aktif saja
        AND pp.Sub_Kelompok IN ('0301','0302','0303','0304','0305','0306','0308','0311') -- hanya untuk produk Rawat Jalan, Rawat Inap, Rawat Gigi, Lahir, Kacamata, Keluarga Berencana, MCU, Manfaat Khusus
      ORDER BY
        pa.No_Proposal,
        --sub.Urutan,
        pp.Id_Produk,
        premp.Urut
 
 
 
  ------------------------------------------------------
  
  DECLARE @group_perusahaan SMALLINT
  SELECT
    @group_perusahaan = prs.Group_Perusahaan
  FROM
    dbo.Polis_Asuransi AS pa INNER JOIN
    dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
  WHERE
    pa.No_Proposal = @NoProposal
 
  ------------------------------------------------------
  DECLARE @id_produk VARCHAR(10)
  DECLARE @kelas VARCHAR(10)
  DECLARE @sub_kelompok VARCHAR(6)
 
  -- buat cursor dengan isi berupa key dari data pada tabel temporary list plan diatas
  DECLARE list_cursor CURSOR FOR  
  SELECT id_produk, kelas, tmp_sub_kelompok FROM @list_plan 
 
  -- buka cursor
  OPEN list_cursor  
  FETCH NEXT FROM list_cursor INTO @id_produk, @kelas, @sub_kelompok
 
  -- fetch cursor
  WHILE @@FETCH_STATUS = 0  
  BEGIN
 
    DECLARE
      @rectype VARCHAR(1),
      @payor_code VARCHAR(7),
      @plan_id VARCHAR(20),
      @revision_number VARCHAR(10),
      @corp_code VARCHAR(10),
      @effective_date VARCHAR(8),
      @terminate_date VARCHAR(8),
      @active_flag VARCHAR(1),
      @short_name VARCHAR(20),
      @long_name VARCHAR(50),
      @reserve_amount VARCHAR(13),
      @remarks VARCHAR(1000),
      @policy_no VARCHAR(30),
      @prorate VARCHAR(1),
      @frequency_code VARCHAR(2),
      @limit_code VARCHAR(2),
      @min_value VARCHAR(9),
      @max_value VARCHAR(13),
      @attach_code VARCHAR(15),
      @family_max_value VARCHAR(13),
      @client_plan VARCHAR(15),
      @print_text VARCHAR(30),
      @divert_coverage_list VARCHAR(50),
      @benefit_sharing_flag VARCHAR(1),
      @tmp_sub_kelompok VARCHAR(6),
      @proposal VARCHAR(9),
      @produk varchar(10)
                        
 
    SELECT
      @rectype = rectype,
      @payor_code = payor_code,
      @plan_id = plan_id,
      @revision_number = revision_number,
      @corp_code = corp_code,
      @effective_date = effective_date,
      @terminate_date = terminate_date,
      @active_flag = active_flag,
      @short_name = short_name,
      @long_name = long_name,
      @reserve_amount = reserve_amount,
      @remarks = remarks,
      @policy_no = policy_no,
      @prorate = prorate,
      @frequency_code = frequency_code,
      @limit_code = limit_code,
      @min_value = min_value,
      @max_value = max_value,
      @attach_code = attach_code,
      @family_max_value = family_max_value,
      @client_plan = client_plan,
      @print_text = print_text,
      @divert_coverage_list = divert_coverage_list,
      @benefit_sharing_flag = benefit_sharing_flag,
      @tmp_sub_kelompok = tmp_sub_kelompok,
      @proposal = proposal,
      @produk = id_produk
    FROM
      @list_plan
    WHERE
      id_produk = @id_produk
      AND kelas = @kelas
    
    -- start: mapping Coverge Code
    DECLARE @ambil_lahir BIT
    IF EXISTS(SELECT 1 FROM dbo.Produk_Polis WHERE No_Proposal=@NoProposal AND Sub_Kelompok=@tmp_sub_kelompok) -- cek apakah No_Proposal tsb mengambil produk Lahir?
      SET @ambil_lahir = 1
    ELSE
      SET @ambil_lahir = 0
 
    DECLARE @coverage_code VARCHAR(15)  
    SELECT
      @coverage_code = CASE @tmp_sub_kelompok
                         WHEN '0302' THEN '01'  -- Rawat Inap
                         WHEN '0301' THEN '02'  -- Rawat Jalan
                         --WHEN @ambil_lahir=0 AND @tmp_sub_kelompok='0301' THEN '03'  -- Maternity Pre & Post (OP)
                         WHEN '0305' THEN '04'  -- Kacamata
                         WHEN '0303' THEN '05'  -- Gigi
                         WHEN '0304' THEN '06'  -- Lahir
                         WHEN '0306' THEN '07'  -- KB
                         WHEN '0308' THEN '07'  -- Medical Check Up
                         WHEN '0311' THEN '07'  -- Manfaat Khusus
                       END
                       
    DECLARE @loa_desc VARCHAR(50)  
    SELECT
      @loa_desc = CASE @coverage_code
                    WHEN '01' THEN 'RAWAT INAP'
                    WHEN '02' THEN 'RAWAT JALAN'
                    WHEN '03' THEN 'CEK HAMIL'
                    WHEN '04' THEN 'KACAMATA'
                    WHEN '05' THEN 'RAWAT GIGI'
                    WHEN '06' THEN 'PERSALINAN'
                    --WHEN '07' THEN 'KELUARGA BERENCANA'
                    WHEN '07' THEN 
                                CASE @tmp_sub_kelompok
                                  WHEN '0306' THEN 'KELUARGA BERENCANA'  -- KB
                                  WHEN '0307' THEN 'MEDICAL CHECK UP'  -- KB
                                  WHEN '0311' THEN 'MANFAAT KHUSUS'  -- Manfaat Khusus
                                END

                  END
    -- end: mapping Coverge Code
    
    -- definisi 'as charge' menurut bu wiwid ssb:
    -- 1. jika GT: item santunan yg mengandung kata 'maksimum'
    -- 2. jika OG: - benefit ada, nominal 0
    --             - ada kata maksimum
    -- intinya, santunan yg max-nya true berarti as charge
    
    -- cek apakah produknya AS Charge atau Inner Limit
    DECLARE @v_ascharge BIT = 0
    --IF EXISTS(SELECT * FROM dbo.Item_Santunan_Polis WHERE Id_Produk = @id_produk AND Deskripsi LIKE 'Maksimum%')
    --  SET @v_ascharge = 1
    
    -- modified by prib. 2017-04-12
    DECLARE @tmp_aa TINYINT = 0
    SELECT @tmp_aa = COUNT(DISTINCT Bts_Maks) FROM dbo.Item_Santunan_Polis WHERE Id_Produk = @id_produk
    IF @tmp_aa=1
      BEGIN
        SELECT DISTINCT @v_ascharge = Bts_Maks FROM dbo.Item_Santunan_Polis WHERE Id_Produk = @id_produk
      END
    ELSE IF @tmp_aa>=2
      BEGIN
        DECLARE @gtog VARCHAR(2)
        SELECT
        	@gtog = prs.GT_OG
        FROM
        	dbo.Polis_Asuransi AS pa INNER JOIN
        	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
        WHERE
          pa.No_Proposal = @NoProposal
        IF @gtog='GT' --if gt
          BEGIN
            IF EXISTS(SELECT * FROM dbo.Item_Santunan_Polis WHERE Id_Produk = @id_produk AND Deskripsi LIKE 'Maksimum%')
              SET @v_ascharge = 1
          END
        ELSE IF @gtog='OG'  --if og
          BEGIN
            IF EXISTS(SELECT * FROM dbo.Item_Santunan_Polis WHERE Id_Produk = @id_produk AND Deskripsi LIKE 'Maksimum%')
              SET @v_ascharge = 1
            ELSE
              BEGIN
                DECLARE @tmp_benefit NUMERIC(14,0)
                SELECT @tmp_benefit = CAST(SUM(COALESCE(Benefit,0)) AS NUMERIC(14,0)) FROM dbo.Benefit_Polis  WHERE Id_Produk = @id_produk
                IF @tmp_benefit = 0
                  SET @v_ascharge = 1
              END
          END
      END
    
      
    -- 1. insert Plan
    INSERT INTO @hasil
            (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z,proposal,produk)
      SELECT
        @rectype,             -- a - varchar(max)
        @payor_code,          -- b - varchar(max)
        @plan_id,             -- c - varchar(max)
        @revision_number,     -- d - varchar(max)
        @corp_code,           -- e - varchar(max)
        @effective_date,      -- f - varchar(max)
        @terminate_date,      -- g - varchar(max)
        @active_flag,         -- h - varchar(max)
        @short_name,          -- i - varchar(max)
        @long_name,           -- j - varchar(max)
        @reserve_amount,      -- k - varchar(max)
        @remarks,             -- l - varchar(max)
        @policy_no,           -- m - varchar(max)
        @prorate,             -- n - varchar(max)
        @frequency_code,      -- o - varchar(max)
        @limit_code,          -- p - varchar(max)
        @min_value,           -- q - varchar(max)
        @max_value,           -- r - varchar(max)
        @attach_code,         -- s - varchar(max)
        @family_max_value,    -- t - varchar(max)
        @client_plan,         -- u - varchar(max)
        @print_text,          -- v - varchar(max)
        @divert_coverage_list,-- w - varchar(max)
        '',   -- Buffer Amount 
        'N',  -- ASO Only Flag 
        '',    -- PLAN_TYPE
        @proposal,
        @produk
            
    -- 2. Coverage
    INSERT INTO @hasil
            (a, b, c, d, e, f, g, h, i, j, k, l,proposal,produk)
      SELECT
        '2',            -- a - RecType
        @plan_id,       -- b - Plan ID
        @corp_code,     -- c - Corp code
        @coverage_code, -- d - Coverage code
        'Y',            -- e - Active flag
        '',             -- f - Client coverage
        @loa_desc,      -- g - LOA Desc
        '1',           -- h - Limit Code   -- modified by Pribadi Pamungkas, 2015-09-18, berdasar petunjuk: And always use limit code "1"
        '8',            -- i - Frequency code
        @min_value,     -- j - Min value
        @max_value,     -- k - Max value
        @family_max_value,    -- l - Family max value
        @proposal,
        @produk
    
    -- 3. Benefit 
    --INSERT INTO @hasil
    --        (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t,proposal,produk)
    --SELECT
    --  '3',              -- a - RecType
    --  @plan_id,         -- b - Plan_ID
    --  @corp_code,       -- c - Corp_Code
    --  @coverage_code,   -- d - Coverage_Code
    --  --isp.Kode_Santunan,-- e - AdMedika_Benefit_Code
    --  ism.Kode_Admedika_new,-- e - AdMedika_Benefit_Code
    --  'Y',              -- f - Active_Flag
    --  UPPER(LEFT(isp.Deskripsi,250)),    -- g - Condition_Desc
    --  @loa_desc,        -- h - LOA_Desc
    --  isp.Kode_Santunan,-- i - Client_Benefit_Code
    --  @min_value,       -- j - Min_Value
    --  --CASE Limit_Maks
    --  --  WHEN 0 THEN @max_value
    --  --  ELSE CAST(CAST(Prosen_Benefit/100 * @max_value AS DECIMAL(13,0)) AS VARCHAR(13))
    --  --END,
    --  --CAST(CAST(CASE
    --  --  WHEN COALESCE(bp.Benefit,0) = 0 THEN 999999999
    --  --  ELSE bp.Benefit
    --  --END AS INT) AS VARCHAR(12)),              -- k - Max_Value
    --  CASE
    --    WHEN pp.Penggantian=100 THEN CASE
    --                                    --WHEN @v_ascharge=1 THEN '999999999' -- As Charge
    --                                    WHEN @v_ascharge=1 THEN CASE -- modified by Pribadi Pamungkas. 2017-08-28
    --                                                              WHEN @tmp_aa=1 THEN CAST(CAST(bp.Benefit AS INT) AS VARCHAR(12))
    --                                                              ELSE '999999999' -- As Charge
    --                                                            END
    --                                    ELSE CAST(CAST(bp.Benefit AS INT) AS VARCHAR(12)) -- Inner Limit
    --                                 END
    --    ELSE CAST(CAST(pp.Penggantian AS INT) AS VARCHAR(12))
    --  END,              -- k - Max_Value      -- modified by Pribadi Pamungkas 2017-03-17 ticket RQ2017030336
    --  --'1',             -- l - Limit_Code      -- modified by Pribadi Pamungkas 2015-11-03 atas rekomendasi pak Waton, agar dicoba menggunakan 1 (maximum amount)
    --  CASE
    --    WHEN pp.Penggantian=100 THEN '1'
    --    ELSE '7'
    --  END,             -- l - Limit_Code      -- modified by Pribadi Pamungkas 2017-03-17 ticket RQ2017030336
    --  ism.Frequency_Code,  --'8',              -- m - Frequency_Code
    --  '',               -- n - Upgrade_Amount
    --  --'',               -- o - YTD_Limit
    --  CASE
    --    WHEN pp.Penggantian=100 THEN ''
    --    ELSE CAST(CAST(bp.Benefit AS INT) AS VARCHAR(12))
    --  END,               -- o - YTD_Limit      -- modified by Pribadi Pamungkas 2017-03-17 ticket RQ2017030336
    --  '',               -- p - Condition_Type (leave it blank)
    --  --'',               -- q - Multiple_Condition
    --  CASE
    --    WHEN COALESCE(isp.F_Hari,0)=0 AND COALESCE(isp.F_Kali,0)=0 THEN ''
    --    ELSE CASE
    --           WHEN COALESCE(isp.F_Hari,0)>0 THEN '27='+CAST(isp.F_Hari AS VARCHAR(6))+'|F1'
    --           WHEN COALESCE(isp.F_Kali,0)>0 THEN '27='+CAST(isp.F_Kali AS VARCHAR(6))+'|F10'
    --         END
    --  END,
    --  --'',               -- r - Prompt_Divert_Coverage
    --  CASE
    --    WHEN @sub_kelompok='0302' THEN  -- Rawat Inap
    --      CASE
    --        WHEN @group_perusahaan = 23017 THEN -- MAP group
    --          CASE
    --            --RI6204	Melahirkan dengan operasi per tahun
    --            --RI6205	Melahirkan normal per tahun
    --            --RI6214	Keguguran atas pertimbangan medis per tahun
    --            WHEN isp.Kode_Santunan IN ('RI6204','RI6205','RI6214') THEN 'MAT'
    --            ELSE ''
    --          END
    --        ELSE ''
    --      END
    --    WHEN @sub_kelompok='0304' THEN  -- lahir
    --      --CASE 
    --      --  WHEN COALESCE(lahir.jml,0)>0 THEN 'SP'  -- jika ada santunan LH12xx
    --      --  ELSE 'MAT'
    --      --END
    --      CASE
    --        WHEN isp.Kode_Santunan LIKE 'LH12%' THEN 'SP'
    --        ELSE 'MAT'
    --      END
    --    WHEN @sub_kelompok='0301' THEN  -- rawat jalan
    --      CASE
    --        WHEN isp.Kode_Santunan LIKE 'RJG%' THEN 'DENTAL'
    --        WHEN isp.Kode_Santunan IN ('RJ09') THEN 'DENTAL'
    --        WHEN isp.Kode_Santunan IN ('RJ34','RJ41') THEN 'SP'
    --        ELSE 'GP'
    --      END
    --    ELSE ''
    --  END,               -- r - Prompt_Divert_Coverage
    --  '',               -- s - Buffer Amount
    --  @benefit_sharing_flag,                -- t - Benefit Sharing Flag
    --  @proposal,
    --  @produk               
    --FROM 
    --  Item_Santunan_Polis AS isp LEFT JOIN
    --  dbo.Benefit_Polis AS bp ON isp.Id_Produk = bp.Id_Produk AND isp.Kode_Santunan = bp.Kode_Santunan INNER JOIN
    --  dbo.Item_Santunan AS ism ON isp.Kode_Santunan = ism.Kode_Item INNER JOIN
    --  dbo.Produk_Polis AS pp ON isp.Id_Produk = pp.Id_Produk
      
    --  --LEFT JOIN
    --  --(
    --  --  SELECT
    --  --    COUNT(*) AS jml,
    --  --    isp.Id_Produk,
    --  --    pp.No_Proposal
    --  --  FROM 
    --  --    Item_Santunan_Polis AS isp LEFT JOIN
    --  --    dbo.Benefit_Polis AS bp ON isp.Id_Produk = bp.Id_Produk AND isp.Kode_Santunan = bp.Kode_Santunan INNER JOIN
    --  --    dbo.Item_Santunan AS ism ON isp.Kode_Santunan = ism.Kode_Item INNER JOIN
    --  --    dbo.Produk_Polis AS pp ON isp.Id_Produk = pp.Id_Produk
    --  --  WHERE
    --  --    isp.Id_Produk = @id_produk
    --  --    AND pp.Sub_Kelompok = '0304'
    --  --    AND isp.Klaim = 1
    --  --    AND isp.Kode_Santunan LIKE 'LH12%'
    --  --  GROUP BY
    --  --    isp.Id_Produk,
    --  --    pp.No_Proposal
    --  --) AS lahir ON isp.Id_Produk = lahir.Id_Produk
    --WHERE
    --  isp.Id_Produk = @id_produk
    --  AND bp.Kelas = @kelas
    --  AND isp.Klaim = 1 -- added by Pribadi Pamungkas 2015-12-08 atas rekomendasi fahrul (hanya item yg bisa diklaim)
    --ORDER BY
    --  isp.Urutan
 

    DECLARE 
        @b_a VARCHAR(max),
        @b_b VARCHAR(max),
        @b_c VARCHAR(max),
        @b_d VARCHAR(max),
        @b_e VARCHAR(max),
        @b_f VARCHAR(max),
        @b_g VARCHAR(max),
        @b_h VARCHAR(max),
        @b_i VARCHAR(max),
        @b_j VARCHAR(max),
        @b_k VARCHAR(max),
        @b_l VARCHAR(max),
        @b_m VARCHAR(max),
        @b_n VARCHAR(max),
        @b_o VARCHAR(max),
        @b_p VARCHAR(max),
        @b_q VARCHAR(max),
        @b_r VARCHAR(max),
        @b_s VARCHAR(max),
        @b_t VARCHAR(max),
		@b_proposal varchar(MAX),
        @b_produk varchar(MAX),
		@b_limit_maks BIT,
		@temp_limit_maks BIT = 1	-- init
    DECLARE benefit_cursor CURSOR FAST_FORWARD FOR  
    SELECT
      '3',              -- a - RecType
      @plan_id,         -- b - Plan_ID
      @corp_code,       -- c - Corp_Code
      @coverage_code,   -- d - Coverage_Code
      --isp.Kode_Santunan,-- e - AdMedika_Benefit_Code
      ism.Kode_Admedika_new,-- e - AdMedika_Benefit_Code
      'Y',              -- f - Active_Flag
      UPPER(LEFT(isp.Deskripsi,250)),    -- g - Condition_Desc
      @loa_desc,        -- h - LOA_Desc
      isp.Kode_Santunan,-- i - Client_Benefit_Code
      @min_value,       -- j - Min_Value
      --CASE Limit_Maks
      --  WHEN 0 THEN @max_value
      --  ELSE CAST(CAST(Prosen_Benefit/100 * @max_value AS DECIMAL(13,0)) AS VARCHAR(13))
      --END,
      --CAST(CAST(CASE
      --  WHEN COALESCE(bp.Benefit,0) = 0 THEN 999999999
      --  ELSE bp.Benefit
      --END AS INT) AS VARCHAR(12)),              -- k - Max_Value
      CASE
        WHEN pp.Penggantian=100 THEN CASE
                                        --WHEN @v_ascharge=1 THEN '999999999' -- As Charge
                                        WHEN @v_ascharge=1 THEN CASE -- modified by Pribadi Pamungkas. 2017-08-28
                                                                  WHEN @tmp_aa=1 THEN CAST(CAST(bp.Benefit AS INT) AS VARCHAR(12))
                                                                  ELSE '999999999' -- As Charge
                                                                END
                                        ELSE CAST(CAST(bp.Benefit AS INT) AS VARCHAR(12)) -- Inner Limit
                                     END
        ELSE CAST(CAST(pp.Penggantian AS INT) AS VARCHAR(12))
      END,              -- k - Max_Value      -- modified by Pribadi Pamungkas 2017-03-17 ticket RQ2017030336
      --'1',             -- l - Limit_Code      -- modified by Pribadi Pamungkas 2015-11-03 atas rekomendasi pak Waton, agar dicoba menggunakan 1 (maximum amount)
      CASE
        WHEN pp.Penggantian=100 THEN '1'
        ELSE '7'
      END,             -- l - Limit_Code      -- modified by Pribadi Pamungkas 2017-03-17 ticket RQ2017030336
      ism.Frequency_Code,  --'8',              -- m - Frequency_Code
      '',               -- n - Upgrade_Amount
      --'',               -- o - YTD_Limit
      CASE
        WHEN pp.Penggantian=100 THEN ''
        ELSE CAST(CAST(bp.Benefit AS INT) AS VARCHAR(12))
      END,               -- o - YTD_Limit      -- modified by Pribadi Pamungkas 2017-03-17 ticket RQ2017030336
      '',               -- p - Condition_Type (leave it blank)
      --'',               -- q - Multiple_Condition
      CASE
        WHEN COALESCE(isp.F_Hari,0)=0 AND COALESCE(isp.F_Kali,0)=0 THEN ''
        ELSE CASE
               WHEN COALESCE(isp.F_Hari,0)>0 THEN '27='+CAST(isp.F_Hari AS VARCHAR(6))+'|F1'
               WHEN COALESCE(isp.F_Kali,0)>0 THEN '27='+CAST(isp.F_Kali AS VARCHAR(6))+'|F10'
             END
      END,
      --'',               -- r - Prompt_Divert_Coverage
      CASE
        WHEN @sub_kelompok='0302' THEN  -- Rawat Inap
          CASE
            WHEN @group_perusahaan = 23017 THEN -- MAP group
              CASE
                --RI6204	Melahirkan dengan operasi per tahun
                --RI6205	Melahirkan normal per tahun
                --RI6214	Keguguran atas pertimbangan medis per tahun
                WHEN isp.Kode_Santunan IN ('RI6204','RI6205','RI6214') THEN 'MAT'
                ELSE ''
              END
            ELSE ''
          END
        WHEN @sub_kelompok='0304' THEN  -- lahir
          --CASE 
          --  WHEN COALESCE(lahir.jml,0)>0 THEN 'SP'  -- jika ada santunan LH12xx
          --  ELSE 'MAT'
          --END
          CASE
            WHEN isp.Kode_Santunan LIKE 'LH12%' THEN 'SP'
            ELSE 'MAT'
          END
        WHEN @sub_kelompok='0301' THEN  -- rawat jalan
          CASE
            WHEN isp.Kode_Santunan LIKE 'RJG%' THEN 'DENTAL'
            WHEN isp.Kode_Santunan IN ('RJ09') THEN 'DENTAL'
            WHEN isp.Kode_Santunan IN ('RJ34','RJ41') THEN 'SP'
            ELSE 'GP'
          END
        ELSE ''
      END,               -- r - Prompt_Divert_Coverage
      '',               -- s - Buffer Amount
      @benefit_sharing_flag,                -- t - Benefit Sharing Flag
      @proposal,
      @produk,
	  isp.Limit_Maks
    FROM 
      Item_Santunan_Polis AS isp LEFT JOIN
      dbo.Benefit_Polis AS bp ON isp.Id_Produk = bp.Id_Produk AND isp.Kode_Santunan = bp.Kode_Santunan INNER JOIN
      dbo.Item_Santunan AS ism ON isp.Kode_Santunan = ism.Kode_Item INNER JOIN
      dbo.Produk_Polis AS pp ON isp.Id_Produk = pp.Id_Produk
      
      --LEFT JOIN
      --(
      --  SELECT
      --    COUNT(*) AS jml,
      --    isp.Id_Produk,
      --    pp.No_Proposal
      --  FROM 
      --    Item_Santunan_Polis AS isp LEFT JOIN
      --    dbo.Benefit_Polis AS bp ON isp.Id_Produk = bp.Id_Produk AND isp.Kode_Santunan = bp.Kode_Santunan INNER JOIN
      --    dbo.Item_Santunan AS ism ON isp.Kode_Santunan = ism.Kode_Item INNER JOIN
      --    dbo.Produk_Polis AS pp ON isp.Id_Produk = pp.Id_Produk
      --  WHERE
      --    isp.Id_Produk = @id_produk
      --    AND pp.Sub_Kelompok = '0304'
      --    AND isp.Klaim = 1
      --    AND isp.Kode_Santunan LIKE 'LH12%'
      --  GROUP BY
      --    isp.Id_Produk,
      --    pp.No_Proposal
      --) AS lahir ON isp.Id_Produk = lahir.Id_Produk
    WHERE
      isp.Id_Produk = @id_produk
      AND bp.Kelas = @kelas
      AND isp.Klaim = 1 -- added by Pribadi Pamungkas 2015-12-08 atas rekomendasi fahrul (hanya item yg bisa diklaim)
    ORDER BY
      isp.Urutan

    -- buka cursor
    OPEN benefit_cursor  
    FETCH NEXT FROM benefit_cursor INTO @b_a, @b_b, @b_c, @b_d, @b_e, @b_f, @b_g, @b_h, @b_i, @b_j, @b_k, @b_l, @b_m, @b_n, @b_o, @b_p, @b_q, @b_r, @b_s, @b_t, @b_proposal, @b_produk, @b_limit_maks
 
    -- fetch cursor
    WHILE @@FETCH_STATUS = 0  
    BEGIN
	  IF (@b_produk = '2019.00480' AND @temp_limit_maks <> @b_limit_maks)
	    BEGIN

		   DECLARE @hitachi_jumlah VARCHAR(20)
		   SELECT 
				@hitachi_jumlah = CAST(CAST(SUM(bp.Benefit) AS NUMERIC(16,0)) AS VARCHAR(20))
			FROM 
				Item_Santunan_Polis AS isp LEFT JOIN
				dbo.Benefit_Polis AS bp ON isp.Id_Produk = bp.Id_Produk AND isp.Kode_Santunan = bp.Kode_Santunan INNER JOIN
				dbo.Item_Santunan AS ism ON isp.Kode_Santunan = ism.Kode_Item INNER JOIN
				dbo.Produk_Polis AS pp ON isp.Id_Produk = pp.Id_Produk
			WHERE
				isp.Id_Produk = @b_produk
				AND bp.Kelas = @kelas
				AND isp.Klaim = 1
				AND isp.Limit_Maks = 0
				AND COALESCE(isp.Exclude,0) = 0

		   -- 1. Plan
		   INSERT INTO @hasil
            (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z,proposal,produk)
			SELECT
				@rectype,             -- a - varchar(max)
				@payor_code,          -- b - varchar(max)
				'RM-' + @kelas,	--@plan_id,             -- c - varchar(max)
				@revision_number,     -- d - varchar(max)
				@corp_code,           -- e - varchar(max)
				@effective_date,      -- f - varchar(max)
				@terminate_date,      -- g - varchar(max)
				@active_flag,         -- h - varchar(max)
				'RM-' + @kelas,	--@short_name,          -- i - varchar(max)
				'RM-' + @kelas,	--@long_name,           -- j - varchar(max)
				@reserve_amount,      -- k - varchar(max)
				@remarks,             -- l - varchar(max)
				@policy_no,           -- m - varchar(max)
				@prorate,             -- n - varchar(max)
				@frequency_code,      -- o - varchar(max)
				@limit_code,          -- p - varchar(max)
				@min_value,           -- q - varchar(max)
				@hitachi_jumlah,	--@max_value,           -- r - varchar(max)
				@attach_code,         -- s - varchar(max)
				@family_max_value,    -- t - varchar(max)
				@client_plan,         -- u - varchar(max)
				@print_text,          -- v - varchar(max)
				@divert_coverage_list,-- w - varchar(max)
				'',   -- Buffer Amount 
				'N',  -- ASO Only Flag 
				'',    -- PLAN_TYPE
				@proposal,
				@produk

			-- 2. Coverage
			INSERT INTO @hasil
					(a, b, c, d, e, f, g, h, i, j, k, l,proposal,produk)
			  SELECT
				'2',            -- a - RecType
				@plan_id,       -- b - Plan ID
				@corp_code,     -- c - Corp code
				'RM-RI',	--@coverage_code, -- d - Coverage code
				'Y',            -- e - Active flag
				'',             -- f - Client coverage
				'RAWAT INAP-TAMBAHAN',	--@loa_desc,      -- g - LOA Desc
				'1',           -- h - Limit Code   -- modified by Pribadi Pamungkas, 2015-09-18, berdasar petunjuk: And always use limit code "1"
				'8',            -- i - Frequency code
				@min_value,     -- j - Min value
				@hitachi_jumlah,	--@max_value,     -- k - Max value
				@family_max_value,    -- l - Family max value
				@proposal,
				@produk

		   -- 3. Benefit
	       INSERT INTO @hasil
           (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t,proposal,produk)
		   VALUES
		   (@b_a, @b_b, @b_c, 'RM-RI', @b_e, @b_f, @b_g, @b_h, @b_i, @b_j, @b_k, @b_l, @b_m, @b_n, @b_o, @b_p, @b_q, @b_r, @b_s, @b_t, @b_proposal, @b_produk)
		END
	  ELSE
	    BEGIN
	       INSERT INTO @hasil
           (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t,proposal,produk)
		   VALUES
		   (@b_a, @b_b, @b_c, @b_d, @b_e, @b_f, @b_g, @b_h, @b_i, @b_j, @b_k, @b_l, @b_m, @b_n, @b_o, @b_p, @b_q, @b_r, @b_s, @b_t, @b_proposal, @b_produk)
		END

	  SET @temp_limit_maks = @b_limit_maks

      -- next row pada cursor
      FETCH NEXT FROM benefit_cursor INTO @b_a, @b_b, @b_c, @b_d, @b_e, @b_f, @b_g, @b_h, @b_i, @b_j, @b_k, @b_l, @b_m, @b_n, @b_o, @b_p, @b_q, @b_r, @b_s, @b_t, @b_proposal, @b_produk, @b_limit_maks
    END
    CLOSE benefit_cursor  
    DEALLOCATE benefit_cursor 

    -- next row pada cursor
    FETCH NEXT FROM list_cursor INTO @id_produk, @kelas, @sub_kelompok
    
  END  
 
  CLOSE list_cursor  
  DEALLOCATE list_cursor 
 
 
  SELECT
    *
  FROM
    @hasil
 
 
 
 
 
 
 END
