CREATE TABLE ppat (
 kode_ppat character(17) NOT NULL DEFAULT 0,
 nama_ppat character(35) NOT NULL,
 alamat_ppat character(35) NOT NULL,
 telp_ppat character(15) NOT NULL,
 CONSTRAINT pk_ppat PRIMARY KEY (kode_ppat)
) 
========
CREATE TABLE kecamatan (
 kode_kec character(3) NOT NULL DEFAULT 0,
 nama_kec character(25) NOT NULL,
 CONSTRAINT pk_kecamatan PRIMARY KEY (kode_kec)
)
=========
CREATE TABLE jenis_dok (
 kode_jsd character(2) NOT NULL DEFAULT 0,
 jenis_dok character(15) NOT NULL,
 CONSTRAINT pk_jenis_dok PRIMARY KEY (kode_jsd)
) 
=====

 CREATE TABLE via_bayar (
 kode_vib smallint NOT NULL,
 via_bayar character(25) NOT NULL,
 CONSTRAINT pk_via_bayar PRIMARY KEY (kode_vib)
)
========
 CREATE TABLE jenis_prh (
 kode_jns character(2) NOT NULL DEFAULT 0,
 jenis_prh character(25) NOT NULL,
 npoptkp double precision NOT NULL,
 CONSTRAINT pk_jenis_prh PRIMARY KEY (kode_jns)
)
=======
 CREATE TABLE sk (
 kode_sk character(3) NOT NULL DEFAULT 0,
 nama_sk character(15) NOT NULL,
 CONSTRAINT pk_sk PRIMARY KEY (kode_sk)
) 
==========
CREATE TABLE dok_terima (
 no_reg integer NOT NULL DEFAULT 0,
 no_bayar character(11) NOT NULL,
 no_form character(20) NOT NULL,
 kode_jns character(2) NOT NULL,
 tahun character(4) NOT NULL,
 sts_prs character(1) NOT NULL,
 sts_byr character(1) NOT NULL,
 sts_nhl character(1) NOT NULL,
 no_op character(18) NOT NULL,
 kode_jsd character(2) NOT NULL,
 kode_ppat character(17) NOT NULL,
 nik character(16) NOT NULL,
 nama_wp character(50) NOT NULL,
 alamat_wp character(50) NOT NULL,
 kec_wp character(50) NOT NULL,
 kel_wp character(50) NOT NULL,
 rt_wp character(3) NOT NULL,
 rw_wp character(3) NOT NULL,
 kota_wp character(15) NOT NULL,
 kode_poswp character(7) NOT NULL,
 tgl_masuk timestamp without time zone NOT NULL DEFAULT now(),
 tgl_selesai timestamp without time zone NOT NULL DEFAULT now(),
 ket character(255) NOT NULL,
 tgl_val timestamp without time zone NOT NULL DEFAULT now(),
 no_val character(5) NOT NULL,
 noreg_njop character(50) NOT NULL,

 CONSTRAINT pk_dok_terima PRIMARY KEY (no_reg),
 CONSTRAINT fk_dok_terima_objek_pajak FOREIGN KEY (no_op)
	REFERENCES objek_pajak (no_op) MATCH SIMPLE
	ON UPDATE CASCADE ON DELETE NO ACTION,
CONSTRAINT fk_dok_terima_jenis_prh FOREIGN KEY (kode_jns)
	REFERENCES jenis_prh (kode_jns) MATCH SIMPLE
	ON UPDATE CASCADE ON DELETE NO ACTION,
CONSTRAINT fk_dok_terima_jenis_dok FOREIGN KEY (kode_jsd)
	REFERENCES jenis_dok (kode_jsd) MATCH SIMPLE
	ON UPDATE CASCADE ON DELETE NO ACTION,
CONSTRAINT fk_dok_terima_ppat FOREIGN KEY (kode_ppat)
	REFERENCES ppat (kode_ppat) MATCH SIMPLE
	ON UPDATE CASCADE ON DELETE NO ACTION
)WITH (
 OIDS=FALSE
);
ALTER TABLE dok_terima
 OWNER TO faundry; 
 ===========
