SELECT  
  T6.Id_Dcp, 
  T1.Id_Contact,
  SUM(isnull(T1.Diskon_Nominal,0) + T4.Tipe*T2.Premi_Netto*(T4.Tipe*T5.Jml_Lunas/(T4.Tipe*T4.Total_Terbit))*(0.01*isnull(T1.Diskon_Prosen,0))) as Jml_DCP,
  dbo.func_PajakProgDCP(T6.Id_DCP, T1.Id_Contact, SUM(isnull(T1.Diskon_Nominal,0) + (T4.Tipe*T2.Premi_Netto)*(T4.Tipe*T5.Jml_Lunas/(T4.Tipe*T4.Total_Terbit))*(0.01*isnull(T1.Diskon_Prosen,0))), T6.Tgl_Lunas) as Pajak
FROM
  (
    SELECT 
      V1.Id_Contact, 
      V1.Id_produk, 
      V1.Diskon_Prosen, 
      V1.Diskon_Nominal 
    FROM 
      dbo.Contact_Detail V1 
      INNER JOIN 
      dbo.Contact_Header V2 
      ON V1.Id_Contact=V2.Id_Contact
  ) as T1 
  INNER JOIN
  (
    SELECT 
      V1.No_Ref, 
      V1.Id_Produk, 
      V1.Sub_Kelompok, 
      SUM(V1.Jumlah) as Premi_Brutto,  
      SUM(V1.Jumlah*(1-0.01*V2.Diskon_Pr)) AS Premi_Netto 
    FROM 
      Peserta_Produk_Detail V1 
      INNER JOIN 
      Produk_Polis V2 
      ON V1.Id_Produk=V2.Id_Produk 
    GROUP BY 
      V1.No_Ref, 
      V1.Id_Produk, 
      V1.Sub_Kelompok
   ) T2 
   ON T1.Id_Produk=T2.Id_Produk 
   INNER JOIN 
  (
    SELECT 
      Id_produk, 
      Diskon_Pr 
    FROM Produk_Polis
  ) T3 
  ON T2.Id_Produk=T3.Id_Produk 
  INNER JOIN
  (
    SELECT 
      No_Ref, 
      abs(Total) as Total_Terbit, 
      Tipe 
    FROM Note_Terbit_Master
  ) T4 
  ON T2.No_Ref=T4.No_ref 
  INNER JOIN
  (
    SELECT 
      No_Ref, 
      Tgl_Bayar, 
      Sum(Jumlah) as Jml_Lunas 
    FROM Note_Lunas_Master 
    GROUP BY No_Ref, Tgl_Bayar
  ) T5 
  ON T2.No_Ref=T5.No_Ref 
  INNER JOIN 
  (
    SELECT 
      V1.ID_DCP, 
      V1.No_Ref, 
      V2.Tgl_Bayar as Tgl_Lunas, 
      V1.Tgl_Bayar, 
      V1.Jml_DCP 
    FROM 
      DCP_Terbit_Detail V1 
      INNER JOIN 
      DCP_Terbit_Header V2 
      ON V1.Id_DCP=V2.ID_DCP
  ) T6 
  ON T2.No_Ref=T6.No_Ref AND T5.Tgl_Bayar=T6.Tgl_Bayar
WHERE 
  T6.Id_DCP='2013.00375'
GROUP BY 
  T6.Id_Dcp, 
  T1.Id_Contact, 
  T6.Tgl_Lunas
  


------------------------

SELECT Sum(Jml_Lunas) AS Jml_Lunas, Sum(Jml_DCP) AS Jml_DCP FROM DCP_Terbit_Detail WHERE Id_DCP='2013.00375'
SELECT Round(Sum(isnull(Pajak_Prog,0)),0) AS Pajak_Prog FROM DCP_Terbit_Contact WHERE Id_DCP='2013.00375'


    SELECT 
      V1.ID_DCP, 
      V1.No_Ref, 
      V2.Tgl_Bayar as Tgl_Lunas, 
      V1.Tgl_Bayar, 
      V1.Jml_DCP 
    FROM 
      DCP_Terbit_Detail V1 
      INNER JOIN 
      DCP_Terbit_Header V2 
      ON V1.Id_DCP=V2.ID_DCP
    WHERE v1.Id_DCP='2013.00375'
    
    
DECLARE @p_id_dcp varchar(10) = '2008.00001'
SELECT top 10 * FROM dcp_terbit_header WHERE id_dcp = @p_id_dcp
SELECT top 10 * FROM dcp_terbit_detail WHERE id_dcp = @p_id_dcp
SELECT top 10 * FROM DCP_Terbit_Contact WHERE id_dcp = @p_id_dcp



