USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_CreateKlaimToAdMedikaText]    Script Date: 05/12/2016 15:39:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--ALTER Procedure [dbo].[proc_CreateKlaimToAdMedikaText]
DECLARE
@TglProses1 Datetime,
@TglProses2 Datetime,
@PisahYa bit,
@PolisYa bit,
@Status varchar(1),
@TglProses1Ya bit,
@TglProses2Ya bit
--AS
EXEC TmpInsurance.[dbo].[gproc_DelTable] 'tmp_prib_KlaimTo_Detail'
CREATE TABLE TmpInsurance.[dbo].[tmp_prib_KlaimTo_Detail] (
	[Claim_Ref] [varchar] (11) NOT NULL ,
	[Ref_No] [int] NOT NULL ,
	[Benefit_Code] [varchar] (15) NOT NULL ,
	[Member_Id] [varchar] (12) NULL ,
	[Benefit_Desc] [varchar] (255) NULL ,
	[Benefit_Limit] [float] NULL ,
	[Amt_Incurred] [float] NULL ,
	[Amt_Approved] [float] NULL ,
	[Amt_Not_Approved] [float] NULL ,
	[Coverage_Type] [varchar] (2) NULL ,
	[Plan_Id] [varchar] (15) NULL ,
	[AdMedika_Klaim_Id] [varchar] (15) NULL ,
        [Nama_Payor] [varchar] (20) NULL ,
        [Nama_File] [varchar] (100) NULL ,
	CONSTRAINT [PK_tmp_prib_KlaimTo_Detail] PRIMARY KEY  CLUSTERED 
	(
		[Claim_Ref],
		[Ref_No],
		[Benefit_Code]
	)  ON [PRIMARY] 
) ON [PRIMARY]


