USE [AJK]
GO
/****** Object:  StoredProcedure [dbo].[proc_export_data_pelunasan]    Script Date: 09/07/2016 09:25:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--- data ini untuk keperluan departemen finance (jaslina)
--ALTER PROCEDURE [dbo].[proc_export_data_pelunasan] (
DECLARE
  @pi_status             VARCHAR(1),
  @pi_tgl_input_awal     DATETIME,
  @pi_tgl_input_akhir    DATETIME,
  @pi_tgl_lunas_awal     DATETIME,
  @pi_tgl_lunas_akhir    DATETIME,
  @pi_id_perusahaan      VARCHAR(11),
  @pi_no_polis           VARCHAR(13) = '09004-01-000',
  @pi_id_cabang          VARCHAR(4),
  @pi_no_bukti           VARCHAR(50) = 'JV 2016/04/0012236',
  @pi_id_rekening_eli    VARCHAR(3),
  @pi_tgl_input_awalYa   BIT = 0,
  @pi_tgl_input_akhirYa  BIT = 0,
  @pi_tgl_lunas_awalYa   BIT = 0,
  @pi_tgl_lunas_akhirYa  BIT = 0,
  @pi_processed_by       VARCHAR(4) = 'SUP',
  @pi_processed_date     DATETIME,
  @pi_processed_dateYa   BIT = 0,
  @po_message            VARCHAR(100) --OUTPUT
--)
--AS
--BEGIN
  SELECT
    nl.tgl_lunas
    ,nl.create_date
    ,CASE 
    WHEN nl.status = 'G' THEN 'tdk otor'
    ELSE 'otor'
    END stat
    ,nl.id_note_lunas
    ,nl.approve_date
    ,nl.id_note_terbit
    ,ps.nama_perusahaan
    ,nt.no_ref
    ,nt.no_polis
    ,COALESCE(c.nama_cabang,'') AS nama_cabang
    ,nt.jml_terbit
    ,nl.jml_lunas
    ,nl.no_bukti_transaksi
    ,re.no_rekening
    ,b.nama_bank
    ,nl.create_by
    ,nl.approve_date
    ,nl.approve_by
    
  FROM
    dbo.note_lunas nl
    INNER JOIN
    dbo.note_terbit nt
    ON nl.id_note_terbit = nt.id_note_terbit
    INNER JOIN
    dbo.polis p
    ON nt.no_polis = p.no_polis
    INNER JOIN
    dbo.perusahaan ps
    ON p.id_perusahaan = ps.id_perusahaan
    LEFT JOIN
    dbo.cabang c
    ON nt.id_cabang = c.id_cabang
    INNER JOIN
    dbo.rekening_eli re
    ON nl.id_rekening_eli = re.id_rekening_eli
    INNER JOIN
    dbo.bank b
    ON re.id_bank = b.id_bank
  WHERE
     nt.id_note_jenis = '01'
     AND
    (
      ((COALESCE(@pi_status, '') <> '') AND (nl.status = @pi_status))OR
      (COALESCE(@pi_status, '') = '') 
    )
    AND
    (
      (@pi_tgl_input_awalYa = 1) AND (dbo.func_fixed_date(nl.create_date) >= dbo.func_fixed_date(@pi_tgl_input_awal)) OR
      (@pi_tgl_input_awalYa = 0)
    ) AND 
    (
      ((@pi_tgl_input_akhirYa = 1) AND (dbo.func_fixed_date(nl.create_date) < dbo.func_fixed_date(@pi_tgl_input_akhir) + 1)) OR 
      (@pi_tgl_input_akhirYa = 0)
    ) 
    AND
    (
      ((@pi_tgl_lunas_awalYa = 1) AND (dbo.func_fixed_date(nl.tgl_lunas) >= dbo.func_fixed_date(@pi_tgl_lunas_awal))) OR 
      (@pi_tgl_lunas_awalYa = 0)
    ) AND 
    (
      ((@pi_tgl_lunas_akhirYa = 1) AND (dbo.func_fixed_date(nl.tgl_lunas) < dbo.func_fixed_date(@pi_tgl_lunas_akhir) + 1)) OR 
      (@pi_tgl_lunas_akhirYa = 0)
    )  
    AND
    (
      ((COALESCE(@pi_id_perusahaan, '') <> '') AND (p.id_perusahaan = @pi_id_perusahaan)) OR
      (COALESCE(@pi_id_perusahaan, '') = '') 
    )  
    AND
    (
      ((COALESCE(@pi_no_polis, '') <> '') AND (nt.no_polis = @pi_no_polis)) OR
      (COALESCE(@pi_no_polis, '') = '') 
    )  
    AND
    (
      ((COALESCE(@pi_id_cabang, '') <> '') AND (nt.id_cabang = @pi_id_cabang)) OR
      (COALESCE(@pi_id_cabang, '') = '') 
    )  
    AND
    (
      ((COALESCE(@pi_no_bukti, '') <> '') AND (nl.no_bukti_transaksi = @pi_no_bukti)) OR
      (COALESCE(@pi_no_bukti, '') = '') 
    )  
    AND
    (
      ((COALESCE(@pi_id_rekening_eli, '') <> '') AND (nl.id_rekening_eli = @pi_id_rekening_eli)) OR
      (COALESCE(@pi_id_rekening_eli, '') = '') 
    )  
  ORDER BY
   nl.tgl_lunas
--END


 