USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_convert_claim_from_admedika_header]    Script Date: 01/10/2017 15:57:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--ALTER PROCEDURE [dbo].[proc_convert_claim_from_admedika_header]
DECLARE
 @pi_rows varchar(4000) = '"ELI_TRD","20061398","200602220","2006006129-1","","","8000100606239748","PUJI LESTARI","57254630","R","40","NPK","22122016","22122016","1","GP","201503035RJ5000","","Z34","","40000","40000","0","0","","~","","R17010300458771","03012017","2017000009.","03012017","06012017","RXUMMI"'
,@pi_column_delimiter varchar(1) = '|'
,@pi_id_user varchar(4) = 'SYS'
,@pi_source_file varchar(255) = 'Upload Header_2017JAN06_010000PM'
--AS

IF coalesce(@pi_rows,'')='' RETURN -1
DECLARE 
 @line_str varchar(4000)
,@fields varchar(200)
,@loop int
,@urut int
,@len_str int
,@field_no int
---------------------------
,@id_claim_from_admedika_header int
,@PAYOR_ID VARCHAR(17) 
,@CORPORATE_ID VARCHAR(10) 
,@POLICY_NUMBER VARCHAR(30) 
,@MEMBER_ID VARCHAR(22) 
,@NIK VARCHAR(50) 
,@BRANCH_CODE VARCHAR(50) 
,@CARD_NO VARCHAR(19) 
,@MEMBER_NAME VARCHAR(30) 
,@CLAIM_ID VARCHAR(8) 
,@CLAIM_TYPE VARCHAR(1) 
,@CLAIM_STATUS VARCHAR(2) 
,@PROVIDER_CODE VARCHAR(15) 
,@ADMISSION_DATE VARCHAR(10) 
,@DISCHARGE_DATE VARCHAR(10) 
,@DURATION_DAYS VARCHAR(3) 
,@COVERAGE_ID VARCHAR(15) 
,@PLAN_ID VARCHAR(15) 
,@DISABILITY_NO VARCHAR(10) 
,@DIAGNOSIS_CODE VARCHAR(6) 
,@SECONDARY_DIAGNOSIS_CODE_LIST VARCHAR(100) 
,@AMT_INCURRED VARCHAR(15) 
,@AMT_APPROVED VARCHAR(15) 
,@AMT_NOT_APPROVED VARCHAR(15) 
,@AMT_ASO_APPROVED VARCHAR(15) 
,@ON_PLAN_OR_HIGH_PLAN VARCHAR(15) 
,@REMARKS VARCHAR(1200) 
,@PROVIDER_EXCESS_PAID VARCHAR(15) 
,@PAYOR_INVOICE_ID VARCHAR(30) 
,@HOSPITAL_INVOICE_DATE VARCHAR(10) 
,@HOSPITAL_INVOICE_NO VARCHAR(30) 
,@RECEIVED_DATE VARCHAR(10) 
,@SUBMISSION_DATE VARCHAR(10) 
,@VERIFY_BY_OR_APPROVAL VARCHAR(8) 
,@CLAIM_PROSES_STATUS VARCHAR(1)
,@APP_DATE varchar(11)
,@APP_BY varchar(35)
,@DIAGNOSIS_DESK varchar(255)
,@status					[varchar](1)
,@create_date				[datetime]
,@create_by					[varchar](4)
,@change_date				[datetime]
,@change_by					[varchar](4)
----------------------------------------
,@oke bit

SET @oke=1

SET @status='G'
SET @create_date=GETDATE()
SET @create_by=@pi_id_user
SET @change_date=null
SET @change_by=null

