-- Function: penetapan_massal_baru(character, character, character, character, character, character, character, text, numeric, numeric, numeric, numeric, character, character, character, character, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, character, character, date, date, date, date, date, date, date, date, date, date, character, date, numeric)

-- DROP FUNCTION penetapan_massal_baru(character, character, character, character, character, character, character, text, numeric, numeric, numeric, numeric, character, character, character, character, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, character, character, date, date, date, date, date, date, date, date, date, date, character, date, numeric);

CREATE OR REPLACE FUNCTION penetapan_massal_baru(
    vls_prop character,
    vls_dat character,
    vls_kec character,
    vls_kel character,
    vls_blk character,
    vls_urut character,
    vls_jns character,
    vls_persil text,
    vli_njop_bumi numeric,
    vli_njop_bng numeric,
    vli_luas_bumi numeric,
    vli_luas_bng numeric,
    vls_wp_id character,
    vls_kd_kanwil character,
    vls_kd_kantor character,
    vls_kd_tp character,
    vli_njoptkp numeric,
    vli_min_b1 numeric,
    vli_max_b1 numeric,
    vli_min_b2 numeric,
    vli_max_b2 numeric,
    vli_min_b3 numeric,
    vli_max_b3 numeric,
    vli_min_b4 numeric,
    vli_max_b4 numeric,
    vli_min_b5 numeric,
    vli_max_b5 numeric,
    vls_jpt character,
    v_thn character,
    v_tjtt_bk1 date,
    v_tjtt_bk2 date,
    v_tjtt_bk3 date,
    v_tjtt_bk4 date,
    v_tjtt_bk5 date,
    v_trb_bk1 date,
    v_trb_bk2 date,
    v_trb_bk3 date,
    v_trb_bk4 date,
    v_trb_bk5 date,
    v_nip character,
    v_nip_tgl date,
    pbb_min numeric)
  RETURNS void AS
