USE [GrpInsurance]
GO


SELECT 
  dbo.Polis_Asuransi.Status,
  dbo.Skema_Polis.No_Polis,
  dbo.Perusahaan.Nama_Perusahaan,
  --dbo.Skema_Polis.Pickup_Klaim,
  CASE dbo.Skema_Polis.Pickup_Klaim
    WHEN 1 THEN 'Y'
    ELSE 'T'
  END AS Pickup_Klaim,
  dbo.Skema_Polis.Id_Skema,
  dbo.Skema_Klaim.Deskripsi,
  dbo.Skema_Klaim.Jml_Hari,
  COALESCE(dbo.Perusahaan.Email,'') + '; ' + COALESCE(dbo.Perusahaan.Email3,'') AS Email,
  dbo.Skema_Polis.Kolektor,
  dbo.Skema_Polis.Create_By,
  dbo.Skema_Polis.Create_Date,
  dbo.Skema_Polis.Change_By,
  dbo.Skema_Polis.Change_Date,
  dbo.Skema_Polis.Status AS St2
FROM
  dbo.Skema_Klaim
  RIGHT OUTER JOIN dbo.Skema_Polis ON dbo.Skema_Klaim.Id_Skema = dbo.Skema_Polis.Id_Skema
  LEFT OUTER JOIN dbo.Perusahaan
  INNER JOIN dbo.Polis_Asuransi ON dbo.Perusahaan.No_Prs = dbo.Polis_Asuransi.No_Prs ON dbo.Skema_Polis.No_Polis = dbo.Polis_Asuransi.No_Polis
                                                                                        AND dbo.Polis_Asuransi.Active_Ya = 1
WHERE
  dbo.Polis_Asuransi.Status = 'I'
ORDER BY
  --dbo.Skema_Polis.Id_Skema
  dbo.Perusahaan.Nama_Perusahaan

