USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep16111_tes]    Script Date: 03/20/2012 11:19:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_Rep16111_tes]
--@TglAwal Datetime, 
--@TglAkhir Datetime, 
--@Built bit,
--@TglAwalYa bit, 
--@TglAkhirYa bit 
AS
DECLARE 
@TglAwal Datetime='2010-01-01', 
@TglAkhir Datetime='2010-02-01', 
@Built bit=1,
@TglAwalYa bit=1, 
@TglAkhirYa bit =1

--IF @Built=1 
--BEGIN
  EXEC TmpInsurance.dbo.gproc_DelTable 'Peserta_Current'
  CREATE TABLE [TmpInsurance].[dbo].[Peserta_Current] (
	[No_Proposal] [varchar] (9) NOT NULL ,
	[No_Peserta] [varchar] (12) NOT NULL ,
	[Tgl_Mulai] [datetime] NULL ,
	[Tgl_Selesai] [datetime] NULL ,
	[Tgl_Lapse] [datetime] NULL ,
	[Peserta_Awal] [int] NULL CONSTRAINT [DF_Peserta_Current_Peserta_Awal] DEFAULT (0),
	[Peserta_Baru] [int] NULL CONSTRAINT [DF_Peserta_Current_Peserta_Baru] DEFAULT (0),
	[Peserta_Lapse] [int] NULL CONSTRAINT [DF_Peserta_Current_Peserta_Lapse] DEFAULT (0),
	[Peserta_Akhir] [int] NULL CONSTRAINT [DF_Peserta_Current_Peserta_Akhir] DEFAULT (0),
	[UP_Awal] [float] NULL ,
	[UP_Baru] [float] NULL ,
	[UP_Lapse] [float] NULL ,
	[UP_Akhir] [float] NULL ,
	[Premi_Awal] [float] NULL ,
	[Premi_Baru] [float] NULL ,
	[Premi_Lapse] [float] NULL ,
	[Premi_Akhir] [float] NULL ,
--	CONSTRAINT [PK_Peserta_Current] PRIMARY KEY  CLUSTERED 
--	(
--		[No_Proposal],
--		[No_Peserta]
--	)  ON [PRIMARY] 
) ON [PRIMARY]

EXEC TmpInsurance.dbo.gproc_DelTable 'Peserta_Produk_Current'
  CREATE TABLE [TmpInsurance].[dbo].[Peserta_Produk_Current] (
	[No_Proposal] [varchar] (11) NOT NULL ,
	[No_Peserta] [varchar] (12) NOT NULL ,
	[Id_Produk] [varchar] (10) NOT NULL ,
	[Kelas] [varchar] (50) NULL ,
	[Jml_Premi] [float] NULL ,
	[Jml_Up] [float] NULL ,
	[New_Up] [float] NULL ,
	--CONSTRAINT [PK_Peserta_Produk_Current] PRIMARY KEY  CLUSTERED 
	--(
	--	[No_Proposal],
	--	[No_Peserta],
	--	[Id_Produk]
	--)  ON [PRIMARY] 
) ON [PRIMARY]