$BODY$
DECLARE
	vls_tnh_ks char(1);
	v_komp numeric(15);
	v_komp_stm numeric(15);
	vli_pct_pst numeric(5,2);
	vli_pct_prmn numeric(5,2);
	vli_pct_jpb numeric(5,2);
	vli_njop_1 numeric(15);
	vli_njop_bumi_beban numeric(15);
	vli_njop_bng_beban numeric(15);
	vli_luas_bumi_beban numeric(12);
	vli_luas_bng_beban numeric(12);
	vli_njop_bumi_m2 numeric(15,3);
	vli_njop_bng_m2 numeric(15,3);
	vli_njop_bumi_m2_beban numeric(15,3);
	vli_njop_bng_m2_beban numeric(15,3);
	vls_kls_bumi char(3);
	vls_thn_bumi char(4);
	vli_nl_m2_bumi numeric(8,2);
	vls_kls_bumi_beban char(3);
	vls_thn_bumi_beban char(4);
	vli_nl_m2_bumi_beban numeric(8,2);
	vls_kls_bng char(3);
	vls_thn_bng char(4);
	vli_nl_m2_bng numeric(8,2);
	vls_kls_bng_beban char(3);
	vls_thn_bng_beban char(4);
	vli_nl_m2_bng_beban numeric(8,2);
	vli_njop numeric(15);
	vls_nm_wp varchar(30);
	vls_jln_wp varchar(30);
	vls_blk_kav_wp varchar(15);
	vls_rw_wp char(2);
	vls_rt_wp char(3);
	vls_kel_wp varchar(30);
	vls_kota_wp varchar(30);
	vls_kd_pos varchar(5);
	vls_npwp varchar(15);
	vli_flag_njoptkp numeric(1);
	vli_njoptkp_sppt numeric(8);
	vli_pbb_tht numeric(15);
	vli_pbb_tht1 numeric(15);
	vli_pgrn_jpb numeric(15);
	vli_pgrn_prmn numeric(15);
	vli_pgrn_pst numeric(15);
	vli_prgn_all numeric(15);
	vli_kompensasi numeric(15);
	vli_pbb_hrsbyr numeric(15);
	vli_flag_bersama numeric(1);
	v_tjtt date;
	v_trb date;
	vls_njkp varchar(2);
	v_tmp numeric(2);
	v_temp char(1);
	v_pbbnol char(1);
	vli_tarif numeric(3,3);
	vli_jns_bumi char(1);
	vli_njkp numeric(3);
	vli_pbb_njkp numeric(15);

  -- cari kompensasi
  c_komp CURSOR is
    Select nilai_yang_dikompensasi
	from pbb.penerima_kompensasi
	Where kd_propinsi_kompensasi = vls_prop and  kd_dati2_kompensasi 		 = vls_dat and
	  kd_kecamatan_kompensasi 	 = vls_kec and  kd_kelurahan_kompensasi 	 = vls_kel and
	  kd_blok_kompensasi 		 = vls_blk and  no_urut_kompensasi 		 = vls_urut and
	  kd_jns_op_kompensasi 		 = vls_jns and  thn_pajak_kompensasi 		 = v_thn;

    c_komp2 CURSOR is
    Select nilai_yang_dikompensasi
	from pbb.penerima_kompensasi
	Where kd_propinsi_kompensasi = vls_prop and  kd_dati2_kompensasi 		 = vls_dat and
	  kd_kecamatan_kompensasi 	 = vls_kec and  kd_kelurahan_kompensasi 	 = vls_kel and
	  kd_blok_kompensasi 		 = vls_blk and  no_urut_kompensasi 		 = vls_urut and
	  kd_jns_op_kompensasi 		 = vls_jns and  thn_pajak_kompensasi 		 = v_thn-1;

  cek_bersama CURSOR IS
	  SELECT luas_bumi_beban, luas_bng_beban, njop_bumi_beban, njop_bng_beban
	  FROM pbb.DAT_OP_ANGGOTA
	  WHERE kd_propinsi = vls_prop AND  kd_dati2 = vls_dat AND
		kd_kecamatan = vls_kec AND  kd_kelurahan = vls_kel AND
		kd_blok = vls_blk AND	no_urut = vls_urut AND
		kd_jns_op = vls_jns;

  cek_sppt CURSOR is
	  select siklus_sppt from pbb.sppt
	  Where kd_propinsi = vls_prop and   kd_dati2 		= vls_dat and
		kd_kecamatan 	= vls_kec and	kd_kelurahan 	= vls_kel and
		kd_blok 		= vls_blk and	no_urut 		= vls_urut and
		kd_jns_op 		= vls_jns and	thn_pajak_sppt 	= v_thn;

  cek_sppt_bersama CURSOR is
	  select 'x' from pbb.sppt_op_bersama
	  Where kd_propinsi = vls_prop and   kd_dati2 		= vls_dat and
		kd_kecamatan 	= vls_kec and	kd_kelurahan 	= vls_kel and
		kd_blok 		= vls_blk and	no_urut 		= vls_urut and
		kd_jns_op 		= vls_jns and	thn_pajak_sppt 	= v_thn;


BEGIN

