SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

----------------------------------------------
--------- proc_CreateKlaimToAdMedika ---------
----------------------------------------------
DECLARE
        @TglBuat1 DATETIME,
        @TglBuat2 DATETIME,
        @TglBuat1Ya BIT,
        @TglBuat2Ya BIT,
        --@NoKlaim VARCHAR(100),
        @CreateBy VARCHAR(4)
        
SET @TglBuat1 = '2015-01-01'
SET @TglBuat2 = '2015-01-31'
SET @TglBuat1Ya = 1
SET @TglBuat2Ya = 1
--SET @NoKlaim = ''
SET @CreateBy = ''

--DECLARE @p_no_polis VARCHAR(400)
--SET @p_no_polis = '2015.0011.1, 2015.0010.1, 2015.0009.1'


DECLARE @No_Proposal VARCHAR(9)
DECLARE @No_Polis VARCHAR(11)
DECLARE @No_Peserta VARCHAR(12)
DECLARE @No_Istri VARCHAR(12)
DECLARE @Nama_Istri VARCHAR(30)
DECLARE @Sub_Kelompok VARCHAR(10)
DECLARE @No_Ref VARCHAR(11)
DECLARE @Claim_Process_Status VARCHAR(1)
DECLARE @Ref_No INT
DECLARE @No_Klaim VARCHAR(11)
DECLARE @Claim_Ref VARCHAR(11)
DECLARE @Tgl_Buat DATETIME
DECLARE @Create_Date DATETIME
DECLARE @Tgl_Max DATETIME
DECLARE @Diajukan_Old FLOAT
DECLARE @Dibayar_Old FLOAT
DECLARE @Ditolak_Old FLOAT
DECLARE @Diajukan_New FLOAT
DECLARE @Dibayar_New FLOAT
DECLARE @Ditolak_New FLOAT
DECLARE @Add_Ya BIT
DECLARE @Kode_Provider VARCHAR(06)
DECLARE @Kode_AdProvider VARCHAR(15)
SET @Create_Date = dbo.func_FixedDate(GETDATE())

DECLARE Employee_Cursor CURSOR
FOR
SELECT
    dbo.Note_Terbit_Master.Tgl_Buat,
    dbo.Klaim_Ref_Detail.No_Klaim,
    Polis_Asuransi.No_Polis
FROM
    dbo.Note_Terbit_Master
    INNER JOIN dbo.Klaim_Ref_Detail ON dbo.Note_Terbit_Master.No_Ref = dbo.Klaim_Ref_Detail.No_Ref
    INNER JOIN Polis_Asuransi ON dbo.Note_Terbit_Master.No_Proposal = Polis_Asuransi.No_Proposal
    INNER JOIN produk_polis ON Klaim_Ref_Detail.No_Proposal = produk_polis.No_Proposal
                               AND Klaim_Ref_Detail.id_produk = produk_polis.id_produk
                               AND Produk_Polis.AdMedika_Ya = 1
WHERE
    Polis_Asuransi.No_Polis IN ('2015.0011.1', '2015.0010.1', '2015.0009.1')
    AND dbo.Note_Terbit_Master.Jenis IN (20, 21, 40, 41)
    AND (ISNULL(Polis_Asuransi.AdMedika_Ya, 0) = 1)
    AND (dbo.Note_Terbit_Master.Tgl_Buat >= @TglBuat1 OR ISNULL(@TglBuat1Ya, 0) = 0)
    AND (dbo.Note_Terbit_Master.Tgl_Buat <= @TglBuat2 OR ISNULL(@TglBuat2Ya, 0) = 0)
    --AND (CHARINDEX(dbo.Klaim_Ref_Detail.No_Klaim, ISNULL(@NoKlaim, '')) <> 0 OR ISNULL(@NoKlaim, '') = '')
    AND (ISNULL(dbo.Klaim_Ref_Detail.Tgl_Kwitansi, dbo.Klaim_Ref_Detail.Tgl_Klaim) >= Polis_Asuransi.Tgl_Mulai AND ISNULL(dbo.Klaim_Ref_Detail.Tgl_Kwitansi, dbo.Klaim_Ref_Detail.Tgl_Klaim) <= Polis_Asuransi.Tgl_Selesai)
    AND (@Create_Date >= Polis_Asuransi.Tgl_Mulai AND @Create_Date <= Polis_Asuransi.Tgl_Selesai)
