btn-mdw-ace-server-local/mwapp/init/000.MWBP.sql

DROP TABLE IF EXISTS MWBP.APP_SETTING~
CREATE TABLE MWBP.APP_SETTING (
	CODE VARCHAR(50) NOT NULL,
	NAME VARCHAR(50) NOT NULL,
	DESCRIPTION VARCHAR(255),
	SETTING_VALUE VARCHAR(255),
	MODIFIED_BY VARCHAR(32) NOT NULL,
	MODIFIED_DATE TIMESTAMP NOT NULL,
	PRIMARY KEY (CODE)
)~

DROP TABLE IF EXISTS MWBP.NH_BILLER_CAT~
CREATE TABLE MWBP.NH_BILLER_CAT (
	CODE VARCHAR(50) NOT NULL,
	NAME VARCHAR(50) NOT NULL,
	STATUS VARCHAR(10) NOT NULL,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	MODIFIED_DATE TIMESTAMP NOT NULL,
	PRIMARY KEY (CODE)
)~

DROP TABLE IF EXISTS MWBP.NH_BILLER~
CREATE TABLE MWBP.NH_BILLER (
	CODE VARCHAR(50) NOT NULL,
	NAME VARCHAR(255) NOT NULL,
	BILLER_CATEGORY VARCHAR(50) NOT NULL,
	PRIMARY KEY (CODE),
	FOREIGN KEY (BILLER_CATEGORY) REFERENCES MWBP.NH_BILLER_CAT (CODE)
)~

DROP TABLE IF EXISTS MWBP.NH_APP_USER~
CREATE TABLE MWBP.NH_APP_USER (
	LOGIN_NAME VARCHAR(32) NOT NULL,
	PASSWORD VARCHAR(128) NOT NULL,
	FIRST_NAME VARCHAR(50) NOT NULL,
	LAST_NAME VARCHAR(50),
	BILLER VARCHAR(50) NOT NULL,
	BILLER_GROUP BIGINT NOT NULL,
	FIRST_LOGIN INTEGER NOT NULL,
	LOGIN_RETRY INTEGER NOT NULL,
	STATUS VARCHAR(10) NOT NULL,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	MODIFIED_DATE TIMESTAMP NOT NULL,
	PRIMARY KEY (LOGIN_NAME),
	FOREIGN KEY (BILLER) REFERENCES MWBP.NH_BILLER (CODE)
)~

DROP TABLE IF EXISTS MWBP.NH_APP_USER_ACCOUNT~
CREATE TABLE MWBP.NH_APP_USER_ACCOUNT (
	ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20),
	ACCTNO VARCHAR(50) NOT NULL,
	APP_USER VARCHAR(32),
	PRIMARY KEY (ID),
	FOREIGN KEY (APP_USER) REFERENCES MWBP.NH_APP_USER (LOGIN_NAME)
)~

DROP TABLE IF EXISTS MWBP.NH_ROLE~
CREATE TABLE MWBP.NH_ROLE (
	CODE VARCHAR(50) NOT NULL,
	NAME VARCHAR(50) NOT NULL,
	STATUS VARCHAR(10) NOT NULL,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	MODIFIED_DATE TIMESTAMP NOT NULL,
	PRIMARY KEY (CODE)
)~

DROP TABLE IF EXISTS MWBP.NH_APP_USER_ROLE~
CREATE TABLE MWBP.NH_APP_USER_ROLE (
	APP_USER VARCHAR(32) NOT NULL,
	ROLE VARCHAR(50) NOT NULL,
	PRIMARY KEY (APP_USER,ROLE),
	FOREIGN KEY (APP_USER) REFERENCES MWBP.NH_APP_USER (LOGIN_NAME),
	FOREIGN KEY (ROLE) REFERENCES MWBP.NH_ROLE (CODE)
)~

