USE [GrpInsurance]
GO


-- data yang gagal:
DECLARE @list_temp TABLE ( claim_id VARCHAR(10))

INSERT INTO @list_temp ( claim_id ) VALUES ('62487413')
INSERT INTO @list_temp ( claim_id ) VALUES ('63015123')
INSERT INTO @list_temp ( claim_id ) VALUES ('63349311')
INSERT INTO @list_temp ( claim_id ) VALUES ('63561631')
INSERT INTO @list_temp ( claim_id ) VALUES ('63599232')
INSERT INTO @list_temp ( claim_id ) VALUES ('63734232')
INSERT INTO @list_temp ( claim_id ) VALUES ('63959979')
INSERT INTO @list_temp ( claim_id ) VALUES ('64001343')
INSERT INTO @list_temp ( claim_id ) VALUES ('64001664')
INSERT INTO @list_temp ( claim_id ) VALUES ('64001817')
INSERT INTO @list_temp ( claim_id ) VALUES ('64001996')
INSERT INTO @list_temp ( claim_id ) VALUES ('64002359')
INSERT INTO @list_temp ( claim_id ) VALUES ('64002915')
INSERT INTO @list_temp ( claim_id ) VALUES ('64003020')
INSERT INTO @list_temp ( claim_id ) VALUES ('64003097')
INSERT INTO @list_temp ( claim_id ) VALUES ('64003162')
INSERT INTO @list_temp ( claim_id ) VALUES ('64008558')
INSERT INTO @list_temp ( claim_id ) VALUES ('64010169')
INSERT INTO @list_temp ( claim_id ) VALUES ('64025878')
INSERT INTO @list_temp ( claim_id ) VALUES ('64026007')
INSERT INTO @list_temp ( claim_id ) VALUES ('64026249')
INSERT INTO @list_temp ( claim_id ) VALUES ('64027267')
INSERT INTO @list_temp ( claim_id ) VALUES ('64028072')
INSERT INTO @list_temp ( claim_id ) VALUES ('64028401')
INSERT INTO @list_temp ( claim_id ) VALUES ('64029103')
INSERT INTO @list_temp ( claim_id ) VALUES ('64029305')
INSERT INTO @list_temp ( claim_id ) VALUES ('64030140')
INSERT INTO @list_temp ( claim_id ) VALUES ('64030684')
INSERT INTO @list_temp ( claim_id ) VALUES ('64030935')
INSERT INTO @list_temp ( claim_id ) VALUES ('64031579')
INSERT INTO @list_temp ( claim_id ) VALUES ('64031948')
INSERT INTO @list_temp ( claim_id ) VALUES ('64032538')
INSERT INTO @list_temp ( claim_id ) VALUES ('64032743')
INSERT INTO @list_temp ( claim_id ) VALUES ('64032832')
INSERT INTO @list_temp ( claim_id ) VALUES ('64033911')
INSERT INTO @list_temp ( claim_id ) VALUES ('64033988')
INSERT INTO @list_temp ( claim_id ) VALUES ('64034166')
INSERT INTO @list_temp ( claim_id ) VALUES ('64034511')
INSERT INTO @list_temp ( claim_id ) VALUES ('64034540')
INSERT INTO @list_temp ( claim_id ) VALUES ('64034654')
INSERT INTO @list_temp ( claim_id ) VALUES ('64034823')
INSERT INTO @list_temp ( claim_id ) VALUES ('64034843')
INSERT INTO @list_temp ( claim_id ) VALUES ('64035368')
INSERT INTO @list_temp ( claim_id ) VALUES ('64035597')
INSERT INTO @list_temp ( claim_id ) VALUES ('64035972')
INSERT INTO @list_temp ( claim_id ) VALUES ('64036322')
INSERT INTO @list_temp ( claim_id ) VALUES ('64036391')
INSERT INTO @list_temp ( claim_id ) VALUES ('64039850')
INSERT INTO @list_temp ( claim_id ) VALUES ('64041414')
INSERT INTO @list_temp ( claim_id ) VALUES ('64041976')
INSERT INTO @list_temp ( claim_id ) VALUES ('64042462')
INSERT INTO @list_temp ( claim_id ) VALUES ('64042539')
INSERT INTO @list_temp ( claim_id ) VALUES ('64042681')
INSERT INTO @list_temp ( claim_id ) VALUES ('64043098')
INSERT INTO @list_temp ( claim_id ) VALUES ('64043235')
INSERT INTO @list_temp ( claim_id ) VALUES ('64043411')
INSERT INTO @list_temp ( claim_id ) VALUES ('64043556')
INSERT INTO @list_temp ( claim_id ) VALUES ('64043819')
INSERT INTO @list_temp ( claim_id ) VALUES ('64044071')
INSERT INTO @list_temp ( claim_id ) VALUES ('64044343')
INSERT INTO @list_temp ( claim_id ) VALUES ('64044683')
INSERT INTO @list_temp ( claim_id ) VALUES ('64045217')
INSERT INTO @list_temp ( claim_id ) VALUES ('64045278')
INSERT INTO @list_temp ( claim_id ) VALUES ('64045399')
INSERT INTO @list_temp ( claim_id ) VALUES ('64046077')
INSERT INTO @list_temp ( claim_id ) VALUES ('64046871')
INSERT INTO @list_temp ( claim_id ) VALUES ('64046914')
INSERT INTO @list_temp ( claim_id ) VALUES ('64047139')
INSERT INTO @list_temp ( claim_id ) VALUES ('64047411')
INSERT INTO @list_temp ( claim_id ) VALUES ('64047469')
INSERT INTO @list_temp ( claim_id ) VALUES ('64047521')
INSERT INTO @list_temp ( claim_id ) VALUES ('64048104')
INSERT INTO @list_temp ( claim_id ) VALUES ('64048310')
INSERT INTO @list_temp ( claim_id ) VALUES ('64049651')
INSERT INTO @list_temp ( claim_id ) VALUES ('64051375')
INSERT INTO @list_temp ( claim_id ) VALUES ('64051649')
INSERT INTO @list_temp ( claim_id ) VALUES ('64051925')
INSERT INTO @list_temp ( claim_id ) VALUES ('64052232')
INSERT INTO @list_temp ( claim_id ) VALUES ('64053166')
INSERT INTO @list_temp ( claim_id ) VALUES ('64054013')
INSERT INTO @list_temp ( claim_id ) VALUES ('64057724')
INSERT INTO @list_temp ( claim_id ) VALUES ('64058827')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060167')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060504')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060725')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061024')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061957')
INSERT INTO @list_temp ( claim_id ) VALUES ('64062118')
INSERT INTO @list_temp ( claim_id ) VALUES ('64062253')
INSERT INTO @list_temp ( claim_id ) VALUES ('64062727')
INSERT INTO @list_temp ( claim_id ) VALUES ('64062835')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063096')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063377')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063476')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063623')
INSERT INTO @list_temp ( claim_id ) VALUES ('64064394')
INSERT INTO @list_temp ( claim_id ) VALUES ('64064662')
INSERT INTO @list_temp ( claim_id ) VALUES ('64064786')
INSERT INTO @list_temp ( claim_id ) VALUES ('64064945')
INSERT INTO @list_temp ( claim_id ) VALUES ('64065103')
INSERT INTO @list_temp ( claim_id ) VALUES ('64065125')
INSERT INTO @list_temp ( claim_id ) VALUES ('64065218')
INSERT INTO @list_temp ( claim_id ) VALUES ('64065944')
INSERT INTO @list_temp ( claim_id ) VALUES ('64065956')
INSERT INTO @list_temp ( claim_id ) VALUES ('64066297')
INSERT INTO @list_temp ( claim_id ) VALUES ('64066611')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067131')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067228')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067238')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067761')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067870')
INSERT INTO @list_temp ( claim_id ) VALUES ('64072025')
INSERT INTO @list_temp ( claim_id ) VALUES ('64074047')
INSERT INTO @list_temp ( claim_id ) VALUES ('62340228')
INSERT INTO @list_temp ( claim_id ) VALUES ('62617001')
INSERT INTO @list_temp ( claim_id ) VALUES ('62651316')
INSERT INTO @list_temp ( claim_id ) VALUES ('62772570')
INSERT INTO @list_temp ( claim_id ) VALUES ('63254843')
INSERT INTO @list_temp ( claim_id ) VALUES ('63819977')
INSERT INTO @list_temp ( claim_id ) VALUES ('63820452')
INSERT INTO @list_temp ( claim_id ) VALUES ('63820886')
INSERT INTO @list_temp ( claim_id ) VALUES ('64011606')
INSERT INTO @list_temp ( claim_id ) VALUES ('64058369')
INSERT INTO @list_temp ( claim_id ) VALUES ('64058503')
INSERT INTO @list_temp ( claim_id ) VALUES ('64058650')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059042')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059178')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059405')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059523')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059743')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059824')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059829')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059966')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060001')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060271')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060358')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060579')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060903')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061259')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061398')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061648')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061772')
INSERT INTO @list_temp ( claim_id ) VALUES ('64062399')
INSERT INTO @list_temp ( claim_id ) VALUES ('64062443')
INSERT INTO @list_temp ( claim_id ) VALUES ('64062555')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063234')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063473')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063947')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063996')
INSERT INTO @list_temp ( claim_id ) VALUES ('64064361')
INSERT INTO @list_temp ( claim_id ) VALUES ('64064373')
INSERT INTO @list_temp ( claim_id ) VALUES ('64064763')
INSERT INTO @list_temp ( claim_id ) VALUES ('64065293')
INSERT INTO @list_temp ( claim_id ) VALUES ('64065366')
INSERT INTO @list_temp ( claim_id ) VALUES ('64065561')
INSERT INTO @list_temp ( claim_id ) VALUES ('64065614')
INSERT INTO @list_temp ( claim_id ) VALUES ('64065791')
INSERT INTO @list_temp ( claim_id ) VALUES ('64065835')
INSERT INTO @list_temp ( claim_id ) VALUES ('64066139')
INSERT INTO @list_temp ( claim_id ) VALUES ('64066140')
INSERT INTO @list_temp ( claim_id ) VALUES ('64066499')
INSERT INTO @list_temp ( claim_id ) VALUES ('64066646')
INSERT INTO @list_temp ( claim_id ) VALUES ('64066789')
INSERT INTO @list_temp ( claim_id ) VALUES ('64066972')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067138')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067164')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067246')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067388')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067410')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067481')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067519')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067613')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067656')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067817')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067892')
INSERT INTO @list_temp ( claim_id ) VALUES ('64067946')
INSERT INTO @list_temp ( claim_id ) VALUES ('64068024')
INSERT INTO @list_temp ( claim_id ) VALUES ('64068780')
INSERT INTO @list_temp ( claim_id ) VALUES ('64068972')
INSERT INTO @list_temp ( claim_id ) VALUES ('64069218')
INSERT INTO @list_temp ( claim_id ) VALUES ('64070679')
INSERT INTO @list_temp ( claim_id ) VALUES ('64070926')
INSERT INTO @list_temp ( claim_id ) VALUES ('64070991')
INSERT INTO @list_temp ( claim_id ) VALUES ('64071098')
INSERT INTO @list_temp ( claim_id ) VALUES ('64071191')
INSERT INTO @list_temp ( claim_id ) VALUES ('64071230')
INSERT INTO @list_temp ( claim_id ) VALUES ('64071833')
INSERT INTO @list_temp ( claim_id ) VALUES ('64071905')
INSERT INTO @list_temp ( claim_id ) VALUES ('64071941')
INSERT INTO @list_temp ( claim_id ) VALUES ('64072168')
INSERT INTO @list_temp ( claim_id ) VALUES ('64072226')
INSERT INTO @list_temp ( claim_id ) VALUES ('64073044')
INSERT INTO @list_temp ( claim_id ) VALUES ('64073326')
INSERT INTO @list_temp ( claim_id ) VALUES ('64073631')
INSERT INTO @list_temp ( claim_id ) VALUES ('64073656')
INSERT INTO @list_temp ( claim_id ) VALUES ('64073704')
INSERT INTO @list_temp ( claim_id ) VALUES ('64074193')
INSERT INTO @list_temp ( claim_id ) VALUES ('64074713')
INSERT INTO @list_temp ( claim_id ) VALUES ('64074778')
INSERT INTO @list_temp ( claim_id ) VALUES ('64074888')
INSERT INTO @list_temp ( claim_id ) VALUES ('64074944')
INSERT INTO @list_temp ( claim_id ) VALUES ('64074985')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076566')
INSERT INTO @list_temp ( claim_id ) VALUES ('64077083')
INSERT INTO @list_temp ( claim_id ) VALUES ('64077206')
INSERT INTO @list_temp ( claim_id ) VALUES ('64077315')
INSERT INTO @list_temp ( claim_id ) VALUES ('64077539')
INSERT INTO @list_temp ( claim_id ) VALUES ('64058662')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059011')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059080')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059170')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059399')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059548')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059761')
INSERT INTO @list_temp ( claim_id ) VALUES ('64059949')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060026')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060175')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060290')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060434')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060445')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060553')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060727')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060807')
INSERT INTO @list_temp ( claim_id ) VALUES ('64060931')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061058')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061131')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061208')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061401')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061471')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061507')
INSERT INTO @list_temp ( claim_id ) VALUES ('64061768')
INSERT INTO @list_temp ( claim_id ) VALUES ('64062162')
INSERT INTO @list_temp ( claim_id ) VALUES ('64062767')
INSERT INTO @list_temp ( claim_id ) VALUES ('64062910')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063081')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063151')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063446')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063752')
INSERT INTO @list_temp ( claim_id ) VALUES ('64063851')
INSERT INTO @list_temp ( claim_id ) VALUES ('64071257')
INSERT INTO @list_temp ( claim_id ) VALUES ('64071465')
INSERT INTO @list_temp ( claim_id ) VALUES ('64072381')
INSERT INTO @list_temp ( claim_id ) VALUES ('64072468')
INSERT INTO @list_temp ( claim_id ) VALUES ('64072622')
INSERT INTO @list_temp ( claim_id ) VALUES ('64073923')
INSERT INTO @list_temp ( claim_id ) VALUES ('64074147')
INSERT INTO @list_temp ( claim_id ) VALUES ('64074224')
INSERT INTO @list_temp ( claim_id ) VALUES ('64074390')
INSERT INTO @list_temp ( claim_id ) VALUES ('64074821')
INSERT INTO @list_temp ( claim_id ) VALUES ('64074929')
INSERT INTO @list_temp ( claim_id ) VALUES ('64075270')
INSERT INTO @list_temp ( claim_id ) VALUES ('64075275')
INSERT INTO @list_temp ( claim_id ) VALUES ('64075339')
INSERT INTO @list_temp ( claim_id ) VALUES ('64075358')
INSERT INTO @list_temp ( claim_id ) VALUES ('64075437')
INSERT INTO @list_temp ( claim_id ) VALUES ('64075512')
INSERT INTO @list_temp ( claim_id ) VALUES ('64075526')
INSERT INTO @list_temp ( claim_id ) VALUES ('64075575')
INSERT INTO @list_temp ( claim_id ) VALUES ('64075650')
INSERT INTO @list_temp ( claim_id ) VALUES ('64075653')
INSERT INTO @list_temp ( claim_id ) VALUES ('64075732')
INSERT INTO @list_temp ( claim_id ) VALUES ('64075742')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076009')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076172')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076286')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076362')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076389')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076495')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076572')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076639')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076697')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076794')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076815')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076847')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076867')
INSERT INTO @list_temp ( claim_id ) VALUES ('64076929')
INSERT INTO @list_temp ( claim_id ) VALUES ('64077176')
INSERT INTO @list_temp ( claim_id ) VALUES ('64077726')
INSERT INTO @list_temp ( claim_id ) VALUES ('64077910')
INSERT INTO @list_temp ( claim_id ) VALUES ('64078055')
INSERT INTO @list_temp ( claim_id ) VALUES ('64078185')
INSERT INTO @list_temp ( claim_id ) VALUES ('64078399')
INSERT INTO @list_temp ( claim_id ) VALUES ('64078749')
INSERT INTO @list_temp ( claim_id ) VALUES ('64078806')
INSERT INTO @list_temp ( claim_id ) VALUES ('64079020')
INSERT INTO @list_temp ( claim_id ) VALUES ('64079222')
INSERT INTO @list_temp ( claim_id ) VALUES ('64079553')
INSERT INTO @list_temp ( claim_id ) VALUES ('64080019')
INSERT INTO @list_temp ( claim_id ) VALUES ('64080112')
INSERT INTO @list_temp ( claim_id ) VALUES ('64080160')
INSERT INTO @list_temp ( claim_id ) VALUES ('64080271')
INSERT INTO @list_temp ( claim_id ) VALUES ('64080353')
INSERT INTO @list_temp ( claim_id ) VALUES ('64080486')
INSERT INTO @list_temp ( claim_id ) VALUES ('64080542')
INSERT INTO @list_temp ( claim_id ) VALUES ('64080625')
INSERT INTO @list_temp ( claim_id ) VALUES ('64080905')
INSERT INTO @list_temp ( claim_id ) VALUES ('64081390')
INSERT INTO @list_temp ( claim_id ) VALUES ('64081533')
INSERT INTO @list_temp ( claim_id ) VALUES ('64081727')
INSERT INTO @list_temp ( claim_id ) VALUES ('64081813')


