USE [GrpInsurance]
GO

/****** Object:  View [dbo].[view_KlaimRegister]    Script Date: 09/12/2014 10:15:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*PROC_eod*/
CREATE VIEW [dbo].[view_KlaimRegister]
AS
SELECT     dbo.Klaim_Register.Status, dbo.Klaim_Register.Id_RegKlaim, dbo.Klaim_Register.No_Surat, 
                      CASE dbo.Klaim_Register.Jenis WHEN 1 THEN dbo.Klaim_Register.No_Polis WHEN 3 THEN dbo.Klaim_Register.No_Polis ELSE dbo.Klaim_Register.Kode_Provider END AS Vendor_Kode, 
                      CASE dbo.Klaim_Register.Jenis WHEN 1 THEN dbo.func_GetVendorKlaimRegister(dbo.Klaim_Register.No_Polis, dbo.Klaim_Register.Jenis) WHEN 3 THEN dbo.func_GetVendorKlaimRegister(dbo.Klaim_Register.No_Polis, dbo.Klaim_Register.Jenis) 
                      ELSE dbo.func_GetVendorKlaimRegister(dbo.Klaim_Register.Kode_Provider, dbo.Klaim_Register.Jenis) END AS Vendor_Nama, 
                      dbo.Payor_Header.Nama_Payor, dbo.Klaim_Register.Tgl_Terima, dbo.Klaim_Register.Jam_Terima, dbo.Klaim_Register.Tot_Transaksi, 
                      dbo.Klaim_Register.Tot_Diajukan, dbo.Klaim_Register.Keterangan, dbo.Klaim_Register.Create_By, dbo.Klaim_Register.Create_Date, 
                      dbo.Klaim_Register.Change_By, dbo.Klaim_Register.Change_Date, dbo.Klaim_Register.No_Polis, dbo.Klaim_Register.Jenis
FROM         dbo.Payor_Header INNER JOIN
                      dbo.Polis_Asuransi ON dbo.Payor_Header.Id_Payor = dbo.Polis_Asuransi.Id_Payor RIGHT OUTER JOIN
                      dbo.Klaim_Register ON dbo.Polis_Asuransi.No_Polis = dbo.Klaim_Register.No_Polis AND dbo.Polis_Asuransi.Active_Ya = 1 AND 
                      dbo.Polis_Asuransi.Status <> 'C' LEFT OUTER JOIN
                      dbo.Perusahaan ON dbo.Perusahaan.No_Prs = dbo.Polis_Asuransi.No_Prs
WHERE     (dbo.Klaim_Register.Create_Date >= CONVERT(DATETIME, '2011-01-01 00:00:00', 102))


GO