CREATE TABLE kelurahan (
 kode_kel character(7) NOT NULL DEFAULT 0,
 kode_kec character(3) NOT NULL,
 nama_kel character(25) NOT NULL,
 CONSTRAINT pk_kelurahan PRIMARY KEY (kode_kel),
 CONSTRAINT fk_kelurahan_kecamatan FOREIGN KEY (kode_kec)
	REFERENCES kecamatan (kode_kec) MATCH SIMPLE
	ON UPDATE CASCADE ON DELETE NO ACTION
)WITH (
 OIDS=FALSE
);
ALTER TABLE kelurahan
 OWNER TO faundry;
 ================
 
 
 
CREATE SEQUENCE kode_sptpd_seq;
 CREATE TABLE sptpd (
 kode_sptpd integer NOT NULL DEFAULT nextval('kode_sptpd_seq'),
 no_reg integer NOT NULL,
 no_form character(9) NOT NULL,
 thn_sptpd character(4) NOT NULL,
 nik_wp character(25) NOT NULL,
 kode_sk character(25) NOT NULL,
 ls_tnh double precision NOT NULL,
 njop_tan double precision NOT NULL,
 jml_tnh double precision NOT NULL,
 ls_bng double precision NOT NULL,
 njop_bng double precision NOT NULL,
 jml_bng double precision NOT NULL,
 tot_njop1 double precision NOT NULL,
 ls_tnh2 double precision NOT NULL,
 njop_tan2 double precision NOT NULL,
 jml_tnh2 double precision NOT NULL,
 ls_bng2 double precision NOT NULL,
 njop_bng2 double precision NOT NULL,
 jml_bng2 double precision NOT NULL,
 tot_njop2 double precision NOT NULL,
 harga_psr double precision NOT NULL,
 npop_off double precision NOT NULL,
 npop_self double precision NOT NULL,
 npoptkp_off double precision NOT NULL,
 npoptkp_self double precision NOT NULL,
 nilai_pjk1 double precision NOT NULL,
 nilai_pjk2 double precision NOT NULL,
 nilai_tng1 double precision NOT NULL,
 nilai_tng2 double precision NOT NULL,
 prs_1 double precision NOT NULL,
 prs_2 double precision NOT NULL,
 sanksi smallint NOT NULL,
 prs_bunga double precision NOT NULL,
 selisih double precision NOT NULL,
 pajak_bphtb double precision NOT NULL,
 npopkp double precision NOT NULL,
 tgl_setor timestamp without time zone NOT NULL DEFAULT now(),
 no_dok character(15) NOT NULL,
 no_pbb character(18) NOT NULL,
 no_sk character(8) NOT NULL,
 tgl_prs timestamp without time zone NOT NULL DEFAULT now(),
 sts_prs character(1) NOT NULL,
 denda double precision NOT NULL,
 bunga double precision NOT NULL,
 bulan smallint NOT NULL,
 tot_pajak double precision NOT NULL,
 nilai_konf double precision NOT NULL,
 nihil character(1) NOT NULL,
 CONSTRAINT pk_sptpd PRIMARY KEY (kode_sptpd),
 CONSTRAINT fk_sptpd FOREIGN KEY (no_reg)
	REFERENCES dok_terima (no_reg) MATCH SIMPLE
	ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
 OIDS=FALSE
);
ALTER TABLE sptpd
 OWNER TO faundry;
 =======

