USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[Proc_Rep10387]    Script Date: 03/18/2015 10:29:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- EXEC dbo.Proc_Rep10387 '06/01/2008', '06/30/2008', 1, 1
-- 10387

--Laporan Jatuh Tempo ERP
-- last edited by Pribadi Pamungkas, 2015-03-18
ALTER PROCEDURE [dbo].[Proc_Rep10387]
      @TglAwal DATETIME,
      @TglAkhir DATETIME,
      @TglAwalYa BIT,
      @TglAkhirYa BIT
AS 
SET NOCOUNT ON
DECLARE @hasil TABLE ( 
                  No_Proposal VARCHAR(9),
                  Nama_Perusahaan VARCHAR(50),
                  No_Polis VARCHAR(11),
                  GT_OG VARCHAR(2),
                  Tgl_Mulai DATETIME,
                  Tgl_Selesai DATETIME,
                  Deskripsi VARCHAR(10),
                  ERP_Prosen FLOAT,
                  Aging INT
               )

DECLARE @no_polis VARCHAR(11)

DECLARE list_cursor CURSOR FAST_FORWARD FOR  
SELECT DISTINCT 
  No_Polis
FROM
  dbo.Polis_Asuransi 
WHERE
  Sub_Init = 'S'
  AND Status = 'I'
  AND (DATEADD(month, 3, Tgl_Mulai) <= @TglAkhir)
  AND (
        (DATEADD(month, 3, Tgl_Mulai) >= @TglAwal) 
        OR ISNULL(@TglAwalYa, 0) = 0
      )

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_polis  

WHILE @@FETCH_STATUS = 0  
BEGIN

  DECLARE @tahun_ke_saat_ini SMALLINT

  SELECT
    @tahun_ke_saat_ini = MAX(Tahun_Ke)
  FROM
    dbo.Polis_Asuransi
  WHERE
    No_Polis = @no_polis
  
  INSERT INTO @hasil
    (No_Proposal,
     Nama_Perusahaan,
     No_Polis,
     GT_OG,
     Tgl_Mulai,
     Tgl_Selesai,
     Deskripsi,
     ERP_Prosen,
     Aging)    
  SELECT
    pa.No_Proposal,       -- no_proposal - varchar(9)
    prs.Nama_Perusahaan,  -- nama_perusahaan - varchar(50)
    pa.No_Polis,          -- no_polis - varchar(11)
    prs.GT_OG,            -- gt_og - varchar(2)
    pa.Tgl_Mulai,         -- tgl_mulai - datetime
    pa.Tgl_Selesai,       -- tgl_selesai - datetime
    pb.Deskripsi,         -- cara_bayar - varchar(10)
    pa.ERP_Prosen,        -- erp_prosen - float
    DATEDIFF(day,pa.Tgl_Mulai,@TglAkhir) as Aging  -- aging - int
  FROM
    dbo.Polis_Asuransi AS pa INNER JOIN
    dbo.Perusahaan     AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
    dbo.Periode_Bayar  AS pb ON pa.Periode_Bayar = pb.Kode
  WHERE
    pa.No_Polis = @no_polis
    AND pa.Tahun_Ke = (@tahun_ke_saat_ini - 1) -- proposal yg tahun kemarin
    AND pa.Sub_Init = 'S' -- bukan new bisnis
    AND (ISNULL(pa.ERP_Prosen, 0) <> 0) -- yg nilai ERP-nya tidak nol

  FETCH NEXT FROM list_cursor INTO @no_polis  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

SELECT
  *
FROM
  @hasil
ORDER BY
  nama_perusahaan

---------------------------------------------------------------------------------------------------------------------------------------
-- -- query sebelumnya adalah:
--SELECT     dbo.Polis_Asuransi.No_Proposal, dbo.Perusahaan.Nama_Perusahaan, dbo.Polis_Asuransi.No_Polis, dbo.Perusahaan.GT_OG, 
--                      dbo.Polis_Asuransi.Tgl_Mulai, dbo.Polis_Asuransi.Tgl_Selesai, dbo.Periode_Bayar.Deskripsi, dbo.Polis_Asuransi.ERP_Prosen, DATEDIFF(day,dbo.Polis_Asuransi.Tgl_Mulai,@TglAkhir) as Aging
--FROM         dbo.Polis_Asuransi INNER JOIN
--                      dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs INNER JOIN
--                      dbo.Periode_Bayar ON dbo.Polis_Asuransi.Periode_Bayar = dbo.Periode_Bayar.Kode
--  WHERE (dbo.Polis_Asuransi.Status = 'I') 
--  AND (dbo.Polis_Asuransi.Sub_Init = 'S')
--  AND (dbo.Polis_Asuransi.Tgl_Mulai<=@TglAwal)
--  AND (isnull(dbo.Polis_Asuransi.Erp_Prosen,0)<>0)
--  AND ((DATEADD(month,3,dbo.Polis_Asuransi.Tgl_Mulai)>=@TglAwal) OR isnull(@TglAwalYa,0)=0)
--  AND (DATEADD(month,3,dbo.Polis_Asuransi.Tgl_Mulai)<=@TglAkhir)
--  ORDER BY dbo.Perusahaan.Nama_Perusahaan

GO