SELECT
	*
FROM
	dbo.KlaimFrom_Header AS kfh
WHERE
  --Provider_Code = 'NPK' -- 13849
  Claim_Type = 'R'  -- 13849

SELECT
	*
FROM
	dbo.KlaimFrom_Header_org AS kfho

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

-- data rekonsel tidak pernah dikirim sebelumnya => 13025, semua NPK
SELECT
	kfh.*
FROM
	dbo.KlaimFrom_Header AS kfh LEFT JOIN
	dbo.KlaimFrom_Header_org AS kfho ON kfh.Claim_Ref = kfho.Claim_Ref
WHERE
  kfh.Admission_Date >= '2013-03-26'
  AND kfh.Admission_Date <= '2014-12-31'
  AND kfho.Claim_Ref IS NULL
  
SELECT
	kfho.*
FROM
	dbo.KlaimFrom_Header AS kfh RIGHT JOIN
	dbo.KlaimFrom_Header_org AS kfho ON kfh.Claim_Ref = kfho.Claim_Ref
WHERE
  kfh.Admission_Date >= '2013-03-26'
  AND kfh.Admission_Date <= '2014-12-31'
  AND kfh.Claim_Ref IS NULL
  
-------------------

SELECT
	*
FROM
	dbo.KlaimFrom_Header
WHERE
  Provider_Code = 'NPK'
  AND Member_Id = '20120199132'

SELECT
	*
FROM
	dbo.KlaimTo_Header_org
WHERE
  --ktho.Claim_Ref = '35021272'
  Member_Id = '20120199132'

SELECT
	*
FROM
	dbo.KlaimFrom_Header AS admedika INNER JOIN
	dbo.KlaimTo_Header_org AS equity ON admedika.Member_Id = equity.Member_Id 
	                                    AND admedika.Claim_Type = equity.Claim_Type
	                                    AND admedika.Policy_No = equity.Policy_No
	                                    AND admedika.Member_Name = equity.Member_Name
	                                    AND admedika.Coverage_Type = equity.Coverage_Type
	                                    AND admedika.Admission_Date = CONVERT(DATETIME, RIGHT(equity.Admission_Date,4)+SUBSTRING(equity.Admission_Date,3,2)+LEFT(equity.Admission_Date,2))
	                                    AND admedika.Discharge_Date = CONVERT(DATETIME, RIGHT(equity.Discharge_Date,4)+SUBSTRING(equity.Discharge_Date,3,2)+LEFT(equity.Discharge_Date,2))
	                                    AND admedika.App_Date = CONVERT(DATETIME, RIGHT(equity.App_Date,4)+SUBSTRING(equity.App_Date,3,2)+LEFT(equity.App_Date,2))
	                                    AND admedika.Diagnosis_Code = equity.Diagnosis_Code
	                                    AND admedika.Plan_Id = equity.Plan_Id
	                                    AND admedika.Tot_Amt_Approved = equity.Tot_Amt_Approved
	                                    AND admedika.Tot_Amt_Disapproved = equity.Tot_Amt_Disapproved
	                                    AND admedika.Tot_Amt_Incurred = equity.Tot_Amt_Incurred
WHERE
  admedika.Provider_Code = 'NPK'

-------------------------------------------------------
-- cek klaim di equity yg tidak ada di admedika --
-- hanya klaim NPK/reimbursement
DECLARE @tabel_hasil TABLE ( tgl_proses DATETIME,
                             claim_ref_equity VARCHAR(15),
                             no_klaim_equity VARCHAR(11),
                             ada BIT,
                             claim_ref_admedika VARCHAR(15))

DECLARE
  @tgl_proses DATETIME, 
  @no_klaim VARCHAR(11), 
  @claim_ref_equity VARCHAR(15)
  
DECLARE list_cursor CURSOR FAST_FORWARD FOR
SELECT
  --Tgl_Proses,
  --No_Klaim,
  --Claim_Ref
  *
FROM
  dbo.KlaimTo_Header_org AS kth INNER JOIN
  dbo.Polis_Asuransi AS pa ON kth.Policy_No = REPLACE(pa.No_Polis,'.','') 
