USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[admedika_plan_import]    Script Date: 09/21/2015 13:43:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
-- ==============================================
-- Author:		Pribadi Pamungkas
-- Create date: 2015-02-24
-- Last modified by Pribadi Pamungkas, 2015-09-18
-- Description:	Plan Import for AdMedika
-- ==============================================
ALTER PROCEDURE [dbo].[admedika_plan_import]
   @NoProposal VARCHAR(9)
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(15) 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,
                        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,
           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)
        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)
        '29' AS Limit_Code,                                       -- limit_code - varchar(2)                  [ ? --> Masih harus dipastikan ttg kondisinya ]
        '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)
        '' 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)
        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
      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 AdMedika
        AND pp.Status='I' -- hanya produk yg aktif saja
      ORDER BY
        pa.No_Proposal,
        sub.Urutan,
        premp.Urut
 
 
 
  ------------------------------------------------------
  DECLARE @id_produk VARCHAR(10)
  DECLARE @kelas VARCHAR(10)
 
  -- buat cursor dengan isi berupa key dari data pada tabel temporary list plan diatas
  DECLARE list_cursor CURSOR FOR  
  SELECT id_produk, kelas FROM @list_plan 
 
  -- buka cursor
  OPEN list_cursor  
  FETCH NEXT FROM list_cursor INTO @id_produk, @kelas
 
  -- fetch cursor
  WHILE @@FETCH_STATUS = 0  
  BEGIN
 
    DECLARE
      @rectype VARCHAR(1),
      @payor_code VARCHAR(7),
      @plan_id VARCHAR(15),
      @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),
      @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,
      @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
                       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'
                  END
    -- end: mapping Coverge Code
      
    -- 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
      '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
      '29',             -- l - Limit_Code
      '8',              -- m - Frequency_Code
      '',               -- n - Upgrade_Amount
      '',               -- o - YTD_Limit
      '',               -- p - Condition_Type (leave it blank)
      '',               -- q - Multiple_Condition
      '',               -- r - Prompt_Divert_Coverage
      '',               -- s - Buffer Amount
      '',                -- 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
    WHERE
      isp.Id_Produk = @id_produk
      AND bp.Kelas = @kelas
    ORDER BY
      isp.Urutan
 
 
    -- next row pada cursor
    FETCH NEXT FROM list_cursor INTO @id_produk, @kelas  
    
  END  
 
  CLOSE list_cursor  
  DEALLOCATE list_cursor 
 
 
  SELECT
    *
  FROM
    @hasil
 
 
 
 
 
 
 END