SET @fields=''
SET @line_str=REPLACE(@pi_rows,'","','|')+@pi_column_delimiter
SET @line_str=REPLACE(@line_str,'"','')
SET @loop=1
SET @len_str=LEN(@line_str)
SET @field_no=1
PRINT @line_str
WHILE @loop<=@len_str
BEGIN
     IF SUBSTRING(@line_str,@loop,1)=@pi_column_delimiter --AND coalesce(@fields,'')<>''
     BEGIN
		PRINT @fields
		--SET @fields= REPLACE(@fields,'"','')
		IF charindex('UPLOAD', upper(@pi_source_file))<>0
		BEGIN		
				 IF @field_no=  1 SET @PAYOR_ID = @fields
			ELSE IF @field_no=  2 SET @CORPORATE_ID = @fields 
			ELSE IF @field_no=  3 SET @POLICY_NUMBER = @fields
			ELSE IF @field_no=  4 SET @MEMBER_ID = @fields
			ELSE IF @field_no=  5 SET @NIK = @fields
			ELSE IF @field_no=  6 SET @BRANCH_CODE = @fields 
			ELSE IF @field_no=  7 SET @CARD_NO = @fields
			ELSE IF @field_no=  8 SET @MEMBER_NAME = @fields
			ELSE IF @field_no=  9 SET @CLAIM_ID = @fields
			ELSE IF @field_no=  10 SET @CLAIM_TYPE = @fields
			ELSE IF @field_no=  11 SET @CLAIM_STATUS = @fields
			ELSE IF @field_no=  12 SET @PROVIDER_CODE = @fields
			ELSE IF @field_no=  13 SET @ADMISSION_DATE = @fields
			ELSE IF @field_no=  14 SET @DISCHARGE_DATE = @fields
			ELSE IF @field_no=  15 SET @DURATION_DAYS = @fields
			ELSE IF @field_no=  16 SET @COVERAGE_ID = @fields
			ELSE IF @field_no=  17 SET @PLAN_ID = @fields
			ELSE IF @field_no=  18 SET @DISABILITY_NO = @fields
			ELSE IF @field_no=  19 SET @DIAGNOSIS_CODE = @fields
			ELSE IF @field_no=  20 SET @SECONDARY_DIAGNOSIS_CODE_LIST = @fields
			ELSE IF @field_no=  21 SET @AMT_INCURRED = @fields
			ELSE IF @field_no=  22 SET @AMT_APPROVED = @fields
			ELSE IF @field_no=  23 SET @AMT_NOT_APPROVED = @fields
			ELSE IF @field_no=  24 SET @AMT_ASO_APPROVED = @fields
			ELSE IF @field_no=  25 SET @ON_PLAN_OR_HIGH_PLAN = @fields
			ELSE IF @field_no=  26 SET @REMARKS = @fields
			ELSE IF @field_no=  27 SET @PROVIDER_EXCESS_PAID = @fields
			ELSE IF @field_no=  28 SET @PAYOR_INVOICE_ID = @fields
			ELSE IF @field_no=  29 SET @HOSPITAL_INVOICE_DATE = @fields
			ELSE IF @field_no=  30 SET @HOSPITAL_INVOICE_NO = @fields
			ELSE IF @field_no=  31 SET @RECEIVED_DATE = @fields
			ELSE IF @field_no=  32 SET @SUBMISSION_DATE = @fields
			ELSE IF @field_no=  33 SET @VERIFY_BY_OR_APPROVAL = @fields
		END
		ELSE IF charindex('DAILY', upper(@pi_source_file))<>0
		BEGIN		
				 IF @field_no=   1 SET @MEMBER_ID = @fields
			ELSE IF @field_no=   2 SET @CLAIM_TYPE = @fields
			ELSE IF @field_no=   3 SET @CLAIM_ID = @fields
			ELSE IF @field_no=   4 SET @POLICY_NUMBER = @fields
			ELSE IF @field_no=   5 SET @MEMBER_NAME = @fields
			ELSE IF @field_no=   6 SET @COVERAGE_ID = @fields
			ELSE IF @field_no=   7 SET @PROVIDER_CODE = @fields
			ELSE IF @field_no=   8 SET @ADMISSION_DATE = @fields
			ELSE IF @field_no=   9 SET @DISCHARGE_DATE = @fields
			ELSE IF @field_no=  10 SET @DURATION_DAYS = @fields
			ELSE IF @field_no=  11 SET @CLAIM_PROSES_STATUS = @fields
			ELSE IF @field_no=  12 SET @APP_DATE = @fields
			ELSE IF @field_no=  13 SET @APP_BY = @fields
			ELSE IF @field_no=  14 SET @DIAGNOSIS_CODE = @fields
			ELSE IF @field_no=  15 SET @DIAGNOSIS_DESK = @fields
			ELSE IF @field_no=  16 SET @PLAN_ID = @fields
			ELSE IF @field_no=  17 SET @AMT_INCURRED = @fields
			ELSE IF @field_no=  18 SET @AMT_APPROVED = @fields
			ELSE IF @field_no=  19 SET @AMT_NOT_APPROVED = @fields
			IF @PROVIDER_CODE='NPK' SET @oke=10
			IF @DIAGNOSIS_CODE like '%NPK%' SET @oke=10
		END
		
		SET @fields=''
		SET @field_no=@field_no+1
	 END
     ELSE
     SET @fields=@fields+SUBSTRING(@line_str,@loop,1)
     SET @loop=@loop+1

