--
-- Host: private-ngp-db-svr1-do-user-4066240-0.b.db.ondigitalocean.com    Database: ngp_collection
-- ------------------------------------------------------


--
-- Temporary view structure for view `BANK_MTD_AMCOL`
--

/*!50001 DROP VIEW IF EXISTS `BANK_MTD_AMCOL`*/;
/*!50001 CREATE VIEW `BANK_MTD_AMCOL` AS SELECT 
 1 AS `BANK`,
 1 AS `CABANG`,
 1 AS `OS`,
 1 AS `TOTNOA`,
 1 AS `AMCOL`,
 1 AS `AMCOLNOA`*/;

--
-- Temporary view structure for view `LIST_RAW_AMCOL`
--

/*!50001 DROP VIEW IF EXISTS `LIST_RAW_AMCOL`*/;
/*!50001 CREATE VIEW `LIST_RAW_AMCOL` AS SELECT 
 1 AS `BANK`,
 1 AS `LOANNUM`,
 1 AS `NAMA_DEBITUR`,
 1 AS `CABANG_INTERNAL`,
 1 AS `YTDAmcol`,
 1 AS `YTDAmcolNOA`,
 1 AS `MTDAmcol`,
 1 AS `MTDAmcolNOA`,
 1 AS `TDAmcol`,
 1 AS `TDAmcolNOA`*/;

--
-- Table structure for table `ci_sessions`
--

