USE [GrpInsurance]

--ALTER PROCEDURE [dbo].[proc_RepJurnalProvider]
DECLARE
      @pi_tgl_awal DATETIME = '2016-06-29',
      @pi_tgl_akhir DATETIME = '2016-06-29',
      @pi_bank_asal VARCHAR(5) = '003',
      @pi_no_bukti VARCHAR(20) = '003P 2016/6/29-0001',
      @pi_gt_og VARCHAR(2) = '',
      @pi_tgl_awalya BIT = 1,
      @pi_tgl_akhirya BIT = 1

SET @pi_gt_og = UPPER(ISNULL(@pi_gt_og, ''))
SET @pi_tgl_awal = dbo.func_FixedDate(@pi_tgl_awal)
SET @pi_tgl_akhir = dbo.func_FixedDate(@pi_tgl_akhir)

SELECT 
  T1.No_Ref,
  T1.No_Ref_Provider,
  T3.tgl_bayar, 
  T3.No_Bukti, 
  t3.Bank_Asal, 
  T2.mata_uang,
  T2.GT_OG,
  T2.Afiliasi,
  T2.Produk,
  Tot_lunas = ROUND(SUM(T3.Jml_Lunas * (CASE WHEN ISNULL(T3.Sub_Total, 0) <> 0 THEN (1.0 * ISNULL(t2.jml_diajukan, 0) / T3.Sub_Total)
                                             ELSE 0.0
                                        END)), 2)
--INTO
--  temp_prib_20160628_20502
FROM
  Note_Terbit_Provider_detail T1
  INNER JOIN (
              SELECT
                polis_asuransi.no_proposal,
                Perusahaan.GT_OG,
                Perusahaan.afiliasi,
                Klaim_Ref_Master.No_Ref,
                Mata_uang.Terbilang AS mata_uang,
                Sub_Kelompok.Nama_Sub AS Produk,
                SUM(Klaim_Ref_Master.Jml_Diajukan) AS jml_diajukan 
--SUM(Klaim_Ref_Master.Jml_Ditolak) AS jml_ditolak,
--SUM(Klaim_Ref_Master.Uang_Muka) as uang_muka,
--SUM(Klaim_Ref_Master.Jml_Deductible) AS jml_deductible, 
--SUM(Klaim_Ref_Master.Diskon_Rs) AS jml_diskon_rs
              FROM
                Klaim_Ref_Master
                INNER JOIN Sub_Kelompok ON CASE WHEN ISNULL(Klaim_Ref_Master.Sub_Kelompok, '') = '0302' THEN '0302'
                                                ELSE '0301'
                                           END = Sub_Kelompok.Sub_Kelompok
                INNER JOIN Polis_Asuransi ON Klaim_Ref_Master.no_proposal = Polis_Asuransi.no_proposal
                INNER JOIN Perusahaan ON Polis_Asuransi.No_Prs = Perusahaan.No_Prs
                INNER JOIN Mata_Uang ON Polis_Asuransi.Mata_Uang = Mata_Uang.Kode
              GROUP BY
                Klaim_Ref_Master.No_Ref,
                Sub_Kelompok.Nama_Sub,
                polis_asuransi.no_proposal,
                Perusahaan.GT_OG,
                Perusahaan.afiliasi,
                Mata_uang.Terbilang
             ) T2 ON T1.No_Ref = T2.No_Ref
  INNER JOIN (
              SELECT
                Note_Lunas_Provider_Header.Tgl_Bayar,
                Note_Lunas_Provider_Header.Bank_Asal,
                Note_Lunas_Provider_Header.No_Bukti,
                Note_Terbit_Provider_Header.No_Ref_Provider,
                Note_Terbit_Provider_Header.Sub_Total,
                Note_Terbit_Provider_Header.Uang_Muka,
                tot_diskon = ISNULL(Note_Terbit_Provider_Header.Diskon, 0) + ISNULL(Note_Terbit_Provider_Header.Jml_Diskon, 0),
                Note_Terbit_Provider_Header.Total,
                SUM(Note_Lunas_Provider_Header.Jumlah) AS jml_lunas
              FROM
                Note_Terbit_Provider_Header
                INNER JOIN Note_Lunas_Provider_Header ON Note_Terbit_Provider_Header.No_Ref_Provider = Note_Lunas_Provider_Header.No_Ref_Provider
              GROUP BY
                Note_Terbit_Provider_Header.No_Ref_Provider,
                Note_Terbit_Provider_Header.Sub_Total,
                Note_Terbit_Provider_Header.Uang_Muka,
                Note_Terbit_Provider_Header.Jml_Diskon,
                Note_Terbit_Provider_Header.Diskon,
                Note_Terbit_Provider_Header.Total,
                Note_Lunas_Provider_Header.Tgl_Bayar,
                Note_Lunas_Provider_Header.No_Bukti,
                Note_Lunas_Provider_Header.Bank_Asal
             ) T3 ON T1.no_ref_provider = T3.no_ref_provider
WHERE
  (T3.tgl_bayar >= @pi_tgl_awal OR @pi_tgl_awalya = 0)
  AND (T3.tgl_bayar <= @pi_tgl_akhir OR @pi_tgl_akhirya = 0)
  AND (T3.No_Bukti = @pi_no_bukti OR @pi_no_bukti = '')
  AND (T3.Bank_Asal = @pi_bank_asal OR @pi_bank_asal = '')
  AND (GT_OG = @pi_gt_og OR @pi_gt_og = '')
GROUP BY
  T1.No_Ref,
  T1.No_Ref_Provider,
  T3.tgl_bayar, 
  T3.No_Bukti, 
  t3.Bank_Asal, 
  T2.mata_uang,
  T2.GT_OG,
  T2.Afiliasi,
  T2.Produk
ORDER BY
  T3.Tgl_bayar, 
  T3.No_Bukti, 
  t3.Bank_Asal,
  T2.mata_uang, 
  T2.GT_OG,
  T2.Afiliasi,
  T2.Produk
