USE [GrpInsurance]
GO

/****** Object:  View [dbo].[view_NotePolis]    Script Date: 22/06/2018 14:58:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- last modified by Pribadi Pamungkas. 2018-06-22 => ticket RQ2018060484, tambah kolom cabang
ALTER VIEW [dbo].[view_NotePolis]
AS
SELECT     TOP (100) PERCENT 
    dbo.Note_Terbit_Master.Status, 
    dbo.Note_Terbit_Master.No_Ref, 
    dbo.Note_Jenis.Description, 
    dbo.Note_Terbit_Master.No_Note, 
    dbo.Note_Terbit_Master.Tgl_Buat, 
    dbo.Note_Terbit_Master.Tgl_Produksi, 
    dbo.Note_Terbit_Master.Total, 
    dbo.Note_Terbit_Master.Tgl_Lunas, 
    dbo.Note_Terbit_Master.Jml_Lunas, 
    dbo.Note_Terbit_Master.Saldo, 
    dbo.Note_Terbit_Master.Change_Date, 
    dbo.Note_Terbit_Master.Change_By, 
    dbo.Note_Terbit_Master.No_Proposal, 
    dbo.Note_Shipping.Tgl_Kirim, 
    dbo.Note_Shipping.Pengirim, 
    dbo.Note_Shipping.Tgl_Terima, 
    dbo.Note_Shipping.Penerima, 
    dbo.Note_Jenis.Kode, 
    MAX(dbo.Note_Lunas_Master.Change_Date) AS FinDt, 
    dbo.Note_Terbit_Master.Jml_Peserta,
    cabang.Nama AS Cabang
FROM
    dbo.Note_Lunas_Master 
    RIGHT OUTER JOIN
    dbo.Note_Terbit_Master 
    ON dbo.Note_Lunas_Master.No_Ref = dbo.Note_Terbit_Master.No_Ref 
    LEFT OUTER JOIN
    dbo.Note_Shipping 
    ON dbo.Note_Terbit_Master.No_Ref = dbo.Note_Shipping.No_Ref 
    LEFT OUTER JOIN
    dbo.Note_Jenis 
    ON dbo.Note_Terbit_Master.Jenis = dbo.Note_Jenis.Kode LEFT JOIN
    (
	   SELECT
		  pa.No_Proposal,
		  cb.Kode,
		  cb.Nama
	   FROM
		  dbo.Polis_Asuransi AS pa LEFT JOIN
		  dbo.Cabang AS cb ON pa.No_Prs = cb.No_Prs
    ) AS cabang ON dbo.Note_Terbit_Master.No_Proposal = cabang.No_Proposal
			    AND dbo.Note_Terbit_Master.Cabang = cabang.Kode
GROUP BY 
    dbo.Note_Terbit_Master.Status, 
    dbo.Note_Terbit_Master.No_Ref, 
    dbo.Note_Jenis.Description, 
    dbo.Note_Terbit_Master.No_Note, 
    dbo.Note_Terbit_Master.Tgl_Buat, 
    dbo.Note_Terbit_Master.Tgl_Produksi, 
    dbo.Note_Terbit_Master.Total, 
    dbo.Note_Terbit_Master.Tgl_Lunas, 
    dbo.Note_Terbit_Master.Jml_Lunas, 
    dbo.Note_Terbit_Master.Saldo, 
    dbo.Note_Terbit_Master.Change_Date, 
    dbo.Note_Terbit_Master.Change_By, 
    dbo.Note_Terbit_Master.No_Proposal, 
    dbo.Note_Shipping.Tgl_Kirim, 
    dbo.Note_Shipping.Pengirim, 
    dbo.Note_Shipping.Tgl_Terima, 
    dbo.Note_Shipping.Penerima, 
    dbo.Note_Jenis.Kode, 
    dbo.Note_Terbit_Master.Jml_Peserta,
    cabang.Nama
ORDER BY 
    dbo.Note_Terbit_Master.No_Ref


GO