CREATE TABLE objek_pajak (
 no_op character(18) NOT NULL DEFAULT 0,
 kode_kel character(7) NOT NULL,
 kode_jns character(3) NOT NULL,
 kode_blok character(3) NOT NULL,
 nama_pmk character(35) NOT NULL,
 alamat_pmk character(50) NOT NULL,
 kab_pmk character(25) NOT NULL,
 nik_pmk character(16) NOT NULL,
 rt_pmk character(3) NOT NULL,
 rw_pmk character(3) NOT NULL,
 kel_pmk character(25) NOT NULL,
 kec_pmk character(25) NOT NULL,
 kodepos_pmk character(7) NOT NULL,
 lokasi_op character(35) NOT NULL,
 rt_op character(3) NOT NULL,
 rw_op character(3) NOT NULL,
 kota_op character(25) NOT NULL,
 luas_tan double precision NOT NULL,
 njop_tan double precision NOT NULL,
 nilai_tan double precision NOT NULL,
 luas_tanbng double precision NOT NULL,
 njop_tanbng double precision NOT NULL,
 nilai_tanbng double precision NOT NULL,
 tot_nilai double precision NOT NULL,
 hrg_taksir double precision NOT NULL,
 no_sertf character(15) NOT NULL,
 tgl_prs timestamp without time zone NOT NULL DEFAULT now(),
 tahun character(4) NOT NULL,
 sts_rec smallint NOT NULL,
 sts_png character(1) NOT NULL,
 sts_blok character(1) NOT NULL,
 CONSTRAINT pk_objek_pajak PRIMARY KEY (no_op),
 CONSTRAINT fk_objek_pajak_kelurahan FOREIGN KEY (kode_kel)
	REFERENCES kelurahan (kode_kel) MATCH SIMPLE
	ON UPDATE CASCADE ON DELETE NO ACTION,
CONSTRAINT fk_objek_pajak_jenis_prh FOREIGN KEY (kode_jns)
	REFERENCES jenis_prh (kode_jns) MATCH SIMPLE
	ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
 OIDS=FALSE
);
ALTER TABLE objek_pajak
 OWNER TO faundry;
 
 
 
 
 ========
 
 CREATE SEQUENCE nomor_seq_wp;
 CREATE TABLE wajib_pajak (
 nomor integer NOT NULL DEFAULT nextval('nomor_seq_wp'),
 nama_pmk character(35) NOT NULL,
 no_op character(18) NOT NULL,
 nik_wp character(18) NOT NULL,
 nama_wp character(35) NOT NULL,
 alamat_wp character(50) NOT NULL,
 nama_kec character(15) NOT NULL,
 nama_kel character(15) NOT NULL,
 rt_wp character(5) NOT NULL,
 rw_wp character(5) NOT NULL,
 kab_wp character(15) NOT NULL,
 kode_pos character(5) NOT NULL,
 CONSTRAINT pk_wajib_pajak PRIMARY KEY (nomor),
 CONSTRAINT fk_wajib_pajak FOREIGN KEY (no_op)
	REFERENCES objek_pajak (no_op) MATCH SIMPLE
	ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
 OIDS=FALSE
);
ALTER TABLE wajib_pajak
 OWNER TO faundry;
 
 ============
  CREATE SEQUENCE nomor_seq;
 CREATE TABLE setoran (
 nomor integer NOT NULL DEFAULT nextval('nomor_seq'),
 no_bayar character(11) NOT NULL,
 kode_sptpd integer NOT NULL,
 thn_pajak character(4) NOT NULL,
 pajak_bphtb double precision NOT NULL,
 tgl_bayar timestamp without time zone NOT NULL DEFAULT now(),
 jenis_sk character(1) NOT NULL,
 kode_vib smallint NOT NULL,
 via_bayar character(25) NOT NULL,
 no_rev character(30) NOT NULL,
 sts character(1) NOT NULL,
 CONSTRAINT pk_setoran PRIMARY KEY (nomor),
 CONSTRAINT fk_setoran FOREIGN KEY (kode_sptpd)
	REFERENCES sptpd (kode_sptpd) MATCH SIMPLE
	ON UPDATE CASCADE ON DELETE NO ACTION,
 CONSTRAINT fk_setoran_via_bayar FOREIGN KEY (kode_vib)
	REFERENCES via_bayar (kode_vib) MATCH SIMPLE
	ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
 OIDS=FALSE
);
ALTER TABLE setoran
 OWNER TO faundry;


INSERT INTO ppat (kode_ppat, nama_ppat, alamat_ppat, telp_ppat) VALUES
('333', 'KI GENDENG PAMUNGKAS', 'YOYO', '898743');

 
 INSERT INTO kecamatan (kode_kec, nama_kec) VALUES
