--DELETE FROM dbo.KlaimFrom_Header
--DELETE FROM dbo.KlaimFrom_Detail

INSERT INTO dbo.KlaimFrom_Header
        (Claim_Ref,
         Member_Id,
         Claim_Type,
         Policy_No,
         Member_Name,
         Coverage_Type,
         Provider_Code,
         Admission_Date,
         Discharge_Date,
         Duration_Days,
         Claim_Process_Status,
         App_Date,
         App_By,
         Diagnosis_Code,
         Diagnosis_Desc,
         Plan_Id,
         Tot_Amt_Incurred,
         Tot_Amt_Approved,
         Tot_Amt_Disapproved,
         Status,
         Create_Date)
SELECT
	  [Column 2],                                         -- Claim_Ref - varchar(15)
	  [Column 0],                                         -- Member_Id - varchar(22)
	  [Column 1],                                         -- Claim_Type - varchar(1)
	  [Column 3],                                         -- Policy_No - varchar(30)
	  [Column 4],                                         -- Member_Name - varchar(30)
	  [Column 5],                                         -- Coverage_Type - varchar(2)
	  [Column 6],                                         -- Provider_Code - varchar(15)
	  CONVERT(DATETIME, [Column 7], 6) AS [Column 7],     -- Admission_Date - datetime
	  CONVERT(DATETIME, [Column 8], 6) AS [Column 8],     -- Discharge_Date - datetime
	  [Column 9],                                         -- Duration_Days - varchar(3)
	  [Column 10],                                        -- Claim_Process_Status - varchar(1)
	  CONVERT(DATETIME, [Column 11], 6) AS [Column 11],   -- App_Date - datetime
	  [Column 12],                                        -- App_By - varchar(10)
	  [Column 13],                                        -- Diagnosis_Code - varchar(6)
	  [Column 14],                                        -- Diagnosis_Desc - varchar(255)
	  [Column 15],                                        -- Plan_Id - varchar(15)
	  CAST([Column 16] AS FLOAT) AS [Column 16],          -- Tot_Amt_Incurred - float
	  CAST([Column 17] AS FLOAT) AS [Column 17],          -- Tot_Amt_Approved - float
	  CAST([Column 18] AS FLOAT) AS [Column 18],          -- Tot_Amt_Disapproved - float
	  'I',          -- Status - varchar(1)
	  GETDATE()     -- Create_Date - datetime
FROM
	dbo.KlaimFrom_Header_import AS kfh


------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---- berhubung ada 5 klaim (detail) yg error, agar bisa masuk dulu, maka edit saja, nanti diakhir turut dilaporkan bahwa ke-5 klaim ini error
	
--SELECT
--	  *
--FROM
--	dbo.KlaimFrom_Detail_import
--WHERE
--  [Column 0] IN ('35818175','31195274','35881627','32506478','34263821')
--  AND [Column 2] = 'RI67'
--  AND [Column 3] = 'PRWT JALAN DR'

--UPDATE
--  dbo.KlaimFrom_Detail_import
--SET
--  [Column 2] = 'RI68'
--WHERE
--  [Column 0] IN ('35818175','31195274','35881627','32506478','34263821')
--  AND [Column 2] = 'RI67'
--  AND [Column 3] = 'PRWT JALAN DR'


------------------------------------------------------------------------------------------------------------------------------------------------------------------------

INSERT INTO dbo.KlaimFrom_Detail
        (Claim_Ref,
         Benefit_Code,
         Member_Id,
         Benefit_Desc,
         Benefit_Limit,
         Amt_Incurred,
         Amt_Approved,
         Amt_Not_Approved,
         Coverage_Type,
         Plan_Id,
         Status,
         Create_Date)
SELECT
	  [Column 0], -- Claim_Ref - varchar(11)
	  [Column 2], -- Benefit_Code - varchar(15)
	  [Column 1], -- Member_Id - varchar(12)
	  [Column 3], -- Benefit_Desc - varchar(100)
	  [Column 4], -- Benefit_Limit - float
	  [Column 5], -- Amt_Incurred - float
	  [Column 6], -- Amt_Approved - float
	  [Column 7], -- Amt_Not_Approved - float
	  [Column 8], -- Coverage_Type - varchar(2)
	  [Column 9], -- Plan_Id - varchar(15)
	  'I',      -- Status - varchar(1)
	  GETDATE() -- Create_Date - datetime
FROM            
  dbo.KlaimFrom_Detail_import