GROUP BY
    dbo.Klaim_Ref_Detail.No_Ref,
    dbo.Note_Terbit_Master.Tgl_Buat,
    dbo.Klaim_Ref_Detail.No_Klaim,
    Polis_Asuransi.No_Polis,
    Polis_Asuransi.No_Proposal
  
  --ORDER BY dbo.Note_Terbit_Master.Tgl_Buat
  
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor INTO @Tgl_Buat, @No_Klaim, @No_Polis
WHILE @@FETCH_STATUS = 0 
      BEGIN
            SET @Claim_Process_Status = '1'
            SET @Claim_Ref = ''
            SET @Add_Ya = 1
            SET @Diajukan_Old = 0
            SET @Dibayar_Old = 0
            SET @Ditolak_Old = 0
 
   --Hapus bila dalam satu tanggal proses lebih dari 1
            IF EXISTS ( SELECT
                            *
                        FROM
                            tmp_KlaimTo_Header
                        WHERE
                            No_Klaim = @No_Klaim
                            AND Tgl_Proses = @Tgl_Buat ) 
               BEGIN
                     DELETE FROM
                        tmp_KlaimTo_Header
                     WHERE
                        No_Klaim = @No_Klaim
                        AND Tgl_Proses = @Tgl_Buat
                     DELETE FROM
                        tmp_KlaimTo_Detail
                     WHERE
                        No_Klaim = @No_Klaim
                        AND Tgl_Proses = @Tgl_Buat
               END
   -- Definisi
            SELECT
                @Claim_Ref = ISNULL(Claim_Ref, ''),
                @Kode_Provider = ISNULL(Kode_Provider, ''),
                @Diajukan_New = Jml_Diajukan,
                @Dibayar_New = Jml_Dibayar,
                @Ditolak_New = Jml_Ditolak,
                @No_Proposal = No_Proposal,
                @No_Peserta = No_Peserta,
                @Sub_Kelompok = Sub_Kelompok
            FROM
                Klaim_Peserta
            WHERE
                No_Klaim = @No_Klaim
            IF EXISTS ( SELECT * FROM tmp_KlaimTo_Header WHERE No_Klaim = @No_Klaim ) 
               BEGIN
                     SELECT
                        @Ref_No = CAST(MAX(ISNULL(Ref_No, 0)) AS INT)
                     FROM
                        tmp_KlaimTo_Header
                     WHERE
                        No_Klaim = @No_Klaim 
                     SET @Ref_No = ISNULL(@Ref_No, 0) + 1
               END
            ELSE 
               SET @Ref_No = 0 
            SET @Add_Ya = 1
            SET @Kode_AdProvider = ''
   -- Klaim dari EQUITY
            IF ISNULL(@Claim_Ref, '') = '' 
               BEGIN
                     SET @Kode_AdProvider = 'NPK'
                     IF @Ref_No = 0 
                        SET @Claim_Process_Status = '1'
                     ELSE 
                        SET @Claim_Process_Status = '3'
                     IF EXISTS ( SELECT
                                    *
                                 FROM
                                    tmp_KlaimTo_Header
                                 WHERE
                                    No_Klaim = @No_Klaim ) 
                        BEGIN
                              SELECT
                                @Tgl_Max = Tgl_Proses
                              FROM
                                tmp_KlaimTo_Header
                              WHERE
                                No_Klaim = @No_Klaim 
                              SELECT
                                @Diajukan_Old = Tot_Amt_Incurred,
                                @Dibayar_Old = Tot_Amt_Approved,
                                @Ditolak_Old = Tot_Amt_Disapproved
                              FROM
                                tmp_KlaimTo_Header
                              WHERE
                                No_Klaim = @No_Klaim
                                AND Tgl_Proses = @Tgl_Max
                              IF ISNULL(@Diajukan_New, 0) = ISNULL(@Diajukan_Old, 0)
                                 AND ISNULL(@Dibayar_New, 0) = ISNULL(@Dibayar_Old, 0)
                                 AND ISNULL(@Ditolak_New, 0) = ISNULL(@Ditolak_Old, 0) 
                                 SET @Add_Ya = 0
                        END 
               END
            ELSE    -- Klaim dari AdMedika
               BEGIN
                     SELECT
                        @Kode_AdProvider = Kode_AdProvider
                     FROM
                        Provider
                     WHERE
                        IDI = @Kode_Provider
                     SET @Claim_Process_Status = '3'
                     SET @Ref_No = @Ref_No + 1
                     IF @Ref_No = 1 
                        BEGIN 
                              IF EXISTS ( SELECT * FROM KlaimFROM_Header WHERE Claim_Ref = @Claim_Ref ) 
                                 BEGIN
                                       SELECT
                                        @Diajukan_Old = Tot_Amt_Incurred,
                                        @Dibayar_Old = Tot_Amt_Approved,
                                        @Ditolak_Old = Tot_Amt_Disapproved
                                       FROM
                                        KlaimFROM_Header
                                       WHERE
                                        Claim_Ref = @Claim_Ref
                                       IF ISNULL(@Diajukan_New, 0) = ISNULL(@Diajukan_Old, 0)
                                          AND ISNULL(@Dibayar_New, 0) = ISNULL(@Dibayar_Old, 0)
                                          AND ISNULL(@Ditolak_New, 0) = ISNULL(@Ditolak_Old, 0) 
                                          SET @Add_Ya = 0
                                 END
                        END
                     ELSE 
                        BEGIN
                              IF EXISTS ( SELECT * FROM tmp_KlaimTo_Header WHERE Claim_Ref = @Claim_Ref ) 
                                 BEGIN
                                       SELECT
                                        @Tgl_Max = Tgl_Proses
                                       FROM
                                        tmp_KlaimTo_Header
                                       WHERE
                                        Claim_Ref = @Claim_Ref 
                                       SELECT
                                        @Diajukan_Old = Tot_Amt_Incurred,
                                        @Dibayar_Old = Tot_Amt_Approved,
                                        @Ditolak_Old = Tot_Amt_Disapproved
                                       FROM
                                        tmp_KlaimTo_Header
                                       WHERE
                                        Claim_Ref = @Claim_Ref
                                        AND Tgl_Proses = @Tgl_Max
                                       IF ISNULL(@Diajukan_New, 0) = ISNULL(@Diajukan_Old, 0)
                                          AND ISNULL(@Dibayar_New, 0) = ISNULL(@Dibayar_Old, 0)
                                          AND ISNULL(@Ditolak_New, 0) = ISNULL(@Ditolak_Old, 0) 
                                          SET @Add_Ya = 0
                                 END 
                        END   
               END
            IF @Add_Ya = 1 
               BEGIN  
                     INSERT INTO tmp_KlaimTo_Header
                            (Tgl_Proses,
                             No_Klaim,
                             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,
                             Status,
                             Create_Date,
                             Create_By)
                            SELECT
                                @Tgl_Buat,
                                @No_Klaim,
                                LEFT(Klaim_Peserta.No_Peserta, 10) + RIGHT(Klaim_Peserta.No_Peserta, 1) AS Member_Id,
                                Claim_Type = CASE WHEN ISNULL(Klaim_Peserta.AdMedika_Ya, 0) = 1 THEN 'M'
                                                  WHEN ISNULL(Klaim_Peserta.AdMedika_Ya, 0) = 0 THEN 'R'
                                                  ELSE ''
                                             END,
                                LEFT(Klaim_Peserta.No_Klaim, 4) + RIGHT(Klaim_Peserta.No_Klaim, 6) AS Claim_Ref,
                                LEFT(Polis_Asuransi.No_Polis, 4) + SUBSTRING(Polis_Asuransi.No_Polis, 6, 4) + RIGHT(Polis_Asuransi.No_Polis, 1) AS Policy_No,
                                Peserta.Nama AS Member_Name,
                                Coverage_Type = CASE WHEN Klaim_Peserta.Id_Produk = '2005.00015' THEN '05'
                                                     ELSE dbo.Sub_Kelompok.Type_Coverage
                                                END,
                                Provider_Code = CASE WHEN ISNULL(Klaim_Peserta.AdMedika_Ya, 0) = 1 THEN CAST(dbo.Klaim_Peserta.Kode_Provider AS VARCHAR)
                                                     WHEN ISNULL(Klaim_Peserta.AdMedika_Ya, 0) = 0 THEN 'NPK'
                                                     ELSE ''
                                                END,
                                Admission_Date = CASE WHEN (ISNULL(dbo.Klaim_Peserta.Tgl_Masuk, '') = '')
                                                           OR (Klaim_Peserta.Tgl_Masuk IS NULL) THEN dbo.func_Tanggal1(ISNULL(Klaim_Peserta.Tgl_Kwitansi, dbo.Klaim_Peserta.Tgl_Klaim))
                                                      ELSE dbo.func_Tanggal1(dbo.Klaim_Peserta.Tgl_Masuk)
                                                 END,
                                Discharge_Date = CASE WHEN (ISNULL(dbo.Klaim_Peserta.Tgl_Keluar, '') = '')
                                                           OR (Klaim_Peserta.Tgl_Keluar IS NULL) THEN dbo.func_Tanggal1(ISNULL(Klaim_Peserta.Tgl_Kwitansi, dbo.Klaim_Peserta.Tgl_Klaim))
                                                      ELSE dbo.func_Tanggal1(dbo.Klaim_Peserta.Tgl_Keluar)
                                                 END,
                                '' AS Duration_Day,
                                @Claim_Process_Status AS Claim_Process_Status, -- 3 if Adjust
                                dbo.func_Tanggal1(dbo.Klaim_Peserta.Create_Date) AS App_Date,
                                dbo.Klaim_Peserta.Create_By AS App_By,
                                Diagnosis_Code = CASE WHEN ISNULL(dbo.Klaim_Peserta.Kode_Diagnosa, '') = '' THEN 'Z71.9'
                                                      WHEN ISNULL(dbo.Klaim_Peserta.Kode_Diagnosa, '') <> '' THEN CAST(dbo.Klaim_Peserta.Kode_Diagnosa AS VARCHAR)
                                                      ELSE ''
                                                 END,
                                Diagnosis_Desc = CASE WHEN ISNULL(dbo.Klaim_Peserta.Kode_Diagnosa, '') = '' THEN 'COUNSELLING- UNSPECIFIED'
                                                      WHEN ISNULL(dbo.Klaim_Peserta.Kode_Diagnosa, '') <> '' THEN dbo.Diagnosa.Diagnosis1
                                                      ELSE ''
                                                 END,
                                LEFT(dbo.Peserta_Produk.Id_Produk, 4) + RIGHT(dbo.Peserta_Produk.Id_Produk, 5) + dbo.Peserta_Produk.Kelas AS Plan_Id,
                                ISNULL(dbo.Klaim_Peserta.Jml_Diajukan, 0) AS Tot_Amt_Incurred,
                                ISNULL(dbo.Klaim_Peserta.Jml_Dibayar, 0) AS Tot_Amt_Approved,
                                ISNULL(dbo.Klaim_Peserta.Jml_Ditolak, 0) AS Tot_Amt_Disapproved,
                                Klaim_Peserta.Claim_Ref,
                                @Ref_No,
                                'G',
                                @Create_Date,
                                @CreateBy
                            FROM
                                dbo.Klaim_Peserta
                                INNER JOIN dbo.Peserta ON dbo.Klaim_Peserta.No_Proposal = dbo.Peserta.No_Proposal
                                                          AND dbo.Klaim_Peserta.No_Peserta = dbo.Peserta.No_Peserta
                                INNER JOIN dbo.Polis_Asuransi ON dbo.Klaim_Peserta.No_Proposal = dbo.Polis_Asuransi.No_Proposal
                                INNER JOIN dbo.Sub_Kelompok ON dbo.Klaim_Peserta.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok
                                INNER JOIN dbo.Peserta_Produk ON dbo.Klaim_Peserta.No_Proposal = dbo.Peserta_Produk.No_Proposal
                                                                 AND dbo.Klaim_Peserta.No_Santunan = dbo.Peserta_Produk.No_Peserta
                                                                 AND dbo.Klaim_Peserta.Id_Produk = dbo.Peserta_Produk.Id_Produk
                                LEFT OUTER JOIN dbo.Diagnosa ON dbo.Klaim_Peserta.Kode_Diagnosa = dbo.Diagnosa.Kode
                            WHERE
                                (dbo.Klaim_Peserta.No_Klaim = @No_Klaim)
                     UPDATE
                        tmp_KlaimTo_Header
                     SET
                        Provider_Code = 'NPK'
                     WHERE
                        Provider_Code = '0'
                        AND No_Klaim = @No_Klaim