('005', 'PASD.ABIAN GIANYAR'),
('010', 'SUKAWATI'),
('020', 'BLAHBATUH'),
('021', 'PSD.Y.PEKERISAN TENGAH II'),
('030', 'GIANYAR'),
('040', 'TAMPAKSIRING'),
('050', 'UBUD'),
('060', 'KEC. TEGALLALANG'),
('071', 'KEC. PAYANGAN'),
('303', 'DESA BITERA');



INSERT INTO jenis_dok (kode_jsd, jenis_dok) VALUES
('01', 'SPTPD'),
('02', 'KERINGANAN'),
('03', 'KEBERATAN');


INSERT INTO via_bayar (kode_vib, via_bayar) VALUES
(1, 'BANK'),
(2, 'BENDAHARA');

INSERT INTO jenis_prh (kode_jns, jenis_prh, npoptkp) VALUES
('01', 'PEMBELIAN', 60000000.00),
('02', 'HIBAH', 60000000.00),
('03', 'HADIAH', 60000000.00),
('04', 'HAK WARIS', 300000000.00),
('05', 'HAK PERALIHAN', 300000000.00),
('06', 'HIBAH WASIAT', 300000000.00);



INSERT INTO sk (kode_sk, nama_sk) VALUES
('01', 'SPTPD'),
('02', 'SKPDKB'),
('03', 'SKPDKBT'),
('04', 'SKPDLB'),
('05', 'SKPDN');

INSERT INTO kelurahan (kode_kel, kode_kec, nama_kel) VALUES
('005009', '005', 'DESA BITERA'),
('005010', '005', 'DESA PACUNG'),
('010001', '010', 'BATUBULAN'),
('010002', '010', 'BATUBULAN KANGIN'),
('010003', '010', 'KETEWEL'),
('010004', '010', 'GUWANG'),
('010005', '010', 'SUKAWATI'),
('010006', '010', 'CELUK'),
('010007', '010', 'SINGAPADU'),
('010008', '010', 'BATUAN'),
('010009', '010', 'KEMENUH'),
('010010', '010', 'SINGAPADU TENGAH'),
('010011', '010', 'SINGAPADU KALER'),



INSERT INTO objek_pajak (no_op, 
kode_kel, kode_jns, 
kode_blok, nama_pmk, 
alamat_pmk, kab_pmk, 
nik_pmk, rt_pmk,
rw_pmk, kel_pmk, 
kec_pmk, kodepos_pmk, 
lokasi_op, rt_op, 
rw_op, kota_op, 
luas_tan, njop_tan, 
nilai_tan, luas_tanbng, 
njop_tanbng, nilai_tanbng, 
tot_nilai, hrg_taksir, no_sertf, tgl_prs, tahun, sts_rec, sts_png, sts_blok) VALUES


('510430300100000047', '005009', '06', '000', 'AA GD PUTRA PGL PADI AGUNG', 'BR TRIWANGSA', '', '', '', '', '', '', '', 'DESA BITERA', '000', '00', 'GIANYAR', 800, 48000, 38400000, 200, 50000, 10000000, 48400000, '5000','2000', '2011-12-13', '2011', '1', '0', '0')

INSERT INTO wajib_pajak (nomor, nama_pmk, no_op, nik_wp, nama_wp, alamat_wp, nama_kec, nama_kel, rt_wp, rw_wp, kab_wp, kode_pos) VALUES
(9, 'ANDI CAHAYA', '510430300100000047', '4444444444444444', 'HENDY', 'RECOSARI', 'MOJOSONGO', 'KRAGILAN', '02', '04', 'BOYOLALI', '57323');


INSERT INTO setoran (nomor, no_bayar, kode_sptpd, thn_pajak, pajak_bphtb, tgl_bayar, jenis_sk, jml_bayar, via_bayar, no_rev, sts) VALUES
(9, '1/0000001', 0, '2013', 0.00, '2013-08-07', '0', 2000000.00, '0', 'B/000001/2013-1', '0');