USE [AJK]
GO
/****** Object:  StoredProcedure [dbo].[proc_export_note_lunas_new]    Script Date: 02/01/2016 11:50:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- last modified by Pribadi Pamungkas. 2016-02-01
ALTER PROCEDURE [dbo].[proc_export_note_lunas_new] (
  @pi_no_polis              VARCHAR(13),
  @pi_tgl_otorisasi_awal    datetime,
  @pi_tgl_otorisasi_akhir   datetime,
  @pi_id_agen               VARCHAR(5),
  @pi_tgl_otorisasi_awalYa  bit,
  @pi_tgl_otorisasi_akhirYa bit,
  @pi_processed_by          varchar(4),
  @pi_processed_date        datetime,
  @pi_processed_dateYa      bit,
  @po_message               varchar(100) OUTPUT
)
AS
BEGIN 
SELECT 
  v.id_note_terbit,
  v.id_peserta,
  p.no_peserta,
  p.nama_peserta_1 AS nama_peserta,
  nt.no_polis,
  p.id_perusahaan,
  prsh.nama_perusahaan,
  pol.id_agen_penutup AS id_agen,
  a.nama_agen,
  nt.no_ref,
  v.id_produk,
  v.jml_terbit,
  nl.id_note_lunas,
  nlp.jml_lunas,
  nl.no_bukti_transaksi,
  p.uang_pertanggungan,
  p.premi,
  p.extra_premi,
  COALESCE(p.id_cabang,'') AS cabang_eli,
  COALESCE(c.ref,'') AS cabang_bni,
  COALESCE(c.nama_cabang,'') AS nama_cabang,
  COALESCE(c.id_wilayah,'') As cabang_wilayah_bni,
  COALESCE(c.nama_wilayah,'') AS nama_wilayah_bni,
  dbo.func_dateToISODatestr(v.tgl_lunas_terakhir) AS tgl_lunas,
  dbo.func_DateToISODatestr(nt.tgl_terbit) AS tgl_terbit,
  dbo.func_dateToISODatestr(nl.create_date) AS tgl_input_finance,
  dbo.func_dateToISODatestr(nl.approve_date) AS tgl_otorisasi
FROM 
  (
    SELECT 
      id_note_terbit,
      id_peserta,
      id_produk,
      jml_terbit,
      jml_lunas,
      tgl_lunas_terakhir
    FROM view_note_lunas_outstanding_peserta 
    WHERE jml_outstanding = 0 
      --AND id_note_terbit = '2014.0017952'
  ) v  
  --INNER JOIN
  LEFT JOIN   
  dbo.note_lunas_peserta nlp
  ON v.id_peserta = nlp.id_peserta
  INNER JOIN
  (
    SELECT
      id_note_lunas,
      tgl_lunas,
      id_note_terbit,
      no_bukti_transaksi,
      jml_lunas,
      create_date,
      approve_date
    FROM note_lunas
    WHERE 
      (status = 'I') AND
      (
        ((@pi_tgl_otorisasi_awalYa = 1) AND (approve_date >= dbo.func_fixed_date(@pi_tgl_otorisasi_awal))) OR 
        (@pi_tgl_otorisasi_awalYa = 0)
      ) AND 
      (
        ((@pi_tgl_otorisasi_akhirYa = 1) AND (approve_date < dbo.func_fixed_date(@pi_tgl_otorisasi_akhir) + 1)) OR 
        (@pi_tgl_otorisasi_akhirYa = 0)
      )
  ) nl
  ON nlp.id_note_lunas = nl.id_note_lunas
  LEFT JOIN  
  (
    SELECT
      id_note_terbit,
      no_polis,
      no_ref,
      tgl_terbit
    FROM note_terbit 
    WHERE 
      (status <> 'C') AND
      (
        ((COALESCE(@pi_no_polis, '') <> '') AND (no_polis = @pi_no_polis)) OR
        (COALESCE(@pi_no_polis, '') = '') 
      ) 
  ) nt
  ON nl.id_note_terbit = nt.id_note_terbit
  INNER JOIN  
  (
    SELECT
      id_peserta,
      id_perusahaan,
      no_peserta,
      nama_peserta_1,
      id_cabang,
      uang_pertanggungan,
      premi,
      extra_premi
    FROM peserta 
    WHERE status <> 'C'
  ) p
  ON v.id_peserta = p.id_peserta
  LEFT JOIN
  (
    SELECT
      c.id_cabang,
      c.ref,
      c.nama_cabang,
      c.id_cabang_induk AS id_wilayah,
      cc.nama_cabang AS nama_wilayah
    FROM
      cabang c
      --INNER JOIN  -- versi sebelum diubah
      LEFT JOIN -- modified by Pribadi Pamungkas. 2016-02-01
      cabang cc
      ON c.id_cabang_induk = cc.id_cabang
  ) c
  ON p.id_cabang = c.id_cabang
  INNER JOIN
  perusahaan prsh
  ON p.id_perusahaan = prsh.id_perusahaan
  INNER JOIN
  (
    SELECT 
      no_polis
      ,id_agen_penutup
    FROM dbo.polis 
    WHERE
      (
        ((COALESCE(@pi_id_agen, '') <> '') AND (id_agen_penutup = @pi_id_agen)) OR
        (COALESCE(@pi_id_agen, '') = '') 
      ) 
  )pol
  ON nt.no_polis = pol.no_polis
  INNER JOIN
  dbo.agen a
  ON pol.id_agen_penutup = a.id_agen
ORDER BY 
  nt.no_polis,
  p.no_peserta
END