EXEC TmpInsurance.dbo.gproc_DelTable 'Polis_Current'
  CREATE TABLE [TmpInsurance].[dbo].[Polis_Current] (
	[No_Proposal] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Sub_Kelompok] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Nama_Perusahaan] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[No_Prs] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Tgl_Masuk] [datetime] NULL ,
	[Tgl_Mulai] [datetime] NULL ,
	[Tgl_Selesai] [datetime] NULL ,
	
	[Polis_Awal] [int] NULL CONSTRAINT [DF_Polis_Current_Polis_Awal] DEFAULT (0),
	[Polis_Baru] [int] NULL CONSTRAINT [DF_Polis_Current_Polis_Baru] DEFAULT (0),
	[Polis_Lapse] [int] NULL CONSTRAINT [DF_Polis_Current_Polis_Lapse] DEFAULT (0),
	[Polis_Akhir] [int] NULL CONSTRAINT [DF_Polis_Current_Polis_Akhir] DEFAULT (0),
	CONSTRAINT [PK_Polis_Current] PRIMARY KEY  CLUSTERED 
	(
		[No_Proposal],[Sub_Kelompok]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--  exec proc_eod
  SET NOCOUNT ON
  ---------------------------------------------
  -- INSERT POLIS INFORCE DALAM PERIODE LAPORAN
  --DELETE FROM TmpInsurance.dbo.Polis_Current
  INSERT INTO TmpInsurance.dbo.Polis_Current (No_Proposal, Sub_Kelompok, Nama_Perusahaan, No_Prs, Tgl_Masuk, Tgl_Mulai, Tgl_Selesai)
  SELECT TOP 50  dbo.Polis_Asuransi.No_Proposal, Produk_Polis.Id_Produk, dbo.Perusahaan.Nama_Perusahaan, dbo.Polis_Asuransi.No_Prs, 
          dbo.Perusahaan.Tgl_Masuk, dbo.Polis_Asuransi.Tgl_Mulai, dbo.Polis_Asuransi.Tgl_Selesai
  FROM    dbo.Polis_Asuransi INNER JOIN
          dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs INNER JOIN Produk_Polis ON Polis_Asuransi.No_Proposal = Produk_Polis.No_Proposal
        
  WHERE   (dbo.Polis_Asuransi.Status <> 'C') AND (dbo.Polis_Asuransi.Status <> 'G') AND (ISNULL(dbo.Polis_Asuransi.No_Polis, '') <> '')
          AND (Tgl_Selesai>=@TglAwal-1) AND (Tgl_Mulai<=@TglAkhir)
          
          
          
  ------------------------
  UPDATE TmpInsurance.dbo.Polis_Current SET Polis_Awal=1   WHERE Tgl_Mulai<=@TglAwal-1 AND Tgl_Selesai>=@TglAwal-1
  UPDATE TmpInsurance.dbo.Polis_Current SET Polis_Baru=1   WHERE Tgl_Mulai>=@TglAwal AND Tgl_Mulai<=@TglAkhir
  UPDATE TmpInsurance.dbo.Polis_Current SET Polis_Lapse=1  WHERE Tgl_Selesai<=@TglAkhir
  UPDATE TmpInsurance.dbo.Polis_Current SET Polis_Akhir=1  WHERE Tgl_Selesai>=@TglAkhir+1 AND Polis_Lapse<>1
  
--SELECT     TmpInsurance.dbo.Polis_Current.No_Proposal, TmpInsurance.dbo.Polis_Current.Sub_Kelompok, Produk_Polis.Sub_Kelompok AS Expr1, 
--                      Sub_Kelompok.Nama_Sub, TmpInsurance.dbo.Polis_Current.Nama_Perusahaan, TmpInsurance.dbo.Polis_Current.No_Prs, 
--                      TmpInsurance.dbo.Polis_Current.Tgl_Masuk, TmpInsurance.dbo.Polis_Current.Tgl_Mulai, TmpInsurance.dbo.Polis_Current.Tgl_Selesai, 
--                      TmpInsurance.dbo.Polis_Current.Polis_Awal, TmpInsurance.dbo.Polis_Current.Polis_Baru, TmpInsurance.dbo.Polis_Current.Polis_Lapse, 
--                      TmpInsurance.dbo.Polis_Current.Polis_Akhir
--FROM         TmpInsurance.dbo.Polis_Current INNER JOIN
--                      Produk_Polis ON TmpInsurance.dbo.Polis_Current.No_Proposal = Produk_Polis.No_Proposal AND 
--                      TmpInsurance.dbo.Polis_Current.Sub_Kelompok = Produk_Polis.Id_Produk INNER JOIN
--                      Sub_Kelompok ON Produk_Polis.Sub_Kelompok = Sub_Kelompok.Sub_Kelompok
                      ---------------------------
  -- UPDATE PLAN MASTER POLIS
 
  DECLARE @No_Proposal varchar(9)
   /*
   DECLARE Employee_Cursor CURSOR FOR
  SELECT  No_Proposal FROM TmpInsurance.dbo.Polis_Current 
  OPEN Employee_Cursor
  FETCH NEXT FROM Employee_Cursor INTO @No_Proposal
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF EXISTS(SELECT * FROM Produk_Polis WHERE No_Proposal = @No_Proposal AND Status='I' AND Sub_Kelompok='0100')
    UPDATE TmpInsurance.dbo.Polis_Current SET Sub_Kelompok='0100' WHERE No_Proposal=@No_Proposal
    ELSE IF EXISTS(SELECT * FROM Produk_Polis WHERE No_Proposal=@No_Proposal AND Status='I' AND Sub_Kelompok='0200')
    UPDATE TmpInsurance.dbo.Polis_Current SET Sub_Kelompok='0200' WHERE No_Proposal=@No_Proposal
    ELSE UPDATE TmpInsurance.dbo.Polis_Current SET Sub_Kelompok='0300' WHERE No_Proposal=@No_Proposal
    FETCH NEXT FROM Employee_Cursor INTO @No_Proposal
  END
  CLOSE Employee_Cursor
  DEALLOCATE Employee_Cursor
*/
  ----------------------------------------------
  -- INSERT PESERTA INFORCE PADA PERIODE LAPORAN
  DELETE FROM TmpInsurance.dbo.Peserta_Current
  INSERT INTO TmpInsurance.dbo.Peserta_Current (No_Proposal, No_Peserta, Tgl_Mulai, Tgl_Selesai, Tgl_Lapse)
  SELECT TOP 50 dbo.Peserta.No_Proposal, dbo.Peserta.No_Peserta, dbo.Peserta.Tgl_Mulai, dbo.Peserta.Tgl_Selesai, dbo.Peserta.Tgl_Selesai
  FROM   TmpInsurance.dbo.Polis_Current as T1 INNER JOIN
         dbo.Peserta ON T1.No_Proposal = dbo.Peserta.No_Proposal
  WHERE  (dbo.Peserta.Status<>'C') AND (dbo.Peserta.Status <> 'G') 
  AND (dbo.Peserta.Tgl_Selesai>=@TglAwal-1) AND (dbo.Peserta.Tgl_Mulai<=@TglAkhir)


  ----------------------------------------------
  -- UPDATE TGL SELESAI PESERTA PADA PERIODE LAPORAN
  DECLARE @No_Peserta varchar(12) 
--  DECLARE @Tgl_Mulai datetime
  DECLARE @Tgl_Buat datetime
  DECLARE Employee_Cursor CURSOR FOR
  SELECT TOP 50  T3.No_Proposal, T3.No_Peserta, MAX(dbo.Note_Terbit_Master.Tgl_Buat)
  FROM    TmpInsurance.dbo.Peserta_Current as T3 INNER JOIN
          dbo.Peserta_Produk_Detail  ON T3.No_Peserta = dbo.Peserta_Produk_Detail.No_Peserta AND 
          T3.No_Proposal = dbo.Peserta_Produk_Detail.No_Proposal INNER JOIN
          dbo.Note_Terbit_Master ON dbo.Peserta_Produk_Detail.No_Ref = dbo.Note_Terbit_Master.No_Ref AND 
          T3.No_Proposal = dbo.Note_Terbit_Master.No_Proposal
  WHERE   (dbo.Note_Terbit_Master.Jenis IN (6, 11)) AND (dbo.Note_Terbit_Master.Status = 'I')

  GROUP BY T3.No_Proposal, T3.No_Peserta, T3.Tgl_Mulai, T3.Tgl_Selesai
  OPEN Employee_Cursor
  FETCH NEXT FROM Employee_Cursor INTO @No_Proposal, @No_Peserta, @Tgl_Buat
  WHILE @@FETCH_STATUS = 0
  BEGIN
    UPDATE TmpInsurance.dbo.Peserta_Current SET Tgl_Lapse=@Tgl_Buat  WHERE No_Proposal=@No_Proposal AND No_Peserta=@No_Peserta
    FETCH NEXT FROM Employee_Cursor INTO @No_Proposal, @No_Peserta, @Tgl_Buat
  END
  CLOSE Employee_Cursor
  DEALLOCATE Employee_Cursor

  ----------------------------------------------
  -- UPDATE STATUS PESERTA 

  UPDATE TmpInsurance.dbo.Peserta_Current SET Peserta_Awal=1   WHERE Tgl_Mulai<=@TglAwal-1 AND Tgl_Selesai>=@TglAwal-1
  UPDATE TmpInsurance.dbo.Peserta_Current SET Peserta_Baru=1   WHERE Tgl_Mulai>=@TglAwal AND Tgl_Mulai<=@TglAkhir
  UPDATE TmpInsurance.dbo.Peserta_Current SET Peserta_Lapse=1  WHERE Tgl_Lapse<=@TglAkhir OR Tgl_Selesai<=@TglAkhir
  UPDATE TmpInsurance.dbo.Peserta_Current SET Peserta_Akhir=1  WHERE Tgl_Selesai>=@TglAkhir+1 AND Peserta_Lapse<>1

-- ###########

DELETE FROM TmpInsurance.dbo.Peserta_Produk_Current
INSERT INTO TmpInsurance.dbo.Peserta_Produk_Current (No_Proposal, No_Peserta, Id_produk, Kelas, Jml_Up, New_Up)
SELECT TOP 50     T3.No_Proposal, T3.No_Peserta, dbo.Peserta_Produk.Id_Produk, dbo.Peserta_Produk.Kelas, 
                      dbo.Peserta_Produk.Curr_Santunan, dbo.Peserta_Produk.Curr_Santunan
FROM         TmpInsurance.dbo.Peserta_Current as T3 INNER JOIN
                      dbo.Peserta_Produk ON T3.No_Proposal=dbo.Peserta_Produk.No_Proposal AND T3.No_Peserta = dbo.Peserta_Produk.No_Peserta 
WHERE 	dbo.Peserta_Produk.Status<>'C'


  -----------------------------------
  -- UPDATE UP PESERTA BARU
  DECLARE @Id_produk varchar(10) 
  DECLARE @Kelas varchar(10) 
  DECLARE @Jml_Up float
  DECLARE @Up_New float
  DECLARE Employee_Cursor CURSOR FOR
  SELECT TOP 50  No_Proposal, No_Peserta, Id_Produk, Kelas, Jml_Up   
  FROM    TmpInsurance.dbo.Peserta_Produk_Current
  WHERE (Jml_Up>=1000000000)
  OPEN Employee_Cursor
  FETCH NEXT FROM Employee_Cursor INTO @No_Proposal, @No_Peserta, @Id_produk, @Kelas, @Jml_Up
  WHILE @@FETCH_STATUS = 0
  BEGIN
       SET @Up_New=IsNull(@Jml_Up,0)
       EXEC proc_GetUnlimited @Id_Produk, @Kelas, @Up_New OUTPUT
       UPDATE TmpInsurance.dbo.Peserta_Produk_Current SET New_UP=@Up_New WHERE No_Proposal=@No_Proposal AND No_Peserta=@No_Peserta AND Id_Produk=@Id_Produk
       FETCH NEXT FROM Employee_Cursor INTO @No_Proposal, @No_Peserta, @Id_produk, @Kelas, @Jml_Up
  END
  CLOSE Employee_Cursor
  DEALLOCATE Employee_Cursor

  -- UPDATE UP PESERTA BARU
  DECLARE @Up_Awal float 
  DECLARE @Up_Baru float 
  DECLARE @Up_Lapse float 
  DECLARE @Up_Akhir float
  DECLARE Employee_Cursor CURSOR FOR
SELECT TOP 50     T3.No_Proposal, T3.No_Peserta, SUM(T3.Peserta_Awal * T4.New_Up) 
                      AS Up_Awal, SUM(T3.Peserta_Baru * T4.New_Up) AS Up_Baru, 
                      SUM(T3.Peserta_Lapse * T4.New_Up) AS Up_Lapse, 
                      SUM(T3.Peserta_Akhir * T4.New_Up) AS Up_Akhir
FROM         TmpInsurance.dbo.Peserta_Current as T3 INNER JOIN
                      TmpInsurance.dbo.Peserta_Produk_Current as T4 ON T3.No_Proposal = T4.No_Proposal AND 
                      T3.No_Peserta = T4.No_Peserta
GROUP BY T3.No_Proposal, T3.No_Peserta

  OPEN Employee_Cursor
  FETCH NEXT FROM Employee_Cursor INTO @No_Proposal, @No_Peserta, @Up_Awal, @Up_Baru, @Up_Lapse, @Up_Akhir
  WHILE @@FETCH_STATUS = 0
  BEGIN
       UPDATE TmpInsurance.dbo.Peserta_Current SET Up_Awal=IsNull(@Up_Awal,0), Up_Baru=IsNull(@Up_Baru,0), Up_Lapse=IsNull(@Up_Lapse,0), Up_Akhir=IsNull(@Up_Akhir,0) WHERE No_Proposal=@No_Proposal AND No_Peserta=@No_Peserta 
       FETCH NEXT FROM Employee_Cursor INTO @No_Proposal, @No_Peserta, @Up_Awal, @Up_Baru, @Up_Lapse, @Up_Akhir
  END
  CLOSE Employee_Cursor
  DEALLOCATE Employee_Cursor
-----------
  -- UPDATE PREMI DAN UP PESERTA LAPSE/KELUAR
  DECLARE @Premi float
  DECLARE Employee_Cursor CURSOR FOR
  SELECT TOP 50  T3.No_Proposal, T3.No_Peserta, SUM(dbo.Peserta_Produk_Detail.Curr_Premi) 
  FROM    TmpInsurance.dbo.Peserta_Current as T3 INNER JOIN
          dbo.Note_Terbit_Master ON T3.No_Proposal = dbo.Note_Terbit_Master.No_Proposal INNER JOIN
          dbo.Peserta_Produk_Detail ON T3.No_Peserta = dbo.Peserta_Produk_Detail.No_Peserta AND 
          T3.No_Proposal = dbo.Peserta_Produk_Detail.No_Proposal
  WHERE  (dbo.Note_Terbit_Master.Jenis IN (6, 11))
     AND (dbo.Note_Terbit_Master.Tgl_Buat>=@TglAwal) AND (dbo.Note_Terbit_Master.Tgl_Buat<=@TglAkhir)
  GROUP BY T3.No_Proposal, T3.No_Peserta
  OPEN Employee_Cursor
  FETCH NEXT FROM Employee_Cursor INTO @No_Proposal, @No_Peserta, @Premi
  WHILE @@FETCH_STATUS = 0
  BEGIN
      UPDATE TmpInsurance.dbo.Peserta_Current SET Premi_Lapse=@Premi WHERE No_Proposal=@No_Proposal AND No_Peserta=@No_Peserta
    FETCH NEXT FROM Employee_Cursor INTO @No_Proposal,@No_Peserta, @Premi
  END
  CLOSE Employee_Cursor
  DEALLOCATE Employee_Cursor
-------------------------
  DECLARE Employee_Cursor CURSOR FOR
  SELECT TOP 50  T3.No_Proposal, T3.No_Peserta, SUM(dbo.Peserta_Produk_Detail.Curr_Premi) 
  FROM    TmpInsurance.dbo.Peserta_Current as T3 INNER JOIN
          dbo.Note_Terbit_Master ON T3.No_Proposal = dbo.Note_Terbit_Master.No_Proposal INNER JOIN
          dbo.Peserta_Produk_Detail ON T3.No_Peserta = dbo.Peserta_Produk_Detail.No_Peserta AND 
          T3.No_Proposal = dbo.Peserta_Produk_Detail.No_Proposal
  WHERE  (dbo.Note_Terbit_Master.Jenis IN (1,2,3))
     AND (dbo.Note_Terbit_Master.Tgl_Buat>=@TglAwal) AND (dbo.Note_Terbit_Master.Tgl_Buat<=@TglAkhir)
  GROUP BY T3.No_Proposal, T3.No_Peserta
  OPEN Employee_Cursor
  FETCH NEXT FROM Employee_Cursor INTO @No_Proposal, @No_Peserta, @Premi
  WHILE @@FETCH_STATUS = 0
  BEGIN
      UPDATE TmpInsurance.dbo.Peserta_Current SET Premi_Baru=@Premi WHERE No_Proposal=@No_Proposal AND No_Peserta=@No_Peserta
      FETCH NEXT FROM Employee_Cursor INTO @No_Proposal,@No_Peserta, @Premi
  END
  CLOSE Employee_Cursor
  DEALLOCATE Employee_Cursor


  EXEC TmpInsurance.dbo.gproc_DelTable 'Rep_Portopolio'
  CREATE TABLE [TmpInsurance].[dbo].[Rep_Portopolio](
	[Urut] [int] IDENTITY (1, 1) NOT NULL ,
	[No_Proposal] [varchar] (10) NULL ,
	[Nama_Perusahaan] [varchar] (60) NULL ,
	[No_Polis] [varchar] (11) NULL ,
	[Tgl_Mulai] [varchar] (10) NULL ,
	[Tgl_Selesai] [varchar] (10) NULL ,
	[Produk] [varchar] (10) NULL ,
	[Mata_Uang] [varchar] (10) NULL ,
	[Polis_Awal] [int] NULL ,
	[Polis_Baru] [int] NULL ,
	[Polis_Lapse] [int] NULL ,
	[Polis_Akhir] [int] NULL ,
	[Polis_Total] [int] NULL ,
	[Peserta_Awal] [int] NULL ,
	[Peserta_Baru] [int] NULL ,
	[Peserta_Lapse] [int] NULL ,
	[Peserta_Akhir] [int] NULL ,
	[Peserta_Total] [int] NULL ,
	[UP_Awal] [float] NULL ,
	[UP_Baru] [float] NULL ,
	[UP_Lapse] [float] NULL ,
	[UP_Akhir] [float] NULL ,
	[UP_Total] [float] NULL ,
	[Premi_Awal] [float] NULL ,
	[Premi_Baru] [float] NULL ,
	[Premi_Lapse] [float] NULL ,
	[Premi_Akhir] [float] NULL ,
	[Premi_Total] [float] NULL ,
	CONSTRAINT [PK_Rep_Portopolio] PRIMARY KEY  CLUSTERED 
	(
		[Urut]
	)  ON [PRIMARY] 
) ON [PRIMARY]

INSERT INTO TmpInsurance.dbo.Rep_Portopolio
SELECT TOP 50     T1.No_Proposal AS Expr1, dbo.Perusahaan.Nama_Perusahaan, dbo.Polis_Asuransi.No_Polis, 
                      dbo.func_DateToChar(dbo.Polis_Asuransi.Tgl_Mulai) AS Expr2, dbo.func_DateToChar(dbo.Polis_Asuransi.Tgl_Selesai) AS Expr3, 
                      dbo.Sub_Kelompok.Nama_Sub AS Produk, dbo.Mata_Uang.Deskripsi AS Mata_Uang, 
T1.Polis_Awal AS Polis_Awal, 
                      T1.Polis_Baru AS Polis_Baru, T1.Polis_Lapse AS Polis_Lapse, T1.Polis_Akhir AS Polis_Akhir, 
                      T1.Polis_Awal + T1.Polis_Baru + T1.Polis_Lapse + T1.Polis_Akhir
                       AS Polis_Total, 
SUM(T3.Peserta_Awal) AS Peserta_Awal, SUM(T3.Peserta_Baru) AS Peserta_Baru, 
                      SUM(T3.Peserta_Lapse) AS Peserta_Lapse, SUM(T3.Peserta_Akhir) AS Peserta_Akhir, 
                      SUM(T3.Peserta_Awal) + SUM(T3.Peserta_Baru) + SUM(T3.Peserta_Lapse) 
                      + SUM(T3.Peserta_Akhir) AS Peserta_Total, SUM(T3.UP_Awal) AS UP_Awal, SUM(T3.UP_Baru) 
                      AS UP_Baru, SUM(T3.UP_Lapse) AS UP_Lapse, SUM(T3.UP_Akhir) AS UP_Akhir, 
                      SUM(T3.UP_Awal) + SUM(T3.UP_Baru) + SUM(T3.UP_Lapse) 
                      + SUM(T3.UP_Akhir) AS UP_Total, SUM(T3.Premi_Awal) AS Premi_Awal, SUM(T3.Premi_Baru) 
                      AS Premi_Baru, SUM(T3.Premi_Lapse) AS Premi_Lapse, SUM(T3.Premi_Akhir) AS Premi_Akhir, 
                      SUM(T3.Premi_Awal) + SUM(T3.Premi_Baru) + SUM(T3.Premi_Lapse) 
                      + SUM(T3.Premi_Akhir) AS Premi_Total
FROM         TmpInsurance.dbo.Polis_Current As T1 INNER JOIN
                      dbo.Sub_Kelompok ON T1.Sub_Kelompok = dbo.Sub_Kelompok.Sub_Kelompok INNER JOIN
                      dbo.Polis_Asuransi ON T1.No_Proposal = dbo.Polis_Asuransi.No_Proposal INNER JOIN
                      dbo.Mata_Uang ON dbo.Polis_Asuransi.Mata_Uang = dbo.Mata_Uang.Kode INNER JOIN
                      TmpInsurance.dbo.Peserta_Current as T3 ON T1.No_Proposal = T3.No_Proposal INNER JOIN
                      dbo.Perusahaan ON T1.No_Prs = dbo.Perusahaan.No_Prs
GROUP BY dbo.Sub_Kelompok.Nama_Sub, dbo.Mata_Uang.Deskripsi, dbo.Perusahaan.Nama_Perusahaan, T1.No_Proposal, 
                      dbo.Polis_Asuransi.No_Polis, dbo.Polis_Asuransi.Tgl_Mulai, dbo.Polis_Asuransi.Tgl_Selesai, T1.Polis_Awal, 
                      T1.Polis_Baru, T1.Polis_Lapse, T1.Polis_Akhir


INSERT INTO TmpInsurance.dbo.Rep_Portopolio
SELECT NULL, NULL, NULL, NULL, NULL, 
Produk, Mata_Uang, SUM(Polis_Awal) AS Expr1, SUM(Polis_Baru) AS Expr2, SUM(Polis_Lapse) AS Expr3, SUM(Polis_Akhir) AS Expr4, 
                      SUM(Polis_Total) AS Expr5, SUM(Peserta_Awal) AS Expr6, SUM(Peserta_Baru) AS Expr7, SUM(Peserta_Lapse) AS Expr8, SUM(Peserta_Akhir) 
                      AS Expr9, SUM(Peserta_Total) AS Expr10, SUM(UP_Awal) AS Expr11, SUM(UP_Baru) AS Expr12, SUM(UP_Lapse) AS Expr13, SUM(UP_Akhir) 
                      AS Expr14, SUM(UP_Total) AS Expr15, SUM(Premi_Awal) AS Expr16, SUM(Premi_Baru) AS Expr17, SUM(Premi_Lapse) AS Expr18, SUM(Premi_Akhir) 
                      AS Expr19, SUM(Premi_Total) AS Expr20
FROM         TmpInsurance.dbo.Rep_Portopolio
GROUP BY Produk, Mata_Uang


INSERT INTO TmpInsurance.dbo.Rep_Portopolio
SELECT NULL, NULL, NULL, NULL, NULL, NULL,
Mata_Uang, SUM(Polis_Awal) AS Expr1, SUM(Polis_Baru) AS Expr2, SUM(Polis_Lapse) AS Expr3, SUM(Polis_Akhir) AS Expr4, 
                      SUM(Polis_Total) AS Expr5, SUM(Peserta_Awal) AS Expr6, SUM(Peserta_Baru) AS Expr7, SUM(Peserta_Lapse) AS Expr8, SUM(Peserta_Akhir) 
                      AS Expr9, SUM(Peserta_Total) AS Expr10, SUM(UP_Awal) AS Expr11, SUM(UP_Baru) AS Expr12, SUM(UP_Lapse) AS Expr13, SUM(UP_Akhir) 
                      AS Expr14, SUM(UP_Total) AS Expr15, SUM(Premi_Awal) AS Expr16, SUM(Premi_Baru) AS Expr17, SUM(Premi_Lapse) AS Expr18, SUM(Premi_Akhir) 
                      AS Expr19, SUM(Premi_Total) AS Expr20
FROM  TmpInsurance.dbo.Rep_Portopolio
WHERE IsNull(No_Proposal,'')=''
GROUP BY Produk, Mata_Uang
--END
SELECT TOP 50 * FROM  TmpInsurance.dbo.Rep_Portopolio