DROP TABLE IF EXISTS MWBP.NH_BILLER_CONFIG~
CREATE TABLE MWBP.NH_BILLER_CONFIG (
	ID VARCHAR(255) NOT NULL,
	DATASOURCE_PROVIDER VARCHAR(255),
	DATABASE_HOST VARCHAR(255),
	DATABASE_NAME VARCHAR(255),
	DATABASE_PASSWORD VARCHAR(255),
	DATABASE_PORT VARCHAR(255),
	DATABASE_USER VARCHAR(255),
	DBMS VARCHAR(255),
	DEFAULT_SCHEMA VARCHAR(255),
	NAME VARCHAR(255),
	AS400FILE VARCHAR(255) NOT NULL,
	SYNCHRONIZE_TYPE INTEGER NOT NULL,
	BILLER_TYPE VARCHAR(20) NOT NULL,
	STATUS VARCHAR(10),
	LAST_SYNCH TIMESTAMP,
	LAST_STATUS VARCHAR(10),
	MAX_TAGIHAN INTEGER,
	MODIFIED_BY VARCHAR(32) NOT NULL,
	MODIFIED_DATE TIMESTAMP NOT NULL,
	LAST_SYNCH_PEMBAYARAN TIMESTAMP,
	LAST_SYNCH_TAGIHAN TIMESTAMP,
	THREAD_STATUS_PEMBAYARAN VARCHAR(256),
	THREAD_STATUS_TAGIHAN VARCHAR(256),
	PRIMARY KEY (ID)
)~

DROP TABLE IF EXISTS MWBP.NH_BILLER_GROUP~
CREATE TABLE MWBP.NH_BILLER_GROUP (
	ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH +1 INCREMENT BY +1 MINVALUE +1 MAXVALUE +9223372036854775807 NO CYCLE NO CACHE NO ORDER),
	CODE VARCHAR(50) NOT NULL,
	NAME VARCHAR(255) NOT NULL,
	PARENT BIGINT,
	BILLER VARCHAR(50) NOT NULL,
	STATUS VARCHAR(10) NOT NULL,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	MODIFIED_DATE TIMESTAMP NOT NULL,
	PRIMARY KEY (ID),
	FOREIGN KEY (BILLER) REFERENCES MWBP.NH_BILLER (CODE),
	FOREIGN KEY (PARENT) REFERENCES MWBP.NH_BILLER_GROUP (ID),
	UNIQUE(CODE,BILLER)
)~

DROP TABLE IF EXISTS MWBP.NH_FILTER~
CREATE TABLE MWBP.NH_FILTER (
	CODE VARCHAR(50) NOT NULL,
	NAME VARCHAR(50) NOT NULL,
	PRIMARY KEY (CODE)
)~

DROP TABLE IF EXISTS MWBP.NH_PRIVILEGE~
CREATE TABLE MWBP.NH_PRIVILEGE (
	CODE VARCHAR(50) NOT NULL,
	NAME VARCHAR(50) NOT NULL,
	TYPE VARCHAR(20) NOT NULL,
	PATH VARCHAR(250) NOT NULL,
	PARENT VARCHAR(250),
	PRIORITY INTEGER,
	STATUS VARCHAR(10) NOT NULL,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	MODIFIED_DATE TIMESTAMP NOT NULL,
	PRIMARY KEY (CODE),
	FOREIGN KEY (PARENT) REFERENCES MWBP.NH_PRIVILEGE (CODE)
)~

DROP TABLE IF EXISTS MWBP.NH_REPORT~
CREATE TABLE MWBP.NH_REPORT (
	CODE VARCHAR(50) NOT NULL,
	BILLER_CAT VARCHAR(50) NOT NULL,
	REPORT VARCHAR(255) NOT NULL,
	FILENAME VARCHAR(250),
	JASPER BLOB(1048576),
	FILTER VARCHAR(50) NOT NULL,
	STATUS VARCHAR(10) NOT NULL,
	MODIFIED_BY VARCHAR(64) NOT NULL,
	MODIFIED_DATE TIMESTAMP NOT NULL,
	PRIMARY KEY (CODE),
	FOREIGN KEY (FILTER) REFERENCES MWBP.NH_FILTER (CODE),
	FOREIGN KEY (BILLER_CAT) REFERENCES MWBP.NH_BILLER_CAT (CODE)
)~

DROP TABLE IF EXISTS MWBP.NH_BILLER_REPORT~
CREATE TABLE MWBP.NH_BILLER_REPORT (
	BILLER_CODE VARCHAR(50) NOT NULL,
	REPORT VARCHAR(50) NOT NULL,
	FOREIGN KEY (REPORT) REFERENCES MWBP.NH_REPORT (CODE),
	FOREIGN KEY (BILLER_CODE) REFERENCES MWBP.NH_BILLER (CODE)
)~