WHERE
  CONVERT(DATETIME, RIGHT(kth.Admission_Date,4)+SUBSTRING(kth.Admission_Date,3,2)+LEFT(kth.Admission_Date,2)) >= '2013-03-26'
  AND CONVERT(DATETIME, RIGHT(kth.Admission_Date,4)+SUBSTRING(kth.Admission_Date,3,2)+LEFT(kth.Admission_Date,2)) <= '2014-12-31'
  AND kth.Status = 'I'
  AND kth.Create_Date < pa.Tgl_Selesai AND kth.Create_Date > pa.Tgl_Mulai
  AND kth.Provider_Code = 'NPK'
  AND CONVERT(DATETIME, RIGHT(kth.Admission_Date,4)+SUBSTRING(kth.Admission_Date,3,2)+LEFT(kth.Admission_Date,2)) >= pa.Tgl_Mulai
  AND CONVERT(DATETIME, RIGHT(kth.Admission_Date,4)+SUBSTRING(kth.Admission_Date,3,2)+LEFT(kth.Admission_Date,2)) <= pa.Tgl_Selesai
  AND pa.Status = 'I'
  AND pa.Active_Ya = 1
  
OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @tgl_proses, @no_klaim, @claim_ref_equity

WHILE @@FETCH_STATUS = 0  
BEGIN
  DECLARE @claim_ref_admedika VARCHAR(15) = NULL
  IF EXISTS ( SELECT
                admedika.Claim_Ref
              FROM
              	dbo.KlaimFrom_Header AS admedika INNER JOIN
	              dbo.KlaimTo_Header_org AS equity ON admedika.Member_Id = equity.Member_Id 
	                                                  AND admedika.Claim_Type = equity.Claim_Type
	                                                  AND admedika.Policy_No = equity.Policy_No
	                                                  AND admedika.Member_Name = equity.Member_Name
	                                                  AND admedika.Coverage_Type = equity.Coverage_Type
	                                                  AND admedika.Admission_Date = CONVERT(DATETIME, RIGHT(equity.Admission_Date,4)+SUBSTRING(equity.Admission_Date,3,2)+LEFT(equity.Admission_Date,2))
	                                                  AND admedika.Discharge_Date = CONVERT(DATETIME, RIGHT(equity.Discharge_Date,4)+SUBSTRING(equity.Discharge_Date,3,2)+LEFT(equity.Discharge_Date,2))
	                                                  AND admedika.App_Date = CONVERT(DATETIME, RIGHT(equity.App_Date,4)+SUBSTRING(equity.App_Date,3,2)+LEFT(equity.App_Date,2))
	                                                  AND admedika.Diagnosis_Code = equity.Diagnosis_Code
	                                                  AND admedika.Plan_Id = equity.Plan_Id
	                                                  AND admedika.Tot_Amt_Approved = equity.Tot_Amt_Approved
	                                                  AND admedika.Tot_Amt_Disapproved = equity.Tot_Amt_Disapproved
	                                                  AND admedika.Tot_Amt_Incurred = equity.Tot_Amt_Incurred
              WHERE
                equity.Tgl_Proses = @tgl_proses
                AND equity.No_Klaim = @no_klaim
                AND equity.Claim_Ref = @claim_ref_equity
            )
            BEGIN
            
              SELECT
                @claim_ref_admedika = admedika.Claim_Ref
              FROM
              	dbo.KlaimFrom_Header AS admedika INNER JOIN
	              dbo.KlaimTo_Header_org AS equity ON admedika.Member_Id = equity.Member_Id 
	                                                  AND admedika.Claim_Type = equity.Claim_Type
	                                                  AND admedika.Policy_No = equity.Policy_No
	                                                  AND admedika.Member_Name = equity.Member_Name
	                                                  AND admedika.Coverage_Type = equity.Coverage_Type
	                                                  AND admedika.Admission_Date = CONVERT(DATETIME, RIGHT(equity.Admission_Date,4)+SUBSTRING(equity.Admission_Date,3,2)+LEFT(equity.Admission_Date,2))
	                                                  AND admedika.Discharge_Date = CONVERT(DATETIME, RIGHT(equity.Discharge_Date,4)+SUBSTRING(equity.Discharge_Date,3,2)+LEFT(equity.Discharge_Date,2))
	                                                  AND admedika.App_Date = CONVERT(DATETIME, RIGHT(equity.App_Date,4)+SUBSTRING(equity.App_Date,3,2)+LEFT(equity.App_Date,2))
	                                                  AND admedika.Diagnosis_Code = equity.Diagnosis_Code
	                                                  AND admedika.Plan_Id = equity.Plan_Id
	                                                  AND admedika.Tot_Amt_Approved = equity.Tot_Amt_Approved
	                                                  AND admedika.Tot_Amt_Disapproved = equity.Tot_Amt_Disapproved
	                                                  AND admedika.Tot_Amt_Incurred = equity.Tot_Amt_Incurred
              WHERE
                equity.Tgl_Proses = @tgl_proses
                AND equity.No_Klaim = @no_klaim
                AND equity.Claim_Ref = @claim_ref_equity
                
              INSERT INTO @tabel_hasil
                      (tgl_proses,
                       claim_ref_equity,
                       no_klaim_equity,
                       ada,
                       claim_ref_admedika)
              VALUES
                      (@tgl_proses, -- tgl_proses - datetime
                       @claim_ref_equity, -- claim_ref_equity - varchar(15)
                       @no_klaim, -- no_klaim_equity - varchar(11)
                       1, -- ada - bit
                       @claim_ref_admedika  -- claim_ref_admedika - varchar(15)
                       )
            END
  ELSE
            BEGIN
              INSERT INTO @tabel_hasil
                      (tgl_proses,
                       claim_ref_equity,
                       no_klaim_equity,
                       ada,
                       claim_ref_admedika)
              VALUES
                      (@tgl_proses, -- tgl_proses - datetime
                       @claim_ref_equity, -- claim_ref_equity - varchar(15)
                       @no_klaim, -- no_klaim_equity - varchar(11)
                       0, -- ada - bit
                       NULL  -- claim_ref_admedika - varchar(15)
                       )
            END

  FETCH NEXT FROM list_cursor INTO @tgl_proses, @no_klaim, @claim_ref_equity  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

