USE [GrpInsurance]
GO

/****** Object:  View [dbo].[view_Polis_per_marketing]    Script Date: 09/06/2017 16:34:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- last edited by Pribadi Pamungkas, 2017-09-06. RQ2017080049

/*ORDER BY dbo.Perusahaan.Nama_Perusahaan*/
ALTER VIEW [dbo].[view_Polis_per_marketing]
AS
  SELECT
    dbo.Polis_Asuransi.Status,
    dbo.Polis_Asuransi.No_Proposal,
    dbo.Perusahaan.Nama_Perusahaan,
    ISNULL(dbo.Polis_Asuransi.AdMedika_Ya, 0) AS AdMedika_Ya,
    ISNULL(dbo.Polis_Asuransi.show_card_ya, 0) AS AdMedika_ShowCard_Ya,
    dbo.Perusahaan.GT_OG,
    dbo.Polis_Asuransi.No_Polis,
    dbo.Polis_Asuransi.Tgl_Mulai,
    dbo.Polis_Asuransi.Tgl_Selesai,
    dbo.Polis_Asuransi.Tahun_Ke,
    dbo.Periode_Bayar.Deskripsi AS Periode_Bayar,
    dbo.Mata_Uang.Deskripsi AS Valas,
    --dbo.Polis_Asuransi.Jml_Peserta,
    COALESCE(Jml_Pst.Jml_Peserta, 0) AS Jml_Peserta,  -- modified by Pribadi Pamungkas, 2015-04-16
    note_Premi.total_premi,
    
    --dbo.Polis_Asuransi.Marketing,
    CASE Sub_Init  -- modified by Pribadi Pamungkas, 2015-06-03
      WHEN 'I' THEN dbo.Polis_Asuransi.Marketing -- new business
      WHEN 'S' THEN CASE LEN(COALESCE(dbo.Polis_Asuransi.Marketing_REN, ''))  -- renewal
                      WHEN 0 THEN dbo.Polis_Asuransi.Marketing
                      ELSE dbo.Polis_Asuransi.Marketing_REN
                    END
    END AS Marketing,
    
    dbo.Polis_Asuransi.SPAK_No,
    dbo.Perusahaan.Fax,
    dbo.Perusahaan.Contact_Person,
    dbo.Polis_Asuransi.Create_By,
    dbo.Polis_Asuransi.Create_Date,
    dbo.Polis_Asuransi.Change_By,
    dbo.Polis_Asuransi.Change_Date,
    dbo.Perusahaan.No_Prs,
    dbo.Payor_Header.Nama_Payor,
    CASE ISNULL(dbo.Polis_Marketing.Id_Marketing, '')
      WHEN '' THEN dbo.Polis_Asuransi.Marketing
      ELSE ISNULL(dbo.Polis_Marketing.Id_Marketing, '')
    END AS Marketing_DCP,
    
    dbo.Polis_Asuransi.kode_bisnis,
    dbo.Group_Perusahaan.Nama_Group,   -- added by Pribadi Pamungkas, 2014-09-26
    --?? dbo.Polis_Asuransi.Contact_Person,   -- added by Pribadi Pamungkas, 2015-07-02
    dbo.Polis_Asuransi.Contact_Email,  -- added by Pribadi Pamungkas, 2015-07-02
    dbo.Polis_Asuransi.plafon_ekses_klaim,
    dbo.Polis_Asuransi.Status AS Status_Polis  -- added by Pribadi Pamungkas, 2016-04-15. Request by bu Yis, karena butuh print di 10311
    --,dbo.func_marketing_hirarki(id_marketing) as group_user
    --,dbo.func_marketing_hirarki(CASE 
    --                              WHEN COALESCE(dbo.Polis_Marketing.Id_Marketing,'')='' THEN --dbo.Polis_Asuransi.Marketing
    --                                                                                        CASE dbo.Polis_Asuransi.Sub_Init
    --                                                                                          WHEN 'S' THEN COALESCE(dbo.Polis_Asuransi.Marketing_REN,dbo.Polis_Asuransi.Marketing)
    --                                                                                          WHEN 'I' THEN dbo.Polis_Asuransi.Marketing
    --                                                                                        END
    --                              ELSE dbo.Polis_Marketing.Id_Marketing
    --                            END
    --                            ) as group_user
    ,dbo.func_marketing_hirarki_polis(dbo.Polis_Asuransi.No_Polis) AS group_user
  FROM
    dbo.Payor_Header RIGHT OUTER JOIN 
    dbo.Polis_Asuransi ON dbo.Payor_Header.Id_Payor = dbo.Polis_Asuransi.Id_Payor LEFT OUTER JOIN 
    dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs LEFT OUTER JOIN 
    dbo.Group_Perusahaan ON dbo.Perusahaan.Group_Perusahaan = dbo.Group_Perusahaan.IDI LEFT OUTER JOIN  -- added by Pribadi Pamungkas, 2014-09-26
    dbo.Periode_Bayar ON dbo.Polis_Asuransi.Periode_Bayar = dbo.Periode_Bayar.Kode LEFT OUTER JOIN 
    dbo.Mata_Uang ON dbo.Polis_Asuransi.Mata_Uang = dbo.Mata_Uang.Kode LEFT OUTER JOIN 
    dbo.Polis_Marketing ON dbo.Polis_Asuransi.No_Polis = dbo.Polis_Marketing.No_Polis LEFT JOIN 
    ( SELECT
        no_proposal,
        SUM((Jml_Terbit - Diskon) * Tipe) AS total_premi
      FROM
        Note_Terbit_Master
      WHERE
        status = 'I' AND jenis <= 19
      GROUP BY
        No_proposal
    ) AS note_Premi ON note_Premi.No_Proposal = Polis_Asuransi.no_proposal 
    LEFT JOIN         -- add by Pribadi Pamungkas, 2015-04-16
    ( SELECT
	      COUNT(*) AS Jml_Peserta,
	      p.No_Proposal
      FROM
	      dbo.Peserta AS p
      WHERE
        p.Status = 'I'
      GROUP BY
        p.No_Proposal
    ) AS Jml_Pst ON dbo.Polis_Asuransi.No_Proposal = Jml_Pst.No_Proposal
  WHERE
    (dbo.Polis_Asuransi.Tgl_Selesai >= '2013-01-01') 
    AND (dbo.Polis_Asuransi.Tgl_Selesai IS NOT NULL) 
    OR (dbo.Polis_Asuransi.Tgl_Selesai IS NULL) 
    OR (dbo.Polis_Asuransi.No_Proposal IN ('2005.0003', '2005.0420', '2005.0472', '2005.0419', '2007.0457', '2006.0466', '2005.0375', '2006.0872', '2005.0850', '2007.0910'))
    




GO

