USE [GrpInsurance]
GO

/****** Object:  StoredProcedure [dbo].[proc_Rep10367]    Script Date: 10/07/2015 17:56:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- last modified by Pribadi Pamungkas, 2015-10-07
-- menghitung nilai ERP
ALTER PROCEDURE [dbo].[proc_Rep10367_hitung]
      @GroupPrs VARCHAR(5),
      @GTOG INT,
      @NoProposal VARCHAR(9),
      @TglMulai1 DATETIME,
      @TglMulai2 DATETIME,
      @TglSelesai1 DATETIME,
      @TglSelesai2 DATETIME,
      @TglMulai1Ya BIT,
      @TglMulai2Ya BIT,
      @TglSelesai1Ya BIT,
      @TglSelesai2Ya BIT
AS 
SET NOCOUNT ON

EXEC TmpInsurance.dbo.gproc_DelTable 'Rep10367'  
CREATE TABLE TmpInsurance.dbo.Rep10367 (
        [No_Proposal] [varchar](9) NOT NULL,
        [Id_Produk] [varchar](10) NOT NULL,
        [Jml_Premi] [float] NULL,
        [Jml_Diajukan] [float] NULL,
        [Jml_Klaim] [float] NULL,
        CONSTRAINT [PK_Rep10367] PRIMARY KEY CLUSTERED ([No_Proposal], [Id_Produk]) ON [PRIMARY]
       )
ON     [PRIMARY]
DECLARE @GT_OG VARCHAR(2) 
DECLARE @No_Proposal VARCHAR(9) 
DECLARE @Id_Produk VARCHAR(10) 
DECLARE @Jml_Premi FLOAT
DECLARE @Jml_Diajukan FLOAT
DECLARE @Jml_Klaim FLOAT
IF @GTOG = 1 
   SET @GT_OG = 'GT'
ELSE 
   IF @GTOG = 2 
      SET @GT_OG = 'OG'
   ELSE 
      SET @GT_OG = ''
DECLARE Tmp_Cursor CURSOR
FOR
SELECT
  dbo.Note_Terbit_Master.No_Proposal,
  dbo.Peserta_Produk_Detail.Id_Produk, 
--         SUM(dbo.Note_Terbit_Master.Tipe * dbo.Peserta_Produk_Detail.Jumlah) AS Jml_Premi
  SUM(dbo.Note_Terbit_Master.Tipe * ABS(ISNULL(dbo.Peserta_Produk_Detail.Jumlah, 0))) AS Jml_Premi
FROM
  dbo.Note_Terbit_Master
  INNER JOIN dbo.Peserta_Produk_Detail ON dbo.Note_Terbit_Master.No_Ref = dbo.Peserta_Produk_Detail.No_Ref
  INNER JOIN dbo.Peserta ON dbo.Note_Terbit_Master.No_Proposal = dbo.Peserta.No_Proposal
                            AND dbo.Peserta_Produk_Detail.No_Peserta = dbo.Peserta.No_Peserta
  INNER JOIN dbo.Polis_Asuransi ON dbo.Note_Terbit_Master.No_Proposal = dbo.Polis_Asuransi.No_Proposal
  INNER JOIN dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs
WHERE
  (dbo.Note_Terbit_Master.Jenis <= 19)
  AND (dbo.Note_Terbit_Master.Status <> 'C')
  AND (LEFT(dbo.Peserta_Produk_Detail.Sub_Kelompok, 2) = '03')
  AND (ISNULL(dbo.Polis_Asuransi.Erp_Prosen, 0) <> 0)
  --AND (dbo.Peserta.Status<>'C') 
  AND (dbo.Perusahaan.Group_Perusahaan = @GroupPrs
  OR ISNULL(@GroupPrs, '') = '')
  AND (dbo.Perusahaan.GT_OG = @GT_OG
  OR ISNULL(@GT_OG, '') = '')
  AND (dbo.Note_Terbit_Master.No_Proposal = @NoProposal
  OR ISNULL(@NoProposal, '') = '') 
  --AND (dbo.Note_Terbit_Master.Tgl_Buat>=@TglPremi1 OR isnull(@TglPremi1Ya,0)=0)
  --AND (dbo.Note_Terbit_Master.Tgl_Buat<=@TglPremi2 OR isnull(@TglPremi2Ya,0)=0)
  AND (dbo.Polis_Asuransi.Tgl_Mulai >= @TglMulai1
  OR ISNULL(@TglMulai1Ya, 0) = 0)
  AND (dbo.Polis_Asuransi.Tgl_Mulai <= @TglMulai2
  OR ISNULL(@TglMulai2Ya, 0) = 0)
  AND (dbo.Polis_Asuransi.Tgl_Selesai >= @TglSelesai1
  OR ISNULL(@TglSelesai1Ya, 0) = 0)
  AND (dbo.Polis_Asuransi.Tgl_Selesai <= @TglSelesai2
  OR ISNULL(@TglSelesai2Ya, 0) = 0)
GROUP BY
  dbo.Note_Terbit_Master.No_Proposal,
  dbo.Peserta_Produk_Detail.Id_Produk
OPEN Tmp_Cursor
FETCH NEXT FROM Tmp_Cursor INTO @No_Proposal, @Id_Produk, @Jml_Premi
WHILE @@FETCH_STATUS = 0 
      BEGIN
            SET @Jml_Klaim = 0
            SET @Jml_Diajukan = 0
            IF EXISTS ( SELECT
                          *
                        FROM
                          dbo.Note_Terbit_Master
                          INNER JOIN dbo.Klaim_Ref_Detail ON dbo.Note_Terbit_Master.No_Ref = dbo.Klaim_Ref_Detail.No_Ref
                        WHERE
                          (dbo.Note_Terbit_Master.Jenis IN (20, 21, 22, 23, 24, 25, 40, 41, 42, 43, 44, 45))
                          AND (dbo.Note_Terbit_Master.Status <> 'C')
                          AND (dbo.Note_Terbit_Master.No_Proposal = @No_Proposal)
                          AND (LEFT(dbo.Klaim_Ref_Detail.Sub_Kelompok, 2) = '03')
                          AND (dbo.Klaim_Ref_Detail.Id_Produk = @Id_Produk) )
    --AND (dbo.Note_Terbit_Master.Tgl_Buat>=@TglKlaim1 OR isnull(@TglKlaim1Ya,0)=0)
    --AND (dbo.Note_Terbit_Master.Tgl_Buat<=@TglKlaim2 OR isnull(@TglKLaim2Ya,0)=0))
               SELECT
                @Jml_Diajukan = SUM(ABS(dbo.Klaim_Ref_Detail.Jml_Diajukan)),
                @Jml_Klaim = SUM(ABS(dbo.Klaim_Ref_Detail.Jml_Dibayar))
               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 dbo.Peserta ON dbo.Note_Terbit_Master.No_Proposal = dbo.Peserta.No_Proposal
                                          AND dbo.Klaim_Ref_Detail.No_Peserta = dbo.Peserta.No_Peserta
                INNER JOIN dbo.Polis_Asuransi ON dbo.Note_Terbit_Master.No_Proposal = dbo.Polis_Asuransi.No_Proposal
               WHERE
                (dbo.Note_Terbit_Master.Jenis IN (20, 21, 22, 23, 24, 25, 40, 41, 42, 43, 44, 45))
                AND (dbo.Note_Terbit_Master.Status <> 'C')
                AND (dbo.Note_Terbit_Master.No_Proposal = @No_Proposal)
                AND (dbo.Klaim_Ref_Detail.Id_Produk = @Id_Produk)
                AND (LEFT(dbo.Klaim_Ref_Detail.Sub_Kelompok, 2) = '03')
    --AND (dbo.Note_Terbit_Master.Tgl_Buat>=@TglKlaim1 OR isnull(@TglKlaim1Ya,0)=0)
    --AND (dbo.Note_Terbit_Master.Tgl_Buat<=@TglKlaim2 OR isnull(@TglKLaim2Ya,0)=0)
            BEGIN TRAN
            INSERT  INTO TmpInsurance.dbo.Rep10367
                    (No_Proposal,
                     Id_Produk,
                     Jml_Premi,
                     Jml_Diajukan,
                     Jml_Klaim)
            VALUES
                    (@No_Proposal,
                     @Id_Produk,
                     ROUND(@Jml_Premi, 2),
                     ROUND(@Jml_Diajukan, 2),
                     ROUND(@Jml_Klaim, 2)) 
            IF @@Error > 0 
               ROLLBACK TRAN
            ELSE 
               COMMIT TRAN
            FETCH NEXT FROM Tmp_Cursor INTO @No_Proposal, @Id_Produk, @Jml_Premi
      END
CLOSE Tmp_Cursor
DEALLOCATE Tmp_Cursor

SELECT
  dbo.Perusahaan.Nama_Perusahaan + ' [' + dbo.Polis_Asuransi.No_Polis + '] (' + dbo.func_DateToStr(dbo.Polis_Asuransi.Tgl_Mulai) + '-' + dbo.func_DateToStr(dbo.Polis_Asuransi.Tgl_Selesai) + ')' AS Nama_Perusahaan,
  dbo.Polis_Asuransi.Erp_Prosen,
  dbo.Polis_Asuransi.Adm_Erp,
  SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) AS Jml_Premi,
  SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr) AS Diskon_Pr,
  SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) - SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr) AS Premi_Net,
  SUM(TmpInsurance.dbo.Rep10367.Jml_Klaim) AS Jml_Dibayar,
  (SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) - SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr)) * (0.01 * dbo.Polis_Asuransi.Adm_Erp) AS Jml_Adm,
  --ISNULL(dbo.Polis_Asuransi.tagihan_lalu, 0) AS tagihan_lalu, -- remark by Pribadi Pamungkas, 2015-10-05
  0.0 AS tagihan_lalu, -- add by Pribadi Pamungkas, 2015-10-05
  ((SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) - SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr)) - (((SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) - SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr)) * (0.01 * dbo.Polis_Asuransi.Adm_Erp)) + SUM(TmpInsurance.dbo.Rep10367.Jml_Klaim) - ISNULL(dbo.Polis_Asuransi.Tagihan_Lalu, 0))) AS Jml_Beban,
  Jml_Erp = CASE WHEN 0.01 * dbo.Polis_Asuransi.Erp_Prosen * ((SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) - SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr)) - (((SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) - SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr)) * (0.01 * dbo.Polis_Asuransi.Adm_Erp)) + SUM(TmpInsurance.dbo.Rep10367.Jml_Klaim) - ISNULL(dbo.Polis_Asuransi.Tagihan_Lalu, 0))) >= 0 THEN 0.01 * dbo.Polis_Asuransi.Erp_Prosen * ((SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) - SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr)) - (((SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) - SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr)) * (0.01 * dbo.Polis_Asuransi.Adm_Erp)) + SUM(TmpInsurance.dbo.Rep10367.Jml_Klaim) - ISNULL(dbo.Polis_Asuransi.Tagihan_Lalu, 0)))
                 ELSE 0
            END
--CASE
--WHEN SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) - SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr)-(SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) - SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr)*(dbo.Polis_Asuransi.Adm_Erp/100)-isnull(dbo.Polis_Asuransi.Tagihan_Lalu,0))<=0 THEN 0
--ELSE 0.01*dbo.Polis_Asuransi.Erp_Prosen*SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) - SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr)-(SUM(TmpInsurance.dbo.Rep10367.Jml_Premi) - SUM(TmpInsurance.dbo.Rep10367.Jml_Premi * 0.01 * dbo.Produk_Polis.Diskon_Pr)*(dbo.Polis_Asuransi.Adm_Erp/100)-isnull(dbo.Polis_Asuransi.Tagihan_Lalu,0))
--END
FROM
  TmpInsurance.dbo.Rep10367
  INNER JOIN dbo.Polis_Asuransi ON TmpInsurance.dbo.Rep10367.No_Proposal = dbo.Polis_Asuransi.No_Proposal
  INNER JOIN dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs
  INNER JOIN dbo.Produk_Polis ON TmpInsurance.dbo.Rep10367.No_Proposal = dbo.Produk_Polis.No_Proposal
                                 AND TmpInsurance.dbo.Rep10367.Id_Produk = dbo.Produk_Polis.Id_Produk
  INNER JOIN dbo.Sub_Kelompok ON dbo.Produk_Polis.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok
GROUP BY
  dbo.Perusahaan.Nama_Perusahaan + ' [' + dbo.Polis_Asuransi.No_Polis + '] (' + dbo.func_DateToStr(dbo.Polis_Asuransi.Tgl_Mulai) + '-' + dbo.func_DateToStr(dbo.Polis_Asuransi.Tgl_Selesai) + ')',
  dbo.Polis_Asuransi.Adm_Erp,
  dbo.Polis_Asuransi.tagihan_lalu,
  dbo.Polis_Asuransi.Erp_Prosen
ORDER BY
  dbo.Perusahaan.Nama_Perusahaan + ' [' + dbo.Polis_Asuransi.No_Polis + '] (' + dbo.func_DateToStr(dbo.Polis_Asuransi.Tgl_Mulai) + '-' + dbo.func_DateToStr(dbo.Polis_Asuransi.Tgl_Selesai) + ')'

GO