CREATE TABLE `ci_sessions` (
  `id` varchar(128) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `timestamp` int unsigned NOT NULL DEFAULT '0',
  `data` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Temporary view structure for view `detail_mtd_amcoll`
--

/*!50001 DROP VIEW IF EXISTS `detail_mtd_amcoll`*/;
/*!50001 CREATE VIEW `detail_mtd_amcoll` AS SELECT 
 1 AS `BANK`,
 1 AS `CABANG_INTERNAL`,
 1 AS `NAMA_DEBITUR`,
 1 AS `loanno`,
 1 AS `amm_col`,
 1 AS `pic`,
 1 AS `fullname`,
 1 AS `action_date`*/;

--
-- Table structure for table `dt_backup`
--

CREATE TABLE `dt_backup` (
  `id` int NOT NULL AUTO_INCREMENT,
  `BANK` varchar(50) DEFAULT NULL,
  `LOANNUM` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CIF` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `OPEN_DATE` date DEFAULT NULL,
  `MATURITY_DATE` date DEFAULT NULL,
  `DUE_WO` varchar(35) DEFAULT NULL,
  `LOB` varchar(15) DEFAULT NULL,
  `ACCOUNT_STATUS` varchar(45) DEFAULT NULL,
  `PLAFOND` decimal(28,10) DEFAULT NULL,
  `OS` decimal(28,10) DEFAULT NULL,
  `kolektibilitas` varchar(20) DEFAULT NULL,
  `DPD` int DEFAULT NULL,
  `TUNGGAKAN_POKOK` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_BUNGA` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_DENDA` decimal(28,10) DEFAULT NULL,
  `TOTAL_TUNGGAKAN` decimal(28,10) DEFAULT NULL,
  `ANGSURAN` decimal(28,10) DEFAULT NULL,
  `LOAN_PRODUK` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `NAMA_DEBITUR` varchar(60) DEFAULT NULL,
  `BANK_BRCODE` varchar(15) DEFAULT NULL,
  `BANK_CABANG` varchar(60) DEFAULT NULL,
  `CABANG_INTERNAL` varchar(50) DEFAULT NULL,
  `CABANG_BANK` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `KETERANGAN` varchar(800) DEFAULT NULL,
  `KETERANGAN_2` text CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  `LAST_PAYMENT` date DEFAULT NULL,
  `JADWAL_BAYAR` date DEFAULT NULL,
  `last_dpd` int DEFAULT NULL,
  `last_bucket` int DEFAULT NULL,
  `flow` varchar(10) DEFAULT NULL,
  `BAST` varchar(15) DEFAULT NULL,
  `TGL_BAST` date DEFAULT NULL,
  `TGL_UPDATE` date DEFAULT NULL,
  `WILAYAH` varchar(25) DEFAULT NULL,
  `ALAMAT_DOMISILI` varchar(250) DEFAULT NULL,
  `KELURAHAN` varchar(60) DEFAULT NULL,
  `KECAMATAN` varchar(60) DEFAULT NULL,
  `KOTA` varchar(60) DEFAULT NULL,
  `TELP` varchar(30) DEFAULT NULL,
  `NAMA_USAHA` varchar(250) DEFAULT NULL,
  `ALAMAT_USAHA` varchar(250) DEFAULT NULL,
  `TELP_USAHA` varchar(60) DEFAULT NULL,
  `KONTAK_DARURAT` varchar(150) DEFAULT NULL,
  `TELP_DARURAT` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `HANDPHONE_1` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `HANDPHONE_2` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `ALAMAT_AGUNAN` varchar(500) DEFAULT NULL,
  `SCORE` int DEFAULT NULL,
  `KATEGORI` varchar(20) DEFAULT NULL,
  `bucket` int DEFAULT NULL,
  `LAST_PAYMENT_DATE` date DEFAULT NULL,
  `last_payment_amount` double DEFAULT NULL,
  `WILAYAH_NGP` varchar(255) NOT NULL,
  `AREA_NGP` varchar(255) NOT NULL,
  `flug` varchar(11) DEFAULT NULL,
  `luas_bangunan` varchar(15) DEFAULT NULL,
  `luas_tanah` varchar(15) DEFAULT NULL,
  `pemilik_agunan` varchar(255) DEFAULT NULL,
  `TGL_DOWNLOAD` date DEFAULT NULL,
  `REKENING` varchar(35) DEFAULT NULL,
  `keterangan_account` varchar(255) DEFAULT NULL,
  `NO_VA` varchar(35) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=965691 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_collect_hist`
--

CREATE TABLE `dt_collect_hist` (
  `id` int NOT NULL AUTO_INCREMENT,
  `cif` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `loanno` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `action` int DEFAULT NULL,
  `action_date` date DEFAULT NULL,
  `respon` int DEFAULT NULL,
  `result` int DEFAULT NULL,
  `pic` int DEFAULT NULL,
  `remarks` text,
  `next_action` int DEFAULT NULL,
  `next_action_date` date DEFAULT NULL,
  `amm_col` double DEFAULT '0',
  `amm_pokok` double DEFAULT '0',
  `amm_denda` double DEFAULT '0',
  `amm_bunga` double DEFAULT '0',
  `amm_ptp` double DEFAULT '0',
  `verified` char(1) NOT NULL DEFAULT 'N',
  `verified_on` date DEFAULT NULL,
  `verified_by` int DEFAULT NULL,
  `verified_file_slip` varchar(100) DEFAULT NULL,
  `verified_valid` char(1) DEFAULT 'N',
  `verified_note` varchar(200) DEFAULT NULL,
  `create_on` date DEFAULT '1900-01-01',
  `create_by` int DEFAULT NULL,
  `update_on` date DEFAULT NULL,
  `update_by` int DEFAULT NULL,
  `latest` char(1) DEFAULT 'Y',
  `photo_visit` varchar(50) DEFAULT NULL,
  `upd_hp_deb` varchar(30) DEFAULT NULL,
  `lon` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `lat` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `orang_ditemui` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `relasi_ke_debitur` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT '',
  `kontak_debitur` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `kondisi_debitur` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `rencana_penyelesaian` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `kondisi_jaminan` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `karakter_debitur` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `kondisi_usaha` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `coll_rekomendasi` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `PRINCIPAL` varchar(20) DEFAULT NULL,
  `kesimpulan` int DEFAULT '0',
  `keterangan_kesimpulan` text CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  `tgl_input_kesimpulan` date DEFAULT NULL,
  `user_input_kesimpulan` int DEFAULT '0',
  `no_hp_tambahan` varchar(30) DEFAULT NULL,
  `status_hunian` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  `user_deleted` int DEFAULT '0',
  `tgl_deleted` date DEFAULT NULL,
  `tahun_periode_bayar` varchar(5) DEFAULT NULL,
  `bulan_periode_bayar` varchar(2) DEFAULT NULL,
  `jrk_sekolah` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `jrk_mall` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `jrk_rs` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `jrk_pusat_kota` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `luas_bangunan` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `luas_tanah` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `kamar_mandi` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `kamar_tidur` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `tingkatan_lantai` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `marketable` varchar(35) DEFAULT NULL,
  `problem_debt` varchar(35) DEFAULT NULL,
  `area_banjir` varchar(35) DEFAULT NULL,
  `listrik` varchar(35) DEFAULT NULL,
  `pdam` varchar(35) DEFAULT NULL,
  `spek_agunan` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_collect_hist` (`loanno`,`action_date`)
) ENGINE=InnoDB AUTO_INCREMENT=292403 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_collect_report`
--

CREATE TABLE `dt_collect_report` (
  `id` int NOT NULL AUTO_INCREMENT,
  `loannum` varchar(35) NOT NULL,
  `cif` varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `action` int DEFAULT NULL,
  `action_date` date DEFAULT NULL,
  `respon` int DEFAULT NULL,
  `result` int DEFAULT NULL,
  `pic` int DEFAULT NULL,
  `next_action` int DEFAULT NULL,
  `next_action_date` date DEFAULT NULL,
  `amm_col` double DEFAULT '0',
  `amm_pokok` double DEFAULT '0',
  `amm_denda` double DEFAULT '0',
  `amm_bunga` double DEFAULT '0',
  `amm_ptp` double DEFAULT '0',
  `slip_payment` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT 'N',
  `upd_hp_deb` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `lon` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `lat` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `orang_ditemui` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `relasi_ke_debitur` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `karakter_debitur` int DEFAULT NULL,
  `modif_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `keterangan` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `kondisi_jaminan` int DEFAULT NULL,
  `status_hunian` varchar(35) NOT NULL,
  `kondisi_debitur` int DEFAULT NULL,
  `kondisi_usaha` int DEFAULT NULL,
  `hasil_penagihan` int DEFAULT NULL,
  `rencana_penyelesaian` int DEFAULT NULL,
  `rekomendasi` int DEFAULT NULL,
  `bank` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `update_on` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_by` int DEFAULT NULL,
  `kesimpulan` int DEFAULT '0',
  `keterangan_kesimpulan` text,
  `tgl_input_kesimpulan` date DEFAULT NULL,
  `user_input_kesimpulan` int DEFAULT '0',
  `no_hp_tambahan` varchar(30) DEFAULT NULL,
  `jrk_sekolah` varchar(15) DEFAULT NULL,
  `jrk_mall` varchar(15) DEFAULT NULL,
  `jrk_rs` varchar(15) DEFAULT NULL,
  `jrk_pusat_kota` varchar(15) DEFAULT NULL,
  `luas_bangunan` varchar(15) DEFAULT NULL,
  `luas_tanah` varchar(15) DEFAULT NULL,
  `kamar_mandi` varchar(15) DEFAULT NULL,
  `kamar_tidur` varchar(15) DEFAULT NULL,
  `tingkatan_lantai` varchar(15) DEFAULT NULL,
  `marketable` varchar(35) DEFAULT NULL,
  `problem_debt` varchar(35) DEFAULT NULL,
  `area_banjir` varchar(35) DEFAULT NULL,
  `listrik` varchar(35) DEFAULT NULL,
  `pdam` varchar(35) DEFAULT NULL,
  `spek_agunan` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loannum` (`loannum`),
  KEY `idx_collect_report` (`loannum`)
) ENGINE=InnoDB AUTO_INCREMENT=225911 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `dt_collection_photo`
--

CREATE TABLE `dt_collection_photo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `loannum` varchar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `photo` varchar(50) NOT NULL,
  `upd_date` date NOT NULL,
  `remarks` varchar(250) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_foto_collection` (`loannum`)
) ENGINE=InnoDB AUTO_INCREMENT=197328 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_deb_classification_detail`
--

CREATE TABLE `dt_deb_classification_detail` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bank` varchar(15) NOT NULL,
  `cabang_internal` varchar(35) NOT NULL,
  `cabang_bank` varchar(35) DEFAULT NULL,
  `cif` varchar(25) NOT NULL,
  `nama_debitur` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `os` decimal(15,3) DEFAULT NULL,
  `total_tunggakan` float(15,3) DEFAULT NULL,
  `kategory` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `dt_debitur_classification`
--

CREATE TABLE `dt_debitur_classification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `cif` varchar(15) NOT NULL,
  `debitur` int NOT NULL,
  `agunan` int NOT NULL,
  `sor` int NOT NULL,
  `score` int DEFAULT NULL,
  `kategory` int DEFAULT NULL,
  `plan` varchar(25) NOT NULL,
  `recomendation` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cif` (`cif`)
) ENGINE=InnoDB AUTO_INCREMENT=1490 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_debitur_mapping`
--

CREATE TABLE `dt_debitur_mapping` (
  `id` int NOT NULL AUTO_INCREMENT,
  `loanno` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `FCCode` int DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `current` char(1) DEFAULT NULL,
  `update_on` date DEFAULT NULL,
  `update_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_debitur_mapping` (`current`,`loanno`)
) ENGINE=InnoDB AUTO_INCREMENT=422242 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_debiturs_contact`
--

CREATE TABLE `dt_debiturs_contact` (
  `id` int NOT NULL AUTO_INCREMENT,
  `CIF` varchar(20) DEFAULT NULL,
  `ALAMAT_DOMISILI` varchar(250) DEFAULT NULL,
  `KELURAHAN` varchar(55) DEFAULT NULL,
  `KECAMATAN` varchar(60) DEFAULT NULL,
  `KOTA` varchar(60) DEFAULT NULL,
  `TELP` varchar(50) DEFAULT NULL,
  `NAMA_USAHA` varchar(60) DEFAULT NULL,
  `ALAMAT_USAHA` varchar(250) DEFAULT NULL,
  `TELP_USAHA` varchar(50) DEFAULT NULL,
  `BANK` varchar(20) DEFAULT NULL,
  `CABANG_INTERNAL` varchar(35) DEFAULT NULL,
  `KONTAK_DARURAT` varchar(80) DEFAULT NULL,
  `TELP_DARURAT` varchar(100) DEFAULT NULL,
  `NO_HP` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `CIF` (`CIF`),
  KEY `idx_debitur_contact` (`CIF`)
) ENGINE=InnoDB AUTO_INCREMENT=197743 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_events`
--

CREATE TABLE `dt_events` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bank` varchar(25) NOT NULL,
  `dt_event` date NOT NULL,
  `subject` varchar(150) NOT NULL,
  `remarks` varchar(255) NOT NULL,
  `input_by` int NOT NULL,
  `input_date` date NOT NULL,
  `ecode` varchar(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_events_photo`
--

CREATE TABLE `dt_events_photo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `event_id` varchar(6) NOT NULL,
  `photo` varchar(35) NOT NULL,
  `caption` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_fileback`
--

CREATE TABLE `dt_fileback` (
  `id` int NOT NULL AUTO_INCREMENT,
  `BANK` varchar(50) DEFAULT NULL,
  `LOANNUM` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CIF` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `OPEN_DATE` date DEFAULT NULL,
  `MATURITY_DATE` date DEFAULT NULL,
  `DUE_WO` varchar(35) DEFAULT NULL,
  `LOB` varchar(15) DEFAULT NULL,
  `ACCOUNT_STATUS` varchar(45) DEFAULT NULL,
  `PLAFOND` decimal(28,10) DEFAULT NULL,
  `OS` decimal(28,10) DEFAULT NULL,
  `kolektibilitas` varchar(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `DPD` int DEFAULT NULL,
  `TUNGGAKAN_POKOK` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_BUNGA` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_DENDA` decimal(28,10) DEFAULT NULL,
  `TOTAL_TUNGGAKAN` decimal(28,10) DEFAULT NULL,
  `ANGSURAN` decimal(28,10) DEFAULT NULL,
  `LOAN_PRODUK` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `NAMA_DEBITUR` varchar(60) DEFAULT NULL,
  `BANK_BRCODE` varchar(15) DEFAULT NULL,
  `BANK_CABANG` varchar(60) DEFAULT NULL,
  `CABANG_INTERNAL` varchar(50) DEFAULT NULL,
  `CABANG_BANK` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `KETERANGAN` varchar(800) DEFAULT NULL,
  `KETERANGAN_2` text CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  `LAST_PAYMENT` date DEFAULT NULL,
  `JADWAL_BAYAR` date DEFAULT NULL,
  `last_dpd` int DEFAULT NULL,
  `last_bucket` int DEFAULT NULL,
  `flow` varchar(10) DEFAULT NULL,
  `BAST` varchar(15) DEFAULT NULL,
  `TGL_BAST` date DEFAULT NULL,
  `TGL_UPDATE` date DEFAULT NULL,
  `WILAYAH` varchar(25) DEFAULT NULL,
  `ALAMAT_DOMISILI` varchar(250) DEFAULT NULL,
  `KELURAHAN` varchar(60) DEFAULT NULL,
  `KECAMATAN` varchar(60) DEFAULT NULL,
  `KOTA` varchar(60) DEFAULT NULL,
  `TELP` varchar(30) DEFAULT NULL,
  `NAMA_USAHA` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `ALAMAT_USAHA` varchar(250) DEFAULT NULL,
  `TELP_USAHA` varchar(60) DEFAULT NULL,
  `KONTAK_DARURAT` varchar(80) DEFAULT NULL,
  `TELP_DARURAT` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `HANDPHONE_1` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `HANDPHONE_2` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `ALAMAT_AGUNAN` varchar(500) DEFAULT NULL,
  `SCORE` int DEFAULT NULL,
  `KATEGORI` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `bucket` int DEFAULT NULL,
  `LAST_PAYMENT_DATE` date DEFAULT NULL,
  `last_payment_amount` double DEFAULT NULL,
  `WILAYAH_NGP` varchar(255) NOT NULL,
  `AREA_NGP` varchar(255) NOT NULL,
  `flug` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_master_loan` (`BANK`,`LOANNUM`)
) ENGINE=InnoDB AUTO_INCREMENT=891526 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_hasil_penagihan`
--

CREATE TABLE `dt_hasil_penagihan` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `BANK` varchar(100) DEFAULT NULL,
  `CUSTOMERID` varchar(50) DEFAULT NULL,
  `NOMOR_PINJAMAN` varchar(50) DEFAULT NULL,
  `TGL_PENAGIHAN` date DEFAULT NULL,
  `JENIS_PENAGIHAN` varchar(100) DEFAULT NULL,
  `RESPON_CUSTOMER` varchar(100) DEFAULT NULL,
  `HASIL_PENAGIHAN` varchar(100) DEFAULT NULL,
  `KETERANGAN_HASIL_PENAGIHAN` text,
  `ORANG_DITEMUI` varchar(100) DEFAULT NULL,
  `RELASI_KEDEBITUR` varchar(100) DEFAULT NULL,
  `KESIMPULAN` varchar(100) DEFAULT NULL,
  `KESIMPULAN_HASIL_PENAGIHAN` text,
  `REKOMENDASI_PENAGIHAN` varchar(100) DEFAULT NULL,
  `JUMLAH_HUTANG_DIBAYAR` decimal(15,2) DEFAULT NULL,
  `JUMLAH_POKOK_HUTANG_DIBAYAR` decimal(15,2) DEFAULT NULL,
  `JUMLAH_DENDA_HUTANG_DIBAYAR` decimal(15,2) DEFAULT NULL,
  `JUMLAH_BUNGA_HUTANG_DIBAYAR` decimal(15,2) DEFAULT NULL,
  `JUMLAH_JANJI_HUTANG_DIBAYAR` decimal(15,2) DEFAULT NULL,
  `KONDISI_DEBITUR` varchar(100) DEFAULT NULL,
  `KONDISI_AGUNAN` varchar(100) DEFAULT NULL,
  `KONDISI_USAHA_DEBITUR` varchar(100) DEFAULT NULL,
  `STATUS_HUNIAN` varchar(100) DEFAULT NULL,
  `KONDISI_LISTRIK` varchar(100) DEFAULT NULL,
  `KONDISI_AIR` varchar(100) DEFAULT NULL,
  `LUAS_BANGUNAN_AGUNAN` decimal(10,2) DEFAULT NULL,
  `LUAS_TANAH_AGUNAN` decimal(10,2) DEFAULT NULL,
  `JUMLAH_KAMAR_MANDI_AGUNAN` int DEFAULT NULL,
  `JUMLAH_KAMAR_TIDUR_AGUNAN` int DEFAULT NULL,
  `JUMLAH_LANTAI_AGUNAN` int DEFAULT NULL,
  `STATUS_MARKETABLE_AGUNAN` varchar(50) DEFAULT NULL,
  `KONDISI_BANJIR_AGUNAN` varchar(50) DEFAULT NULL,
  `SPESIFIKASI_AGUNAN` text,
  `JARAK_AGUNAN_KE_SEKOLAH` decimal(10,2) DEFAULT NULL,
  `JARAK_AGUNAN_KE_MALL` decimal(10,2) DEFAULT NULL,
  `JARAK_AGUNAN_KE_RUMAH_SAKIT` decimal(10,2) DEFAULT NULL,
  `JARAK_AGUNAN_KE_PUSAT_KOTA` decimal(10,2) DEFAULT NULL,
  `PROBLEM_DEBITUR` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `dt_hold`
--

CREATE TABLE `dt_hold` (
  `id` int NOT NULL AUTO_INCREMENT,
  `BANK` varchar(50) DEFAULT NULL,
  `LOANNUM` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CIF` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `OPEN_DATE` date DEFAULT NULL,
  `MATURITY_DATE` date DEFAULT NULL,
  `DUE_WO` varchar(35) DEFAULT NULL,
  `LOB` varchar(15) DEFAULT NULL,
  `ACCOUNT_STATUS` varchar(45) DEFAULT NULL,
  `PLAFOND` decimal(28,10) DEFAULT NULL,
  `OS` decimal(28,10) DEFAULT NULL,
  `kolektibilitas` varchar(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `DPD` int DEFAULT NULL,
  `TUNGGAKAN_POKOK` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_BUNGA` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_DENDA` decimal(28,10) DEFAULT NULL,
  `TOTAL_TUNGGAKAN` decimal(28,10) DEFAULT NULL,
  `ANGSURAN` decimal(28,10) DEFAULT NULL,
  `LOAN_PRODUK` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `NAMA_DEBITUR` varchar(60) DEFAULT NULL,
  `BANK_BRCODE` varchar(15) DEFAULT NULL,
  `BANK_CABANG` varchar(60) DEFAULT NULL,
  `CABANG_INTERNAL` varchar(50) DEFAULT NULL,
  `CABANG_BANK` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `KETERANGAN` varchar(800) DEFAULT NULL,
  `KETERANGAN_2` text CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  `LAST_PAYMENT` date DEFAULT NULL,
  `JADWAL_BAYAR` date DEFAULT NULL,
  `last_dpd` int DEFAULT NULL,
  `last_bucket` int DEFAULT NULL,
  `flow` varchar(10) DEFAULT NULL,
  `BAST` varchar(15) DEFAULT NULL,
  `TGL_BAST` date DEFAULT NULL,
  `TGL_UPDATE` date DEFAULT NULL,
  `WILAYAH` varchar(25) DEFAULT NULL,
  `ALAMAT_DOMISILI` varchar(250) DEFAULT NULL,
  `KELURAHAN` varchar(60) DEFAULT NULL,
  `KECAMATAN` varchar(60) DEFAULT NULL,
  `KOTA` varchar(60) DEFAULT NULL,
  `TELP` varchar(30) DEFAULT NULL,
  `NAMA_USAHA` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `ALAMAT_USAHA` varchar(250) DEFAULT NULL,
  `TELP_USAHA` varchar(60) DEFAULT NULL,
  `KONTAK_DARURAT` varchar(80) DEFAULT NULL,
  `TELP_DARURAT` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `HANDPHONE_1` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `HANDPHONE_2` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `ALAMAT_AGUNAN` varchar(500) DEFAULT NULL,
  `SCORE` int DEFAULT NULL,
  `KATEGORI` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `bucket` int DEFAULT NULL,
  `LAST_PAYMENT_DATE` date DEFAULT NULL,
  `last_payment_amount` double DEFAULT NULL,
  `WILAYAH_NGP` varchar(255) NOT NULL,
  `AREA_NGP` varchar(255) NOT NULL,
  `flug` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_master_loan` (`BANK`,`LOANNUM`)
) ENGINE=InnoDB AUTO_INCREMENT=890959 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_jadwal_kunjungan_debitur`
--

CREATE TABLE `dt_jadwal_kunjungan_debitur` (
  `id` int NOT NULL AUTO_INCREMENT,
  `MAPPING_ID` int DEFAULT NULL,
  `jadwal_kunjungan` date DEFAULT NULL,
  `sudah_dikunjungi` tinyint(1) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `dt_laporan_cabang`
--

CREATE TABLE `dt_laporan_cabang` (
  `id` int NOT NULL AUTO_INCREMENT,
  `wilayah_ngp` varchar(225) NOT NULL,
  `arcol` varchar(225) NOT NULL,
  `cabang_internal` varchar(255) NOT NULL,
  `principal` varchar(225) NOT NULL,
  `pic` varchar(225) NOT NULL,
  `status` varchar(25) DEFAULT NULL,
  `keterangan` text,
  `photo_kunjungan` varchar(50) DEFAULT NULL,
  `tgl_kunjungan` date NOT NULL,
  `input_date` date NOT NULL,
  `input_by` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=433 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `dt_master_BTN`
--

CREATE TABLE `dt_master_BTN` (
  `id` int NOT NULL AUTO_INCREMENT,
  `BANK` varchar(50) DEFAULT NULL,
  `LOANNUM` varchar(35) DEFAULT NULL,
  `CIF` varchar(35) DEFAULT NULL,
  `OPEN_DATE` date DEFAULT NULL,
  `MATURITY_DATE` date DEFAULT NULL,
  `DUE_WO` varchar(35) DEFAULT NULL,
  `LOB` varchar(15) DEFAULT NULL,
  `ACCOUNT_STATUS` varchar(45) DEFAULT NULL,
  `PLAFOND` decimal(28,10) DEFAULT NULL,
  `OS` decimal(28,10) DEFAULT NULL,
  `kolektibilitas` varchar(20) DEFAULT NULL,
  `DPD` int DEFAULT NULL,
  `TUNGGAKAN_POKOK` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_BUNGA` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_DENDA` decimal(28,10) DEFAULT NULL,
  `TOTAL_TUNGGAKAN` decimal(28,10) DEFAULT NULL,
  `ANGSURAN` decimal(28,10) DEFAULT NULL,
  `LOAN_PRODUK` varchar(100) DEFAULT NULL,
  `NAMA_DEBITUR` varchar(60) DEFAULT NULL,
  `BANK_BRCODE` varchar(15) DEFAULT NULL,
  `BANK_CABANG` varchar(60) DEFAULT NULL,
  `CABANG_INTERNAL` varchar(50) DEFAULT NULL,
  `CABANG_BANK` varchar(50) DEFAULT NULL,
  `KETERANGAN` varchar(800) DEFAULT NULL,
  `KETERANGAN_2` text,
  `LAST_PAYMENT` date DEFAULT NULL,
  `JADWAL_BAYAR` date DEFAULT NULL,
  `last_dpd` int DEFAULT NULL,
  `last_bucket` int DEFAULT NULL,
  `flow` varchar(10) DEFAULT NULL,
  `BAST` varchar(15) DEFAULT NULL,
  `TGL_BAST` date DEFAULT NULL,
  `TGL_UPDATE` date DEFAULT NULL,
  `WILAYAH` varchar(25) DEFAULT NULL,
  `ALAMAT_DOMISILI` varchar(250) DEFAULT NULL,
  `KELURAHAN` varchar(60) DEFAULT NULL,
  `KECAMATAN` varchar(60) DEFAULT NULL,
  `KOTA` varchar(60) DEFAULT NULL,
  `TELP` varchar(30) DEFAULT NULL,
  `NAMA_USAHA` varchar(250) DEFAULT NULL,
  `ALAMAT_USAHA` varchar(250) DEFAULT NULL,
  `TELP_USAHA` varchar(60) DEFAULT NULL,
  `KONTAK_DARURAT` varchar(150) DEFAULT NULL,
  `TELP_DARURAT` varchar(100) DEFAULT NULL,
  `HANDPHONE_1` varchar(50) DEFAULT NULL,
  `HANDPHONE_2` varchar(50) DEFAULT NULL,
  `ALAMAT_AGUNAN` varchar(500) DEFAULT NULL,
  `SCORE` int DEFAULT NULL,
  `KATEGORI` varchar(20) DEFAULT NULL,
  `bucket` int DEFAULT NULL,
  `LAST_PAYMENT_DATE` date DEFAULT NULL,
  `last_payment_amount` decimal(28,10) DEFAULT NULL,
  `WILAYAH_NGP` varchar(255) NOT NULL,
  `AREA_NGP` varchar(255) NOT NULL,
  `flug` varchar(11) DEFAULT NULL,
  `luas_bangunan` varchar(15) DEFAULT NULL,
  `luas_tanah` varchar(15) DEFAULT NULL,
  `pemilik_agunan` varchar(255) DEFAULT NULL,
  `TGL_DOWNLOAD` date DEFAULT NULL,
  `REKENING` varchar(35) DEFAULT NULL,
  `keterangan_account` varchar(255) DEFAULT NULL,
  `NO_VA` varchar(35) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_master_loan` (`BANK`,`LOANNUM`)
) ENGINE=InnoDB AUTO_INCREMENT=967860 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_master_collateral`
--

CREATE TABLE `dt_master_collateral` (
  `id` int NOT NULL AUTO_INCREMENT,
  `coll_cif` varchar(25) DEFAULT NULL,
  `coll_no` varchar(500) DEFAULT NULL,
  `coll_descr` varchar(500) DEFAULT NULL,
  `coll_provinsi` varchar(200) DEFAULT NULL,
  `coll_kota` varchar(200) DEFAULT NULL,
  `coll_address` varchar(500) DEFAULT NULL,
  `coll_value` double DEFAULT NULL,
  `coll_valu_np` double DEFAULT NULL,
  `tgl_appraisal` date DEFAULT NULL,
  `luas_tanah` varchar(15) NOT NULL,
  `luas_bangunan` varchar(15) NOT NULL,
  `nilai_pasar` decimal(15,3) NOT NULL,
  `doc_penyerahan_debitur` varchar(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `status_jual_agunan` tinyint(1) NOT NULL DEFAULT '0',
  `jenis_penjualan` varchar(40) DEFAULT NULL,
  `foto_agunan` varchar(100) DEFAULT NULL,
  `create_on` datetime DEFAULT NULL,
  `create_by` int DEFAULT NULL,
  `BANK` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=148703 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_master_document`
--

CREATE TABLE `dt_master_document` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_cif` varchar(15) NOT NULL,
  `doc_title` varchar(100) NOT NULL,
  `doc_no` varchar(30) DEFAULT NULL,
  `doc_descr` varchar(200) NOT NULL,
  `doc_file` varchar(100) NOT NULL,
  `create_on` date DEFAULT NULL,
  `create_by` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_master_loan`
--

CREATE TABLE `dt_master_loan` (
  `id` int NOT NULL AUTO_INCREMENT,
  `BANK` varchar(50) DEFAULT NULL,
  `LOANNUM` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CIF` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `OPEN_DATE` date DEFAULT NULL,
  `MATURITY_DATE` date DEFAULT NULL,
  `DUE_WO` varchar(35) DEFAULT NULL,
  `LOB` varchar(15) DEFAULT NULL,
  `ACCOUNT_STATUS` varchar(45) DEFAULT NULL,
  `PLAFOND` decimal(28,10) DEFAULT NULL,
  `OS` decimal(28,10) DEFAULT NULL,
  `kolektibilitas` varchar(20) DEFAULT NULL,
  `DPD` int DEFAULT NULL,
  `TUNGGAKAN_POKOK` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_BUNGA` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_DENDA` decimal(28,10) DEFAULT NULL,
  `TOTAL_TUNGGAKAN` decimal(28,10) DEFAULT NULL,
  `ANGSURAN` decimal(28,10) DEFAULT NULL,
  `LOAN_PRODUK` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `NAMA_DEBITUR` varchar(60) DEFAULT NULL,
  `BANK_BRCODE` varchar(15) DEFAULT NULL,
  `BANK_CABANG` varchar(60) DEFAULT NULL,
  `CABANG_INTERNAL` varchar(50) DEFAULT NULL,
  `CABANG_BANK` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `KETERANGAN` varchar(800) DEFAULT NULL,
  `KETERANGAN_2` text CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  `LAST_PAYMENT` date DEFAULT NULL,
  `JADWAL_BAYAR` date DEFAULT NULL,
  `last_dpd` int DEFAULT NULL,
  `last_bucket` int DEFAULT NULL,
  `flow` varchar(10) DEFAULT NULL,
  `BAST` varchar(15) DEFAULT NULL,
  `TGL_BAST` date DEFAULT NULL,
  `TGL_UPDATE` date DEFAULT NULL,
  `WILAYAH` varchar(225) DEFAULT NULL,
  `ALAMAT_DOMISILI` varchar(250) DEFAULT NULL,
  `KELURAHAN` varchar(60) DEFAULT NULL,
  `KECAMATAN` varchar(60) DEFAULT NULL,
  `KOTA` varchar(60) DEFAULT NULL,
  `TELP` varchar(30) DEFAULT NULL,
  `NAMA_USAHA` varchar(250) DEFAULT NULL,
  `ALAMAT_USAHA` varchar(250) DEFAULT NULL,
  `TELP_USAHA` varchar(60) DEFAULT NULL,
  `KONTAK_DARURAT` varchar(150) DEFAULT NULL,
  `TELP_DARURAT` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `HANDPHONE_1` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `HANDPHONE_2` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `ALAMAT_AGUNAN` varchar(500) DEFAULT NULL,
  `SCORE` int DEFAULT NULL,
  `KATEGORI` varchar(20) DEFAULT NULL,
  `bucket` int DEFAULT NULL,
  `LAST_PAYMENT_DATE` date DEFAULT NULL,
  `last_payment_amount` double DEFAULT NULL,
  `WILAYAH_NGP` varchar(255) NOT NULL,
  `AREA_NGP` varchar(255) NOT NULL,
  `flug` varchar(11) DEFAULT NULL,
  `luas_bangunan` varchar(15) DEFAULT NULL,
  `luas_tanah` varchar(15) DEFAULT NULL,
  `pemilik_agunan` varchar(255) DEFAULT NULL,
  `TGL_DOWNLOAD` date DEFAULT NULL,
  `REKENING` varchar(35) DEFAULT NULL,
  `keterangan_account` varchar(255) DEFAULT NULL,
  `NO_VA` varchar(35) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_master_loan` (`BANK`,`LOANNUM`)
) ENGINE=InnoDB AUTO_INCREMENT=981304 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_master_location`
--

CREATE TABLE `dt_master_location` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `loannum` varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `lat_agunan` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `long_agunan` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `lat_debitur` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `long_debitur` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `added_by` int DEFAULT NULL,
  `added_date` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `updated_date` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22481 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `dt_master_photo`
--

CREATE TABLE `dt_master_photo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pic_cif` varchar(15) NOT NULL,
  `pic_title` varchar(100) NOT NULL,
  `pic_file` varchar(100) NOT NULL,
  `pic_category` varchar(15) NOT NULL,
  `create_on` date NOT NULL,
  `create_by` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_master_position`
--

CREATE TABLE `dt_master_position` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id_wilayah` int NOT NULL,
  `id_jabatan` int NOT NULL,
  `id_user` int NOT NULL,
  `id_atasan` int NOT NULL,
  `create_on` datetime NOT NULL,
  `create_by` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `dt_master_report`
--

CREATE TABLE `dt_master_report` (
  `id` int NOT NULL AUTO_INCREMENT,
  `loanno` varchar(20) NOT NULL,
  `penanganan_mulai` tinytext,
  `penanganan_akhir` tinytext,
  `pic` varchar(35) DEFAULT NULL,
  `hp_pic` varchar(20) DEFAULT NULL,
  `kunjungan` varchar(20) DEFAULT NULL,
  `upd_hp_debitur` varchar(20) DEFAULT NULL,
  `kondisi_agunan` varchar(30) DEFAULT NULL,
  `kategori_debitur` varchar(20) DEFAULT NULL,
  `penyebab_tunggakan` varchar(50) DEFAULT NULL,
  `action_plan` varchar(50) DEFAULT NULL,
  `tgl_komitmen_dibuat` tinytext,
  `tgl_janji_bayar` tinytext,
  `rencana_bayar` decimal(28,10) DEFAULT NULL,
  `realisasi_bayar` decimal(28,10) DEFAULT NULL,
  `realisasi_bayar_mtd` decimal(28,10) DEFAULT NULL,
  `keterangan` varchar(50) DEFAULT NULL,
  `cif` varchar(20) DEFAULT NULL,
  `usulan_penanganan` varchar(25) DEFAULT NULL,
  `alasan_dikembalikan` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loanno` (`loanno`)
) ENGINE=InnoDB AUTO_INCREMENT=64295 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_partial_payment`
--

CREATE TABLE `dt_partial_payment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `acctno` varchar(25) NOT NULL,
  `plan_amount` decimal(15,3) NOT NULL,
  `dt_start` date NOT NULL,
  `dt_end` date NOT NULL,
  `pic` int NOT NULL,
  `status` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_partial_payment_hist`
--

CREATE TABLE `dt_partial_payment_hist` (
  `id` int NOT NULL AUTO_INCREMENT,
  `partial_plan_id` int NOT NULL,
  `pay_date` date NOT NULL,
  `pay_amount` double(15,3) NOT NULL,
  `input_date` date NOT NULL,
  `input_by` int NOT NULL,
  `descr` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_photo_laporan_principal`
--

CREATE TABLE `dt_photo_laporan_principal` (
  `id_photo` int NOT NULL AUTO_INCREMENT,
  `id_laporan_cabang` int NOT NULL,
  `name_photo` varchar(50) DEFAULT NULL,
  `deleted` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_photo`)
) ENGINE=InnoDB AUTO_INCREMENT=615 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `dt_pushNotify`
--

CREATE TABLE `dt_pushNotify` (
  `id` int NOT NULL AUTO_INCREMENT,
  `toid` int DEFAULT NULL,
  `msg` varchar(150) DEFAULT NULL,
  `contentid` int DEFAULT NULL,
  `crdate` datetime DEFAULT NULL,
  `fromid` int DEFAULT NULL,
  `isread` char(1) DEFAULT 'N',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=362140 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_somasi`
--

CREATE TABLE `dt_somasi` (
  `id` int NOT NULL AUTO_INCREMENT,
  `cif` varchar(20) NOT NULL,
  `pic` int NOT NULL,
  `no_surat` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `tgl_somasi` date DEFAULT NULL,
  `note` text NOT NULL,
  `input_date` date NOT NULL,
  `finish_date` date DEFAULT NULL,
  `somasi_idx` int NOT NULL,
  `status` varchar(10) DEFAULT 'PENGAJUAN',
  `file_somasi` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `status_somasi` varchar(35) DEFAULT NULL,
  `tgl_deskcall` date DEFAULT NULL,
  `user_deskcall` int DEFAULT NULL,
  `keterangan_deskcall` text,
  `status_deskcall` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6648 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `dt_sumanalisadebitur`
--

CREATE TABLE `dt_sumanalisadebitur` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `bank_cabang` varchar(35) DEFAULT NULL,
  `noa` bigint DEFAULT NULL,
  `os` double DEFAULT NULL,
  `kunjungan` varchar(20) DEFAULT NULL,
  `kategori_debitur` varchar(20) DEFAULT NULL,
  `kondisi_agunan` varchar(35) DEFAULT NULL,
  `action_plan` varchar(40) DEFAULT NULL,
  `penyebab_tunggakan` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1254 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_surat_resmi`
--

CREATE TABLE `dt_surat_resmi` (
  `id` int NOT NULL AUTO_INCREMENT,
  `cif` varchar(20) NOT NULL,
  `pic` int NOT NULL,
  `no_surat` varchar(30) DEFAULT NULL,
  `tgl_pengajuan` date DEFAULT NULL,
  `note` text NOT NULL,
  `input_date` date NOT NULL,
  `finish_date` date DEFAULT NULL,
  `somasi_idx` int NOT NULL,
  `status` varchar(10) DEFAULT 'PENGAJUAN',
  `file_somasi` varchar(50) DEFAULT NULL,
  `status_somasi` varchar(35) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `dt_surat_tugas`
--

CREATE TABLE `dt_surat_tugas` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pic_id` int NOT NULL,
  `date_create` date NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `no_surat` varchar(50) NOT NULL,
  `principal` varchar(30) NOT NULL,
  `file` varchar(150) NOT NULL,
  `input_by` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `dt_target`
--

CREATE TABLE `dt_target` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bank` varchar(15) NOT NULL,
  `cabang` int DEFAULT NULL,
  `target` decimal(10,0) DEFAULT NULL,
  `period` date DEFAULT NULL,
  `create_on` date DEFAULT NULL,
  `create_by` int DEFAULT NULL,
  `update_on` date DEFAULT NULL,
  `update_by` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_temp_tgl_penagihan`
--

CREATE TABLE `dt_temp_tgl_penagihan` (
  `id` int NOT NULL AUTO_INCREMENT,
  `loanno` varchar(35) NOT NULL,
  `tgl_temporary` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21462 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `dt_top`
--

CREATE TABLE `dt_top` (
  `id` int NOT NULL AUTO_INCREMENT,
  `loanno` varchar(15) NOT NULL,
  `userid` int NOT NULL,
  `flag` char(3) NOT NULL DEFAULT 'TOP',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1255 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_user`
--

CREATE TABLE `dt_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `roleid` int NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `status` char(1) NOT NULL DEFAULT '1',
  `fullname` varchar(35) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `gender` int DEFAULT NULL,
  `branch` varchar(30) NOT NULL,
  `branchbank` varchar(30) DEFAULT NULL,
  `position` int NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  `hp` varchar(15) DEFAULT NULL,
  `hp2` varchar(15) DEFAULT NULL,
  `nomor_ktp` varchar(25) DEFAULT NULL,
  `photo` varchar(100) DEFAULT NULL,
  `create_on` datetime DEFAULT NULL,
  `create_by` int DEFAULT NULL,
  `update_on` datetime DEFAULT NULL,
  `update_by` int DEFAULT NULL,
  `rstat` char(1) DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `fromIP` varchar(20) DEFAULT NULL,
  `device` varchar(200) DEFAULT NULL,
  `rek_bank` varchar(35) DEFAULT NULL,
  `rek_no` varchar(25) DEFAULT NULL,
  `rek_nama` varchar(50) DEFAULT NULL,
  `principal` varchar(25) DEFAULT NULL,
  `validasi` char(1) DEFAULT '0',
  `validasi_token` varchar(10) DEFAULT NULL,
  `wilayah` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2364 DEFAULT CHARSET=latin1;

--
-- Table structure for table `dt_user_logs`
--

CREATE TABLE `dt_user_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userid` int NOT NULL,
  `logtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ipaddr` varchar(20) NOT NULL,
  `device` varchar(200) NOT NULL,
  `lon` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `lat` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=57182 DEFAULT CHARSET=latin1;

--
-- Table structure for table `hasil_dt_master_loan`
--

CREATE TABLE `hasil_dt_master_loan` (
  `BANK` varchar(100) DEFAULT NULL,
  `CUSTOMERID` varchar(50) NOT NULL,
  `NOMOR_PINJAMAN` varchar(50) DEFAULT NULL,
  `NO_REKENING` varchar(50) DEFAULT NULL,
  `SISA_PINJAMAN` decimal(15,2) DEFAULT NULL,
  `TOTAL_TUNGGAKAN` decimal(15,2) DEFAULT NULL,
  `NAMA_DEBITUR` varchar(255) DEFAULT NULL,
  `AREA` varchar(100) DEFAULT NULL,
  `KANTOR_CABANG` varchar(100) DEFAULT NULL,
  `ALAMAT_DOMISILI` text,
  `KELURAHAN` varchar(100) DEFAULT NULL,
  `KECAMATAN` varchar(100) DEFAULT NULL,
  `KOTA` varchar(100) DEFAULT NULL,
  `TELP` varchar(50) DEFAULT NULL,
  `NAMA_USAHA` varchar(255) DEFAULT NULL,
  `ALAMAT_USAHA` text,
  `TELP_USAHA` varchar(50) DEFAULT NULL,
  `ALAMAT_AGUNAN` text,
  `LUAS_AGUNAN` decimal(10,2) DEFAULT NULL,
  `LUAS_TANAH` decimal(10,2) DEFAULT NULL,
  `PEMILIK_AGUNAN` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`CUSTOMERID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Temporary view structure for view `mapping_debitur`
--

/*!50001 DROP VIEW IF EXISTS `mapping_debitur`*/;
/*!50001 CREATE VIEW `mapping_debitur` AS SELECT 
 1 AS `id`,
 1 AS `loanno`,
 1 AS `FCCode`,
 1 AS `start_date`,
 1 AS `end_date`,
 1 AS `current`,
 1 AS `update_on`,
 1 AS `update_by`,
 1 AS `fullname`*/;

--
-- Table structure for table `ref_action`
--

CREATE TABLE `ref_action` (
  `id` int NOT NULL,
  `action` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_action_plan`
--

CREATE TABLE `ref_action_plan` (
  `ID` int NOT NULL,
  `LABEL` varchar(54) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_action_result`
--

CREATE TABLE `ref_action_result` (
  `id` int NOT NULL AUTO_INCREMENT,
  `action` int NOT NULL,
  `result` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_agunan_marketable`
--

CREATE TABLE `ref_agunan_marketable` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LABEL` varchar(30) NOT NULL,
  `SCORE` int NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_area_banjir`
--

CREATE TABLE `ref_area_banjir` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LABEL` varchar(30) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_area_ngp_bank`
--

CREATE TABLE `ref_area_ngp_bank` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `BANK` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `AREA_NGP` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `wa_group_owner` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
  `wa_group_id` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `ref_bank`
--

CREATE TABLE `ref_bank` (
  `id` int NOT NULL AUTO_INCREMENT,
  `label` varchar(30) DEFAULT NULL,
  `KP` char(1) DEFAULT 'Y',
  `FC` char(1) DEFAULT 'Y',
  `FULL_LABEL` varchar(35) NOT NULL,
  `logo` varchar(35) NOT NULL,
  `segmen` varchar(25) NOT NULL,
  `wa_group_owner` varchar(20) NOT NULL,
  `wa_group_id` varchar(20) NOT NULL,
  `deleted` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_branch`
--

CREATE TABLE `ref_branch` (
  `BRANCHID` varchar(3) NOT NULL,
  `BRANCH` varchar(50) DEFAULT NULL,
  `AREA` varchar(50) DEFAULT NULL,
  `WILAYAH` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`BRANCHID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_cabang_bank`
--

CREATE TABLE `ref_cabang_bank` (
  `BANK` varchar(50) DEFAULT NULL,
  `CABANG_INTERNAL` varchar(50) DEFAULT NULL,
  `CABANG_BANK` varchar(62) DEFAULT NULL,
  `ID` int NOT NULL,
  `IBRCODE` int DEFAULT NULL,
  `wa_group_owner` varchar(20) NOT NULL,
  `wa_group_id` varchar(20) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_collect`
--

CREATE TABLE `ref_collect` (
  `id` int NOT NULL,
  `bucket` varchar(2) DEFAULT NULL,
  `kol` varchar(2) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_collect_result`
--

CREATE TABLE `ref_collect_result` (
  `id` int NOT NULL AUTO_INCREMENT,
  `result` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_deb_class_item`
--

CREATE TABLE `ref_deb_class_item` (
  `id` int NOT NULL AUTO_INCREMENT,
  `category` varchar(20) DEFAULT NULL,
  `label` varchar(45) DEFAULT NULL,
  `points` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_deb_classification`
--

CREATE TABLE `ref_deb_classification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `low` int DEFAULT NULL,
  `up` int DEFAULT NULL,
  `label` varchar(20) DEFAULT NULL,
  `kategori` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_gender`
--

CREATE TABLE `ref_gender` (
  `id` int NOT NULL,
  `gender` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_jabatan`
--

CREATE TABLE `ref_jabatan` (
  `id` int NOT NULL AUTO_INCREMENT,
  `jabatan` varchar(50) NOT NULL,
  `atasan` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `ref_karakter_debitur`
--

CREATE TABLE `ref_karakter_debitur` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LABEL` varchar(30) NOT NULL,
  `SCORE` int NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_kelurahan`
--

CREATE TABLE `ref_kelurahan` (
  `id` int NOT NULL AUTO_INCREMENT,
  `kelurahan` varchar(35) NOT NULL,
  `kecamatan` varchar(35) NOT NULL,
  `kota` varchar(35) NOT NULL,
  `lat` varchar(15) DEFAULT NULL,
  `lng` varchar(15) DEFAULT NULL,
  `cif` int DEFAULT NULL,
  `os` double DEFAULT NULL,
  `cabang_internal` varchar(35) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31962871 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_kesimpulan`
--

CREATE TABLE `ref_kesimpulan` (
  `id` int NOT NULL AUTO_INCREMENT,
  `label` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `ref_kondisi_agunan`
--

CREATE TABLE `ref_kondisi_agunan` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LABEL` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_kondisi_debitur`
--

CREATE TABLE `ref_kondisi_debitur` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LABEL` varchar(30) DEFAULT NULL,
  `SCORE` int NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_kondisi_usaha`
--

CREATE TABLE `ref_kondisi_usaha` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LABEL` varchar(30) NOT NULL,
  `SCORE` int NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_kontak_debitur`
--

CREATE TABLE `ref_kontak_debitur` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LABEL` varchar(30) NOT NULL,
  `SCORE` int NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_listrik`
--

CREATE TABLE `ref_listrik` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LABEL` varchar(30) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_loanStatus`
--

CREATE TABLE `ref_loanStatus` (
  `id` int NOT NULL AUTO_INCREMENT,
  `label` varchar(20) DEFAULT NULL,
  `blabel` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_mapping`
--

CREATE TABLE `ref_mapping` (
  `id` int NOT NULL AUTO_INCREMENT,
  `label` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_pdam`
--

CREATE TABLE `ref_pdam` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LABEL` varchar(30) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_penanganan`
--

CREATE TABLE `ref_penanganan` (
  `ID` int NOT NULL,
  `LABEL` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_penyebab_tunggakan`
--

CREATE TABLE `ref_penyebab_tunggakan` (
  `ID` int NOT NULL,
  `LABEL` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_position`
--

CREATE TABLE `ref_position` (
  `id` int NOT NULL AUTO_INCREMENT,
  `position` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_problem_debt`
--

CREATE TABLE `ref_problem_debt` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LABEL` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_rekomendasi`
--

CREATE TABLE `ref_rekomendasi` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LABEL` varchar(35) NOT NULL,
  `nomor_hp` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `nomor_wag` varchar(50) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `ref_relasi`
--

CREATE TABLE `ref_relasi` (
  `LABEL` varchar(25) NOT NULL,
  PRIMARY KEY (`LABEL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `ref_role`
--

CREATE TABLE `ref_role` (
  `id` int NOT NULL AUTO_INCREMENT,
  `description` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_scoring_value`
--

CREATE TABLE `ref_scoring_value` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `SUBJECT` varchar(30) NOT NULL,
  `SCORE` int NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_setting`
--

CREATE TABLE `ref_setting` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `title` varchar(20) DEFAULT NULL,
  `value` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_status`
--

CREATE TABLE `ref_status` (
  `id` int NOT NULL,
  `label` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_status_hunian`
--

CREATE TABLE `ref_status_hunian` (
  `ID` int NOT NULL,
  `LABEL` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `ref_status_somasi`
--

CREATE TABLE `ref_status_somasi` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LABEL` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ref_wilayah`
--

CREATE TABLE `ref_wilayah` (
  `id` int NOT NULL AUTO_INCREMENT,
  `wilayah` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `ref_working_day`
--

CREATE TABLE `ref_working_day` (
  `id` int NOT NULL AUTO_INCREMENT,
  `period_date` date NOT NULL,
  `period_group` date NOT NULL,
  `business_day` varchar(10) NOT NULL,
  `last_period_group` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=731 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `rpt_ao_productivity`
--

CREATE TABLE `rpt_ao_productivity` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uid` int NOT NULL,
  `fullname` varchar(50) NOT NULL,
  `bank` varchar(15) NOT NULL,
  `branch` varchar(50) NOT NULL,
  `telp` int NOT NULL,
  `visit` int NOT NULL,
  `cif_bayar` int NOT NULL,
  `amm_col` decimal(12,3) NOT NULL,
  `cif_ptp` int NOT NULL,
  `amm_ptp` int NOT NULL,
  `period_date` date NOT NULL,
  `period_group` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=256 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `rpt_penanganan`
--

CREATE TABLE `rpt_penanganan` (
  `id` int NOT NULL AUTO_INCREMENT,
  `LOANNUM` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `NAMA_DEBITUR` varchar(50) NOT NULL,
  `PRINCIPAL` varchar(20) NOT NULL,
  `CABANG_INTERNAL` varchar(50) NOT NULL,
  `CABANG_BANK` varchar(50) NOT NULL,
  `OS` decimal(28,10) DEFAULT NULL,
  `TELP` int NOT NULL,
  `VISIT` int NOT NULL,
  `PIC` varchar(50) NOT NULL,
  `PIC_ID` int DEFAULT NULL,
  `START_DATE` date DEFAULT NULL,
  `END_DATE` date DEFAULT NULL,
  `FLAG` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2676671 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tmp_before_deskcall`
--

CREATE TABLE `tmp_before_deskcall` (
  `id` int NOT NULL DEFAULT '0',
  `BANK` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `LOANNUM` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CIF` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `OPEN_DATE` date DEFAULT NULL,
  `MATURITY_DATE` date DEFAULT NULL,
  `DUE_WO` varchar(35) CHARACTER SET latin1 DEFAULT NULL,
  `LOB` varchar(15) CHARACTER SET latin1 DEFAULT NULL,
  `ACCOUNT_STATUS` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
  `PLAFOND` decimal(28,10) DEFAULT NULL,
  `OS` decimal(28,10) DEFAULT NULL,
  `kolektibilitas` varchar(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `DPD` int DEFAULT NULL,
  `TUNGGAKAN_POKOK` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_BUNGA` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_DENDA` decimal(28,10) DEFAULT NULL,
  `TOTAL_TUNGGAKAN` decimal(28,10) DEFAULT NULL,
  `ANGSURAN` decimal(28,10) DEFAULT NULL,
  `LOAN_PRODUK` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `NAMA_DEBITUR` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  `BANK_BRCODE` varchar(15) CHARACTER SET latin1 DEFAULT NULL,
  `BANK_CABANG` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  `CABANG_INTERNAL` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `CABANG_BANK` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `KETERANGAN` varchar(800) CHARACTER SET latin1 DEFAULT NULL,
  `KETERANGAN_2` text CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  `LAST_PAYMENT` date DEFAULT NULL,
  `JADWAL_BAYAR` date DEFAULT NULL,
  `last_dpd` int DEFAULT NULL,
  `last_bucket` int DEFAULT NULL,
  `flow` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `BAST` varchar(15) CHARACTER SET latin1 DEFAULT NULL,
  `TGL_BAST` date DEFAULT NULL,
  `TGL_UPDATE` date DEFAULT NULL,
  `WILAYAH` varchar(25) CHARACTER SET latin1 DEFAULT NULL,
  `ALAMAT_DOMISILI` varchar(250) CHARACTER SET latin1 DEFAULT NULL,
  `KELURAHAN` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  `KECAMATAN` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  `KOTA` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  `TELP` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `NAMA_USAHA` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `ALAMAT_USAHA` varchar(250) CHARACTER SET latin1 DEFAULT NULL,
  `TELP_USAHA` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  `KONTAK_DARURAT` varchar(80) CHARACTER SET latin1 DEFAULT NULL,
  `TELP_DARURAT` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `HANDPHONE_1` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `HANDPHONE_2` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `ALAMAT_AGUNAN` varchar(300) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `SCORE` int DEFAULT NULL,
  `KATEGORI` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `bucket` int DEFAULT NULL,
  `LAST_PAYMENT_DATE` date DEFAULT NULL,
  `WILAYAH_NGP` varchar(255) CHARACTER SET latin1 NOT NULL,
  `AREA_NGP` varchar(255) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tmp_collect_hist`
--

CREATE TABLE `tmp_collect_hist` (
  `id` int NOT NULL AUTO_INCREMENT,
  `cif` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `loanno` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `action` int DEFAULT NULL,
  `action_date` date DEFAULT NULL,
  `respon` int DEFAULT NULL,
  `result` int DEFAULT NULL,
  `pic` int DEFAULT NULL,
  `remarks` text CHARACTER SET latin1,
  `next_action` int DEFAULT NULL,
  `next_action_date` date DEFAULT NULL,
  `amm_col` double DEFAULT '0',
  `amm_pokok` double DEFAULT '0',
  `amm_denda` double DEFAULT '0',
  `amm_bunga` double DEFAULT '0',
  `amm_ptp` double DEFAULT '0',
  `verified` char(1) CHARACTER SET latin1 NOT NULL DEFAULT 'N',
  `verified_on` date DEFAULT NULL,
  `verified_by` int DEFAULT NULL,
  `verified_file_slip` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `verified_valid` char(1) CHARACTER SET latin1 DEFAULT 'N',
  `verified_note` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
  `create_on` date DEFAULT '1900-01-01',
  `create_by` int DEFAULT NULL,
  `update_on` date DEFAULT NULL,
  `update_by` int DEFAULT NULL,
  `latest` char(1) CHARACTER SET latin1 DEFAULT 'Y',
  `photo_visit` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `upd_hp_deb` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `lon` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `lat` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `orang_ditemui` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `relasi_kedebitur` varchar(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT '',
  `kontak_debitur` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `kondisi_debitur` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `rencana_penyelesaian` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `kondisi_jaminan` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `karakter_debitur` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `kondisi_usaha` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `coll_rekomendasi` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `PRINCIPAL` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  `kesimpulan` int DEFAULT '0',
  `keterangan_kesimpulan` text CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  `tgl_input_kesimpulan` date DEFAULT NULL,
  `user_input_kesimpulan` int DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=223684 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tmp_master_loan`
--

CREATE TABLE `tmp_master_loan` (
  `id` int NOT NULL AUTO_INCREMENT,
  `BANK` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `LOANNUM` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CIF` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `OPEN_DATE` date DEFAULT NULL,
  `MATURITY_DATE` date DEFAULT NULL,
  `DUE_WO` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `LOB` varchar(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `ACCOUNT_STATUS` varchar(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `PLAFOND` decimal(28,10) DEFAULT NULL,
  `OS` decimal(28,10) DEFAULT NULL,
  `kolektibilitas` varchar(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `DPD` int DEFAULT NULL,
  `TUNGGAKAN_POKOK` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_BUNGA` decimal(28,10) DEFAULT NULL,
  `TUNGGAKAN_DENDA` decimal(28,10) DEFAULT NULL,
  `TOTAL_TUNGGAKAN` decimal(28,10) DEFAULT NULL,
  `ANGSURAN` decimal(28,10) DEFAULT NULL,
  `LOAN_PRODUK` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `NAMA_DEBITUR` varchar(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `BANK_BRCODE` varchar(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `BANK_CABANG` varchar(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CABANG_INTERNAL` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CABANG_BANK` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `KETERANGAN` varchar(800) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `KETERANGAN_2` text CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  `LAST_PAYMENT` date DEFAULT NULL,
  `JADWAL_BAYAR` date DEFAULT NULL,
  `last_dpd` int DEFAULT NULL,
  `last_bucket` int DEFAULT NULL,
  `flow` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `BAST` varchar(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `TGL_BAST` date DEFAULT NULL,
  `TGL_UPDATE` date DEFAULT NULL,
  `WILAYAH` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `ALAMAT_DOMISILI` varchar(250) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `KELURAHAN` varchar(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `KECAMATAN` varchar(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `KOTA` varchar(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `TELP` varchar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `NAMA_USAHA` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `ALAMAT_USAHA` varchar(250) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `TELP_USAHA` varchar(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `KONTAK_DARURAT` varchar(80) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `TELP_DARURAT` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `HANDPHONE_1` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `HANDPHONE_2` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `ALAMAT_AGUNAN` varchar(300) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `SCORE` int DEFAULT NULL,
  `KATEGORI` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `bucket` int DEFAULT NULL,
  `LAST_PAYMENT_DATE` date DEFAULT NULL,
  `WILAYAH_NGP` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `AREA_NGP` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=888796 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `tmp_scoring`
--

CREATE TABLE `tmp_scoring` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LOANNUM` varchar(20) NOT NULL,
  `kontak_debitur` int NOT NULL,
  `kondisi_debitur` int NOT NULL,
  `kondisi_jaminan` int NOT NULL,
  `karakter_debitur` int NOT NULL,
  `kondisi_usaha` int NOT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Final view structure for view `BANK_MTD_AMCOL`
--

/*!50001 DROP VIEW IF EXISTS `BANK_MTD_AMCOL`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`doadmin`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `BANK_MTD_AMCOL` AS select `A`.`BANK` AS `BANK`,`A`.`CABANG` AS `CABANG`,`A`.`OS` AS `OS`,`A`.`NOA` AS `TOTNOA`,`B`.`AMCOL` AS `AMCOL`,`B`.`AMCOLNOA` AS `AMCOLNOA` from ((select `dt_master_loan`.`BANK` AS `BANK`,`dt_master_loan`.`CABANG_INTERNAL` AS `CABANG`,sum(`dt_master_loan`.`OS`) AS `OS`,count(0) AS `NOA` from `dt_master_loan` group by `dt_master_loan`.`BANK`,`dt_master_loan`.`CABANG_INTERNAL`) `A` left join (select `b`.`BANK` AS `BANK`,`b`.`CABANG_INTERNAL` AS `CABANG`,sum(`a`.`amm_col`) AS `AMCOL`,count(0) AS `AMCOLNOA` from (`dt_collect_hist` `a` join `dt_master_loan` `b` on((`a`.`loanno` = `b`.`LOANNUM`))) where ((`a`.`verified_valid` = 'Y') and (month(`a`.`action_date`) = month(now())) and (year(`a`.`action_date`) = year(now()))) group by `b`.`BANK`,`b`.`CABANG_INTERNAL`) `B` on(((`A`.`BANK` = `B`.`BANK`) and (`A`.`CABANG` = `B`.`CABANG`)))) where (`A`.`BANK` <> '') */;

--
-- Final view structure for view `LIST_RAW_AMCOL`
--

/*!50001 DROP VIEW IF EXISTS `LIST_RAW_AMCOL`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`doadmin`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `LIST_RAW_AMCOL` AS select `A`.`BANK` AS `BANK`,`A`.`LOANNUM` AS `LOANNUM`,`A`.`NAMA_DEBITUR` AS `NAMA_DEBITUR`,`A`.`CABANG_INTERNAL` AS `CABANG_INTERNAL`,ifnull(`B`.`YTDAmcol`,0) AS `YTDAmcol`,ifnull(`B`.`YTDAmcolNOA`,0) AS `YTDAmcolNOA`,ifnull(`B`.`MTDAmcol`,0) AS `MTDAmcol`,ifnull(`B`.`MTDAmcolNOA`,0) AS `MTDAmcolNOA`,ifnull(`B`.`TDAmcol`,0) AS `TDAmcol`,ifnull(`B`.`TDAmcolNOA`,0) AS `TDAmcolNOA` from (`dt_master_loan` `A` join (select `dt_collect_hist`.`loanno` AS `loanno`,`dt_collect_hist`.`amm_col` AS `amm_col`,(case when (year(`dt_collect_hist`.`action_date`) = year(now())) then `dt_collect_hist`.`amm_col` else 0 end) AS `YTDAmcol`,(case when (year(`dt_collect_hist`.`action_date`) = year(now())) then 1 else 0 end) AS `YTDAmcolNOA`,(case when ((year(`dt_collect_hist`.`action_date`) = year(now())) and (month(`dt_collect_hist`.`action_date`) = month(now()))) then `dt_collect_hist`.`amm_col` else 0 end) AS `MTDAmcol`,(case when ((year(`dt_collect_hist`.`action_date`) = year(now())) and (month(`dt_collect_hist`.`action_date`) = month(now()))) then 1 else 0 end) AS `MTDAmcolNOA`,(case when (date_format(`dt_collect_hist`.`action_date`,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')) then `dt_collect_hist`.`amm_col` else 0 end) AS `TDAmcol`,(case when (date_format(`dt_collect_hist`.`action_date`,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')) then 1 else 0 end) AS `TDAmcolNOA` from `dt_collect_hist` where ((`dt_collect_hist`.`amm_col` > 0) and (`dt_collect_hist`.`verified_valid` = 'Y'))) `B` on((`A`.`LOANNUM` = `B`.`loanno`))) */;

--
-- Final view structure for view `detail_mtd_amcoll`
--

/*!50001 DROP VIEW IF EXISTS `detail_mtd_amcoll`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`doadmin`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `detail_mtd_amcoll` AS select `B`.`BANK` AS `BANK`,`B`.`CABANG_INTERNAL` AS `CABANG_INTERNAL`,`B`.`NAMA_DEBITUR` AS `NAMA_DEBITUR`,`A`.`loanno` AS `loanno`,`A`.`amm_col` AS `amm_col`,`A`.`pic` AS `pic`,`C`.`fullname` AS `fullname`,`A`.`action_date` AS `action_date` from (((select distinct `dt_collect_hist`.`loanno` AS `loanno`,`dt_collect_hist`.`amm_col` AS `amm_col`,`dt_collect_hist`.`pic` AS `pic`,`dt_collect_hist`.`action_date` AS `action_date` from `dt_collect_hist` where ((month(`dt_collect_hist`.`action_date`) = month(now())) and (year(`dt_collect_hist`.`action_date`) = year(now())) and (`dt_collect_hist`.`verified_valid` = 'Y'))) `A` join (select `dt_master_loan`.`BANK` AS `BANK`,`dt_master_loan`.`CABANG_INTERNAL` AS `CABANG_INTERNAL`,`dt_master_loan`.`NAMA_DEBITUR` AS `NAMA_DEBITUR`,`dt_master_loan`.`LOANNUM` AS `LOANNUM` from `dt_master_loan`) `B` on((`A`.`loanno` = `B`.`LOANNUM`))) join (select `dt_user`.`id` AS `id`,`dt_user`.`fullname` AS `fullname` from `dt_user`) `C` on((`A`.`pic` = `C`.`id`))) */;

--
-- Final view structure for view `mapping_debitur`
--

/*!50001 DROP VIEW IF EXISTS `mapping_debitur`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`doadmin`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `mapping_debitur` AS select `a`.`id` AS `id`,`a`.`loanno` AS `loanno`,`a`.`FCCode` AS `FCCode`,`a`.`start_date` AS `start_date`,`a`.`end_date` AS `end_date`,`a`.`current` AS `current`,`a`.`update_on` AS `update_on`,`a`.`update_by` AS `update_by`,`b`.`fullname` AS `fullname` from (`dt_debitur_mapping` `a` left join `dt_user` `b` on((`a`.`FCCode` = `b`.`id`))) where ((`a`.`current` like 'Y') and (`b`.`fullname` is not null)) */;