SELECT
  *
FROM
  @tabel_hasil
  
SELECT
  *
INTO
  hasil_klaim_dari_equity_ke_admedika
FROM
  @tabel_hasil


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

--DECLARE @tabel_hasil TABLE ( claim_ref_admedika VARCHAR(15),
--                             no_peserta VARCHAR(12),
--                             no_polis VARCHAR(11),
--                             kode_diagnosa VARCHAR(6),
--                             tgl_kwitansi DATETIME,
--                             claim_ref VARCHAR(15),
--                             hasil_pencarian_manual BIT)
                             
--DECLARE list_cursor CURSOR FAST_FORWARD FOR  
--SELECT
--	Claim_Ref
--FROM
--	dbo.KlaimFrom_Header
--WHERE
--  Provider_Code = 'NPK'

--OPEN list_cursor  
--FETCH NEXT FROM list_cursor INTO @claim_ref_admedika

--WHILE @@FETCH_STATUS = 0  
--BEGIN

--  IF EXISTS(  SELECT
--	              *
--              FROM
--	              dbo.KlaimFrom_Header AS admedika INNER JOIN
--	              dbo.KlaimTo_Header_org AS equity ON admedika.Member_Id = equity.Member_Id 
--	                                                  AND admedika.Claim_Type = equity.Claim_Type
--	                                                  AND admedika.Policy_No = equity.Policy_No
--	                                                  AND admedika.Member_Name = equity.Member_Name
--	                                                  AND admedika.Coverage_Type = equity.Coverage_Type
--	                                                  AND admedika.Admission_Date = CONVERT(DATETIME, RIGHT(equity.Admission_Date,4)+SUBSTRING(equity.Admission_Date,3,2)+LEFT(equity.Admission_Date,2))
--	                                                  AND admedika.Discharge_Date = CONVERT(DATETIME, RIGHT(equity.Discharge_Date,4)+SUBSTRING(equity.Discharge_Date,3,2)+LEFT(equity.Discharge_Date,2))
--	                                                  AND admedika.App_Date = CONVERT(DATETIME, RIGHT(equity.App_Date,4)+SUBSTRING(equity.App_Date,3,2)+LEFT(equity.App_Date,2))
--	                                                  AND admedika.Diagnosis_Code = equity.Diagnosis_Code
--	                                                  AND admedika.Plan_Id = equity.Plan_Id
--	                                                  AND admedika.Tot_Amt_Approved = equity.Tot_Amt_Approved
--	                                                  AND admedika.Tot_Amt_Disapproved = equity.Tot_Amt_Disapproved
--	                                                  AND admedika.Tot_Amt_Incurred = equity.Tot_Amt_Incurred
--              WHERE
--                admedika.Claim_Ref = @claim_ref_admedika
--  )

--  FETCH NEXT FROM list_cursor INTO @claim_ref_admedika  
--END  

--CLOSE list_cursor  
--DEALLOCATE list_cursor 