DROP TABLE IF EXISTS MWBP.NH_ROLE_PRIVILEGE~
CREATE TABLE MWBP.NH_ROLE_PRIVILEGE (
	ROLE VARCHAR(50) NOT NULL,
	PRIVILEGE VARCHAR(250) NOT NULL,
	FOREIGN KEY (ROLE) REFERENCES MWBP.NH_ROLE (CODE),
	FOREIGN KEY (PRIVILEGE) REFERENCES MWBP.NH_PRIVILEGE (CODE)
)~

DROP TABLE IF EXISTS MWBP.NH_SERVER_CONFIG~
CREATE TABLE MWBP.NH_SERVER_CONFIG (
	ID BIGINT NOT NULL,
	LAST_SYNCH TIMESTAMP NOT NULL,
	CURRENT_NODE VARCHAR(20),
	SERVER_STATUS VARCHAR(10) NOT NULL,
	LAST_STATUS VARCHAR(10) NOT NULL,
	MODIFIED_BY VARCHAR(32),
	MODIFIED_DATE TIMESTAMP,
	SYNCH_START_TIME TIME NOT NULL,
	SYNCH_STOP_TIME TIME NOT NULL,
	PRIMARY KEY (ID)
)~

DROP TABLE IF EXISTS MWBP.UNHISD_COPY~
CREATE TABLE MWBP.UNHISD_COPY (
	UNCUD CHAR(3) NOT NULL DEFAULT ' ',
	UNWSID CHAR(10) NOT NULL DEFAULT ' ',
	UNUSID CHAR(10) NOT NULL DEFAULT ' ',
	UNSVID CHAR(10) NOT NULL DEFAULT ' ',
	UNSEQ DECIMAL(8 , 0) NOT NULL DEFAULT 0.0,
	UNTCD DECIMAL(4 , 0) NOT NULL DEFAULT 0.0,
	UNTDT DECIMAL(8 , 0) NOT NULL DEFAULT 0.0,
	UNTD7 DECIMAL(7 , 0) NOT NULL DEFAULT 0.0,
	UNBR# DECIMAL(5 , 0) NOT NULL DEFAULT 0.0,
	UNCODE DECIMAL(4 , 0) NOT NULL DEFAULT 0.0,
	UNANKT CHAR(4) NOT NULL DEFAULT ' ',
	UNCFAK CHAR(10) NOT NULL DEFAULT ' ',
	UNFAKU CHAR(40) NOT NULL DEFAULT ' ',
	UNTEST CHAR(20) NOT NULL DEFAULT ' ',
	UNNIU CHAR(20) NOT NULL DEFAULT ' ',
	UNNIF CHAR(20) NOT NULL DEFAULT ' ',
	UNPSTU CHAR(10) NOT NULL DEFAULT ' ',
	UNNPST CHAR(40) NOT NULL DEFAULT ' ',
	UNNIM CHAR(20) NOT NULL DEFAULT ' ',
	UNIDSM CHAR(11) NOT NULL DEFAULT ' ',
	UNSTNM CHAR(40) NOT NULL DEFAULT ' ',
	UNSMST CHAR(1) NOT NULL DEFAULT ' ',
	UNSMTH CHAR(4) NOT NULL DEFAULT ' ',
	UNPYTP CHAR(3) NOT NULL DEFAULT ' ',
	UNACC# DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNREK# DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAM1 DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAM2 DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAM3 DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAM4 DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAM5 DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAM6 DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAM7 DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAM8 DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAM9 DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAMA DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAMB DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAMC DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAMD DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAME DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAMF DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAMG DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAMH DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAMI DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAMJ DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPAMK DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPTOT DECIMAL(19 , 0) NOT NULL DEFAULT 0.0,
	UNPREM CHAR(40) NOT NULL DEFAULT ' ',
	UNPDSC CHAR(40) NOT NULL DEFAULT ' ',
	UNPTIM DECIMAL(6 , 0) NOT NULL DEFAULT 0.0,
	UNPRVF CHAR(1) NOT NULL DEFAULT ' ',
	UNRVSQ DECIMAL(8 , 0) NOT NULL DEFAULT 0.0,
	UNPRSO CHAR(10) NOT NULL DEFAULT ' ',
	UNPRVT DECIMAL(6 , 0) NOT NULL DEFAULT 0.0,
	UNPERR DECIMAL(2 , 0) NOT NULL DEFAULT 0.0,
	IS_UPD INTEGER,
	INSERTED_DATE TIMESTAMP,
	PRIMARY KEY (UNCUD, UNWSID, UNUSID, UNSEQ, UNTCD, UNTDT)
)~