-- cek status OP bersama
  BEGIN
	OPEN cek_bersama;
	FETCH cek_bersama INTO vli_luas_bumi_beban, vli_luas_bng_beban, vli_njop_bumi_beban, vli_njop_bng_beban;
	IF NOT FOUND THEN
	  vli_luas_bumi_beban := 0;
	  vli_luas_bng_beban := 0;
	  vli_njop_bumi_beban := 0;
	  vli_njop_bng_beban := 0;
	  vli_flag_bersama := 0;
	ELSE vli_flag_bersama := 1;
	END IF;
	CLOSE cek_bersama;
	EXCEPTION WHEN OTHERS THEN
		vli_luas_bumi_beban := 0;
		vli_luas_bng_beban := 0;
		vli_njop_bumi_beban := 0;
		vli_njop_bng_beban := 0;
  END;

  -- cari njoptkp di dat_subjek_pajak_njoptkp
  begin
    select count(*) into STRICT  vli_flag_njoptkp from pbb.dat_subjek_pajak_njoptkp
	Where subjek_pajak_id = vls_wp_id and
		kd_propinsi  = vls_prop and kd_dati2 	 = vls_dat and
		kd_kecamatan = vls_kec and  kd_kelurahan = vls_kel and
		kd_blok 	 = vls_blk and	no_urut 	 = vls_urut and
		kd_jns_op 	 = vls_jns;
   exception when no_data_found then vli_flag_njoptkp := 0;
  end;

  if coalesce(vli_flag_njoptkp,0) = 0 then
    vli_njoptkp_sppt := 0;
  else
    vli_njoptkp_sppt := vli_njoptkp;
  end if;

  -- total NJOP
	vli_njop_1 := (vli_njop_bumi + vli_njop_bng + vli_njop_bumi_beban + vli_njop_bng_beban);
	vli_njop   := (vli_njop_bumi + vli_njop_bng + vli_njop_bumi_beban + vli_njop_bng_beban) - vli_njoptkp_sppt;

	if vli_njop < 0 then
	  vli_njop := 0;
	end if;

  -- cek jns tanah
    begin 
      select jns_bumi into vli_jns_bumi
      from pbb.dat_op_bumi
      where kd_propinsi = vls_prop
	    and kd_dati2 = vls_dat
	    and kd_kecamatan = vls_kec
	    and kd_kelurahan = vls_kel
	    and kd_blok = vls_blk
	    and no_urut = vls_urut
	    and kd_jns_op = vls_jns;
      exception when others then vli_jns_bumi:= 3;
    end;

    -- cari tarif
    if vli_jns_bumi = '5' then
        begin
	select nilai_tarif into vli_tarif 
	from pbb.tarif_baru
	where kd_propinsi = vls_prop
	and kd_dati2 = vls_dat and
	jns_tarif = '2' and
	v_thn between thn_awal and thn_akhir and
	vli_njop between njop_min and njop_max;
	exception when others then vli_tarif:= 100;
        end;
    else
	begin
	select nilai_tarif into vli_tarif 
	from pbb.tarif_baru
	where kd_propinsi = vls_prop
	and kd_dati2 = vls_dat and
	jns_tarif = '1' and
	v_thn between thn_awal and thn_akhir and
	vli_njop between njop_min and njop_max;
	exception when others then vli_tarif:= 100;
        end;
    end if;

    begin
	select nilai_njkp into vli_njkp
	from pbb.njkp
	where kd_propinsi = vls_prop
	and kd_dati2 = vls_dat and
	v_thn between thn_awal and thn_akhir and
	vli_njop between njop_min and njop_max;
	exception when others then vli_tarif:= 100;
    end;

  --perhitungan njkp
    vli_pbb_njkp := round(vli_njop * (vli_njkp/100));
    
  -- PBB terhutang
    vli_pbb_tht := round(vli_pbb_njkp * (vli_tarif/100));
    
  -- cari pengurangan JPB
	begin
	  begin
	    Select pct_pengurangan_jpb into vli_pct_jpb from pbb.pengurangan_pengenaan_jpb
		Where kd_propinsi_pemohon = vls_prop and
		  kd_dati2_pemohon 		  = vls_dat and
		  kd_kecamatan_pemohon 	  = vls_kec and
		  kd_kelurahan_pemohon 	  = vls_kel and
		  kd_blok_pemohon 		  = vls_blk and
		  no_urut_pemohon 		  = vls_urut and
		  kd_jns_op_pemohon 	  = vls_jns and
		  thn_pengenaan_jpb 	  = v_thn;
	  exception when others then vli_pct_jpb := 0;
	  end;
	  if coalesce(vli_pct_jpb,0) > 0 then
	    vli_pgrn_jpb := round(vli_pbb_tht * (vli_pct_jpb/100));
	  else vli_pgrn_jpb := 0;
	  end if;
	  
	exception when others then vli_pgrn_jpb := 0;
	end;

  vli_pbb_tht1 := vli_pbb_tht - vli_pgrn_jpb;
 
  -- cari pengurangan permanen
    begin
	  begin
	    Select pct_pengurangan_permanen into vli_pct_prmn from pbb.pengurangan_permanen
		Where kd_propinsi_pemohon = vls_prop and
		  kd_dati2_pemohon 		  = vls_dat and
		  kd_kecamatan_pemohon 	  = vls_kec and
		  kd_kelurahan_pemohon 	  = vls_kel and
		  kd_blok_pemohon 		  = vls_blk and
		  no_urut_pemohon 		  = vls_urut and
		  kd_jns_op_pemohon 	  = vls_jns and
		  thn_peng_permanen_awal  <= v_thn and
		  thn_peng_permanen_akhir >= v_thn;
	  exception when others then vli_pct_prmn := 0;
	  end;
	  if coalesce(vli_pct_prmn,0) > 0 then
	    vli_pgrn_prmn := round(vli_pbb_tht1 * (vli_pct_prmn/100));
	  else vli_pgrn_prmn := 0;
	  end if;
	 
	exception when others then vli_pgrn_prmn := 0;
	end;

  -- cari pengurangan pst
	begin
	  begin
	    Select pct_pengurangan_pst into vli_pct_pst from pbb.pengurangan_pst
		Where kd_propinsi_pemohon = vls_prop and
		  kd_dati2_pemohon 		  = vls_dat and
		  kd_kecamatan_pemohon 	  = vls_kec and
		  kd_kelurahan_pemohon 	  = vls_kel and
		  kd_blok_pemohon 		  = vls_blk and
		  no_urut_pemohon 		  = vls_urut and
		  kd_jns_op_pemohon 	  = vls_jns and
		  thn_peng_pst 			  = v_thn;
	  exception when others then vli_pct_pst := 0;
	  end;
	  if coalesce(vli_pct_pst,0) > 0 then
	    vli_pgrn_pst := round(vli_pbb_tht1 * (vli_pct_pst/100));
	  else vli_pgrn_pst := 0;
	  end if;
	  RAISE NOTICE 'vli_pgrn_pst = %',vli_pgrn_pst;
	exception when others then vli_pgrn_pst := 0;
	end;

  -- cari kompensasi
	vli_kompensasi := 0;
	v_komp := 0;
	begin
	  Open c_komp;
      Loop
      Fetch c_komp into v_komp;
	  IF NOT FOUND THEN EXIT; END IF; -- apply on c_komp
		vli_kompensasi := coalesce(vli_kompensasi,0) + v_komp;
	  End loop;
	  Close c_komp;
	exception when others then null;
	end;
	
  -- cari kelas tanah
    IF (coalesce(vli_njop_bumi,0) <> 0) AND (coalesce(vli_luas_bumi,0) <> 0) THEN
	  BEGIN
	    vli_njop_bumi_m2 := round((vli_njop_bumi/vli_luas_bumi)/1000);
		SELECT kd_kls_tanah, thn_awal_kls_tanah, nilai_per_m2_tanah
		INTO vls_kls_bumi, vls_thn_bumi, vli_nl_m2_bumi FROM pbb.KELAS_TANAH
		WHERE thn_awal_kls_tanah <= TO_CHAR(LOCALTIMESTAMP,'yyyy') AND
		  thn_akhir_kls_tanah >= TO_CHAR(LOCALTIMESTAMP,'yyyy') AND
		  nilai_min_tanah < vli_njop_bumi_m2 AND
		  nilai_max_tanah >= vli_njop_bumi_m2 AND
		  kd_kls_tanah NOT IN ('XXX', '00');

	  IF vls_kls_bumi is null THEN  vls_kls_bumi   := 'XXX'; END IF;
	  IF vls_thn_bumi is null THEN  vls_thn_bumi     := '1986'; END IF;
	  IF vli_nl_m2_bumi is null THEN  vli_nl_m2_bumi := vli_njop_bumi_m2; END IF;
	  END;
	ELSE
	  vls_kls_bumi := 'XXX';
	  vls_thn_bumi := '1986';
	  vli_nl_m2_bumi := 0;
	END IF;

	-- cari kelas bangunan
    IF (coalesce(vli_njop_bng,0) <> 0) AND (coalesce(vli_luas_bng,0) <> 0) THEN
	  BEGIN
	    vli_njop_bng_m2 := round((vli_njop_bng/vli_luas_bng)/1000);
		SELECT kd_kls_bng, thn_awal_kls_bng, nilai_per_m2_bng
		INTO vls_kls_bng, vls_thn_bng, vli_nl_m2_bng FROM pbb.KELAS_BANGUNAN
		WHERE thn_awal_kls_bng <= TO_CHAR(LOCALTIMESTAMP,'yyyy') AND  thn_akhir_kls_bng >= TO_CHAR(LOCALTIMESTAMP,'yyyy') AND
		  nilai_min_bng < vli_njop_bng_m2 AND  nilai_max_bng >= vli_njop_bng_m2 AND
		  kd_kls_bng NOT IN ('XXX', '00');
	  IF vls_kls_bng is null THEN  vls_kls_bng   := 'XXX'; END IF;
	  IF vls_thn_bng is null THEN  vls_thn_bng     := '1986'; END IF;
	  IF vli_nl_m2_bng is null THEN  vli_nl_m2_bng := vli_njop_bng_m2; END IF;
	  END;
    ELSE
	  vls_kls_bng := 'XXX';
	  vls_thn_bng := '1986';
	  vli_nl_m2_bng := 0;
    END IF;

	-- cari kelas tanah  beban
	IF vli_flag_bersama = 1 THEN
	BEGIN
		-- cari kelas tanah
	    IF (coalesce(vli_njop_bumi_beban,0) <> 0) AND (coalesce(vli_luas_bumi_beban,0) <> 0) THEN
		  BEGIN
		    vli_njop_bumi_m2_beban := round((vli_njop_bumi_beban/vli_luas_bumi_beban)/1000);
			SELECT kd_kls_tanah, thn_awal_kls_tanah, nilai_per_m2_tanah
			INTO vls_kls_bumi_beban, vls_thn_bumi_beban, vli_nl_m2_bumi_beban FROM pbb.KELAS_TANAH
			WHERE thn_awal_kls_tanah <= TO_CHAR(LOCALTIMESTAMP,'yyyy') AND
			  thn_akhir_kls_tanah >= TO_CHAR(LOCALTIMESTAMP,'yyyy') AND
			  nilai_min_tanah < vli_njop_bumi_m2_beban AND
			  nilai_max_tanah >= vli_njop_bumi_m2_beban AND
			  kd_kls_tanah NOT IN ('XXX', '00');
		  IF vls_kls_bumi_beban is null THEN  vls_kls_bumi_beban   := 'XXX'; END IF;
		  IF vls_thn_bumi_beban is null THEN  vls_thn_bumi_beban     := '1986'; END IF;
		  IF vli_nl_m2_bumi_beban is null THEN  vli_nl_m2_bumi_beban := vli_njop_bumi_m2_beban; END IF;
		  END;
		ELSE
		  vls_kls_bumi_beban := 'XXX';
		  vls_thn_bumi_beban := '1986';
		  vli_nl_m2_bumi_beban := 0;
		END IF;

	-- cari kelas bangunan
	    IF (coalesce(vli_njop_bng_beban,0) <> 0) AND (coalesce(vli_luas_bng_beban,0) <> 0) THEN
		  BEGIN
		    vli_njop_bng_m2_beban := round((vli_njop_bng_beban/vli_luas_bng_beban)/1000);
			SELECT kd_kls_bng, thn_awal_kls_bng, nilai_per_m2_bng
			INTO vls_kls_bng_beban, vls_thn_bng_beban, vli_nl_m2_bng_beban FROM pbb.KELAS_BANGUNAN
			WHERE thn_awal_kls_bng <= TO_CHAR(LOCALTIMESTAMP,'yyyy') AND
			  thn_akhir_kls_bng >= TO_CHAR(LOCALTIMESTAMP,'yyyy') AND
			  nilai_min_bng < vli_njop_bng_m2_beban AND
			  nilai_max_bng >= vli_njop_bng_m2_beban AND
			  kd_kls_bng NOT IN ('XXX', '00');
		  IF vls_kls_bng_beban is null THEN  vls_kls_bng_beban   := 'XXX'; END IF;
		  IF vls_thn_bng_beban is null THEN  vls_thn_bng_beban     := '1986'; END IF;
		  IF vli_nl_m2_bng_beban is null THEN  vli_nl_m2_bng_beban := vli_njop_bng_m2_beban; END IF;
		  END;
		ELSE
		  vls_kls_bng_beban := 'XXX';
		  vls_thn_bng_beban := '1986';
		  vli_nl_m2_bng_beban := 0;

		END IF;
	EXCEPTION WHEN OTHERS THEN NULL;
	END;
	END IF;


  -- lookup subjek_pajak
	begin
	  Select nm_wp, jalan_wp, blok_kav_no_wp, rw_wp, rt_wp, kelurahan_wp, kota_wp,
		kd_pos_wp, npwp
	  into vls_nm_wp, vls_jln_wp, vls_blk_kav_wp, vls_rw_wp, vls_rt_wp, vls_kel_wp,
		vls_kota_wp, vls_kd_pos, vls_npwp
	  from pbb.dat_subjek_pajak
	  Where subjek_pajak_id = vls_wp_id;
	exception when others then null;
	end;

  -- pengurangan total
    vli_prgn_all := coalesce(vli_pgrn_jpb,0) + coalesce(vli_pgrn_prmn,0) + coalesce(vli_pgrn_pst,0) + coalesce(vli_kompensasi,0);

  --  pbb yh harus dibayar
    vli_pbb_hrsbyr := vli_pbb_tht - vli_prgn_all;
  
  if vli_pbb_tht between vli_min_b1 and vli_max_b1 then
	  v_tjtt := v_tjtt_bk1;
	  v_trb :=  v_trb_bk1;
	elsif vli_pbb_tht between vli_min_b2 and vli_max_b2 then
	  v_tjtt := v_tjtt_bk2;
	  v_trb :=  v_trb_bk2;
	elsif vli_pbb_tht between vli_min_b3 and vli_max_b3 then
	  v_tjtt := v_tjtt_bk3;
	  v_trb :=  v_trb_bk3;
	elsif vli_pbb_tht between vli_min_b4 and vli_max_b4 then
	  v_tjtt := v_tjtt_bk4;
	  v_trb :=  v_trb_bk4;
	elsif vli_pbb_tht between vli_min_b5 and vli_max_b5 then
	  v_tjtt := v_tjtt_bk5;
	  v_trb :=  v_trb_bk5;
	end if;


  if vli_pbb_hrsbyr <= coalesce(pbb_min,0) then
    if pbb_min > 0 then
	   v_pbbnol := '0';
	else
	   v_pbbnol := '1';
	end if;
	vli_pbb_hrsbyr := pbb_min;
  else v_pbbnol := '0';
  end if;


  -- insert or update ke SPPT
  begin
	open cek_sppt;
	fetch cek_sppt into v_tmp;
	IF FOUND THEN
	  
	  Update pbb.sppt set siklus_sppt = v_tmp+1, kd_kanwil = vls_kd_kanwil,
		kd_kantor = vls_kd_kantor, kd_tp = vls_kd_tp, nm_wp_sppt = vls_nm_wp,
		jln_wp_sppt = vls_jln_wp, blok_kav_no_wp_sppt = vls_blk_kav_wp,
		rw_wp_sppt =vls_rw_wp, rt_wp_sppt = vls_rt_wp, kelurahan_wp_sppt = vls_kel_wp,
		kota_wp_sppt = vls_kota_wp, kd_pos_wp_sppt = vls_kd_pos, npwp_sppt = vls_npwp,
		no_persil_sppt = vls_persil, kd_kls_tanah = vls_kls_bumi,
		thn_awal_kls_tanah = vls_thn_bumi, kd_kls_bng = vls_kls_bng,
		thn_awal_kls_bng = vls_thn_bng, tgl_jatuh_tempo_sppt = v_tjtt,
		luas_bumi_sppt = vli_luas_bumi, luas_bng_sppt = vli_luas_bng,
		njop_bumi_sppt = vli_njop_bumi, njop_bng_sppt = vli_njop_bng, njop_sppt = vli_njop_1,
		njoptkp_sppt = vli_njoptkp_sppt, pbb_terhutang_sppt = vli_pbb_tht,
		faktor_pengurang_sppt = vli_prgn_all, pbb_yg_harus_dibayar_sppt = vli_pbb_hrsbyr,
		status_pembayaran_sppt = '0' , status_tagihan_sppt = '0', status_cetak_sppt = '1',
		tgl_terbit_sppt = v_trb, tgl_cetak_sppt = v_nip_tgl, nip_pencetak_sppt = v_nip, njkp = vli_pbb_njkp
	  Where kd_propinsi = vls_prop and
	  		kd_dati2 = vls_dat and
			kd_kecamatan = vls_kec and
			kd_kelurahan = vls_kel and
			kd_blok = vls_blk and
			no_urut = vls_urut and
			kd_jns_op = vls_jns and
			thn_pajak_sppt = v_thn;
	else
	  
	  Insert into pbb.sppt(kd_propinsi, kd_dati2, kd_kecamatan, kd_kelurahan, kd_blok, no_urut, kd_jns_op, thn_pajak_sppt, 
        siklus_sppt, kd_kanwil, kd_kantor, kd_tp, 
        nm_wp_sppt, jln_wp_sppt, blok_kav_no_wp_sppt, rw_wp_sppt, rt_wp_sppt, kelurahan_wp_sppt, 
        kota_wp_sppt, kd_pos_wp_sppt, npwp_sppt, no_persil_sppt, kd_kls_tanah,
		thn_awal_kls_tanah, kd_kls_bng, thn_awal_kls_bng, tgl_jatuh_tempo_sppt, luas_bumi_sppt,
		luas_bng_sppt, njop_bumi_sppt, njop_bng_sppt, njop_sppt, njoptkp_sppt, pbb_terhutang_sppt,
		faktor_pengurang_sppt, pbb_yg_harus_dibayar_sppt, status_pembayaran_sppt, status_tagihan_sppt,
		status_cetak_sppt, tgl_terbit_sppt, tgl_cetak_sppt, nip_pencetak_sppt, njkp)
	  Values (vls_prop, vls_dat, vls_kec, vls_kel, vls_blk, vls_urut, vls_jns, v_thn,
  	  	1, vls_kd_kanwil, vls_kd_kantor, vls_kd_tp,
		vls_nm_wp, vls_jln_wp, vls_blk_kav_wp, vls_rw_wp, vls_rt_wp, vls_kel_wp, 
        vls_kota_wp, vls_kd_pos, vls_npwp, vls_persil, vls_kls_bumi, 
        vls_thn_bumi, vls_kls_bng, vls_thn_bng, v_tjtt, vli_luas_bumi, 
        vli_luas_bng, vli_njop_bumi, vli_njop_bng, vli_njop_1, vli_njoptkp_sppt, vli_pbb_tht, vli_prgn_all,
        vli_pbb_hrsbyr, v_pbbnol, '0', '1', v_trb, v_nip_tgl, v_nip, vli_pbb_njkp);
	end if;
	
    Close cek_sppt;
  end;

  -- insert ke sppt op anggota
  if vli_flag_bersama = 1 then
    begin
	  open cek_sppt_bersama;
	  fetch cek_sppt_bersama into v_temp;
	  if found then
	    update pbb.sppt_op_bersama set kd_propinsi = vls_prop, kd_dati2 = vls_dat, kd_kecamatan = vls_kec,
	      kd_kelurahan = vls_kel, kd_blok = vls_blk, no_urut = vls_urut, kd_jns_op = vls_jns,
		  thn_pajak_sppt =v_thn, kd_kls_tanah = vls_kls_bumi_beban,
		  thn_awal_kls_tanah = vls_thn_bumi_beban, kd_kls_bng = vls_kls_bng_beban,
		  thn_awal_kls_bng = vls_thn_bng_beban, luas_bumi_beban_sppt = vli_luas_bumi_beban,
		  luas_bng_beban_sppt = vli_luas_bng_beban, njop_bumi_beban_sppt = vli_njop_bumi_beban,
		  njop_bng_beban_sppt = vli_njop_bng_beban
		Where kd_propinsi = vls_prop and
			  kd_dati2 = vls_dat and
		  	  kd_kecamatan = vls_kec and
			  kd_kelurahan = vls_kel and
		  	  kd_blok = vls_blk and
			  no_urut = vls_urut and
		  	  kd_jns_op = vls_jns and
			  thn_pajak_sppt = v_thn;
	  else
	    insert into pbb.sppt_op_bersama(kd_propinsi, kd_dati2, kd_kecamatan,
	      kd_kelurahan, kd_blok, no_urut, kd_jns_op, thn_pajak_sppt, kd_kls_tanah,
		  thn_awal_kls_tanah, kd_kls_bng, thn_awal_kls_bng, luas_bumi_beban_sppt,
		  luas_bng_beban_sppt, njop_bumi_beban_sppt, njop_bng_beban_sppt)
		values (vls_prop, vls_dat, vls_kec, vls_kel, vls_blk, vls_urut, vls_jns,
		  v_thn, vls_kls_bumi_beban, vls_thn_bumi_beban, vls_kls_bng_beban,
		  vls_thn_bng_beban, vli_luas_bumi_beban, vli_luas_bng_beban, vli_njop_bumi_beban,
		  vli_njop_bng_beban);
	  end if;
	  close cek_sppt_bersama;
	end;
  end if;

  if v_pbbnol = '1' then 
	Update pbb.sppt set status_pembayaran_sppt = 1
    Where kd_propinsi = vls_prop and
		  kd_dati2 = vls_dat and
	  	  kd_kecamatan = vls_kec and
		  kd_kelurahan = vls_kel and
	  	  kd_blok = vls_blk and
		  no_urut = vls_urut and
	  	  kd_jns_op = vls_jns and
		  thn_pajak_sppt = v_thn;
		  
  end if;
end; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION penetapan_massal_baru(character, character, character, character, character, character, character, text, numeric, numeric, numeric, numeric, character, character, character, character, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, character, character, date, date, date, date, date, date, date, date, date, date, character, date, numeric)
  OWNER TO postgres;