END

IF @OKE=1 
BEGIN

IF EXISTS(select 1 from claim_from_admedika_header WHERE CLAIM_ID=@CLAIM_ID AND Coalesce(CLAIM_PROSES_STATUS,'0')=Coalesce(@CLAIM_PROSES_STATUS,'0') 
AND dbo.func_fixed_date(create_date)>dbo.func_fixed_date(@create_date)
)
delete from claim_from_admedika_header 
WHERE CLAIM_ID=@CLAIM_ID AND Coalesce(CLAIM_PROSES_STATUS,'0')=Coalesce(@CLAIM_PROSES_STATUS,'0') 
AND dbo.func_fixed_date(create_date)>dbo.func_fixed_date(@create_date)

IF NOT EXISTS(
select 1 from claim_from_admedika_header WHERE CLAIM_ID=@CLAIM_ID AND Coalesce(CLAIM_PROSES_STATUS,'0')=Coalesce(@CLAIM_PROSES_STATUS,'0') 
AND dbo.func_fixed_date(create_date)>dbo.func_fixed_date(@create_date)
)
PRINT 'INSERT'
INSERT INTO claim_from_admedika_header(
PAYOR_ID 
,CORPORATE_ID 
,POLICY_NUMBER 
,MEMBER_ID 
,NIK
,BRANCH_CODE
,CARD_NO
,MEMBER_NAME
,CLAIM_ID
,CLAIM_TYPE
,CLAIM_STATUS
,PROVIDER_CODE
,ADMISSION_DATE
,DISCHARGE_DATE
,DURATION_DAYS
,COVERAGE_ID
,PLAN_ID
,DISABILITY_NO
,DIAGNOSIS_CODE
,SECONDARY_DIAGNOSIS_CODE_LIST
,AMT_INCURRED
,AMT_APPROVED
,AMT_NOT_APPROVED
,AMT_ASO_APPROVED
,ON_PLAN_OR_HIGH_PLAN
,REMARKS
,PROVIDER_EXCESS_PAID
,PAYOR_INVOICE_ID
,HOSPITAL_INVOICE_DATE
,HOSPITAL_INVOICE_NO
,RECEIVED_DATE
,SUBMISSION_DATE
,VERIFY_BY_OR_APPROVAL
,CLAIM_PROSES_STATUS
,DIAGNOSIS_DESK
,APP_DATE
,APP_BY
,status
,create_date
,create_by
,change_date
,change_by
)

SELECT
@PAYOR_ID 
,@CORPORATE_ID 
,@POLICY_NUMBER 
,@MEMBER_ID 
,@NIK
,@BRANCH_CODE
,@CARD_NO
,@MEMBER_NAME
,@CLAIM_ID
,@CLAIM_TYPE
,@CLAIM_STATUS
,@PROVIDER_CODE
,@ADMISSION_DATE
,@DISCHARGE_DATE
,@DURATION_DAYS
,@COVERAGE_ID
,@PLAN_ID
,@DISABILITY_NO
,@DIAGNOSIS_CODE
,@SECONDARY_DIAGNOSIS_CODE_LIST
,@AMT_INCURRED
,@AMT_APPROVED
,@AMT_NOT_APPROVED
,@AMT_ASO_APPROVED
,@ON_PLAN_OR_HIGH_PLAN
,@REMARKS
,@PROVIDER_EXCESS_PAID
,@PAYOR_INVOICE_ID
,@HOSPITAL_INVOICE_DATE
,@HOSPITAL_INVOICE_NO
,@RECEIVED_DATE
,@SUBMISSION_DATE
,@VERIFY_BY_OR_APPROVAL
,@CLAIM_PROSES_STATUS
,@DIAGNOSIS_DESK
,@APP_DATE
,@APP_BY
,@status
,@create_date
,@create_by
,@change_date
,@change_by
END
--GO
--EXEC [dbo].[proc_convert_claim_from_admedika_header]
-- '"ELI_TRD","20010058","199200300","1992003293-3","960.915.65","","8000100606244433","HAFSAH AMALIA R","56478982","R","40","NPK","01122016","01122016","1","GP","201502915RJ500","","M15.0","","436600","436600","0","0","","~","","R16120700425424","07122016","2016029338","07122016","13122016","SXFRIT"'
--,'|'
--,'SYS'
--,'UPLOAD'

