USE [GrpInsurance]
GO

/****** Object:  View [dbo].[View_NoteProviderHeader]    Script Date: 08/14/2015 14:23:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- last modified by Pribadi Pamungkas, 2015-08-14
ALTER VIEW [dbo].[View_NoteProviderHeader]
AS
SELECT
  dbo.Note_Terbit_Provider_Header.Status,
  dbo.Note_Terbit_Provider_Header.No_Ref_Provider,
  dbo.Provider.Nama AS Rumah_Sakit,
  dbo.Note_Terbit_Provider_Header.No_Bukti,
  --dbo.Note_Terbit_Provider_Header.Tgl_Buat,
  dbo.Note_Terbit_Provider_Header.Tgl_Buat AS Tgl_Terima, -- modified by Pribadi Pamungkas, 2015-08-14. Request by Citra Warina
  --dbo.Note_Terbit_Provider_Header.Tgl_Bayar,
  dbo.Note_Terbit_Provider_Header.Tgl_Bayar AS Tgl_PPK, -- modified by Pribadi Pamungkas, 2015-08-14. Request by Citra Warina
  dbo.Note_Lunas_Provider_Header.Tgl_Bayar AS Tgl_Lunas,
  dbo.Note_Terbit_Provider_Header.Sub_Total,
  dbo.Note_Terbit_Provider_Header.Uang_Muka,
  dbo.Note_Terbit_Provider_Header.Jml_Diskon,
  dbo.Note_Terbit_Provider_Header.Total,
  dbo.Note_Terbit_Provider_Header.Jml_Lunas,
  dbo.Note_Terbit_Provider_Header.Saldo,
  dbo.Note_Terbit_Provider_Header.Change_Date,
  dbo.Note_Terbit_Provider_Header.Change_By,
  dbo.Note_Terbit_Provider_Header.Create_By,
  dbo.Note_Terbit_Provider_Header.Create_Date,
  dbo.Payor_Header.Nama_Payor
FROM
  dbo.Polis_Asuransi
  INNER JOIN dbo.Note_Terbit_Provider_Detail ON dbo.Polis_Asuransi.No_Proposal = dbo.Note_Terbit_Provider_Detail.No_Proposal
  INNER JOIN dbo.Payor_Header ON dbo.Polis_Asuransi.Id_Payor = dbo.Payor_Header.Id_Payor
  RIGHT OUTER JOIN dbo.Note_Terbit_Provider_Header ON dbo.Note_Terbit_Provider_Detail.No_Ref_Provider = dbo.Note_Terbit_Provider_Header.No_Ref_Provider
  LEFT OUTER JOIN dbo.Note_Lunas_Provider_Header ON dbo.Note_Terbit_Provider_Header.No_Ref_Provider = dbo.Note_Lunas_Provider_Header.No_Ref_Provider
  LEFT OUTER JOIN dbo.Provider ON dbo.Note_Terbit_Provider_Header.Kode_Provider = dbo.Provider.IDI
GROUP BY
  dbo.Note_Terbit_Provider_Header.Status,
  dbo.Note_Terbit_Provider_Header.No_Ref_Provider,
  dbo.Provider.Nama,
  dbo.Note_Terbit_Provider_Header.No_Bukti,
  dbo.Note_Terbit_Provider_Header.Tgl_Buat,
  dbo.Note_Terbit_Provider_Header.Tgl_Bayar,
  dbo.Note_Lunas_Provider_Header.Tgl_Bayar,
  dbo.Note_Terbit_Provider_Header.Sub_Total,
  dbo.Note_Terbit_Provider_Header.Uang_Muka,
  dbo.Note_Terbit_Provider_Header.Jml_Diskon,
  dbo.Note_Terbit_Provider_Header.Total,
  dbo.Note_Terbit_Provider_Header.Jml_Lunas,
  dbo.Note_Terbit_Provider_Header.Saldo,
  dbo.Note_Terbit_Provider_Header.Change_Date,
  dbo.Note_Terbit_Provider_Header.Change_By,
  dbo.Note_Terbit_Provider_Header.Create_By,
  dbo.Note_Terbit_Provider_Header.Create_Date,
  dbo.Payor_Header.Nama_Payor
--HAVING      (dbo.Note_Terbit_Provider_Header.Create_Date >= CONVERT(DATETIME, '2010-01-01 00:00:00', 102))
--HAVING      (dbo.Note_Terbit_Provider_Header.Create_Date >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))



GO


