-- STAGING at 192.168.13.100

SELECT
i.id_plan,
pp.id_plan,

  k.id_klaim,
  NULL AS SGROUP,               -- SGROUP - varchar(20)
  0.00 AS FPERCENTAGE,             -- FPERCENTAGE - numeric
  NULL AS STIER,                -- STIER - varchar(20)
  ccp.LSEQID AS LFKCLXCLMPOL,   -- LFKCLXCLMPOL - bigint
  jk.dfs_code AS SCLMTYPE,      -- SCLMTYPE - varchar(10)
  cpc.SLIFENO AS SLIFENO,       -- SLIFENO - varchar(2)
  cpd.DPOLCOMNDT AS DEFFDT,     -- DEFFDT - date
  i.id_plan AS SPLANCODE,       -- SPLANCODE - varchar(10)
  '', -- SBENCOMP - varchar(10)  ? dfs to update ?
  cpd.SPOLSTAT AS SSTATUS,      -- SSTATUS - varchar(10)
  cpc.FCOVERUNIT AS FCOVERUNIT, -- FCOVERUNIT - numeric
  CAST(k.total_dibayar AS NUMERIC(19, 2)) AS FGROSSTOT, -- FGROSSTOT - numeric
  ccp.FNETTOT AS FACTAMTPAID,   -- FACTAMTPAID - numeric
  k.otorisasi_by AS SADMITBY,   -- SADMITBY - varchar(20)
  k.otorisasi_date AS DADMITDT, -- DADMITDT - date
  s.dfs_code AS SBENCOMPSTAT,   -- SBENCOMPSTAT - varchar(10)
  NULL AS SREJTYPE,             -- SREJTYPE - varchar(80)
  NULL AS SPAYVOUNO,            -- SPAYVOUNO - varchar(10)
  NULL AS SPAYNO,               -- SPAYNO - varchar(10)
  ccp.FNETTOT AS FPAYAMT,       -- FPAYAMT - numeric
  l.tgl_bayar AS DPAYDT,        -- DPAYDT - date
  'Y' AS BPAYSTAT,              -- BPAYSTAT - char(1)
  ccr.SCAUSECODE AS SCAUSECODE, -- SCAUSECODE - varchar(10)
  ccr.SDIAGTYPE AS SDAICODE,    -- SDAICODE - varchar(10)
  NULL AS SREASON,              -- SREASON - varchar(60)
  NULL AS SADDPREPYMT,          -- SADDPREPYMT - varchar(5)
  CASE WHEN i.id_plan IN (SELECT
                            id_plan
                          FROM
                            IndInsuranceConv.dbo.plan_asuransi
                          WHERE
                            deskripsi LIKE '%waiver%') THEN 'Y'
       ELSE 'N'
  END AS BWAIVER,               -- BWAIVER - char(1)
  dbo.func_tgl_ketemu_tanggal(cpd.DPOLCOMNDT, ccr.DEVENTDT) AS IBENCOMPDUR, -- IBENCOMPDUR - int
  cpc.SPLANSTAT AS SPLANSTAT,   -- SPLANSTAT - varchar(10)
  NULL, -- SPREMIND - varchar(10)  ? dfs update ?
  cpc.SPREMIND AS SPLANPREMIND, -- SPLANPREMIND - varchar(10)
  'CONV' AS SOPID,               -- SOPID - varchar(10)
          
  -------------------------------------------------
  i.id_plan_dasar AS tmp_id_plan_dasar,
  i.id_plan AS tmp_id_plan,
  cpd.tmp_id_valuta AS tmp_id_valuta,
  cpd.tmp_id_periode_bayar AS tmp_id_periode_bayar,
  cpd.tmp_id_jenis_kelamin AS tmp_id_jenis_kelamin,
  pp.kelas AS tmp_kelas,
  '' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(pp.risiko, '[', ''), ']', ''), '+', ''), 'AB 1', 'AB'), 2, ''), 0, ''), '*', ''), ' ', '') + '' AS tmp_risiko,
  cpd.SPOLNO AS tmp_no_polis,
  -------------------------------------------------
  ccr.tmp_id_polis
