351 lines
9.9 KiB
SQL
351 lines
9.9 KiB
SQL
DEFINE tbl_data = dwhdata
|
|
DEFINE gl_data = dwhdata_gl
|
|
DEFINE kredit_tab = dwhdata_kredit
|
|
DEFINE tbl_idx = dwhindex
|
|
DEFINE tiny = 'INITIAL 32k Next 64k PCTINCREASE 0'
|
|
DEFINE small = 'INITIAL 64k Next 128k PCTINCREASE 0'
|
|
DEFINE normal = 'INITIAL 512k Next 2M PCTINCREASE 0'
|
|
DEFINE large = 'INITIAL 2M Next 16M PCTINCREASE 0'
|
|
DEFINE huge = 'INITIAL 10M Next 30M PCTINCREASE 0'
|
|
|
|
PROMPT Create table and index hist_saldo_tab
|
|
|
|
CREATE TABLE hist_saldo_tab (
|
|
nomor_rekening VARCHAR2(10) not null,
|
|
q_period VARCHAR2(6) not null,
|
|
hist_saldo VARCHAR2(4000)
|
|
)
|
|
TABLESPACE &tbl_data
|
|
STORAGE (&huge)
|
|
/
|
|
|
|
ALTER TABLE hist_saldo_tab
|
|
ADD (
|
|
CONSTRAINT hist_saldo_pk PRIMARY KEY ( nomor_rekening, q_period )
|
|
USING INDEX
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&large))
|
|
/
|
|
|
|
|
|
CREATE TABLE reconcile_staging_tab (
|
|
date_process DATE NOT NULL,
|
|
seq_process NUMBER NOT NULL,
|
|
previous_data NUMBER NOT NULL,
|
|
current_data NUMBER NOT NULL,
|
|
ext_data NUMBER NOT NULL,
|
|
user_process VARCHAR2(10) NOT NULL,
|
|
time_process VARCHAR2(6) NOT NULL,
|
|
source_data VARCHAR2(30) NOT NULL,
|
|
target_data VARCHAR2(30) NOT NULL,
|
|
ab2_data VARCHAR2(30) NOT NULL,
|
|
job_id NUMBER NOT NULL
|
|
)
|
|
TABLESPACE &tbl_data
|
|
STORAGE (&large)
|
|
/
|
|
|
|
ALTER TABLE reconcile_staging_tab
|
|
ADD (
|
|
CONSTRAINT pk_reconcile_staging PRIMARY KEY ( date_process, seq_process, ab2_data )
|
|
USING INDEX
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&large))
|
|
/
|
|
|
|
|
|
PROMPT creating table external_data_tab
|
|
|
|
CREATE TABLE external_data_tab
|
|
(
|
|
tab_name VARCHAR2(30) NOT NULL,
|
|
purging_type VARCHAR2(1) NOT NULL,
|
|
modul VARCHAR2(3) NOT NULL,
|
|
purging_field VARCHAR2(30) NULL,
|
|
purging_data_type VARCHAR2(1) NULL,
|
|
purging_filter VARCHAR2(2000) NULL,
|
|
load_method VARCHAR2(2) NOT NULL
|
|
)
|
|
TABLESPACE &tbl_data
|
|
STORAGE (&small)
|
|
/
|
|
ALTER TABLE external_data_tab
|
|
ADD ( CONSTRAINT pk_external_data PRIMARY KEY ( modul, tab_name )
|
|
USING INDEX
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&small))
|
|
/
|
|
COMMENT ON TABLE external_data_tab IS
|
|
'CATEGORY=SYSTEM^NAME=External Table List^';
|
|
|
|
|
|
CREATE TABLE gl_saldo_tab (
|
|
kode_cabang VARCHAR2(3) NOT NULL,
|
|
kode_coa VARCHAR2(5) NOT NULL,
|
|
kode_currency VARCHAR2(3) NOT NULL,
|
|
q_period VARCHAR2(6) NOT NULL,
|
|
base_saldo VARCHAR2(4000) NULL,
|
|
original_saldo VARCHAR2(4000) NULL
|
|
)
|
|
TABLESPACE &gl_data
|
|
STORAGE (&huge)
|
|
/
|
|
|
|
ALTER TABLE gl_saldo_tab
|
|
ADD (
|
|
CONSTRAINT pk_gl_saldo PRIMARY KEY ( kode_cabang, kode_coa, kode_currency, q_period )
|
|
USING INDEX
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&large))
|
|
/
|
|
|
|
|
|
PROMPT creating table kredit_tab
|
|
|
|
|
|
CREATE TABLE kredit_tab (
|
|
krdt_jenfas VARCHAR2(4),
|
|
krdt_sifat VARCHAR2(2),
|
|
krdt_norek VARCHAR2(25),
|
|
krdt_nopkawal VARCHAR2(25),
|
|
krdt_tglpkawal DATE,
|
|
krdt_nopkakhir VARCHAR2(25),
|
|
krdt_tglpkakhir DATE,
|
|
krdt_tglawalkredit DATE,
|
|
krdt_tglmulai DATE,
|
|
krdt_tgljthtempo DATE,
|
|
krdt_baruperpanjangan NUMBER(2),
|
|
krdt_golkredit VARCHAR2(2),
|
|
krdt_jnspenggunaan VARCHAR2(2),
|
|
krdt_orientasipenggunaan VARCHAR2(1),
|
|
krdt_sektorekonomi VARCHAR2(5),
|
|
krdt_dati2lokasiproyek VARCHAR2(4),
|
|
krdt_nilproyek NUMBER(15),
|
|
krdt_idvaluta VARCHAR2(3),
|
|
krdt_skbunga NUMBER(5,2),
|
|
krdt_sftskbunga VARCHAR2(1),
|
|
krdt_plafondinduk NUMBER(15),
|
|
krdt_plafond NUMBER(15),
|
|
krdt_originalplafond NUMBER(15,2),
|
|
krdt_bakidebet NUMBER(15),
|
|
krdt_originalcurrency NUMBER(15,2),
|
|
krdt_keltarik NUMBER(15),
|
|
krdt_originalkeltarik NUMBER(15,2),
|
|
krdt_discount NUMBER(5,2),
|
|
krdt_kolektibilitas VARCHAR2(1),
|
|
krdt_tglmacet DATE,
|
|
krdt_sebabmacet VARCHAR2(2),
|
|
krdt_ketsebabmacet VARCHAR2(100),
|
|
krdt_tgltunggakan DATE,
|
|
krdt_tunggakanpokok NUMBER(15),
|
|
krdt_frektunggakanpokok NUMBER(3),
|
|
krdt_tunggakanbungaintra NUMBER(15),
|
|
krdt_tunggakanbungaextra NUMBER(15),
|
|
krdt_frektunggakanbunga NUMBER(3),
|
|
krdt_denda NUMBER(15),
|
|
krdt_kondisi VARCHAR2(2),
|
|
krdt_tglkondisi DATE,
|
|
krdt_agunan NUMBER(15),
|
|
krdt_ppap NUMBER(15),
|
|
krdt_kumulatifrealisasi NUMBER(15),
|
|
krdt_tglrestrukturisasi DATE,
|
|
krdt_restrukturisasike NUMBER(2),
|
|
krdt_restrukturisasiawal DATE,
|
|
krdt_kondisidebitur VARCHAR2(100),
|
|
krdt_permasalahandebitur VARCHAR2(100),
|
|
krdt_keterangan VARCHAR2(100),
|
|
krdt_din VARCHAR2(20),
|
|
krdt_cabang VARCHAR2(3),
|
|
krdt_bln VARCHAR2(2),
|
|
krdt_thn VARCHAR2(4),
|
|
krdt_cif VARCHAR2(30),
|
|
krdt_updatedata VARCHAR2(1),
|
|
krdt_barulama VARCHAR2(1),
|
|
krdt_produk VARCHAR2(15),
|
|
krdt_kodeprog VARCHAR2(3),
|
|
krdt_kodepenjamin VARCHAR2(3),
|
|
krdt_bagiandijamin VARCHAR2(5),
|
|
krdt_idfasilitas VARCHAR2(52),
|
|
krdt_bmhd NUMBER(15),
|
|
krdt_tunggakanbunganonextra NUMBER(15),
|
|
krdt_idbankbi VARCHAR2(3),
|
|
krdt_ppapkeltarik NUMBER(15),
|
|
krdt_bkdbtblnll NUMBER(15),
|
|
krdt_dendablnll NUMBER(15),
|
|
krdt_nilwjrblnll NUMBER(15),
|
|
krdt_nilwjr NUMBER(15),
|
|
krdt_ktg_debitur VARCHAR2(2),
|
|
krdt_ktg_portofolio VARCHAR2(2),
|
|
krdt_sft_kredit_bsl VARCHAR2(1),
|
|
krdt_ktg_ukur VARCHAR2(1),
|
|
krdt_sektorekonomi_bsl VARCHAR2(6),
|
|
krdt_ckpn_individual NUMBER(15),
|
|
krdt_ckpn_kolektif NUMBER(15),
|
|
krdt_ppap_khusus NUMBER(15),
|
|
krdt_pendapatan_restruk NUMBER(15),
|
|
krdt_kel_tarik_uncommit NUMBER(15),
|
|
krdt_jns_kredit_bassel2 VARCHAR2(2),
|
|
krdt_jns_guna_bassel2 VARCHAR2(2),
|
|
krdt_orient_guna_bassel2 VARCHAR2(1),
|
|
krdt_lokasi_dati2_bassel2 VARCHAR2(4),
|
|
krdt_sk_bunga_induk NUMBER(8,2),
|
|
krdt_bakidenda NUMBER(15),
|
|
krdt_ttl_bakidenda NUMBER(15),
|
|
krdt_net_jaminan NUMBER(15),
|
|
krdt_seq NUMBER(18),
|
|
krdt_kodehost VARCHAR2(3),
|
|
krdt_brid VARCHAR2(3)
|
|
)
|
|
TABLESPACE &kredit_tab
|
|
STORAGE (&huge)
|
|
/
|
|
|
|
-- Create/Recreate indexes
|
|
|
|
CREATE INDEX ix_kredit1
|
|
ON kredit_tab ( krdt_bln, krdt_thn )
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&large)
|
|
/
|
|
|
|
|
|
CREATE INDEX ix_kredit2
|
|
ON kredit_tab ( krdt_cabang, krdt_idvaluta, krdt_bln, krdt_thn )
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&large)
|
|
/
|
|
|
|
|
|
|
|
PROMPT creating table agunan_tab
|
|
|
|
|
|
CREATE TABLE agunan_tab (
|
|
agnn_jnsagunan VARCHAR2(2),
|
|
agnn_peringkat VARCHAR2(4),
|
|
agnn_jnspengikatan VARCHAR2(2),
|
|
agnn_pemilikagunan VARCHAR2(50),
|
|
agnn_buktikepemilikan VARCHAR2(50),
|
|
agnn_dati2lokasiagunan VARCHAR2(4),
|
|
agnn_alamatagunan VARCHAR2(100),
|
|
agnn_nilaiagunan NUMBER(15),
|
|
agnn_nilaiagunanbank NUMBER(15),
|
|
agnn_nilaiagunanpenilai NUMBER(15),
|
|
agnn_nilailikuidasi NUMBER(15),
|
|
agnn_penilaiindependen VARCHAR2(50),
|
|
agnn_tglpenilaian DATE,
|
|
agnn_paripasu NUMBER(5),
|
|
agnn_asuransi VARCHAR2(1),
|
|
agnn_ppap NUMBER(15,2),
|
|
agnn_din VARCHAR2(20),
|
|
agnn_cabang VARCHAR2(3),
|
|
agnn_barulama VARCHAR2(1),
|
|
agnn_bln VARCHAR2(2),
|
|
agnn_thn VARCHAR2(4),
|
|
agnn_iddebitur VARCHAR2(43),
|
|
agnn_idagunan VARCHAR2(32),
|
|
agnn_idbankbi VARCHAR2(3),
|
|
agnn_idvaluta VARCHAR2(3),
|
|
agnn_createdate DATE,
|
|
agnn_updatedate DATE,
|
|
agnn_jns_agunan_bsl VARCHAR2(3),
|
|
agnn_sft_agunan_bsl VARCHAR2(1),
|
|
agnn_lbg_prngkat VARCHAR2(2),
|
|
agnn_peringkat_agunan VARCHAR2(2),
|
|
agnn_tgl_peringkat DATE,
|
|
agnn_tgl_penerbit DATE,
|
|
agnn_tgl_jthtempo DATE,
|
|
agnn_pnrbt_agunan VARCHAR2(3),
|
|
agnn_seq NUMBER(18)
|
|
)
|
|
TABLESPACE &kredit_tab
|
|
STORAGE (&huge)
|
|
/
|
|
|
|
CREATE INDEX ix_agunan1
|
|
ON agunan_tab ( agnn_idbankbi, agnn_bln, agnn_thn )
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&large)
|
|
/
|
|
|
|
CREATE INDEX ix_agunan2
|
|
ON agunan_tab ( agnn_bln, agnn_thn )
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&large)
|
|
/
|
|
|
|
CREATE INDEX ix_agunan3
|
|
ON agunan_tab ( agnn_din, agnn_bln, agnn_thn, agnn_jnsagunan )
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&large)
|
|
/
|
|
|
|
PROMPT creating table kredit_curr_rate_tab
|
|
|
|
CREATE TABLE kredit_curr_rate_tab (
|
|
curr_blnthn VARCHAR2(8) NOT NULL,
|
|
curr_id VARCHAR2(3) NOT NULL,
|
|
curr_convert NUMBER NULL,
|
|
curr__convert NUMBER NOT NULL,
|
|
curr_rate NUMBER(10, 2) NULL,
|
|
curr__rate NUMBER(10, 2) NULL,
|
|
rec_type VARCHAR2(1) NOT NULL,
|
|
opr VARCHAR2(8) NOT NULL,
|
|
oprtime DATE NULL,
|
|
oto VARCHAR2(8) NOT NULL,
|
|
ototime DATE NULL
|
|
)
|
|
TABLESPACE &kredit_tab
|
|
STORAGE (&normal)
|
|
/
|
|
|
|
ALTER TABLE kredit_curr_rate_tab
|
|
ADD (
|
|
CONSTRAINT kredit_curr_rate_pk PRIMARY KEY ( curr_blnthn, curr_id, curr__convert )
|
|
USING INDEX
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&normal))
|
|
/
|
|
|
|
PROMPT creating table trsry_tran_code_tab
|
|
|
|
CREATE TABLE trsry_tran_code_tab
|
|
(
|
|
trans_code VARCHAR2(4) NOT NULL,
|
|
trans_idr_code VARCHAR2(4) NOT NULL,
|
|
trans_type VARCHAR2(1) NOT NULL,
|
|
bn_flag VARCHAR2(1) NOT NULL
|
|
)
|
|
TABLESPACE &tbl_data
|
|
STORAGE (&small)
|
|
/
|
|
|
|
ALTER TABLE trsry_tran_code_tab
|
|
ADD (
|
|
CONSTRAINT trsry_tran_code_pk PRIMARY KEY ( trans_code )
|
|
USING INDEX
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&small))
|
|
/
|
|
|
|
PROMPT creating table lbu_tab
|
|
|
|
CREATE TABLE lbu_tab
|
|
(
|
|
table_name VARCHAR2(30) NOT NULL,
|
|
filter_1 VARCHAR2(2000),
|
|
filter_2 VARCHAR2(2000),
|
|
filter_3 VARCHAR2(2000),
|
|
filter_4 VARCHAR2(2000)
|
|
)
|
|
TABLESPACE &tbl_data
|
|
STORAGE (&small)
|
|
/
|
|
|
|
ALTER TABLE lbu_tab
|
|
ADD (
|
|
CONSTRAINT lbu_pk PRIMARY KEY ( table_name )
|
|
USING INDEX
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&small))
|
|
/ |