----------------------------------------------------------------
                     INSERT INTO tmp_KlaimTo_Detail
                            (Tgl_Proses,
                             No_Klaim,
                             Claim_Ref,
                             Member_Id,
                             Benefit_Code,
                             Benefit_Desc,
                             Benefit_Limit,
                             Amt_Incurred,
                             Amt_Approved,
                             Amt_Not_Approved,
                             Coverage_Type,
                             Plan_Id,
                             Ref_No)
                            SELECT
                                @Tgl_Buat,
                                @No_Klaim,
                                LEFT(Klaim_Peserta.No_Klaim, 4) + RIGHT(Klaim_Peserta.No_Klaim, 6) AS Claim_Ref,
                                LEFT(Klaim_Peserta.No_Peserta, 10) + RIGHT(Klaim_Peserta.No_Peserta, 1) AS Member_Id,
                                Klaim_Detail.Kode_Santunan,
                                Item_Santunan_Polis.Deskripsi,
                                ISNULL(Klaim_Detail.Jml_Benefit, 0),
                                ISNULL(Klaim_Detail.Jml_Diajukan, 0),
                                ISNULL(Klaim_Detail.Jml_Dibayar, 0),
                                ISNULL(Klaim_Detail.Jml_Ditolak, 0),
                                Coverage_Type = CASE WHEN Klaim_Peserta.Id_Produk = '2005.00015' THEN '05'
                                                     ELSE dbo.Sub_Kelompok.Type_Coverage
                                                END, 
     --dbo.Sub_Kelompok.Type_Coverage AS Type_Coverage, 
                                LEFT(dbo.Peserta_Produk.Id_Produk, 4) + RIGHT(dbo.Peserta_Produk.Id_Produk, 5) + dbo.Peserta_Produk.Kelas AS Plan_Id,
                                @Ref_No
                            FROM
                                dbo.Klaim_Peserta
                                INNER JOIN dbo.Peserta ON dbo.Klaim_Peserta.No_Proposal = dbo.Peserta.No_Proposal
                                                          AND dbo.Klaim_Peserta.No_Peserta = dbo.Peserta.No_Peserta
                                INNER JOIN Klaim_Detail ON dbo.Klaim_Peserta.No_Klaim = Klaim_Detail.No_Klaim
                                INNER JOIN Item_Santunan_Polis ON dbo.Klaim_Peserta.Id_Produk = Item_Santunan_Polis.Id_produk
                                                                  AND Klaim_Detail.Kode_Santunan = Item_Santunan_Polis.Kode_Santunan
                                INNER JOIN dbo.Polis_Asuransi ON dbo.Klaim_Peserta.No_Proposal = dbo.Polis_Asuransi.No_Proposal
                                INNER JOIN dbo.Sub_Kelompok ON dbo.Klaim_Peserta.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok
                                INNER JOIN dbo.Peserta_Produk ON dbo.Klaim_Peserta.No_Proposal = dbo.Peserta_Produk.No_Proposal
                                                                 AND dbo.Klaim_Peserta.No_Santunan = dbo.Peserta_Produk.No_Peserta
                                                                 AND dbo.Klaim_Peserta.Id_Produk = dbo.Peserta_Produk.Id_Produk
                                LEFT OUTER JOIN dbo.Diagnosa ON dbo.Klaim_Peserta.Kode_Diagnosa = dbo.Diagnosa.Kode
                            WHERE
                                (dbo.Klaim_Peserta.No_Klaim = @No_Klaim)
               END
            UPDATE
                tmp_KlaimTo_Header
            SET 
                tmp_KlaimTo_Header.Provider_Code = B.Kode_AdProvider
            FROM
                Provider B
            WHERE
                tmp_KlaimTo_Header.Provider_Code = CAST(B.IDI AS VARCHAR)
                AND tmp_KlaimTo_Header.Tgl_Proses = @Tgl_Buat
                AND tmp_KlaimTo_Header.No_Klaim = @No_Klaim
  
            IF NOT (@No_Polis IN ('2007.0208.0', '2007.0209.0', '2007.0203.0', '2007.0196.0', '2007.0210.0', '2007.0205.0', '2007.0195.0', '2000.0130.0', '2007.0204.0', '2007.0206.0', '2007.0201.0', '2007.0198.0', '2007.0202.0', '2007.0200.0', '2007.0207.0', '2007.0211.0', '2007.0199.0'))
               AND RIGHT(@No_Polis, 1) = '0'
               AND @Sub_Kelompok = '0304' 
               BEGIN
                     SET @No_Istri = ''
                     EXEC proc_GetNoIstri @No_Proposal, @No_Peserta, @No_Istri OUTPUT, @Nama_Istri OUTPUT
                     UPDATE
                        tmp_KlaimTo_Header
                     SET
                        Member_Id = LEFT(@No_Istri, 10) + RIGHT(@No_Istri, 1),
                        Member_Name = @Nama_Istri
                     WHERE
                        No_Klaim = @No_Klaim 
               END
            FETCH NEXT FROM Employee_Cursor INTO @Tgl_Buat, @No_Klaim, @No_Polis

      END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