---- proc_post_claim_from_admedika
--ALTER Procedure [dbo].[proc_post_claim_from_admedika]
DECLARE
@pi_id_user varchar(4)='SYS'
--AS
  SET NOCOUNT ON
  DECLARE @AdMedikaYa bit
  ,@Claim_Ref	varchar	(11)
  ,@No_Klaim_Curr varchar	(11)
  ,@No_Klaim	varchar	(11)
  ,@No_Proposal	varchar	(09)
  ,@No_Polis	varchar	(119)
  ,@No_Peserta	varchar	(12)
  ,@No_Santunan	varchar	(12)
  ,@Id_Produk	varchar	(10)
  ,@Sub_Kelompok	varchar	(06)
  ,@Kode_Diagnosa	varchar	(06)
  ,@Kode_AdProvider varchar	(06)
  ,@Kode_Provider	varchar(06)
  ,@Tgl_Klaim	datetime
  ,@Tgl_Aju	datetime
  ,@No_Bukti	varchar	(18)
  ,@Notes	varchar	(150)
  ,@Jenis_Pembayaran varchar (01)
  ,@Claim_Type 	varchar (01)
  ,@Jml_Diajukan	float	
  ,@Jml_Deductible	float	
  ,@Jml_Dibayar	float	
  ,@Jml_Ditolak	float	
  ,@Jml_Dispensasi	float	
  ,@Uang_Muka	float	
  ,@Diskon_Rs	float	
  ,@Status	varchar	(02)
  ,@Flag		smallint	
  ,@Jml_Dibayar_Old float	
  ,@Jenis	smallint	
  ,@Jumlah	float	
  ,@Tolakan	varchar	(200)
  ,@No_Pasien	varchar	(10)
  ,@Create_Date	datetime
  ,@Kode_Equity	varchar	(06)
  ,@Kode_ICD	varchar	(06)
  ,@Mode_Update int
  ,@Keluarga bit
  ,@CLAIM_ID varchar(8)
  ,@last_status varchar(1)
  ,@id_claim_from_admedika_header bigint
  ,@claim_proses_status varchar(1)
  ,@tgl_proses datetime
	
  SET @Create_Date= dbo.func_FixedDate(GetDate())
  SET @last_status='G'
  
  DECLARE claim_from_admedika_header_cursor CURSOR FOR
  SELECT 
    id_claim_from_admedika_header, 
    CLAIM_ID, 
    coalesce(claim_proses_status,'0') 
  FROM 
    claim_from_admedika_header 
  WHERE 
    dbo.func_fixed_date(create_date)>=dbo.func_fixed_date(@Create_Date)
    AND CLAIM_ID IN (SELECT claim_id FROM @list_temp)           ---- hanya untuk data yang gagal

  OPEN claim_from_admedika_header_cursor
  FETCH NEXT FROM claim_from_admedika_header_cursor INTO @id_claim_from_admedika_header, @CLAIM_ID, @claim_proses_status
  WHILE @@FETCH_STATUS = 0
  BEGIN
	    PRINT @CLAIM_ID
  		SET @no_klaim=null;
		IF EXISTS(SELECT 1 from Klaim_Peserta where Claim_Ref=@CLAIM_ID and (dbo.func_fixed_date(Create_Date)=dbo.func_fixed_date(change_date) OR change_date is null))
		BEGIN
			SELECT @last_status=status, @No_Klaim=no_klaim from Klaim_Peserta where Claim_Ref=@CLAIM_ID
			IF @last_status='G'
			BEGIN
				DELETE from Klaim_detail WHERE No_Klaim in 
				(SELECT no_klaim FROM Klaim_Peserta WHERE Claim_Ref=@CLAIM_ID)
				
				DELETE from Klaim_Peserta WHERE Claim_Ref=@CLAIM_ID
			END
		END
		
		IF NOT EXISTS(select 1 from Klaim_Peserta where Claim_Ref=@CLAIM_ID)
		BEGIN
			IF @No_Klaim is null
			EXEC gproc_GetNo 1, @Create_Date, 'no_klaim', @No_Klaim OUTPUT
		
			IF not exists(select 1 from Klaim_Peserta where No_Klaim=@No_Klaim)
			BEGIN
				INSERT INTO Klaim_Peserta (
    			No_Klaim, No_Proposal, No_Peserta, No_Santunan, Id_Produk, Sub_Kelompok, Kode_Diagnosa, Kode_Provider,
                Tgl_Klaim, Jenis_Pembayaran, Jml_Diajukan, Jml_Dibayar, Jml_Ditolak, 
                Create_By, Create_Date, Change_By, Change_Date, Status, Claim_Ref, Tgl_Kwitansi, Jenis, AdMedika_Ya)
                  
			SELECT   TOP 1  @No_Klaim, Polis_Asuransi.No_Proposal, 
			member_id	= CASE WHEN RIGHT(polis_asuransi.no_polis,1)=0 AND produk_polis.sub_kelompok='0304' THEN
      LEFT(
      CASE	WHEN CHARINDEX('-',cfah.MEMBER_ID)=0 
								THEN SUBSTRING(cfah.MEMBER_ID, 1, 10)+ '-' + RIGHT(cfah.MEMBER_ID, 1)
								ELSE cfah.MEMBER_ID
								END,11)+'0'
      ELSE
      CASE	WHEN CHARINDEX('-',cfah.MEMBER_ID)=0 
								THEN SUBSTRING(cfah.MEMBER_ID, 1, 10)+ '-' + RIGHT(cfah.MEMBER_ID, 1)
								ELSE cfah.MEMBER_ID
								END
