SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

-- last modified by Pribadi Pamungkas, 2014-09-16 
ALTER PROCEDURE [dbo].[proc_create_ref_klaim_v2]
      @pi_change_by VARCHAR(4),
      @pi_no_proposal VARCHAR(9),
      @pi_id_dokumen_terima VARCHAR(12),
      @pi_tgl_transaksi DATETIME,
      @pi_jenis INT,
      @pi_jenis_pembayaran VARCHAR(1),
      @pi_admedika_oke BIT,
      @pi_admedikaya BIT,
      @pi_bayarya BIT,
      @pi_kode_provider VARCHAR(6),
      @pi_cabang VARCHAR(4),
      @pi_bayar VARCHAR(10),
      @pi_id_user VARCHAR(4),
      @pi_id_kelklaim VARCHAR(4),
      @pi_tgl_bayar DATETIME,
      @pi_tgl_bayarYa BIT,
      @po_message VARCHAR(200) OUTPUT
AS 
      SET @po_message = ''
      DECLARE @Jml_Peserta INT
      DECLARE @Count INT
      DECLARE @Tipe INT
      DECLARE @Kode_Provider VARCHAR(06)
      DECLARE @No_Ref VARCHAR(11)
      DECLARE @Aso_Ya BIT
      DECLARE @Aso_Paket BIT
      DECLARE @Aso_Jenis SMALLINT
      DECLARE @waktusekarang DATETIME
      
      SET @waktusekarang = dbo.func_FixedDate(GETDATE())

      EXEC gproc_GetNo 1, @pi_tgl_transaksi, 'No_Ref', @No_Ref OUTPUT


      SELECT
        @Tipe = Tipe
      FROM
        Note_Jenis
      WHERE
        Kode = @pi_jenis

      IF NOT EXISTS ( SELECT * FROM Note_Terbit_Master WHERE No_Ref = @No_Ref ) 
         BEGIN   
               BEGIN TRAN   
               INSERT   INTO Klaim_Ref_Detail
                        (No_Ref,
                         No_Klaim,
                         Kode_Santunan,
                         Id_Produk,
                         Sub_Kelompok,
                         No_Peserta,
                         No_Santunan,
                         No_Proposal,
                         Cabang,
                         Kode_Diagnosa,
                         Kode_Provider,
                         Tgl_Klaim,
                         Jenis_Pembayaran,
                         Tgl_Kwitansi,
                         Jml_Benefit,
                         Jml_Diajukan,
                         Jml_Deductible,
                         Jml_Dibayar,
                         Jml_Ditolak,
                         Jml_Dispensasi,
                         Kode_Tolakan,
                         Keterangan,
                         Status,
                         Jml_Dibayar_Old,
                         Uang_Muka,
                         Diskon_Rs,
                         Flag,
                         Create_By,
                         Create_Date)
                        SELECT
                            @No_Ref,
                            Klaim_Peserta.No_Klaim,
                            Klaim_Detail.Kode_Santunan,
                            Klaim_Peserta.Id_Produk,
                            Klaim_Peserta.Sub_Kelompok,
                            Klaim_Peserta.No_Peserta,
                            Klaim_Peserta.No_Santunan,
                            Klaim_Peserta.No_Proposal,
                            Peserta.Cabang,
                            Klaim_Peserta.Kode_Diagnosa,
                            Klaim_Peserta.Kode_Provider,
                            Klaim_Peserta.Tgl_Klaim,
                            Klaim_Peserta.Jenis_Pembayaran,
                            Klaim_Peserta.Tgl_Kwitansi,
                            Klaim_Detail.Jml_Benefit,
                            Klaim_Detail.Jml_Diajukan,
                            Klaim_Detail.Jml_Deductible,
                            Klaim_Detail.Jml_Dibayar,
                            Klaim_Detail.Jml_Ditolak,
                            Klaim_Detail.Jml_Dispensasi,
                            Klaim_Detail.Kode_Tolakan,
                            Klaim_Detail.Keterangan AS Keterangan,
                            Klaim_Peserta.Status,
                            ISNULL(Klaim_Detail.Jml_Dibayar_Old, 0) AS Jml_Dibayar_Old,
                            Klaim_Peserta.Uang_Muka,
                            Klaim_Peserta.Diskon_RS,
                            Klaim_Peserta.Flag,
                            @pi_id_user,
                            @waktusekarang
                        FROM
                            Klaim_Peserta
                            INNER JOIN Klaim_Detail
                            ON Klaim_Peserta.No_Klaim = Klaim_Detail.No_Klaim
                            INNER JOIN Peserta
                            ON Klaim_Peserta.No_Proposal = Peserta.No_Proposal AND Klaim_Peserta.No_Peserta = Peserta.No_Peserta
                            LEFT OUTER JOIN (
                                             SELECT
                                                dbo.Polis_Asuransi.No_Proposal,
                                                dbo.Peserta_Rekening.No_Peserta,
                                                dbo.Peserta_Rekening.Rek_No
                                             FROM
                                                dbo.Peserta_Rekening
                                                INNER JOIN dbo.Polis_Asuransi
                                                ON dbo.Peserta_Rekening.No_Polis = dbo.Polis_Asuransi.No_Polis
                                            ) T2
                            ON dbo.Klaim_Peserta.No_Proposal = T2.No_Proposal AND LEFT(dbo.Klaim_Peserta.No_Santunan, 11) + '0' = T2.No_Peserta
                        WHERE
                            (Klaim_Peserta.change_by = @pi_change_by OR COALESCE(@pi_change_by, '') = '') AND (Klaim_Peserta.Status <> 'C') AND (Klaim_Peserta.Status IN ('G', 'E')) AND (ISNULL(Klaim_Peserta.No_Proposal, '') = ISNULL(@pi_no_proposal, '')) AND (ISNULL(Klaim_peserta.Jenis, 0) <> 0) AND (ISNULL(Klaim_Detail.Jml_Diajukan, 0) <> 0) AND (Klaim_peserta.Tgl_Klaim = @pi_tgl_transaksi) AND (ISNULL(Klaim_peserta.Jenis, 0) = ISNULL(@pi_jenis, 0)) AND (ISNULL(Klaim_peserta.Jenis_Pembayaran, '') = ISNULL(@pi_jenis_pembayaran, '')) AND (ISNULL(Klaim_peserta.AdMedika_Ya, 0) = @pi_admedikaya OR ISNULL(@pi_admedika_oke, 0) = 0) AND (ISNULL(Klaim_peserta.Kode_provider, '') = ISNULL(@pi_kode_provider, '') OR ISNULL(@pi_kode_provider, '') = '' OR ISNULL(@pi_kode_provider, '') = '0') AND ((Jenis >= 20 AND Jenis <= 30) OR (Jenis >= 40 AND Jenis <= 49)) AND (ISNULL(Peserta.Cabang, '') = ISNULL(@pi_cabang, '') OR ISNULL(@pi_cabang, '') = '') AND (UPPER(dbo.func_GetRekNo(dbo.Klaim_Peserta.No_Proposal, dbo.Klaim_Peserta.No_Peserta, ISNULL(@pi_bayarya, 0))) = UPPER(@pi_bayar) OR ISNULL(@pi_bayarya, 0) = 0)
            
               IF EXISTS ( SELECT * FROM Klaim_Ref_master WHERE No_Ref = @No_Ref ) 
                  DELETE FROM
                    Klaim_Ref_master
                  WHERE
                    No_Ref = @No_Ref
            
               INSERT   INTO Klaim_Ref_master
                        (No_Ref,
                         No_Klaim,
                         Id_Produk,
                         Sub_Kelompok,
                         No_Peserta,
                         No_Santunan,
                         No_Proposal,
                         Cabang,
                         Kode_Diagnosa,
                         Kode_Provider,
                         Tgl_Klaim,
                         Jenis_Pembayaran,
                         Tgl_Kwitansi,
                         Jml_Diajukan,
                         Jml_Deductible,
                         Jml_Dibayar,
                         Jml_Ditolak,
                         Jml_Dispensasi,
                         Status,
                         Jml_Dibayar_Old,
                         Uang_Muka,
                         Diskon_Rs,
                         Flag,
                         Create_By,
                         Create_Date)
                        SELECT
                            @No_Ref,
                            Klaim_Peserta.No_Klaim,
                            Klaim_Peserta.Id_Produk,
                            Klaim_Peserta.Sub_Kelompok,
                            Klaim_Peserta.No_Peserta,
                            Klaim_Peserta.No_Santunan,
                            Klaim_Peserta.No_Proposal,
                            Peserta.Cabang,
                            Klaim_Peserta.Kode_Diagnosa,
                            Klaim_Peserta.Kode_Provider,
                            Klaim_Peserta.Tgl_Klaim,
                            Klaim_Peserta.Jenis_Pembayaran,
                            Klaim_Peserta.Tgl_Kwitansi,
                            Klaim_Peserta.Jml_Diajukan,
                            Klaim_Peserta.Jml_Deductible,
                            Klaim_Peserta.Jml_Dibayar,
                            Klaim_Peserta.Jml_Ditolak,
                            Klaim_Peserta.Jml_Dispensasi,
                            Klaim_Peserta.Status,
                            Klaim_Peserta.Jml_Dibayar_Old,
                            Klaim_Peserta.Uang_Muka,
                            Klaim_Peserta.Diskon_RS,
                            Klaim_Peserta.Flag,
                            @pi_id_user,
                            @waktusekarang
                        FROM
                            Klaim_Peserta
                            INNER JOIN Peserta
                            ON Klaim_Peserta.No_Proposal = Peserta.No_Proposal AND Klaim_Peserta.No_Peserta = Peserta.No_Peserta
                            LEFT OUTER JOIN (
                                             SELECT
                                                dbo.Polis_Asuransi.No_Proposal,
                                                dbo.Peserta_Rekening.No_Peserta,
                                                dbo.Peserta_Rekening.Rek_No
                                             FROM
                                                dbo.Peserta_Rekening
                                                INNER JOIN dbo.Polis_Asuransi
                                                ON dbo.Peserta_Rekening.No_Polis = dbo.Polis_Asuransi.No_Polis
                                            ) T2
                            ON dbo.Klaim_Peserta.No_Proposal = T2.No_Proposal AND LEFT(dbo.Klaim_Peserta.No_Santunan, 11) + '0' = T2.No_Peserta
                        WHERE
                            (Klaim_Peserta.change_by = @pi_change_by OR COALESCE(@pi_change_by, '') = '') AND (Klaim_Peserta.Status <> 'C') AND (Klaim_Peserta.Status IN ('G', 'E')) AND (Klaim_Peserta.No_Proposal = @pi_no_proposal) AND (Klaim_peserta.Jenis <> 0) AND (Klaim_peserta.Tgl_Klaim = @pi_tgl_transaksi) AND (Klaim_peserta.Jenis = @pi_jenis) AND (Klaim_peserta.Jenis_Pembayaran = @pi_jenis_pembayaran) AND (ISNULL(Klaim_peserta.AdMedika_Ya, 0) = @pi_admedikaya OR ISNULL(@pi_admedika_oke, 0) = 0) AND (ISNULL(Klaim_peserta.Kode_provider, '') = ISNULL(@pi_kode_provider, '') OR ISNULL(@pi_kode_provider, '') = '' OR ISNULL(@pi_kode_provider, '') = '0') AND ((Jenis >= 20 AND Jenis <= 30) OR (Jenis >= 40 AND Jenis <= 49)) AND (ISNULL(Peserta.Cabang, '') = ISNULL(@pi_cabang, '') OR ISNULL(@pi_cabang, '') = '') AND (UPPER(dbo.func_GetRekNo(dbo.Klaim_Peserta.No_Proposal, dbo.Klaim_Peserta.No_Peserta, @pi_bayarya)) = UPPER(@pi_bayar) OR ISNULL(@pi_bayarya, 0) = 0)
            
               SET @Jml_Peserta = 0
               IF EXISTS ( SELECT 1 FROM Klaim_Ref_Detail WHERE No_Ref = @No_Ref ) 
                  SELECT
                    @jml_peserta = COUNT(DISTINCT (no_klaim))
                  FROM
                    Klaim_Ref_Detail
                  WHERE
                    No_Ref = @No_Ref

               SELECT
                @Count = COUNT(Cabang)
               FROM
                Klaim_Ref_Detail
               WHERE
                ISNULL(Cabang, '') <> '' AND No_Ref = @No_Ref
               IF @Count = 1 
                  BEGIN
                        SELECT
                            @pi_cabang = Cabang
                        FROM
                            Klaim_Ref_Detail
                        WHERE
                            ISNULL(Cabang, '') <> '' AND No_Ref = @No_Ref
                        GROUP BY
                            Cabang
                  END

               SELECT
                @Count = COUNT(Kode_provider)
               FROM
                Klaim_Ref_Detail
               WHERE
                ISNULL(Kode_Provider, '') <> '' AND No_Ref = @No_Ref
               IF @Count = 1 
                  BEGIN
                        SELECT
                            @Kode_Provider = Kode_Provider
                        FROM
                            Klaim_Ref_Detail
                        WHERE
                            ISNULL(Kode_Provider, '') <> '' AND No_Ref = @No_Ref
                  END

               INSERT   INTO Note_Terbit_Master
                        (No_Ref,
                         Look_Ref,
                         Tgl_Buat,
                         No_Proposal,
                         id_dokumen_terima,
                         No_Polis,
                         GT_OG,
                         Sub_Init,
                         Tgl_Berlaku,
                         Kode_Provider,
                         Jenis,
                         Tipe,
                         Jml_Peserta,
                         Periode_Bayar,
                         Mata_Uang,
                         Tgl_Awal,
                         Tgl_Akhir,
                         Cabang,
                         Status,
                         Create_Date,
                         Create_By,
                         Change_Date,
                         Change_By,
                         Id_KelKLaim,
                         Tgl_Bayar,
                         Bayar)
                        SELECT
                            @No_Ref,
                            @No_Ref,
                            dbo.func_FixedDate(GETDATE()),
                            Polis_ASuransi.No_Proposal,
                            @pi_id_dokumen_terima,
                            Polis_ASuransi.No_Polis,
                            Perusahaan.GT_OG,
                            Polis_ASuransi.Sub_Init,
                            Polis_ASuransi.Tgl_Mulai,
                            @Kode_Provider,
                            @pi_jenis,
                            @Tipe,
                            @Jml_Peserta,
                            Polis_ASuransi.Periode_Bayar,
                            Polis_ASuransi.Mata_Uang,
                            @pi_tgl_transaksi,
                            @pi_tgl_transaksi,
                            @pi_cabang,
                            'G',
                            dbo.func_FixedDate(GETDATE()),
                            @pi_id_user,
                            dbo.func_FixedDate(GETDATE()),
                            @pi_id_user,
                            @pi_id_kelklaim,
                            @pi_tgl_bayar,
                            @pi_bayar
                        FROM
                            Polis_Asuransi
                            INNER JOIN Perusahaan
                            ON Polis_Asuransi.No_Prs = Perusahaan.No_Prs
                        WHERE
                            Polis_Asuransi.No_Proposal = @pi_no_proposal

            ----------------------------
               DECLARE @Id_Payor VARCHAR(2)
               IF (@pi_jenis IN (20, 22, 26, 70, 72, 81, 21, 24, 71, 74) OR (@pi_jenis >= 40 AND @pi_jenis <= 49)) 
                  BEGIN
                        IF EXISTS ( SELECT * FROM Note_Terbit_Detail WHERE No_Ref = @No_Ref ) 
                           DELETE FROM
                            Note_Terbit_Detail
                           WHERE
                            No_Ref = @No_Ref     
                        INSERT  INTO Note_Terbit_Detail
                                (No_Ref,
                                 Sub_Kelompok,
                                 Jumlah,
                                 Diskon,
                                 Diskon_Cp,
                                 diskon_rs,
                                 PPN,
                                 Jml_Peserta,
                                 Santunan,
                                 Santunan_Duka,
                                 Santunan_Cacat,
                                 Santunan_Lain)
                                SELECT
                                    @No_Ref,
                                    Klaim_Peserta.Sub_Kelompok,
                                    Jumlah = CASE WHEN @pi_jenis IN (20, 22, 70, 72) THEN ABS(SUM(Jml_Ditolak))
                                                  WHEN @pi_jenis IN (26, 40, 42, 81) THEN ABS(SUM(Jml_Diajukan) - SUM(ISNULL(Diskon_Rs, 0)))
                                                  WHEN @pi_jenis IN (21, 24, 71, 74, 41) THEN ABS(SUM(ISNULL(Jml_Dibayar, 0) - ISNULL(Jml_Dibayar_Old, 0)))
                                             END,
                                    SUM(ISNULL(Uang_muka, 0)) AS Diskon,
                                    0 AS Diskon_Cp,
                                    SUM(ISNULL(diskon_rs, 0)),
                                    0 AS PPN,
                                    COUNT(DISTINCT No_Klaim) AS Jml_Peserta,
                                    0 AS Santunan,
                                    0 AS Santunan_Duka,
                                    0 AS Santunan_Cacat,
                                    0 AS Santunan_Lain
                                FROM
                                    Klaim_Peserta
                                    INNER JOIN Peserta
                                    ON Klaim_Peserta.No_Proposal = Peserta.No_Proposal AND Klaim_Peserta.No_Peserta = Peserta.No_Peserta
                                    LEFT OUTER JOIN (
                                                     SELECT
                                                        dbo.Polis_Asuransi.No_Proposal,
                                                        dbo.Peserta_Rekening.No_Peserta,
                                                        dbo.Peserta_Rekening.Rek_No
                                                     FROM
                                                        dbo.Peserta_Rekening
                                                        INNER JOIN dbo.Polis_Asuransi
                                                        ON dbo.Peserta_Rekening.No_Polis = dbo.Polis_Asuransi.No_Polis
                                                    ) T2
                                    ON dbo.Klaim_Peserta.No_Proposal = T2.No_Proposal AND LEFT(dbo.Klaim_Peserta.No_Santunan, 11) + '0' = T2.No_Peserta
                                WHERE
                                    (Klaim_Peserta.change_by = @pi_change_by OR COALESCE(@pi_change_by, '') = '') AND Klaim_Peserta.Sub_Kelompok IS NOT NULL AND (Klaim_Peserta.Status <> 'C') AND (Klaim_Peserta.Status IN ('G', 'E')) AND (Klaim_Peserta.No_Proposal = @pi_no_proposal) AND (Klaim_peserta.Jenis <> 0) AND (Klaim_peserta.Tgl_Klaim = dbo.func_fixedDate(@pi_tgl_transaksi)) AND (Klaim_peserta.Jenis = @pi_jenis) AND (Klaim_peserta.Jenis_Pembayaran = @pi_jenis_pembayaran) AND (ISNULL(Klaim_peserta.AdMedika_Ya, 0) = @pi_admedikaya OR ISNULL(@pi_admedika_oke, 0) = 0) AND (ISNULL(Klaim_peserta.Kode_provider, '') = ISNULL(@pi_kode_provider, '') OR ISNULL(@pi_kode_provider, '') = '' OR ISNULL(@pi_kode_provider, '') = '0') AND ((Jenis >= 20 AND Jenis <= 30) OR (Jenis >= 40 AND Jenis <= 49)) AND (ISNULL(Peserta.Cabang, '') = ISNULL(@pi_cabang, '') OR ISNULL(@pi_cabang, '') = '') AND (UPPER(dbo.func_GetRekNo(dbo.Klaim_Peserta.No_Proposal, dbo.Klaim_Peserta.No_Peserta, @pi_bayarya)) = UPPER(@pi_bayar) OR ISNULL(@pi_bayarya, 0) = 0)
                                GROUP BY
                                    Klaim_Peserta.Sub_Kelompok
                  END
               ELSE 
                  IF (@pi_jenis BETWEEN 1 AND 19) OR (@pi_jenis = 100) 
                     BEGIN
                           IF EXISTS ( SELECT
                                        *
                                       FROM
                                        Note_Terbit_Detail
                                       WHERE
                                        No_Ref = @No_Ref ) 
                              DELETE FROM
                                Note_Terbit_Detail
                              WHERE
                                No_Ref = @No_Ref     
                           INSERT   INTO Note_Terbit_Detail
                                    (No_Ref,
                                     Sub_Kelompok,
                                     Jumlah,
                                     Diskon,
                                     Diskon_Cp,
                                     PPN,
                                     Jml_Peserta,
                                     Santunan,
                                     Santunan_Duka,
                                     Santunan_Cacat,
                                     Santunan_Lain)
                                    SELECT
                                        @No_Ref AS No_Ref,
                                        Peserta_produk_detail.Sub_Kelompok,
                                        SUM(Peserta_produk_detail.Jumlah) AS Jumlah,
                                        SUM(Jumlah * (Produk_polis.Diskon_Pr / 100)) + SUM(Produk_polis.Diskon_PrRp) AS Diskon,
                                        SUM(Jumlah * (Produk_polis.Diskon_Cp / 100)) + SUM(Produk_polis.Diskon_CpRp) AS Diskon_Cp,
                                        0 AS PPN,
                                        COUNT(Peserta_produk_detail.No_Peserta) AS Jml_Peserta,
                                        SUM(ISNULL(Peserta_produk_detail.Curr_Santunan, 0) - ISNULL(Peserta_produk_detail.Curr_Santunan_Old, 0)) AS Santunan,
                                        SUM(ISNULL(Peserta_produk_detail.Santunan_Duka, 0) - ISNULL(Peserta_produk_detail.Santunan_Duka_Old, 0)) AS Santunan_Duka,
                                        SUM(ISNULL(Peserta_produk_detail.Santunan_Cacat, 0) - ISNULL(Peserta_produk_detail.Santunan_Cacat_Old, 0)) AS Santunan_Cacat,
                                        SUM(ISNULL(Peserta_produk_detail.Santunan_Lain, 0) - ISNULL(Peserta_produk_detail.Santunan_Lain_Old, 0)) AS Santunan_Lain
                                    FROM
                                        Peserta_Produk_Detail
                                        INNER JOIN Produk_Polis
                                        ON (Peserta_produk_detail.Id_Produk = Produk_polis.Id_Produk) AND (Peserta_produk_detail.No_Proposal = Produk_polis.No_Proposal)
                                    WHERE
                                        (No_Ref = @No_Ref) AND Peserta_produk_detail.Sub_Kelompok IS NOT NULL
                                    GROUP BY
                                        Peserta_produk_detail.Sub_Kelompok
                     END
                  ELSE 
                     IF @pi_jenis BETWEEN 60 AND 69 
                        BEGIN
                              DECLARE @No_Proposal VARCHAR(9)
                              DECLARE @Aso_Biaya BIT
                              DECLARE @Biaya_Kartu BIT
                              SELECT
                                @No_Proposal = No_Proposal
                              FROM
                                Note_Terbit_Master
                              WHERE
                                No_Ref = @No_Ref
                              SELECT
                                @Aso_Paket = ISNULL(Aso_Paket, 0),
                                @Aso_Jenis = ISNULL(Aso_Jenis, 0),
                                @Biaya_Kartu = ISNULL(Biaya_Kartu, 0),
                                @Aso_Biaya = ISNULL(Aso_Biaya, 0),
                                @Id_Payor = Id_Payor
                              FROM
                                Polis_Asuransi
                              WHERE
                                No_Proposal = @No_Proposal
                              IF ISNULL(@Aso_Paket, 0) = 1 AND ISNULL(@Aso_Jenis, 0) = 1 
                                 BEGIN
                                       IF EXISTS ( SELECT * FROM Note_Terbit_Detail WHERE No_Ref = @No_Ref ) 
                                          DELETE FROM
                                            Note_Terbit_Detail
                                          WHERE
                                            No_Ref = @No_Ref     
                                       INSERT   INTO Note_Terbit_Detail
                                                (No_Ref,
                                                 Sub_Kelompok,
                                                 Jumlah,
                                                 Diskon,
                                                 Diskon_Cp,
                                                 PPN,
                                                 Jml_Peserta,
                                                 Santunan,
                                                 Santunan_Duka,
                                                 Santunan_Cacat,
                                                 Santunan_Lain)
                                                SELECT
                                                    @No_Ref AS No_Ref,
                                                    '1010' AS Sub_Kelompok,
                                                    SUM(Jumlah) AS Jumlah,
                                                    0 AS Diskon,
                                                    0 AS Diskon_Cp,
                                                    PPN = CASE WHEN @Id_Payor = '01' THEN 0
                                                               ELSE 0.01 * 10 * SUM(Jumlah)
                                                          END,
                                                    COUNT(No_Peserta) AS Jml_Peserta,
                                                    0 AS Santunan,
                                                    0 AS Santunan_Duka,
                                                    0 AS Santunan_Cacat,
                                                    0 AS Santunan_Lain
                                                FROM
                                                    dbo.Note_Peserta_Aso
                                                WHERE
                                                    No_Ref = @No_Ref
                                                HAVING
                                                    SUM(Jumlah) <> 0
                                                UNION
                    --INSERT INTO Note_Terbit_Detail (No_Ref, Sub_Kelompok, Jumlah, Diskon, Diskon_Cp, PPN, Jml_Peserta, Santunan, Santunan_Duka, Santunan_Cacat, Santunan_Lain)
                                                SELECT
                                                    @No_Ref AS No_Ref,
                                                    '1011' AS Sub_Kelompok,
                                                    SUM(Biaya_Kartu) AS Jumlah,
                                                    0 AS Diskon,
                                                    0 AS Diskon_Cp,
                                                    0.01 * 10 * SUM(Biaya_Kartu) AS PPN,
                                                    COUNT(No_Peserta) AS Jml_Peserta,
                                                    0 AS Santunan,
                                                    0 AS Santunan_Duka,
                                                    0 AS Santunan_Cacat,
                                                    0 AS Santunan_Lain
                                                FROM
                                                    dbo.Note_Peserta_Aso
                                                WHERE
                                                    No_Ref = @No_Ref
                                                HAVING
                                                    SUM(Biaya_Kartu) <> 0
                                 END
                        END
               COMMIT TRAN  
   
  
               DECLARE @No_Klaim VARCHAR(11)

               DECLARE Employee_Cursor CURSOR
               FOR
                       SELECT DISTINCT
                        (No_Klaim)
                       FROM
                        Klaim_Ref_Detail
                       WHERE
                        No_Ref = @No_Ref 
               OPEN Employee_Cursor
               FETCH NEXT FROM Employee_Cursor INTO @No_Klaim
          
               WHILE @@FETCH_STATUS = 0 
                     BEGIN
            
                           UPDATE
                            Klaim_Peserta
                           SET
                            Status = 'I',
                            Id_RegKlaim = @pi_id_dokumen_terima -- by Pribadi Pamungkas, 2014-09-16 
                           WHERE
                            status IN ('G', 'E') AND No_Klaim = @No_Klaim
              
                           PRINT @No_Klaim
                           
                           -- start: update custport, by Pribadi Pamungkas. 2016-08-14
                           IF EXISTS(SELECT * FROM custport_klaim WHERE core_no_klaim = @No_Klaim)
                              BEGIN
                                --IF EXISTS(SELECT * FROM custport_klaim_email WHERE )
                                  DECLARE
                                    @custport_create_by INT = NULL,
                                    @custport_no_peserta VARCHAR(12),
                                    @custport_no_polis VARCHAR(12),
                                    @custport_email VARCHAR(100),
                                    @custport_id_klaim INT
                                    
                                  SELECT
                                    @custport_create_by = create_by,
                                    @custport_no_peserta = no_peserta,
                                    @custport_no_polis = no_polis,
                                    @custport_id_klaim = id_klaim
                                  FROM custport_klaim 
                                  WHERE core_no_klaim = @No_Klaim
                                  
                                  IF @custport_create_by IS NULL
                                    BEGIN
                                      SELECT TOP 1
                                      	@custport_email = username
                                      FROM
                                      	dbo.custport_user_login_client
                                      WHERE
                                        no_peserta = @custport_no_peserta
                                        AND no_polis = @custport_no_polis
                                    END
                                  ELSE
                                    BEGIN
                                      SELECT TOP 1
                                      	@custport_email = username
                                      FROM
                                      	dbo.custport_user_login_client
                                      WHERE
                                        id_user = @custport_create_by
                                    END
                                  
                                  DECLARE @status_cusport VARCHAR(20)
                                  SELECT
                                  	@status_cusport = CASE 
                                  	                    WHEN CAST(Jml_Dibayar AS NUMERIC(20,2))=0 THEN 'Not approved'
                                  	                    ELSE 'Approved'
                                  	                  END
                                  FROM
                                  	dbo.Klaim_Peserta
                                  WHERE
                                    No_Klaim = @No_Klaim
                                    
                                  UPDATE
                                    custport_klaim
                                  SET
                                    status = @status_cusport
                                  WHERE
                                    core_no_klaim = @No_Klaim
                                  
                                  INSERT INTO [GrpInsurance].[dbo].[custport_klaim_email]
                                         ([id_klaim]
                                         ,[email_to]
                                         ,[email_link]
                                         ,[queue]
                                         ,[onprocess]
                                         ,[sent])
                                   VALUES
                                         (@custport_id_klaim
                                         ,@custport_email
                                         ,NULL
                                         ,1
                                         ,0
                                         ,0)
                              END
                           -- end: update custport, by Pribadi Pamungkas. 2016-08-14
              
                           FETCH NEXT 
           FROM Employee_Cursor INTO @No_Klaim
                     END

               CLOSE Employee_Cursor
               DEALLOCATE Employee_Cursor

               EXEC proc_SetNoteTerbit3 @No_Ref
               SET @po_message = 'Create Referensi sukses. dengan Nomor Referensi: ' + ISNULL(@No_Ref, '')
               RETURN 0
        -------------------------------
         END
      ELSE 
         BEGIN
               SET @po_message = 'Create Referensi tidak sukses.'
               RETURN 1
         END
GO