DECLARE Employee_Cursor CURSOR
FOR
SELECT
    dbo.Klaim_Peserta.No_Proposal,
    dbo.Klaim_Peserta.No_Peserta,
    dbo.tmp_KlaimTo_Header.No_Klaim
FROM
    dbo.tmp_KlaimTo_Header
    INNER JOIN dbo.Klaim_Peserta ON dbo.tmp_KlaimTo_Header.No_Klaim = dbo.Klaim_Peserta.No_Klaim
--WHERE -- diremarks, soalnya mau liat semua data
--    dbo.tmp_KlaimTo_Header.Status IN ('G', 'P')
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor INTO @No_Proposal, @No_Peserta, @No_Klaim
WHILE @@FETCH_STATUS = 0 
      BEGIN
            IF EXISTS ( SELECT
                            *
                        FROM
                            Enrollment
                        WHERE
                            No_Proposal = @No_Proposal
                            AND No_Peserta = @No_Peserta
                            AND Status = 'I' ) 
               UPDATE
                tmp_KlaimTo_Header
               SET
                Status = 'G'
               WHERE
                No_Klaim = @No_Klaim
            ELSE 
               UPDATE
                tmp_KlaimTo_Header
               SET
                Status = 'P'
               WHERE
                No_Klaim = @No_Klaim
            FETCH NEXT FROM Employee_Cursor INTO @No_Proposal, @No_Peserta, @No_Klaim
      END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