END,      

            NO_SANTUNAN = 
            CASE WHEN isnull(Produk_Polis.keluarga,0)=1 THEN
LEFT(
            CASE WHEN CHARINDEX('-',cfah.MEMBER_ID)=0 
						       THEN SUBSTRING(cfah.MEMBER_ID, 1, 10)+ '-' + RIGHT(cfah.MEMBER_ID, 1)
						       ELSE cfah.MEMBER_ID
								END,11)+'0'
ELSE
            CASE WHEN CHARINDEX('-',cfah.MEMBER_ID)=0 
						       THEN SUBSTRING(cfah.MEMBER_ID, 1, 10)+ '-' + RIGHT(cfah.MEMBER_ID, 1)
						       ELSE cfah.MEMBER_ID
								END
END,
                
                
    --            IF (RIGHT(@No_Polis,1)='0') AND @Sub_Kelompok='0304' SET @No_Peserta=LEFT(@No_Peserta,11)+'0'
    --SET @No_Santunan =LEFT(@No_Peserta,11)+'0'
    --ELSE SET @No_Santunan = @No_Peserta


            id_plan		= SUBSTRING(cfah.PLAN_ID, 1, 4) + '.' + SUBSTRING(cfah.PLAN_ID, 5, 5), 
						produk_polis.Sub_Kelompok,
						cfah.DIAGNOSIS_CODE, 
						--CASE cfah.CLAIM_TYPE WHEN 'R' THEN '9999' ELSE provider.IDI END, 
						provider.IDI,
						--CASE cfah.CLAIM_TYPE WHEN 'R' THEN '9999' ELSE provider.IDI END, 
            ADMISSION_DATE = CASE WHEN CHARINDEX('-',cfah.ADMISSION_DATE)<>0 THEN
            SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            ELSE
            SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            END,
            
            CLAIM_TYPE =CASE cfah.CLAIM_TYPE WHEN 'R' THEN 'R' ELSE 'P' END, 
            cfah.AMT_INCURRED, cfah.AMT_APPROVED,
			cfah.AMT_NOT_APPROVED,
						
			@pi_id_user, GETDATE(), @pi_id_user, GETDATE(), 'G', 
			cfah.CLAIM_ID,
			CASE WHEN CHARINDEX('-',cfah.ADMISSION_DATE)<>0 THEN
            SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            ELSE
            SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            END,                       
                      
            CASE cfah.CLAIM_TYPE WHEN 'R' THEN 21 ELSE 20 END, 1
                       
			FROM   claim_from_admedika_header cfah INNER JOIN
                      Polis_Asuransi ON cfah.POLICY_NUMBER = REPLACE(Polis_Asuransi.No_Polis, '.', '') INNER JOIN
                      Produk_Polis ON SUBSTRING(cfah.PLAN_ID, 1, 4) 
                      + '.' + SUBSTRING(cfah.PLAN_ID, 5, 5) = Produk_Polis.Id_Produk left join 
                      (select Max(IDI) as IDI , Kode_AdProvider from Provider group by Kode_AdProvider) as 
                      Provider on Provider.Kode_AdProvider=cfah.PROVIDER_CODE

			WHERE cfah.CLAIM_ID=@CLAIM_ID 
			
			and id_claim_from_admedika_header= @id_claim_from_admedika_header
			and coalesce(claim_proses_status,'0')= @CLAIM_PROSES_STATUS
			AND
			CASE 
            WHEN CHARINDEX('-',cfah.ADMISSION_DATE)<>0 THEN
            SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            ELSE
            SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            END >=
            CASE WHEN Polis_Asuransi.Open_polis=1 THEN CAST(DATEPART(year,getdate()) as varchar(4))+'-01-01' ELSE Polis_Asuransi.tgl_mulai END
            
		and
		CASE 
            WHEN CHARINDEX('-',cfah.ADMISSION_DATE)<>0 THEN
            SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2) 
            ELSE
            SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
            END<=
            CASE WHEN Polis_Asuransi.Open_polis=1 THEN CAST(DATEPART(year,getdate()) as varchar(4))+'-12-31' ELSE Polis_Asuransi.Tgl_Selesai END
    
		AND LEFT(member_id,1) in ('1','2','3','4','5','6','7','8','9','0')
		AND LEFT(cfah.DIAGNOSIS_CODE,6)<>'PRORAT'
		AND
		
		(LEFT(COVERAGE_ID,1) NOT in ('1','2','3','4','5','6','7','8','9','0') OR PROVIDER_CODE<>'NPK') 
		and (
    Polis_Asuransi.status ='I' OR polis_asuransi.open_polis=0)

		------------------------------------------------
		
		DECLARE detail_cursor CURSOR FOR
		select 
		cfad.CLAIM_ID, 
		SUBSTRING(cfah.PLAN_ID, 1, 4) + '.' + SUBSTRING(cfah.PLAN_ID, 5, 5) ,
		max(dbo.func_fixed_date(cfad.create_date)) as create_date 
		from claim_from_admedika_detail cfad INNER JOIN claim_from_admedika_header cfah
		ON
		cfad.CLAIM_ID=cfah.CLAIM_ID
		
		where 
		id_claim_from_admedika_header= @id_claim_from_admedika_header
		and cfad.CLAIM_ID=@CLAIM_ID
		and coalesce(cfah.claim_proses_status,'0')=@claim_proses_status
		and (
			coalesce(cfah.claim_proses_status,'0')='1' AND LEFT(BENEFIT_CODE,1) in ('1','2','3','4','5','6','7','8','9','0')
			OR
            coalesce(cfah.claim_proses_status,'0')='0' AND LEFT(BENEFIT_CODE,1) not in ('1','2','3','4','5','6','7','8','9','0')
			)
		group by coalesce(cfah.claim_proses_status,'0'), cfad.CLAIM_ID, 
		SUBSTRING(cfah.PLAN_ID, 1, 4) + '.' + SUBSTRING(cfah.PLAN_ID, 5, 5) 
	
		OPEN detail_cursor;
		FETCH NEXT FROM detail_cursor INTO @CLAIM_ID, @id_produk, @tgl_proses
		WHILE @@FETCH_STATUS = 0
		BEGIN
				INSERT INTO Klaim_Detail (No_Klaim, Kode_Santunan, Tgl_Kwitansi, Jml_Benefit, Jml_Diajukan, jml_adm, Jml_Dibayar, Jml_Ditolak, Kode_Tolakan)

				SELECT  @no_klaim, 
				
				CASE WHEN coalesce(isp.benefit_code,'')<>'' THEN  isp.benefit_code
				ELSE
				CASE WHEN coalesce(cfah.claim_proses_status,'0')='1' 
				THEN cfad.KODE_SANTUNAN
				ELSE cfad.BENEFIT_CODE 
				END
				END, 
            
				Max(
				CASE 
				WHEN coalesce(cfah.claim_proses_status,'0')='1' 
				THEN SUBSTRING(cfah.ADMISSION_DATE, 7, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 4, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
				ELSE SUBSTRING(cfah.ADMISSION_DATE, 5, 4) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 3, 2) + '-' + SUBSTRING(cfah.ADMISSION_DATE, 1, 2)
				END), 
            
				Max(
				CASE 
				WHEN coalesce(cfad.benefit_limit,0)=0 OR coalesce(cfad.benefit_limit,0)=999999999 
				THEN dbo.func_get_benefit_limit(
				CASE	WHEN CHARINDEX('-',cfah.MEMBER_ID)=0  THEN SUBSTRING(cfah.MEMBER_ID, 1, 10)+ '-' + RIGHT(cfah.MEMBER_ID, 1)
						ELSE cfah.MEMBER_ID
						END, 
						SUBSTRING(cfah.PLAN_ID, 1, 4) + '.' + SUBSTRING(cfah.PLAN_ID, 5, 5), 
						CASE 
						WHEN coalesce(cfah.claim_proses_status,'0')='1' 
						THEN cfad.KODE_SANTUNAN
						ELSE cfad.BENEFIT_CODE 
						END)
				ELSE coalesce(cfad.benefit_limit,0)
				END)

				,SUM(CAST(cfad.AMT_INCURRED as float)), 
            
				sum(
				CASE cfah.CLAIM_TYPE WHEN 'R' THEN cfad.AMT_NOT_APPROVED ELSE 0 END), 
            
				Sum(CAST(cfad.AMT_APPROVED as float)), 
				sum(CAST(cfad.AMT_NOT_APPROVED as float)), 
				max(mapping_tolakan_admedika.[kode Cit])
            
				FROM claim_from_admedika_detail cfad INNER JOIN
				claim_from_admedika_header cfah ON cfad.CLAIM_ID = cfah.CLAIM_ID 
				LEFT JOIN mapping_tolakan_admedika ON mapping_tolakan_admedika.[REASON CODE]=cfad.[REASON_CODE_OR_DESCRIPTION]
				left join item_santunan_polis isp on isp.id_produk=@id_produk and isp.kode_santunan=
				CASE WHEN coalesce(cfah.claim_proses_status,'0')='1' 
				THEN cfad.KODE_SANTUNAN
				ELSE cfad.BENEFIT_CODE 
				END 
            

				WHERE 
				(
					coalesce(cfah.claim_proses_status,'0')='1' AND LEFT(cfad.BENEFIT_CODE,1) in ('1','2','3','4','5','6','7','8','9','0')
					OR
					coalesce(cfah.claim_proses_status,'0')='0' AND LEFT(cfad.BENEFIT_CODE,1) not in ('1','2','3','4','5','6','7','8','9','0')
				)
				and id_claim_from_admedika_header=@id_claim_from_admedika_header
				and cfah.CLAIM_ID=@CLAIM_ID 
				and dbo.func_FixedDate(cfad.create_date) =dbo.func_FixedDate(@tgl_proses)
				and coalesce(cfah.claim_proses_status,'0')=@claim_proses_status
				group by 
				CASE WHEN coalesce(isp.benefit_code,'')<>'' THEN  isp.benefit_code
				ELSE
				CASE WHEN coalesce(cfah.claim_proses_status,'0')='1' 
				THEN cfad.KODE_SANTUNAN
				ELSE cfad.BENEFIT_CODE 
				END
				END

				FETCH NEXT FROM detail_cursor INTO @CLAIM_ID, @id_produk, @tgl_proses
			END;
			CLOSE detail_cursor;
			DEALLOCATE detail_cursor;
   		
			EXEC proc_PostToKlaim @No_Klaim 
    
			UPDATE claim_from_admedika_header SET No_Klaim=@No_Klaim, Status='I' WHERE CLAIM_ID=@CLAIM_ID
		END
	END
    FETCH NEXT FROM claim_from_admedika_header_cursor INTO @id_claim_from_admedika_header, @CLAIM_ID, @claim_proses_status

  END
  CLOSE claim_from_admedika_header_cursor
  DEALLOCATE claim_from_admedika_header_cursor
