SELECT
  erp.Status,
  lengkap.Status,
  erp.No_Proposal,
  lengkap.No_Proposal,
  erp.No_Ref,
  lengkap.No_Ref,
  erp.Jenis,
  lengkap.Jenis,
  lengkap.Description,
  erp.Jenis_Note,
  Id_Produk,
  erp.Sub_Kelompok,
  lengkap.Sub_Kelompok,
  erp.Jml_Premi,
  lengkap.Jml_Premi
FROM
	(
    SELECT
      dbo.Note_Terbit_Master.Status,
      dbo.Note_Terbit_Master.No_Proposal,
      dbo.Note_Terbit_Master.No_Ref,
      dbo.Note_Terbit_Master.Jenis,
      nj.Description AS Jenis_Note,
      dbo.Peserta_Produk_Detail.Id_Produk, 
      dbo.Peserta_Produk_Detail.Sub_Kelompok,
      SUM(dbo.Note_Terbit_Master.Tipe * ABS(ISNULL(dbo.Peserta_Produk_Detail.Jumlah, 0))) AS Jml_Premi
    FROM
      dbo.Note_Terbit_Master
      INNER JOIN dbo.Peserta_Produk_Detail ON dbo.Note_Terbit_Master.No_Ref = dbo.Peserta_Produk_Detail.No_Ref
      INNER JOIN dbo.Peserta ON dbo.Note_Terbit_Master.No_Proposal = dbo.Peserta.No_Proposal
                                AND dbo.Peserta_Produk_Detail.No_Peserta = dbo.Peserta.No_Peserta
      INNER JOIN dbo.Polis_Asuransi ON dbo.Note_Terbit_Master.No_Proposal = dbo.Polis_Asuransi.No_Proposal
      INNER JOIN dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs INNER JOIN
      dbo.Note_Jenis AS nj ON dbo.Note_Terbit_Master.Jenis = nj.Kode
    WHERE
      (dbo.Note_Terbit_Master.Jenis <= 19)
      AND (dbo.Note_Terbit_Master.Status <> 'C')
      AND (LEFT(dbo.Peserta_Produk_Detail.Sub_Kelompok, 2) = '03')
      AND (ISNULL(dbo.Polis_Asuransi.Erp_Prosen, 0) <> 0)
      --AND (dbo.Peserta.Status<>'C') 
      AND (dbo.Note_Terbit_Master.No_Proposal = '2014.0168') 
    GROUP BY
      dbo.Note_Terbit_Master.No_Proposal,
      dbo.Note_Terbit_Master.No_Ref,
      dbo.Peserta_Produk_Detail.Id_Produk,
      dbo.Note_Terbit_Master.Jenis,
      nj.Description,
      dbo.Peserta_Produk_Detail.Sub_Kelompok,
      dbo.Note_Terbit_Master.Status
	) AS erp LEFT JOIN
	(
    SELECT
      dbo.Note_Terbit_Master.Status,
      dbo.Note_Terbit_Master.No_Proposal,
      dbo.Note_Terbit_Master.No_Ref,
      dbo.Note_Terbit_Master.Jenis,
      dbo.Note_Jenis.Description,
      dbo.Note_Terbit_Detail.Sub_Kelompok,
      dbo.Note_Terbit_Master.Tipe * ABS(ISNULL(dbo.Note_Terbit_Detail.Jumlah, 0)) AS Jml_Premi
    FROM 
      dbo.Payor_Header INNER JOIN
      dbo.Perusahaan INNER JOIN
      dbo.Polis_Asuransi ON dbo.Perusahaan.No_Prs = dbo.Polis_Asuransi.No_Prs ON 
      dbo.Payor_Header.Id_Payor = dbo.Polis_Asuransi.Id_Payor RIGHT OUTER JOIN
      dbo.Note_Jenis INNER JOIN
      dbo.Note_Terbit_Master ON dbo.Note_Jenis.Kode = dbo.Note_Terbit_Master.Jenis LEFT OUTER JOIN
      dbo.Note_Shipping ON dbo.Note_Terbit_Master.No_Ref = dbo.Note_Shipping.No_Ref ON 
      dbo.Polis_Asuransi.No_Proposal = dbo.Note_Terbit_Master.No_Proposal INNER JOIN
      dbo.Note_Terbit_Detail ON dbo.Note_Terbit_Master.No_Ref=dbo.Note_Terbit_Detail.No_Ref
    WHERE 
      (dbo.Note_Terbit_Master.Status = 'I') 
      AND dbo.Note_Terbit_Master.Jenis IN (1,2,3,4,6,9,11,12,18,19)
      AND (dbo.Note_Terbit_Master.No_Proposal = '2014.0168')
	) AS lengkap
	ON erp.No_Proposal = lengkap.No_Proposal
	   AND erp.No_Ref = lengkap.No_Ref
	   AND erp.Sub_Kelompok = lengkap.Sub_Kelompok
	   AND erp.Jenis = lengkap.Jenis