USE [IndInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_copy_SPAJ]    Script Date: 11/06/2012 13:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--declare @po_message varchar(300)
--exec proc_otorisasi_SPAJ_Register 'N67569','03','SUP','I','xxx', @po_message output
--print (@po_message)

create PROCEDURE [dbo].[proc_copy_temp_spaj_to_SPAJ]
@pi_no_spaj varchar(12),
@po_message varchar(300) Output
AS
begin
	declare @p_curr_date datetime,
		@p_id_plan_dasar varchar(4),
		@p_id_agen varchar(9),
		@p_tgl_produksi datetime,
		@p_tgl_mulai datetime,
		@p_masa_kontrak_dasar int,
		@p_premi_berkala float,
		@p_top_up_berkala float,
		@p_id_valuta varchar(3),
		@p_biaya_materai float,
		@p_biaya_polis_diakhir_ya bit,
		@p_biaya_administrasi float,
		@p_biaya_polis float,
		@p_create_date datetime,
		@p_create_by varchar(4),
		@p_id_cabang varchar(4),
		@p_id_unit_pelayanan varchar(4),
		@p_tgl_selesai datetime
		
	
	if(not(exists(select 1 from spaj where no_spaj = @pi_no_spaj)))
	begin
		select 
			@p_id_plan_dasar = id_plan_dasar ,
			@p_id_agen = id_agen,
			@p_tgl_produksi = dbo.func_fixed_date(tgl_produksi),
			@p_masa_kontrak_dasar = masa_kontrak_dasar,
			@p_tgl_mulai = dbo.func_fixed_date(tgl_mulai),
			@p_premi_berkala = premi_berkala,
			@p_top_up_berkala = top_up_berkala,
			@p_id_valuta = id_valuta,
			@p_create_date = create_date,
			@p_create_by = create_by
		from web_spaj
		where no_spaj = @pi_no_spaj
		
		select @p_id_cabang = id_cabang, @p_id_unit_pelayanan = id_unit_pelayanan 
		from dbo.func_get_history_agen_hirarki_per_agen(@p_tgl_produksi, @p_id_agen, 0, 0)
		
		select @p_tgl_selesai = Dateadd(year,@p_masa_kontrak_dasar,@p_tgl_mulai-1)
		
		--Ambil biaya materai
		if(exists(select 1 from Biaya_Materai
		where (@p_premi_berkala + @p_top_up_berkala) between Nilai_awal and Nilai_akhir
		and tgl_Berlaku <= coalesce(@p_tgl_mulai,getdate())
		and id_valuta = @p_id_valuta
		and upper(status) = 'I'))
		begin
			select @p_biaya_materai = coalesce(nilai,0) from Biaya_Materai
			where (@p_premi_berkala + @p_top_up_berkala) between Nilai_awal and Nilai_akhir
			and tgl_Berlaku <= coalesce(@p_tgl_mulai,getdate())
			and id_valuta = @p_id_valuta
			and upper(status) = 'I'
		end
		else
		begin
			set @p_biaya_materai = 0
		end
		
		--Ambil biaya administrasi
		select @p_biaya_polis_diakhir_ya = coalesce(biaya_polis_diakhir_ya,0) from produk p, plan_asuransi pa
		where pa.id_plan = @p_id_plan_dasar
		and p.id_produk = pa.id_produk
		
		if(@p_biaya_polis_diakhir_ya = 0)
		begin
			if(exists(select 1 from Biaya_Polis
			where tgl_Berlaku <= @p_tgl_mulai
			and id_valuta = @p_id_valuta
			and upper(status) = 'I'
			and id_plan = @p_id_plan_dasar))
			begin
				select @p_biaya_polis = coalesce(biaya_polis,0) from Biaya_Polis
				where tgl_Berlaku <= @p_tgl_mulai
				and id_valuta = @p_id_valuta
				and upper(status) = 'I'
				and id_plan = @p_id_plan_dasar
				set @p_biaya_administrasi = coalesce(@p_biaya_polis,0) - @p_biaya_materai
			end
			else
			begin
				SET @po_message = 'Tidak ada biaya polis, Silahkan hubungi bagian keuangan'
				RETURN 1
			end
		end
		else
		begin
			set @p_biaya_administrasi = 0
		end		
		
				
		
		--Copy SPAJ
		insert into spaj
		(no_spaj, no_spajk, id_plan_dasar, id_periode_bayar, id_cara_bayar_nasabah, keterangan_cara_bayar_nasabah, 
		id_valuta, tgl_mulai, tgl_selesai, tgl_produksi, id_jenis_investasi, masa_kontrak_dasar, masa_pembayaran_premi_dasar, 
		uang_pertanggungan_dasar, premi_dasar, premi_rider, premi_berkala, top_up_berkala, total_premi, id_agen, id_cabang, id_cabang_korespondensi, 
		multi_polis_ya, multi_payor_benefit_rider_ya, materai_polis_ya, materai_kuitansi_ya, administrasi_ya, paket_ya, alternatif2_ya, tgl_alternatif2, 
		setor_ya, catatan, tgl_aplikasi, biaya_administrasi, biaya_materai, status, create_date, create_by, change_date, change_by, id_perusahaan, 
		medical_ppm_ya, medical_ttg_ya, id_program_marketing, id_jenis_em, id_jenis_em_ppm, id_reas_fakultatif, prosen_ekstra_premi_ttg, prosen_ekstra_premi_ppm, 
		id_cara_bayar_next, id_unit_pelayanan, id_unit_pelayanan_produksi, id_unit_pelayanan_korespondensi)
		select no_spaj, no_spajk, id_plan_dasar, id_periode_bayar, id_cara_bayar_nasabah, keterangan_cara_bayar_nasabah, id_valuta, 
		tgl_mulai, @p_tgl_selesai, tgl_produksi, id_jenis_investasi, masa_kontrak_dasar, masa_pembayaran_premi_dasar, uang_pertanggungan_dasar, premi_dasar, premi_rider, 
		premi_berkala, top_up_berkala, total_premi, id_agen, @p_id_cabang, @p_id_cabang, multi_polis_ya, multi_payor_benefit_rider_ya, materai_polis_ya, materai_kuitansi_ya, 
		administrasi_ya, paket_ya, alternatif2_ya, tgl_alternatif2, setor_ya, catatan, tgl_aplikasi, @p_biaya_administrasi, @p_biaya_materai, 'G', create_date, create_by, change_date, 
		change_by, id_perusahaan, medical_ppm_ya, medical_ttg_ya, id_program_marketing, id_jenis_em, id_jenis_em_ppm, id_reas_fakultatif, prosen_ekstra_premi_ttg, prosen_ekstra_premi_ppm, 
		id_cara_bayar_next, @p_id_unit_pelayanan, @p_id_unit_pelayanan, @p_id_unit_pelayanan
		from web_spaj
		where no_spaj = @pi_no_spaj
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ gagal.'
			RETURN 1
		END
		
		insert into spaj_plan
		(no_spaj, id_plan_dasar, id_plan, no_urut, deskripsi, kelas, risiko, masa_pembayaran_premi, masa_kontrak, tgl_mulai, uang_pertanggungan, premi, ekstra_premi, prosen_ekstra_premi, total_premi, premi_lama, status, create_date, create_by, change_date, change_by, tgl_selesai, tgl_mulai_berlaku, tgl_selesai_berlaku, diskon_premi)
		select no_spaj,id_plan_dasar, id_plan, no_urut, deskripsi, kelas, risiko, masa_pembayaran_premi, masa_kontrak, tgl_mulai, uang_pertanggungan, premi, ekstra_premi, prosen_ekstra_premi, total_premi, premi_lama, status, create_date, create_by, change_date, change_by, tgl_selesai, tgl_mulai_berlaku, tgl_selesai_berlaku, diskon_premi
		from web_spaj_plan
		where no_spaj = @pi_no_spaj
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ plan gagal.'
			RETURN 1
		END

		
		insert into spaj_pemegang
		(no_spaj, id_pemegang, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, id_jenis_alamat_korespondensi, hp, email, status, create_date, create_by, change_date, change_by, hp2)
		select no_spaj, id_pemegang, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, id_jenis_alamat_korespondensi, hp, email, status, create_date, create_by, change_date, change_by, hp2
		from web_spaj_pemegang
		where no_spaj = @pi_no_spaj
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ pemegang gagal.'
			RETURN 1
		END
		
		insert into spaj_pemegang_alamat
		(no_spaj, id_jenis_alamat, alamat, no_rt, no_rw, kelurahan, kecamatan, kota, id_kd_pos, id_provinsi, id_negara, telp, fax, status, create_date, create_by, change_date, change_by)
		select no_spaj,id_jenis_alamat, alamat, no_rt, no_rw, kelurahan, kecamatan, kota, id_kd_pos, id_provinsi, id_negara, telp, fax, status, create_date, create_by, change_date, change_by
		from web_spaj_pemegang_alamat
		where no_spaj = @pi_no_spaj
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ pemegang alamat gagal.'
			RETURN 1
		END
		
		insert into spaj_tertanggung
		(no_spaj, id_tertanggung, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, id_jenis_alamat_korespondensi, hp, email, id_hubungan, status, create_date, create_by, change_date, change_by, hp2)
		select no_spaj,id_tertanggung, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, id_jenis_alamat_korespondensi, hp, email, id_hubungan, status, create_date, create_by, change_date, change_by, hp2
		from web_spaj_tertanggung
		where no_spaj = @pi_no_spaj
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ tertanggung gagal.'
			RETURN 1
		END
		
		insert into spaj_tertanggung_alamat
		(no_spaj, id_jenis_alamat, alamat, no_rt, no_rw, kelurahan, kecamatan, kota, id_kd_pos, id_provinsi, id_negara, telp, fax, status, create_date, create_by, change_date, change_by)
		select no_spaj,id_jenis_alamat, alamat, no_rt, no_rw, kelurahan, kecamatan, kota, id_kd_pos, id_provinsi, id_negara, telp, fax, status, create_date, create_by, change_date, change_by
		from web_spaj_tertanggung_alamat
		where no_spaj = @pi_no_spaj
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ tertanggung alamat gagal.'
			RETURN 1
		END
		
		insert into spaj_termaslahat
		(no_spaj, no_urut, nama_ahli_waris, gelar_sebelum_nama, gelar_setelah_nama, tgl_lahir, id_hubungan, status, create_date, create_by, change_date, change_by)
		select no_spaj,no_urut, nama_ahli_waris, gelar_sebelum_nama, gelar_setelah_nama, tgl_lahir, id_hubungan, status, create_date, create_by, change_date, change_by
		from web_spaj_termaslahat
		where no_spaj = @pi_no_spaj
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ termaslahat gagal.'
			RETURN 1
		END
		
		insert into spaj_riwayat
		(no_spaj, no_urut, id_hubungan, status_kesehatan, umur, keterangan, status, create_date, create_by, change_date, change_by)
		select no_spaj,no_urut, id_hubungan, status_kesehatan, umur, keterangan, status, create_date, create_by, change_date, change_by
		from web_spaj_riwayat
		where no_spaj = @pi_no_spaj
		IF (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ riwayat gagal.'
			RETURN 1
		END
		
		insert into spaj_asuransi
		(no_spaj, no_urut, nama_polis, no_polis, id_perusahaan_asuransi, id_valuta, nilai_uang_pertanggungan, status, create_date, create_by, change_date, change_by)
		select no_spaj, no_urut, nama_polis, no_polis, id_perusahaan_asuransi, id_valuta, nilai_uang_pertanggungan, status, create_date, create_by, change_date, change_by
		from web_spaj_asuransi
		where no_spaj = @pi_no_spaj
		IF (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ asuransi gagal.'
			RETURN 1
		END
		
		insert into spaj_kesehatan
		(no_spaj, data_1a, data_1b, data_1c, data_2a_ya, data_2b, data_3, data_4a, data_4b_ya, data_5a_ya, data_5b_ya, data_6_ya, data_7_ya, data_8_ya, data_9a_ya, data_9b_ya, data_9c_ya, data_9d_ya, data_9e_ya, data_9f_ya, data_10_ya, data_11_ya, data_12a_ya, data_12b_ya, data_13_ya, data_14_ya, data_15a1, data_15a2_ya, data_15b_ya, data_15c_ya, data_15d1, data_15d2, status, create_date, create_by, change_date, change_by, data_11)
		select no_spaj,data_1a, data_1b, data_1c, data_2a_ya, data_2b, data_3, data_4a, data_4b_ya, data_5a_ya, data_5b_ya, data_6_ya, data_7_ya, data_8_ya, data_9a_ya, data_9b_ya, data_9c_ya, data_9d_ya, data_9e_ya, data_9f_ya, data_10_ya, data_11_ya, data_12a_ya, data_12b_ya, data_13_ya, data_14_ya, data_15a1, data_15a2_ya, data_15b_ya, data_15c_ya, data_15d1, data_15d2, status,create_date, create_by, change_date, change_by, data_11
		from web_spaj_kesehatan
		where no_spaj = @pi_no_spaj
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ kesehatan gagal.'
			RETURN 1
		END
		
		insert into spaj_statistik
		(no_spaj, id_status_tinggal, status_tinggal, id_penghasilan, id_tujuan_asuransi, tujuan_asuransi, status, create_date, create_by, change_date, change_by)
		select no_spaj,id_status_tinggal, status_tinggal, id_penghasilan, id_tujuan_asuransi, tujuan_asuransi, status, create_date, create_by, change_date, change_by
		from web_spaj_statistik
		where no_spaj = @pi_no_spaj
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ statistik gagal.'
			RETURN 1
		END
		
		insert into spaj_pernyataan
		(no_spaj, hubungan, bln_kenal, thn_kenal, kesehatan_ya, perjanjian_ya, pembatalan_ya, no_polis_referensi, status, create_date, create_by, change_date, change_by, id_hubungan_agen)
		select no_spaj,hubungan, bln_kenal, thn_kenal, kesehatan_ya, perjanjian_ya, pembatalan_ya, no_polis_referensi, status, create_date, create_by, change_date, change_by, id_hubungan_agen
		from web_spaj_pernyataan
		where no_spaj = @pi_no_spaj
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ pernyataan gagal.'
			RETURN 1
		END
	end
	
	--copy spaj Register
	
	if(not(exists(select 1 from spaj_register
	where no_spaj = @pi_no_spaj)))
	begin
		insert into spaj_register
		(no_spaj, no_spajk, id_pemegang, nama_pemegang, gelar_sebelum_nama_pemegang, gelar_setelah_nama_pemegang, id_jenis_kelamin_pemegang, tgl_lahir_pemegang, 
		id_tertanggung, nama_tertanggung, gelar_sebelum_nama_tertanggung, gelar_setelah_nama_tertanggung, id_jenis_kelamin_tertanggung, tgl_lahir_tertanggung, id_hubungan, 
		id_plan_dasar, tgl_mulai, tgl_produksi, id_jenis_investasi, id_periode_bayar, id_cara_bayar_nasabah, keterangan_cara_bayar_nasabah, id_valuta, masa_kontrak_dasar, 
		masa_pembayaran_premi_dasar, uang_pertanggungan_dasar, premi_dasar, premi_rider, premi_berkala, top_up_berkala, total_premi, id_agen, id_cabang, id_cabang_korespondensi, 
		multi_polis_ya, multi_payor_benefit_rider_ya, id_status_spaj_register, catatan, administrasi_ya, materai_kuitansi_ya, biaya_administrasi, biaya_materai, status, create_date, 
		create_by, change_date, change_by, approve_date, approve_by, approve_note, id_perusahaan, medical_ttg_ya, medical_ppm_ya, id_program_marketing, id_jenis_em, id_jenis_em_ppm, 
		id_reas_fakultatif, prosen_ekstra_premi_ttg, prosen_ekstra_premi_ppm, id_dokumen_register, fakultatif_ya, tgl_validasi, id_unit_pelayanan, id_unit_pelayanan_produksi, 
		id_unit_pelayanan_korespondensi)
		select s.no_spaj, s.no_spajk, ppm.id_pemegang, ppm.nama_lengkap, ppm.gelar_sebelum_nama, ppm.gelar_setelah_nama, ppm.id_jenis_kelamin, ppm.tgl_lahir, ttg.id_tertanggung, 
		ttg.nama_lengkap, ttg.gelar_sebelum_nama, ttg.gelar_setelah_nama, ttg.id_jenis_kelamin, ttg.tgl_lahir, ttg.id_hubungan, s.id_plan_dasar, s.tgl_mulai, s.tgl_produksi, 
		s.id_jenis_investasi, s.id_periode_bayar, s.id_cara_bayar_nasabah, s.keterangan_cara_bayar_nasabah, s.id_valuta, s.masa_kontrak_dasar, s.masa_pembayaran_premi_dasar, s.uang_pertanggungan_dasar, s.
		premi_dasar, s.premi_rider, s.premi_berkala, s.top_up_berkala, s.total_premi, s.id_agen, s.id_cabang, s.id_cabang_korespondensi, s.multi_polis_ya, s.multi_payor_benefit_rider_ya, '01', s.catatan, s.
		administrasi_ya, s.materai_kuitansi_ya, s.biaya_administrasi, s.biaya_materai, 'G', s.create_date, 
		s.create_by, s.change_date, s.change_by, s.approve_date, s.approve_by, s.approve_note, 
		s.id_perusahaan, s.medical_ttg_ya, s.medical_ppm_ya, s.id_program_marketing, s.id_jenis_em, s.id_jenis_em_ppm, s.id_reas_fakultatif, s.prosen_ekstra_premi_ttg, s.prosen_ekstra_premi_ppm, s.id_dokumen_register, s.fakultatif_ya, s.tgl_validasi, s.id_unit_pelayanan, s.id_unit_pelayanan_produksi, s.id_unit_pelayanan_korespondensi
		from web_spaj s, web_spaj_pemegang ppm, web_spaj_tertanggung ttg
		where s.no_spaj = @pi_no_spaj
		and s.no_spaj = ppm.no_spaj
		and s.no_spaj = ttg.no_spaj
		
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ Register gagal.'
			RETURN 1
		END

		insert into spaj_register_plan
		(no_spaj, id_plan_dasar, id_plan, aktif_ya, no_urut, deskripsi, kelas, risiko, masa_pembayaran_premi, masa_kontrak, tgl_mulai, uang_pertanggungan, premi, ekstra_premi, prosen_ekstra_premi, total_premi, premi_lama, status, create_date, create_by, change_date, change_by, tgl_selesai, tgl_mulai_berlaku, tgl_selesai_berlaku, diskon_premi)
		select no_spaj, id_plan_dasar, id_plan, 1, no_urut, deskripsi, kelas, risiko, masa_pembayaran_premi, masa_kontrak, tgl_mulai, uang_pertanggungan, premi, ekstra_premi, prosen_ekstra_premi, total_premi, premi_lama, status, create_date, create_by, change_date, change_by, tgl_selesai, tgl_mulai_berlaku, tgl_selesai_berlaku, diskon_premi
		from web_spaj_plan
		where no_spaj = @pi_no_spaj
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ Register plan gagal.'
			RETURN 1
		END
		
		insert into spaj_register_plan
		(no_spaj, id_plan_dasar, id_plan, aktif_ya, no_urut, deskripsi, kelas, risiko, masa_pembayaran_premi, masa_kontrak, tgl_mulai, uang_pertanggungan, premi, ekstra_premi, prosen_ekstra_premi, total_premi, premi_lama, status, create_date, create_by, tgl_selesai, tgl_mulai_berlaku, tgl_selesai_berlaku, diskon_premi)
		select @pi_no_spaj, id_plan_dasar, id_plan, 0, no_urut, deskripsi, kelas, risiko, 0, 0, @p_tgl_mulai, 0, 0, 0, 0, 0, 0, 'I', @p_create_date, @p_create_by, @p_tgl_mulai, @p_tgl_mulai, @p_tgl_mulai, 0
		from plan_detail
		where id_plan = @p_id_plan_dasar
		IF (@@RowCount = 0) OR (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ Register plan gagal.'
			RETURN 1
		END

		insert into spaj_register_jenis_em_ppm
		(no_spaj, id_jenis_em, status, create_date, create_by, change_date, change_by)
		select no_spaj, id_jenis_em, status, create_date, create_by, change_date, change_by
		from web_spaj_register_jenis_em_ppm
		where no_spaj = @pi_no_spaj
		IF (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ Register jenis em ppm gagal.'
			RETURN 1
		END
		
		insert into spaj_register_jenis_em_ttg
		(no_spaj, id_jenis_em, status, create_date, create_by, change_date, change_by)
		select no_spaj, id_jenis_em, status, create_date, create_by, change_date, change_by
		from web_spaj_register_jenis_em_ttg
		where no_spaj = @pi_no_spaj
		IF (@@Error <> 0)
		BEGIN
			-- Gagal
			SET @po_message = 'Insert SPAJ Register jenis em ttg gagal.'
			RETURN 1
		END	
	end
end