USE [WebBranches]
GO
/****** Object:  StoredProcedure [dbo].[proc_copy_temp_spaj_setoran_to_SPAJ_setoran]    Script Date: 01/22/2013 08:52:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[proc_copy_temp_spaj_setoran_to_SPAJ_setoran]
@pi_id_premi_pertama int,
@po_message varchar(300) Output
AS
begin
	declare @p_curr_date datetime,
		@p_tgl_bayar datetime,
		@p_premi float,
		@p_top_up float,
		@p_no_bukti varchar(15),
		@p_no_rekening varchar(3),
		@p_create_date datetime,
		@p_create_by varchar(4), 
		@p_change_date datetime,
		@p_change_by varchar(4),
		@p_nilai_kurs float,
		@p_id_valuta_bayar varchar(3),
		@p_id_valuta_polis varchar(3),
		@p_id_cara_bayar_nasabah varchar(4),
		@p_premi_polis float,
		@p_no_urut int,
		@p_tgl_kurs datetime,
		@p_biaya_polis float,
		@p_no_spaj varchar(12)
		
	set @p_curr_date = GETDATE()
	select 
		@p_no_spaj = no_spaj,
		@p_tgl_bayar = tgl_transaksi, 
		@p_premi = coalesce(premi,0), 
		@p_top_up = coalesce(top_up,0), 
		@p_no_bukti = no_bukti, 
		@p_no_rekening = id_rekening_sumber, 
		@p_create_date = create_date, 
		@p_create_by = create_by, 
		@p_change_date = change_date, 
		@p_change_by = change_by,
		@p_id_valuta_bayar = id_valuta,
		@p_tgl_kurs = tgl_kurs,
		@p_biaya_polis = coalesce(biaya_polis,0)
	from web_premi_pertama
	where id_premi_pertama = @pi_id_premi_pertama
	
	begin tran

	if(exists(select 1 from IndInsurance.dbo.kurs
	where IndInsurance.dbo.func_fixed_date(tgl_berlaku) = IndInsurance.dbo.func_fixed_date(@p_tgl_kurs)
	and id_jenis_kurs = '07'
	and id_valuta_source = 'USD'
	and id_valuta_target = 'IDR'))
	begin
		select @p_nilai_kurs = coalesce(nilai_kurs,0) from IndInsurance.dbo.kurs
		where IndInsurance.dbo.func_fixed_date(tgl_berlaku) = IndInsurance.dbo.func_fixed_date(@p_tgl_kurs)
		and id_jenis_kurs = '07'
		and id_valuta_source = 'USD'
		and id_valuta_target = 'IDR'
	end
	else
		set @p_nilai_kurs = 1

	if @p_nilai_kurs <= 0
		set @p_nilai_kurs = 1
		
  
  if(exists(select 1
	from IndInsurance.dbo.spaj
	where no_spaj = @p_no_spaj))
	begin
		select 
			@p_id_valuta_polis = id_valuta,
			@p_id_cara_bayar_nasabah = id_cara_bayar_nasabah
		from IndInsurance.dbo.spaj
		where no_spaj = @p_no_spaj
	end
	else
	begin
		set @p_id_valuta_polis = 'IDR'
		set @p_id_cara_bayar_nasabah ='001'
	end
	if @p_id_valuta_polis = @p_id_valuta_bayar
		set @p_nilai_kurs = 1
	if @p_id_valuta_polis = 'IDR'
	begin
		set @p_premi_polis = @p_premi*@p_nilai_kurs
	end
	else
	begin
		if @p_id_valuta_bayar = 'IDR'
		begin
			set @p_premi_polis = @p_premi/@p_nilai_kurs
		end
		else
		begin
			set @p_premi_polis = @p_premi*@p_nilai_kurs
		end
	end

	if(not(exists(select 1 from IndInsurance.dbo.spaj_setoran where no_spaj = @p_no_spaj)))
	begin
    insert into IndInsurance.dbo.spaj_setoran
    (no_spaj, id_valuta, id_kelompok_produk, jml_premi_pertama, jml_penyesuaian, nilai_kurs, status, create_date, create_by, change_date, change_by,jml_top_up_tunggal)
    select
			@p_no_spaj, 
			id_valuta,
			'01',
			@p_premi_polis,
			0,
			@p_nilai_kurs,
			'G',
			@p_create_date,
			@p_create_by,
			@p_change_date,
			@p_change_by,
			@p_top_up
		from web_premi_pertama
		where no_spaj = @p_no_spaj
		IF (@@ERROR <> 0) OR (@@RowCount = 0)
		BEGIN
			SET @po_message= 'Error on inserting table spaj_setoran'
      Rollback TRAN
			return 1
		END
		
		insert into IndInsurance.dbo.spaj_setoran_detail
		(no_spaj, no_urut, no_bukti_penerimaan, id_cara_bayar_nasabah, no_referensi, id_rekening_terima, id_valuta, jml_bayar_nasabah, tgl_bayar, tgl_kurs, nilai_kurs, status, create_date, create_by, change_date, change_by,id_jenis_kurs, nilai_kurs_rupiah, jml_bayar_nasabah_rupiah, total, biaya)
		values
		(@p_no_spaj,1,'',@p_id_cara_bayar_nasabah,@p_no_bukti, @p_no_rekening, @p_id_valuta_bayar,@p_premi,@p_tgl_bayar,@p_tgl_kurs,@p_nilai_kurs,'I',@p_create_date,@p_create_by,@p_change_date,@p_change_by,'07',@p_nilai_kurs,case when @p_id_valuta_bayar <> 'IDR' then @p_premi*@p_nilai_kurs else @p_premi end, @p_premi,0)
		IF (@@ERROR <> 0) OR (@@RowCount = 0)
		BEGIN
			SET @po_message= 'Error on inserting table spaj_setoran_detail'
      Rollback TRAN
			return 1
		END
		if (@p_biaya_polis > 0)
		begin
			insert into IndInsurance.dbo.spaj_setoran_detail
			(no_spaj, no_urut, no_bukti_penerimaan, id_cara_bayar_nasabah, no_referensi, id_rekening_terima, id_valuta, jml_bayar_nasabah, tgl_bayar, tgl_kurs, nilai_kurs, status, create_date, create_by, change_date, change_by,id_jenis_kurs, nilai_kurs_rupiah, jml_bayar_nasabah_rupiah, total, biaya)
			values
			(@p_no_spaj,2,'',@p_id_cara_bayar_nasabah,@p_no_bukti, @p_no_rekening, @p_id_valuta_bayar,@p_biaya_polis,@p_tgl_bayar,@p_tgl_kurs,@p_nilai_kurs,'I',@p_create_date,@p_create_by,@p_change_date,@p_change_by,'07',@p_nilai_kurs,case when @p_id_valuta_bayar <> 'IDR' then @p_biaya_polis*@p_nilai_kurs else @p_biaya_polis end, @p_biaya_polis,1)
			IF (@@ERROR <> 0) OR (@@RowCount = 0)
			BEGIN
				SET @po_message= 'Error on inserting table spaj_setoran_detail biaya'
				Rollback TRAN
				return 1
			END
		end
	end
	else
	begin
		update IndInsurance.dbo.spaj_setoran
		set jml_premi_pertama = jml_premi_pertama + @p_premi_polis
		where no_spaj = @p_no_spaj
		IF (@@ERROR <> 0)
		BEGIN
			SET @po_message= 'Error on update table spaj_setoran'
      Rollback TRAN
			return 1
		END
		if @p_top_up > 0
		begin
			update IndInsurance.dbo.spaj_setoran
			set jml_top_up_tunggal = @p_top_up
			where no_spaj = @p_no_spaj
			IF (@@ERROR <> 0)
			BEGIN
				SET @po_message= 'Error on update table spaj_setoran top up tunggal'
				Rollback TRAN
				return 1
			END
		end
		select @p_no_urut = max(no_urut) from IndInsurance.dbo.spaj_setoran_detail
		where no_spaj = @p_no_spaj
		set @p_no_urut = @p_no_urut + 1
		
		insert into IndInsurance.dbo.spaj_setoran_detail
		(no_spaj, no_urut, no_bukti_penerimaan, id_cara_bayar_nasabah, no_referensi, id_rekening_terima, id_valuta, jml_bayar_nasabah, tgl_bayar, tgl_kurs, nilai_kurs, status, create_date, create_by, change_date, change_by,id_jenis_kurs, nilai_kurs_rupiah, jml_bayar_nasabah_rupiah, total, biaya)
		values
		(@p_no_spaj,@p_no_urut,@p_no_bukti,@p_id_cara_bayar_nasabah,@p_no_bukti, @p_no_rekening, @p_id_valuta_bayar,@p_premi,@p_tgl_bayar,@p_tgl_kurs,@p_nilai_kurs,'I',@p_create_date,@p_create_by,@p_change_date,@p_change_by,'07',@p_nilai_kurs,case when @p_id_valuta_bayar <> 'IDR' then @p_premi*@p_nilai_kurs else @p_premi end, @p_premi,0)
		IF (@@ERROR <> 0)
		BEGIN
			SET @po_message= 'Error on update table spaj_setoran_detail'
      Rollback TRAN
			return 1
		END
		if (@p_biaya_polis > 0)
		begin
			insert into IndInsurance.dbo.spaj_setoran_detail
			(no_spaj, no_urut, no_bukti_penerimaan, id_cara_bayar_nasabah, no_referensi, id_rekening_terima, id_valuta, jml_bayar_nasabah, tgl_bayar, tgl_kurs, nilai_kurs, status, create_date, create_by, change_date, change_by,id_jenis_kurs, nilai_kurs_rupiah, jml_bayar_nasabah_rupiah, total, biaya)
			values
			(@p_no_spaj,2,'',@p_id_cara_bayar_nasabah,@p_no_bukti, @p_no_rekening, @p_id_valuta_bayar,@p_biaya_polis,@p_tgl_bayar,@p_tgl_kurs,@p_nilai_kurs,'I',@p_create_date,@p_create_by,@p_change_date,@p_change_by,'07',@p_nilai_kurs,case when @p_id_valuta_bayar <> 'IDR' then @p_biaya_polis*@p_nilai_kurs else @p_biaya_polis end, @p_biaya_polis,1)
			IF (@@ERROR <> 0) OR (@@RowCount = 0)
			BEGIN
				SET @po_message= 'Error on inserting table spaj_setoran_detail biaya'
				Rollback TRAN
				return 1
			END
		end
	end
	
	SET @po_message= 'Sukses'
	commit tran
end