EXEC TmpInsurance.[dbo].[gproc_DelTable] 'tmp_prib_KlaimTo_Header'
CREATE TABLE TmpInsurance.[dbo].[tmp_prib_KlaimTo_Header] (
	[Claim_Ref] [varchar] (15) NOT NULL ,
	[Ref_No] [int] NOT NULL ,
	[Member_Id] [varchar] (22) NULL ,
	[Claim_Type] [varchar] (1) NULL ,
	[Policy_No] [varchar] (30) NULL ,
	[Member_Name] [varchar] (30) NULL ,
	[Coverage_Type] [varchar] (2) NULL ,
	[Provider_Code] [varchar] (15) NULL ,
	[Admission_Date] [varchar] (8) NULL ,
	[Discharge_Date] [varchar] (8) NULL ,
	[Duration_Days] [varchar] (3) NULL ,
	[Claim_Process_Status] [varchar] (1) NULL ,
	[App_Date] [varchar] (8) NULL ,
	[App_By] [varchar] (10) NULL ,
	[Diagnosis_Code] [varchar] (6) NULL ,
	[Diagnosis_Desc] [varchar] (255) NULL ,
	[Plan_Id] [varchar] (15) NULL ,
	[Tot_Amt_Incurred] [float] NULL ,
	[Tot_Amt_Approved] [float] NULL ,
	[Tot_Amt_Disapproved] [float] NULL ,
	[AdMedika_Klaim_Id] [varchar] (15) NULL ,
        [Nama_Payor] [varchar] (20) NULL ,
        [Nama_File] [varchar] (100) NULL ,
	CONSTRAINT [PK_tmp_prib_KlaimTo_Header] PRIMARY KEY  CLUSTERED 
	(
		[Claim_Ref],
		[Ref_No]
	)  ON [PRIMARY] 
) ON [PRIMARY]

  DECLARE @Nama_Payor varchar(20)
  DECLARE @Nama_File varchar(100)
  DECLARE @No_Klaim varchar(11)
  DECLARE @Tgl_Proses Datetime
  DECLARE @Nama_Perusahaan varchar(60) 
  DECLARE @PreFile varchar(3)
  DECLARE Payor_Cursor CURSOR FOR
  SELECT
    B.Nama_Perusahaan,
    P.Nama_Payor,
    H.No_Klaim,
    H.Tgl_Proses
  FROM
    KlaimTo_Header H
    INNER JOIN Klaim_Peserta K ON H.No_Klaim = K.No_Klaim
    INNER JOIN Polis_Asuransi A ON K.No_Proposal = A.No_Proposal
    INNER JOIN Payor_Header P ON P.Id_Payor = A.Id_Payor
    INNER JOIN Perusahaan B ON A.No_Prs = B.No_Prs
  WHERE
    H.Policy_No = '201600021'
    --(H.Tgl_Proses >= @TglProses1)
    --AND (H.Tgl_Proses <= @TglProses2)
    --AND (H.Status = @Status
    --OR ISNULL(@Status, '') = '')
  GROUP BY
    B.Nama_Perusahaan,
    P.Nama_Payor,
    H.No_Klaim,
    H.Tgl_Proses
  OPEN Payor_Cursor
  FETCH NEXT FROM Payor_Cursor INTO @Nama_Perusahaan, @Nama_Payor, @No_Klaim, @Tgl_Proses
  WHILE @@FETCH_STATUS = 0
  BEGIN
       IF @Tgl_Proses<@TglProses1 SET @PreFile='2 ' ELSE SET @PreFile=' '
       IF isnull(@PolisYa,0)=0
       BEGIN 
            IF @PisahYa=0 SET @Nama_File=@PreFile+[dbo].[func_spDateToStr](@TglProses1,'-')+' to '+[dbo].[func_spDateToStr](@TglProses2,'-')
            ELSE SET @Nama_File=@PreFile+[dbo].[func_spDateToStr](@Tgl_Proses,'-')
       END
       ELSE
       BEGIN 
            IF @PisahYa=0 SET @Nama_File=@PreFile+[dbo].[func_spDateToStr](@TglProses1,'-')+' to '+[dbo].[func_spDateToStr](@TglProses2,'-')+' '+@Nama_Perusahaan
            ELSE SET @Nama_File=@PreFile+[dbo].[func_spDateToStr](@Tgl_Proses,'-')+' '+@Nama_Perusahaan
       END
       INSERT INTO TmpInsurance.[dbo].[tmp_prib_KlaimTo_Header](Member_Id, Claim_Type, Claim_Ref, 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, AdMedika_Klaim_Id, Ref_No, Nama_Payor, Nama_File)

       SELECT Member_Id, Claim_Type, Claim_Ref, 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, AdMedika_Klaim_Id, Ref_No, @Nama_Payor, @Nama_File
       FROM KlaimTo_Header
       WHERE (No_Klaim=@No_Klaim) AND (Tgl_Proses=@Tgl_Proses)

       INSERT INTO TmpInsurance.[dbo].[tmp_prib_KlaimTo_Detail] (Claim_Ref, Member_Id, Benefit_Code, Benefit_Desc, Benefit_Limit, 
       Amt_Incurred, Amt_Approved, Amt_Not_Approved, Coverage_Type, Plan_Id, 
       AdMedika_Klaim_Id, Ref_No, Nama_Payor, Nama_File)
       SELECT D.Claim_Ref, D.Member_Id, D.Benefit_Code, D.Benefit_Desc, D.Benefit_Limit, 
       D.Amt_Incurred, D.Amt_Approved, D.Amt_Not_Approved, D.Coverage_Type, D.Plan_Id, 
       H.AdMedika_Klaim_Id, D.Ref_No, @Nama_Payor, @Nama_File
       FROM KlaimTo_Detail D INNER JOIN KlaimTo_Header H ON D.No_Klaim=H.No_Klaim AND D.Tgl_Proses=H.Tgl_Proses
       WHERE (H.No_Klaim=@No_Klaim) AND (H.Tgl_Proses=@Tgl_Proses)

       UPDATE KlaimTo_Header SET Status='I' WHERE (No_Klaim=@No_Klaim) AND (Tgl_Proses=@Tgl_Proses) AND (Status='G')
       FETCH NEXT FROM Payor_Cursor  INTO @Nama_Perusahaan, @Nama_Payor, @No_Klaim, @Tgl_Proses
  END
  CLOSE Payor_Cursor
  DEALLOCATE Payor_Cursor
/***
SELECT Member_Id, Claim_Type, Claim_Ref, 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, AdMedika_Klaim_Id, Ref_No, Nama_Payor, Nama_File
FROM TmpInsurance.[dbo].[tmp_prib_KlaimTo_Header]


SELECT Claim_Ref, Member_Id, Benefit_Code, Benefit_Desc, Benefit_Limit, 
               Amt_Incurred, Amt_Approved, Amt_Not_Approved, Coverage_Type, Plan_Id, 
               AdMedika_Klaim_Id, Ref_No, Nama_Payor, Nama_File
               FROM TmpInsurance.[dbo].[tmp_prib_KlaimTo_Detail]
***/