FROM
  PLS.CLXCLMREG AS ccr 
  INNER JOIN 
  PLS.CWMPOLDET AS cpd 
  ON cpd.SPOLNO = ccr.tmp_no_polis 
  INNER JOIN
  PLS.CWMPOLCOV AS cpc 
  ON cpd.SPOLNO = cpc.tmp_no_polis 
     --AND cpd.SPRODUCTCODE=cpc.tmp_id_plan 
     AND cpd.tmp_id_plan=cpc.tmp_id_plan 
     AND cpc.SPLANCODE NOT IN ('POLFEE', 'STAMPDUTY')
  INNER JOIN
  PLS.CLXCLMPOL AS ccp 
  ON ccr.LSEQID = ccp.LFKCLXCLMREG 
  INNER JOIN
  IndInsuranceConv.dbo.klaim AS k 
  ON ccr.SCLMNO = k.id_klaim 
  INNER JOIN
  IndInsuranceConv.dbo.item_klaim AS i 
  ON k.id_item_klaim = i.id_item_klaim 
  INNER JOIN
  IndInsuranceConv.dbo.transaksi AS t 
  ON k.id_klaim = t.no_referensi 
  INNER JOIN
  ( SELECT id_transaksi, 
           MAX(tgl_bayar) AS tgl_bayar 
    FROM IndInsuranceConv.dbo.transaksi_lunas 
    GROUP BY id_transaksi
  ) AS l 
  ON t.id_transaksi = l.id_transaksi 
  INNER JOIN
  dbo.tmp_mapping_status_klaim AS s 
  ON k.status = s.eli_code 
  INNER JOIN
  dbo.tmp_mapping_jenis_klaim AS jk 
  ON k.id_jenis_klaim = jk.eli_code
  INNER JOIN
  dbo.tmp_polis_current AS tpc
  ON cpd.SPOLNO = tpc.no_polis
  INNER JOIN
  IndInsuranceConv.dbo.polis_plan AS pp
  ON ccr.tmp_id_polis = pp.id_polis
     --AND i.id_plan = pp.id_plan
     AND pp.aktif_ya = 1
WHERE
  t.id_jenis_transaksi = '061'
  AND 
  --k.id_klaim IN ('2011.0000492', '2011.0000493', '2014.0001263', '2014.0001444', '2014.0001351', '2014.0001626', '2014.0001688', '2014.0001795', '2014.0001972', '2014.000202', '2014.0002237', '2014.0002421', '2014.000243', '2014.0002576', '2014.0002577', '2014.0002063', '2014.0001941', '2013.0000475')
  k.id_klaim = '2013.0000475'
ORDER BY
  k.id_klaim




--SELECT
--	*
--FROM
--	PLS.CLXCLMREG AS c
--WHERE
--  c.SCLMNO = '2013.0000475'

--SELECT
--	*
--FROM
--	PLS.CLXCLMPOL
--WHERE
--  LFKCLXCLMREG = 22463

--SELECT
--	*
--FROM
--	PLS.CLXCLMPOLBEN AS c
--WHERE
--  c.LFKCLXCLMPOL = 135531




SELECT
	*
FROM
	IndInsuranceConv.dbo.klaim AS k
WHERE
  k.id_klaim = '2013.0000475'
	
SELECT
	*
FROM
	IndInsuranceConv.dbo.item_klaim AS ik
WHERE
  ik.id_item_klaim = '01151106'

SELECT
	*
FROM
	IndInsuranceConv.dbo.polis_plan
WHERE
  id_polis = IndInsuranceConv.dbo.func_get_id_polis_per_tanggal('09.04567.3','2013-03-04',0) -- k.no_polis,k.tgl_pengajuan,0
----

SELECT
	*
FROM
	IndInsuranceConv.dbo.klaim AS k
WHERE
  k.id_klaim = '2014.0001953'
	
SELECT
	*
FROM
	IndInsuranceConv.dbo.item_klaim AS ik
WHERE
  ik.id_item_klaim = '02111121'

SELECT
	*
FROM
	IndInsuranceConv.dbo.polis_plan
WHERE
  id_polis = IndInsuranceConv.dbo.func_get_id_polis_per_tanggal('14.03354.6','2014-08-26',0) -- k.no_polis,k.tgl_pengajuan,0
  