USE [GrpInsurance]
GO

/****** Object:  View [dbo].[custPort_View_Produk]    Script Date: 05/11/2016 10:48:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER VIEW [dbo].[custPort_View_Produk]
AS
--kelas, maksimum
SELECT
  pa.No_Proposal AS no_Proposal,
  pa.No_Polis AS no_Polis,
  pp.Id_Produk AS kode_Produk,
  pp.Nama_Paket AS nama_Produk,
  sk.Nama_Sub AS produk,
  pp.Batas_Usia AS maks_Usia,
  pp.Batas_Anak AS maks_Anak,
  pp.Batas_Usia_Anak AS maks_Umur_Anak,
  CASE pp.Sub_Kelompok
    WHEN '0100' THEN kbp.Kelas  -- AJB
    ELSE pp2.Kelas -- selain AJB
  END AS kelas,
  CASE
	  WHEN pp2.Benefit >= 1000000000 THEN 'Tidak Terbatas'
	  ELSE LTRIM(STR(pp2.Benefit, 10, 0))
	END AS santunan_Maksimum
FROM
  dbo.Polis_Asuransi AS pa INNER JOIN
  dbo.Produk_Polis AS pp ON pa.No_Proposal = pp.No_Proposal INNER JOIN
  dbo.Sub_Kelompok AS sk ON pp.Sub_Kelompok = sk.Sub_Kelompok LEFT JOIN
  dbo.Kelas_Benefit_Polis AS kbp ON pp.Id_Produk = kbp.Id_Produk LEFT JOIN -- '0100' -- AJB
  dbo.Premi_Polis AS pp2 ON pp.Id_Produk = pp2.Id_Produk  -- <> '0100' -- selain AJB
WHERE
  pp.Status = 'I'



GO


--- coret ---

SELECT
  pa.No_Proposal AS no_Proposal,
  pa.No_Polis AS no_Polis,
  pp.Id_Produk AS kode_Produk,
  pp.Nama_Paket AS nama_Produk,
  sk.Nama_Sub AS produk,
  pp.Batas_Usia AS maks_Usia,
  pp.Batas_Anak AS maks_Anak,
  pp.Batas_Usia_Anak AS maks_Umur_Anak,
  --kbp.Kelas,
  --pp2.Kelas,
  CASE pp.Sub_Kelompok
    WHEN '0100' THEN kbp.Kelas  -- AJB
    ELSE pp2.Kelas -- selain AJB
  END AS Kelas,
  pp2.Benefit,
  CASE
	  WHEN pp2.Benefit >= 1000000000 THEN 'Tidak Terbatas'
	  ELSE LTRIM(STR(pp2.Benefit, 10, 0))
	END AS santunan_Maksimum
FROM
  dbo.Polis_Asuransi AS pa INNER JOIN
  dbo.Produk_Polis AS pp ON pa.No_Proposal = pp.No_Proposal INNER JOIN
  dbo.Sub_Kelompok AS sk ON pp.Sub_Kelompok = sk.Sub_Kelompok LEFT JOIN
  dbo.Kelas_Benefit_Polis AS kbp ON pp.Id_Produk = kbp.Id_Produk LEFT JOIN -- '0100' -- AJB
  dbo.Premi_Polis AS pp2 ON pp.Id_Produk = pp2.Id_Produk  -- <> '0100' -- selain AJB
WHERE
  pp.Status = 'I'
  AND pa.No_Proposal = '2015.0668'

SELECT * FROM Premi_Polis
SELECT * FROM Kelas_Benefit_Polis
SELECT * FROM Premi_Polis