USE [STAGING]
GO
/****** Object:  StoredProcedure [dbo].[proc_conv_10_1]    Script Date: 08/15/2014 20:43:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by: Pribadi Pamungkas
-- note: kolom/field yang belum selesai:
--       + SINSTGRP - varchar(20)   => ?
--       + BPAYINST - char(1)       => ?
--       + IINSTSEQ - int           => ?
              
ALTER PROCEDURE [dbo].[proc_conv_10_1]
   @pi_conversion_date  DATETIME
  ,@po_message          VARCHAR(100) OUTPUT
AS
BEGIN 
 
    ---------------------------------------------------------------------------------------------------------------------------------
    --- START --- Insert to table ---
    ---------------------------------------------------------------------------------------------------------------------------------
    -----------------------------------------------------------------
    PRINT IndInsuranceConv.dbo.func_DateTimeToISODateTimeStr(GETDATE()) + ': ' + 'insert into PLS.CLXCLMREG : started'
    -----------------------------------------------------------------
    INSERT INTO PLS.CLXCLMREG
            ( TMODDT ,
              SCLMNO ,
              SCLIENTID ,
              SNTFYNAME ,
              DNTFYDOB ,
              BAPPDD ,
              BAPPDEATH ,
              BAPPHEALTH ,
              BAPPPPD ,
              BAPPTPD ,
              SCAUSECODE ,
              SCAUSEGROUP ,
              SASSESSOR ,
              SCLMREASON ,
              SPAYORREF ,
              DEVENTDT ,
              DNTFYDT ,
              SNTFYREFNO ,
              SOLDICNO ,
              SNTFYREFTYPE ,
              SPROPOSALNO ,
              SSERVBRCH ,
              SINSTGRP ,
              BRECFRRI ,
              DREGDT ,
              SNTFYREL ,
              SPACODE ,
              FPAPERCENT ,
              DACCDT ,
              BADDPYMT ,
              BWEEKINDM ,
              BPAYINST ,
              BFUNERALEXPBEN ,
              BEXCALATEFLG ,
              BGUARANTEELTT ,
              BOUTSTANDPREM ,
              BJETCLAIM ,
              BAPPLT ,
              BAPPGG ,
              IINSTSEQ ,
              BINDEBT ,
              SADD1 ,
              SADD2 ,
              SADD3 ,
              SADD4 ,
              SCITYCODE ,
              SPOSTCODE ,
              SCOUNTRYCODE ,
              SDOC ,
              SHOSP ,
              SNTFYGENDER ,
              SSTATECODE ,
              SPLACEACCCDE ,
              SDIAGTYPE ,
              SREMARK ,
              SREMARKEX ,
              SSANCTIONMSG ,
              SSANCTIONREMARK ,
              DNEXTANNDT ,
              SRMCAPRVID ,
              DRMCAPRVDT ,
              SRMCSTAT ,
              SOPID ,
              tmp_no_polis ,
              tmp_id_polis
            )
   SELECT    DISTINCT
              @pi_conversion_date AS TMODDT,    -- TMODDT - datetime2
              k.id_klaim AS SCLMNO,             -- SCLMNO - varchar(20)
              pp.id_pemegang AS SCLIENTID,      -- SCLIENTID - varchar(20)
              a.nama_agen AS SNTFYNAME,         -- SNTFYNAME - varchar(80)
              a.tgl_lahir AS DNTFYDOB,          -- DNTFYDOB - date
              CASE k.id_jenis_klaim
                  WHEN '05' THEN 'Y'
                  ELSE 'N'
              END AS BAPPDD,                    -- BAPPDD - char(1) --> Dread Disease Claim (Crisis)
              CASE k.id_jenis_klaim
                  WHEN '04' THEN 'Y'
                  ELSE 'N'
              END AS BAPPDEATH,                 -- BAPPDEATH - char(1)  --> Death Claim
              CASE k.id_jenis_klaim
                  WHEN '02' THEN 'Y'
                  ELSE 'N'
              END AS BAPPHEALTH,                -- BAPPHEALTH - char(1) --> Health Claim
              'N' AS BAPPPPD,                   -- BAPPPPD - char(1)  --> Partial Permanent Disability
              CASE k.id_jenis_klaim
                  WHEN '01' THEN 'Y'
                  ELSE 'N'
              END AS BAPPTPD,                   -- BAPPTPD - char(1)  --> Total Permanent Disability. --> menurut kesepakatan, semua jenis klaim Cacat diasumsikan sebagai TPD
              tmcoc.id_cause_of_claim AS SCAUSECODE, -- SCAUSECODE - varchar(10)
              NULL AS SCAUSEGROUP,              -- SCAUSEGROUP - varchar(80)
              COALESCE(k.change_by, k.create_by) AS SASSESSOR, -- SASSESSOR - varchar(80)
              NULL AS SCLMREASON,               -- SCLMREASON - varchar(200)
              NULL AS SPAYORREF,                -- SPAYORREF - varchar(20)           -- Default to Blank
              CASE
                WHEN k.id_jenis_klaim='04' AND k.tgl_meninggal IS NOT NULL THEN k.tgl_meninggal
                WHEN k.tgl_kuitansi IS NULL AND k.tgl_meninggal IS NULL THEN k.tgl_pengajuan
                ELSE k.tgl_kuitansi
              END AS DEVENTDT,                  -- DEVENTDT - date
              COALESCE(k.tgl_kuitansi, k.tgl_pengajuan) AS DNTFYDT, -- DNTFYDT - date
              a.id_agen AS SNTFYREFNO,          -- SNTFYREFNO - varchar(20)
              NULL AS SOLDICNO,                 -- SOLDICNO - varchar(10)             -- Default to Blank
              a.id_jenis_identitas AS SNTFYREFTYPE, -- SNTFYREFTYPE - varchar(20)
              NULL AS SPROPOSALNO,              -- SPROPOSALNO - varchar(20)
              --p.id_unit_pelayanan_korespondensi AS SSERVBRCH, -- SSERVBRCH - varchar(10)
              up.kode_pelayanan AS SSERVBRCH, -- SSERVBRCH - varchar(10)                                         -- mdfy zai 2014-06-10 yang di pakai adalah kode_pelayanan
              NULL , -- SINSTGRP - varchar(20)  ? belum tau diisi apa ?
              'N' AS BRECFRRI,                  -- BRECFRRI - char(1)
              k.tgl_pengajuan AS DREGDT,        -- DREGDT - date
              'OTHERS' AS SNTFYREL,             -- SNTFYREL - varchar(80)
              NULL AS SPACODE,                  -- SPACODE - varchar(80)
              0.00 AS FPAPERCENT,               -- FPAPERCENT - numeric
              CASE k.id_jenis_klaim
                  WHEN '03' THEN k.tgl_kuitansi
                  ELSE NULL
              END AS DACCDT,                    -- DACCDT - date
              'N' AS BADDPYMT,                  -- BADDPYMT - char(1)
              'N' AS BWEEKINDM,                 -- BWEEKINDM - char(1)
              'Y' , -- BPAYINST - char(1)        ? belum tau diisi apa ?
              'N' AS BFUNERALEXPBEN,            -- BFUNERALEXPBEN - char(1)
              'N' AS BEXCALATEFLG,              -- BEXCALATEFLG - char(1)
              'N' AS BGUARANTEELTT,             -- BGUARANTEELTT - char(1)
              'N' AS BOUTSTANDPREM,             -- BOUTSTANDPREM - char(1)
              'N' AS BJETCLAIM,                 -- BJETCLAIM - char(1)
              'N' AS BAPPLT,                    -- BAPPLT - char(1)
              'N' AS BAPPGG,                    -- BAPPGG - char(1)
              0 , -- IINSTSEQ - int             ? belum tau diisi apaan ?
              'N' AS BINDEBT,                   -- BINDEBT - char(1)
              --LEFT(almt.alamat, 40) AS SADD1,   -- SADD1 - varchar(40)
              --NULL AS SADD2,                    -- SADD2 - varchar(40)
              --NULL AS SADD3,                    -- SADD3 - varchar(40)
              --NULL AS SADD4,                    -- SADD4 - varchar(40)
              --LEFT(almt.kota, 20) AS SCITYCODE, -- SCITYCODE - varchar(20)
              --almt.id_kd_pos AS SPOSTCODE,      -- SPOSTCODE - varchar(10)
              --almt.id_negara AS SCOUNTRYCODE,   -- SCOUNTRYCODE - varchar(20)
              almt.SADDR1 AS SADD1,             -- SADD1 - varchar(100)
              almt.SADDR2 AS SADD2,             -- SADD2 - varchar(100)
              almt.SADDR3 AS SADD3,             -- SADD3 - varchar(100)
              almt.SADDR4 AS SADD4,             -- SADD4 - varchar(40)
              almt.SCITYCODE AS SCITYCODE,      -- SCITYCODE - varchar(50)
              almt.SPOSTCODE AS SPOSTCODE,      -- SPOSTCODE - varchar(10)
              almt.SCTYCODE AS SCOUNTRYCODE,    -- SCOUNTRYCODE - varchar(20)
              COALESCE(kkd.change_by,kkd.create_by) AS SDOC, -- SDOC - varchar(80)
              NULL AS SHOSP,                    -- SHOSP - varchar(10)
              tmg.dfs_code AS SNTFYGENDER,      -- SNTFYGENDER - varchar(10)
              --almt.id_negara AS SSTATECODE,     -- SSTATECODE - varchar(20)
              almt.SSTATECODE AS SSTATECODE,    -- SSTATECODE - varchar(20)
              NULL AS SPLACEACCCDE,             -- SPLACEACCCDE - varchar(128)
              diag.CLMDIAGCODE AS SDIAGTYPE,    -- SDIAGTYPE - varchar(10)
              k.keterangan AS SREMARK,          -- SREMARK - varchar(1000)
              NULL AS SREMARKEX,                -- SREMARKEX - varchar(1500)
              NULL AS SSANCTIONMSG,             -- SSANCTIONMSG - varchar(240)
              NULL AS SSANCTIONREMARK,          -- SSANCTIONREMARK - varchar(240)
              NULL AS DNEXTANNDT,               -- DNEXTANNDT - date
              NULL AS SRMCAPRVID,               -- SRMCAPRVID - varchar(10)
              NULL AS DRMCAPRVDT,               -- DRMCAPRVDT - date
              NULL AS SRMCSTAT,                 -- SRMCSTAT - varchar(10)
              'CONV' AS SOPID,                  -- SOPID - varchar(10)
              k.no_polis,
              IndInsuranceConv.dbo.func_get_id_polis_per_tanggal(k.no_polis,k.tgl_pengajuan,0) AS id_polis
    FROM
        IndInsuranceConv.dbo.klaim AS k
        INNER JOIN
            ( SELECT DISTINCT 
                  id_polis AS id_polis_curr,
                  no_polis AS no_polis_curr,
                  id_status_polis AS id_status_polis_curr
              FROM STAGING.dbo.tmp_polis_current
            ) AS pc
          ON k.no_polis=pc.no_polis_curr
          
        INNER JOIN tmp_mapping_diagnosa   AS diag
          ON k.id_diagnosa=diag.id_diagnosa
          
        LEFT JOIN dbo.tmp_mapping_cause_of_claim  AS tmcoc 
          ON diag.CLMDIAGCODE=tmcoc.id_diagnosa
          
        LEFT JOIN IndInsuranceConv.dbo.klaim_keterangan_dokter AS kkd
          ON kkd.id_klaim=k.id_klaim
          
        INNER JOIN IndInsuranceConv.dbo.polis   AS p
          ON p.id_polis=pc.id_polis_curr
          
        INNER JOIN IndInsuranceConv.dbo.polis_pemegang  AS pp
          ON pp.id_polis=p.id_polis
          
        LEFT JOIN IndInsuranceConv.dbo.agen   AS a
          ON p.id_agen=a.id_agen
          
        --OUTER APPLY
        --    ( SELECT TOP 1 
        --          id_agen,
        --          id_jenis_alamat,
        --          alamat,
        --          no_rt,
        --          no_rw,
        --          kelurahan,
        --          kecamatan,
        --          kota,
        --          id_kd_pos,
        --          id_provinsi,
        --          id_negara,
        --          telp,
        --          fax
        --      FROM IndInsuranceConv.dbo.agen_alamat 
        --      WHERE a.id_agen=IndInsuranceConv.dbo.agen_alamat.id_agen
        --     ) AS almt
             
        OUTER APPLY
            ( SELECT TOP 1 
                client.SCLIENTID, -- id_agen
                client.SNAME,
                client.tmp_id_jenis_client,
                ca.SADDR1,
                ca.SADDR2,
                ca.SADDR3,
                ca.SADDR4,
                ca.SCITYCODE,
                ca.SPOSTCODE,
                ca.SSTATECODE,
                ca.SCTYCODE
              FROM 
                PLS.CWMCLIENT AS client
                LEFT JOIN PLS.CWMCLIENTADD AS ca
                ON client.LSEQID = ca.LFKCLIENTMASTER
              WHERE
                client.tmp_id_jenis_client = 7 
                AND a.id_agen=client.SCLIENTID
             ) AS almt
             
        LEFT JOIN STAGING.dbo.tmp_mapping_gender  AS tmg
          ON tmg.eli_code=a.id_jenis_kelamin
        
        INNER JOIN IndInsuranceConv.dbo.polis_plan  AS polis_plan
          ON polis_plan.id_polis=IndInsuranceConv.dbo.func_get_id_polis_per_tanggal(k.no_polis,k.tgl_pengajuan,0) AND polis_plan.aktif_ya=1
        
        LEFT JOIN IndInsuranceConv.dbo.unit_pelayanan   AS up   -- add zai 2014-06-10 join untuk ambil kode_pelayanan
          ON p.id_unit_pelayanan_korespondensi = up.id_unit_pelayanan
          
        INNER JOIN IndInsuranceConv.dbo.transaksi   AS t        -- add by prib 2014-06-24
          ON t.no_referensi = k.id_klaim 
          
        INNER JOIN IndInsuranceConv.dbo.transaksi_lunas   AS tl 
          ON t.id_transaksi = tl.id_transaksi
        
    WHERE 
        polis_plan.uang_pertanggungan>0
        AND k.status = 'I'  -- by Prib, 2014-06-18
        AND t.id_jenis_transaksi = '061'  -- transaksi pembayaran klaim
        AND t.status = 'I' -- sudah dibayar (ada di tabel transaksi_lunas)
        
        
    -----------------------------------------------------------------
    IF @@ERROR <> 0
    BEGIN 
      SET @po_message = 'Error on inserting data to PLS.CLXCLMREG'
      RETURN -1
    END
    -----------------------------------------------------------------   
    PRINT IndInsuranceConv.dbo.func_DateTimeToISODateTimeStr(GETDATE()) + ': ' + 'insert into PLS.CLXCLMREG : finished'
    SET @po_message = 'inserting data to PLS.CLXCLMREG success'
    -----------------------------------------------------------------    
            
    ---------------------------------------------------------------------------------------------------------------------------------
    --- END --- Insert to table ---
    ---------------------------------------------------------------------------------------------------------------------------------        


END