GO

----------------------------------------------
------- proc_CreateKlaimToAdMedikaText -------
----------------------------------------------
DECLARE
        @TglProses1 DATETIME,
        @TglProses2 DATETIME,
        @PisahYa BIT,
        @PolisYa BIT,
        @Status VARCHAR(1),
        @TglProses1Ya BIT,
        @TglProses2Ya BIT
        
SET @TglProses1 = '2015-01-01'
SET @TglProses2 = '2015-01-31'
SET @PisahYa = 1
SET @PolisYa = 1
SET @Status = NULL
SET @TglProses1Ya = 1
SET @TglProses2Ya = 1

EXEC TmpInsurance.[dbo].[gproc_DelTable] 'prib_tmp_KlaimTo_Detail'
CREATE TABLE TmpInsurance.[dbo].[prib_tmp_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_prib_tmp_KlaimTo_Detail] PRIMARY KEY CLUSTERED ([Claim_Ref], [Ref_No], [Benefit_Code]) ON [PRIMARY]
       )
ON     [PRIMARY]


EXEC TmpInsurance.[dbo].[gproc_DelTable] 'prib_tmp_KlaimTo_Header'
CREATE TABLE TmpInsurance.[dbo].[prib_tmp_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_prib_tmp_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
    tmp_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.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].[prib_tmp_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
                        tmp_KlaimTo_Header
                    WHERE
                        (No_Klaim = @No_Klaim)
                        AND (Tgl_Proses = @Tgl_Proses)

            INSERT  INTO TmpInsurance.[dbo].[prib_tmp_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
                        tmp_KlaimTo_Detail D
                        INNER JOIN tmp_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
                tmp_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].[prib_tmp_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].[prib_tmp_KlaimTo_Detail]
***/
GO

----------------------------------------------
-------    hasil                       -------
----------------------------------------------

-- header:
SELECT
    Nama_Payor,
    Nama_File
FROM
    TmpInsurance.dbo.prib_tmp_KlaimTo_Header
GROUP BY
    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_File
FROM
    TmpInsurance.dbo.prib_tmp_KlaimTo_Header
WHERE
    (Nama_Payor = 'EQUITY')
    AND (Nama_File = ' 30-01-2015 OTORITAS JASA KEUANGAN')
  -- Nama_Payor & Nama_File menggunakan hasil query diatas



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
FROM
    TmpInsurance.dbo.prib_tmp_KlaimTo_Detail
WHERE
    (Nama_Payor = 'EQUITY')
    AND (Nama_File = ' 30-01-2015 OTORITAS JASA KEUANGAN')  -- Nama_Payor & Nama_File menggunakan hasil query diatas
    