DROP TABLE payment_order_tbl;
DROP TABLE balance_tbl;
DROP TABLE expenses_tbl;
DROP TABLE bank_tbl;
DROP TABLE loan_tbl;
DROP TABLE pn_tbl;
DROP TABLE registry_of_deeds_tbl;
DROP TABLE collateral_tbl;
DROP TABLE end_user_borrower_tbl;
DROP TABLE role_tbl;
DROP TABLE user_tbl;
DROP TABLE chart_of_accts_tbl;
DROP TABLE rediscounting_interest_rates_tbl;
DROP TABLE transaction_tbl;
DROP TABLE payment_tbl;
DROP TABLE payable_tbl;
DROP TABLE sap_tbl;
DROP TABLE foreclosure_tbl;
DROP TABLE foreclosure_status_tbl;
DROP TABLE approval_status_tbl;
DROP TABLE lawyer_contact_tbl;
DROP TABLE lawyer_tbl;
DROP TABLE petition_tbl;
DROP TABLE winning_bidder_tbl;
DROP TABLE bidder_details_tbl;
DROP TABLE auction_tbl;
DROP TABLE bid_tbl;
DROP TABLE bid_remarks_tbl;
DROP TABLE payment_pending_tbl;
DROP TABLE transaction_remarks_tbl;
DROP TABLE remarks_tbl;
DROP TABLE collateral_documents_tbl;
DROP TABLE demand_letter;
DROP TABLE pre_foreclosure;
DROP TABLE batch_tbl;
DROP TABLE pn_collateral;
DROP TABLE expense_tbl;
DROP TABLE appraisal_report;
DROP TABLE dacion_tbl;
DROP TABLE dacion_transaction_tbl;
DROP TABLE tax_declaration_tbl;
DROP TABLE insurance_details_tbl;
DROP TABLE rem_tbl;
DROP TABLE rem_dacion_tbl;
DROP SEQUENCE DACION_TRANSACTION_SEQ;

CREATE TABLE lawyer_tbl(
	lawyer_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	lawyer_name VARCHAR(100) NOT NULL,
	lawyer_address VARCHAR(200),
	vat_registered INT, -- true/false 0/1
	remarks VARCHAR(100),
	PRIMARY KEY (lawyer_id)
);

CREATE TABLE balance_tbl(
	balance_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	ref_id INT,
	balance_type VARCHAR(5),
	others  DECIMAL(20,2) WITH DEFAULT 0,
	accrued_interest_receivable  DECIMAL(20,2) WITH DEFAULT 0,
	liquidated_damages  DECIMAL(20,2) WITH DEFAULT 0,
	interest_income  DECIMAL(20,2) WITH DEFAULT 0,
	outstanding_principal  DECIMAL(20,2) WITH DEFAULT 0,
	total_outstanding_balance  DECIMAL(20,2) WITH DEFAULT 0,
	cwt  DECIMAL(20,2) WITH DEFAULT 0,
 	miscellaneous_income_others DECIMAL(20,2) WITH DEFAULT 0,
 	miscellaneous_income_liquidating_dividends DECIMAL(20,2) WITH DEFAULT 0,
	delete_flag INT WITH DEFAULT 0,
	created_date TIMESTAMP WITH DEFAULT,
	PRIMARY KEY(balance_id)
);

CREATE TABLE expenses_tbl(
	expense_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	ref_id INT,
	expense_type VARCHAR(25),   -- PN, LOAN OR BANK
	miscellaneous_assets DECIMAL(20,2) WITH DEFAULT 0,  -- MA from Foreclosure Expenses
	accounts_payable DECIMAL(20,2) WITH DEFAULT 0,    -- AP from Foreclosure Expenses
  	total_actual_foreclosure_expenses DECIMAL(20,2) WITH DEFAULT 0,
	delete_flag INT WITH DEFAULT 0,
	created_date TIMESTAMP WITH DEFAULT,
	PRIMARY KEY(expense_id)
);

CREATE TABLE bank_tbl(
	bank_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	bank_code VARCHAR(50),
	banK_entity VARCHAR(50),
	bank_name VARCHAR(100),
	bank_type VARCHAR(50),
	bank_status VARCHAR(50),
	date_joined DATE,
	bank_address VARCHAR(150),
	date_close DATE,
	ref_balance_id INT,
	cwt_flag INT WITH DEFAULT 0,
	delete_flag INT WITH DEFAULT 0,
	PRIMARY KEY(bank_id),
	FOREIGN KEY(ref_balance_id) REFERENCES balance_tbl(balance_id)
);

CREATE TABLE loan_tbl(
	loan_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	loan_application_number INT,
	ref_bank_id INT,
	loan_type VARCHAR(50),
	loan_status VARCHAR(50),
	loan_interest_rate DECIMAL(9,6) WITH DEFAULT 0,
	liquidated_damages_rate DECIMAL(6,6) WITH DEFAULT 0,
	date_granted DATE,
	maturity_date DATE,
	loan_term INT,
	amount_granted  DECIMAL(20,2) WITH DEFAULT 0,
	reprice_rate_flag INT WITH DEFAULT 0,
	delete_flag INT WITH DEFAULT 0,
	ref_balance_id INT,
	PRIMARY KEY(loan_id),
	FOREIGN KEY(ref_bank_id) REFERENCES bank_tbl(bank_id),
	FOREIGN KEY(ref_balance_id) REFERENCES balance_tbl(balance_id)
);

CREATE TABLE end_user_borrower_tbl(
	borrower_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	entity_name VARCHAR(50),
	full_name VARCHAR(100),
	date_of_birth DATE,
	ref_pn_id INT,
	address VARCHAR(200),
	tin VARCHAR(50),
	PRIMARY KEY(borrower_id)
);

CREATE TABLE pn_tbl(
	pn_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	pn_number VARCHAR(50),
	ref_loan_id INT,
	pn_status VARCHAR(50),
	pn_interest_rate DECIMAL(9,6) WITH DEFAULT 0,
	penalty_rate DECIMAL(9,6) WITH DEFAULT 0,
	date_granted DATE,
	maturity_date DATE,
	amount_granted DECIMAL(20,2) WITH DEFAULT 0,
	delete_flag INT WITH DEFAULT 0,
	ref_balance_id INT,
	ref_borrower_id INT,
	ref_expense_id INT,
	ref_lawyer_id INT,
  	ref_bank_id INT,
	rem_amount DECIMAL(20,2) WITH DEFAULT 0,
	tax_receipt VARCHAR(50),
	PRIMARY KEY(pn_id),
	FOREIGN KEY(ref_loan_id) REFERENCES loan_tbl(loan_id),
	FOREIGN KEY(ref_balance_id) REFERENCES balance_tbl(balance_id),
	FOREIGN KEY(ref_borrower_id) REFERENCES end_user_borrower_tbl(borrower_id),
	FOREIGN KEY(ref_lawyer_id) REFERENCES lawyer_tbl(lawyer_id),
 	FOREIGN KEY(ref_bank_id) REFERENCES bank_tbl(bank_id),
	FOREIGN KEY(ref_expense_id) REFERENCES expenses_tbl(expense_id)
);

CREATE TABLE registry_of_deeds_tbl(
	rd_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	registry_of_deeds VARCHAR(100),
	PRIMARY KEY(rd_id)
);


CREATE TABLE rem_tbl(
	rem_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	rem_name VARCHAR(50),
	rem_amount DECIMAL(20,2) WITH DEFAULT 0,
	PRIMARY KEY(rem_id)
);

CREATE TABLE collateral_tbl(
	collateral_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	collateral_number VARCHAR(50),
	collateral_status VARCHAR(50),
	collateral_value DECIMAL(20,2) WITH DEFAULT 0,
	collateral_type VARCHAR(50),
	collateral_classification VARCHAR(50),
	appraisal_date DATE,
	fmv DECIMAL(20,2) WITH DEFAULT 0,
	entry_date DATE,
	ref_rd_id INT,
	address VARCHAR(150),
	rem_amount DECIMAL(20,2) WITH DEFAULT 0,
	registered_owner VARCHAR(150),
	area INT,
	unit_measurement VARCHAR(50),
	ref_rem_id INT,
	PRIMARY KEY(collateral_id),
	FOREIGN KEY(ref_rd_id) REFERENCES registry_of_deeds_tbl(rd_id),
	FOREIGN KEY(ref_rem_id) REFERENCES rem_tbl(rem_id)

);

CREATE TABLE dacion_transaction_tbl(
	transaction_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	transaction_number VARCHAR(50) UNIQUE NOT NULL,
	dts_number VARCHAR(50),
	transaction_date TIMESTAMP WITH DEFAULT,
	active_flag INT WITH DEFAULT 1,
	PRIMARY KEY(transaction_id)
);

CREATE TABLE dacion_tbl(
	dacion_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	dacion_type VARCHAR(50),
	udoa_number VARCHAR(50),
	dacion_value DECIMAL(20,2) WITH DEFAULT 0,
	ref_pn_id INT,
	ref_loan_id INT,
	ref_balance_id INT,
	ref_borrower_id INT,
	ref_bank_id INT,
  	ref_collateral_id INT,
 	ref_dacion_transaction_id INT,
	delete_flag INT WITH DEFAULT 0,
	PRIMARY KEY(dacion_id),
  	FOREIGN KEY(ref_pn_id) REFERENCES pn_tbl(pn_id),
	FOREIGN KEY(ref_loan_id) REFERENCES loan_tbl(loan_id),
	FOREIGN KEY(ref_balance_id) REFERENCES balance_tbl(balance_id),
	FOREIGN KEY(ref_borrower_id) REFERENCES end_user_borrower_tbl(borrower_id),
	FOREIGN KEY(ref_bank_id) REFERENCES bank_tbl(bank_id),
  	FOREIGN KEY(ref_collateral_id) REFERENCES collateral_tbl(collateral_id),
  	FOREIGN KEY(ref_dacion_transaction_id) REFERENCES dacion_transaction_tbl(transaction_id)
);



CREATE TABLE rem_dacion_tbl(
  rem_dacion_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  ref_rem_id INT,
  ref_dacion_id INT,
  PRIMARY KEY(rem_dacion_id),
  FOREIGN KEY(ref_rem_id) REFERENCES rem_tbl(rem_id),
  FOREIGN KEY(ref_dacion_id) REFERENCES dacion_tbl(dacion_id)
);



CREATE TABLE role_tbl(
	role_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	role VARCHAR(50),
	PRIMARY KEY(role_id)
);

CREATE TABLE user_tbl (
	user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	user_name VARCHAR(50) UNIQUE NOT NULL,
	user_role_id INT,
	PRIMARY KEY (user_id),
	FOREIGN KEY (user_role_id) REFERENCES role_tbl(role_id)
);

CREATE TABLE chart_of_accts_tbl(
	chart_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	chart_account_ref_code VARCHAR(200),
	chart_account_description VARCHAR(200),
	chart_account_title VARCHAR(200) UNIQUE NOT NULL,
	chart_account_code VARCHAR(100) UNIQUE NOT NULL,
	chart_default_entry_type VARCHAR(200),
	chart_document_source VARCHAR(200),
	PRIMARY KEY (chart_id)
);

CREATE TABLE payment_order_tbl (
	payment_order_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	payment_order_number VARCHAR(50) UNIQUE NOT NULL,
	payment_order_date DATE,
	payment_order_type VARCHAR(50),
	payment_date DATE,
	payment_order_status VARCHAR(50),
	payment_level VARCHAR(10),
	bank_payment_level INT,
	payment_type VARCHAR(50),
	end_user_borrower VARCHAR(50),
	bank_name VARCHAR(50),
	loan_number INT,
	loan_type VARCHAR(50),
	pn_number VARCHAR(50),
	amount  DECIMAL(20,2) WITH DEFAULT 0,
	or_number VARCHAR(50),
	or_date DATE,
	receiving_office VARCHAR(50),
	particulars VARCHAR(300),
	dts_number VARCHAR(30),
	payor VARCHAR(100),
	total_cash_amount  DECIMAL(20,2) WITH DEFAULT 0,
	total_check_amount  DECIMAL(20,2) WITH DEFAULT 0,
	total_pmo_amount  DECIMAL(20,2) WITH DEFAULT 0,
	total_amount  DECIMAL(20,2) WITH DEFAULT 0,
	total_payment_amount  DECIMAL(20,2) WITH DEFAULT 0,
	payee VARCHAR(50),
	drawee_bank VARCHAR(50),
	check_number INT,
	check_date DATE,
	postal_office VARCHAR(100),
	pmo_amount  DECIMAL(20,2) WITH DEFAULT 0,
	pmo_date DATE,
	pmo_number INT,
	maker VARCHAR(50),
	chart_account_title VARCHAR(200),
	chart_account_code  VARCHAR(100),
	last_updated_date TIMESTAMP,
	user_name VARCHAR(50),
	ref_user_id INT,
	delete_flag INT WITH DEFAULT 0,
	year_created INT,
	bank_level_payment_type VARCHAR(50),
	currency VARCHAR(50),
	approved_by VARCHAR(50),
	approved_date DATE,
	PRIMARY KEY(payment_order_id),
	FOREIGN KEY(user_name) REFERENCES user_tbl(user_name),
	FOREIGN KEY(chart_account_title) REFERENCES chart_of_accts_tbl(chart_account_title),
	FOREIGN KEY(chart_account_code) REFERENCES chart_of_accts_tbl(chart_account_code)
);

CREATE TABLE transaction_tbl(
	transaction_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	sap_number VARCHAR(50),
	transaction_date DATE,
	payment_date DATE,
	payment_order_number VARCHAR(50),
	payment_number VARCHAR(50),
	name_of_bank VARCHAR(100),
	term INT,
	application_number INT,
	oustanding_balance  DECIMAL(20,2) WITH DEFAULT 0,
	rediscounting_rate DECIMAL(9,6) WITH DEFAULT 0,
	liquidated_damage_rate DECIMAL(9,6) WITH DEFAULT 0,
	date_granted DATE,
	date_due DATE,
	principal  DECIMAL(20,2) WITH DEFAULT 0,
	status VARCHAR(100),
	interest_date DATE,
	accrual_date DATE,
	accrued_interest  DECIMAL(20,2) WITH DEFAULT 0,
	unpaid_accrued_interest  DECIMAL(20,2) WITH DEFAULT 0,
	accrued_interest_receivable_days INT,
	interest_days INT,
	liquidated_damage_days INT,
	interest_income  DECIMAL(20,2) WITH DEFAULT 0,
	unpaid_interest_income  DECIMAL(20,2) WITH DEFAULT 0,
	borrower_name VARCHAR(100),
	pn_number VARCHAR(100),
	liquidated_damages  DECIMAL(20,2) WITH DEFAULT 0,
	unpaid_liquidated_damages  DECIMAL(20,2) WITH DEFAULT 0,
	last_payment_date DATE,
	expenses  DECIMAL(20,2) WITH DEFAULT 0,
	cwt  DECIMAL(20,2) WITH DEFAULT 0,
	total_payment  DECIMAL(20,2) WITH DEFAULT 0,
	delete_flag INT WITH DEFAULT 0,
	year_created INT NOT NULL,
	transaction_type VARCHAR(50),
	PRIMARY KEY(transaction_id),
	FOREIGN KEY(payment_order_number) REFERENCES payment_order_tbl(payment_order_number)
);

CREATE TABLE rediscounting_interest_rates_tbl (
	red_interest_rates_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	effectivity_date DATE,
	effectivity_date_until DATE,
	red_interest_rate DECIMAL(9,6) WITH DEFAULT 0,
	window_type VARCHAR(50),
	rediscounting_type VARCHAR(50),
	loan_term INT,
	date_created DATE,
	created_by INT,
	date_updated DATE,
	updated_by INT,
	date_approved DATE,
	approved_by INT,
	record_status VARCHAR(50),
	delete_flag INT WITH DEFAULT 0,
	PRIMARY KEY (red_interest_rates_id),
	FOREIGN KEY(created_by) REFERENCES user_tbl(user_id),
	FOREIGN KEY(updated_by) REFERENCES user_tbl(user_id),
	FOREIGN KEY(approved_by) REFERENCES user_tbl(user_id)
);

CREATE TABLE payment_tbl(
	payment_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	payment_type VARCHAR(50),
	check_number VARCHAR(50),
	check_date DATE,
	drawee_bank VARCHAR(100),
	pmo_number VARCHAR(50),
	pmo_date DATE,
	payment_amount  DECIMAL(20,2) WITH DEFAULT 0,
	issuing_office VARCHAR(100),
	delete_flag INT WITH DEFAULT 0,
	ref_payment_order_id INT,
	FOREIGN KEY(ref_payment_order_id) REFERENCES payment_order_tbl(payment_order_id)
);

CREATE TABLE payable_tbl(
	payable_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	ref_payment_order_id INT,
	promissory_note_number VARCHAR(50),
	loan_application_number VARCHAR(50),
	collateral_number VARCHAR(50),
	borrower_id INT,
	borrower_name VARCHAR(50),
	amount_granted  DECIMAL(20,2) WITH DEFAULT 0,
	pn_outstanding_balance  DECIMAL(20,2) WITH DEFAULT 0,
	outstanding_principal_balance  DECIMAL(20,2) WITH DEFAULT 0,
	total_outstanding_balance  DECIMAL(20,2) WITH DEFAULT 0,
	other_expenses  DECIMAL(20,2) WITH DEFAULT 0,
	payment_amount  DECIMAL(20,2) WITH DEFAULT 0,
	excess_amount  DECIMAL(20,2) WITH DEFAULT 0,
	excess_payment_type VARCHAR(50),
	FOREIGN KEY(ref_payment_order_id) REFERENCES payment_order_tbl(payment_order_id)
);

CREATE TABLE sap_tbl(
	sap_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	sap_number VARCHAR(50),
	payment_order_number VARCHAR(100),
	payment_date DATE,
	pn_number VARCHAR(50),
	rediscounting_rate DECIMAL(9,6) WITH DEFAULT 0,
	ld_rate DECIMAL(9,6) WITH DEFAULT 0,
	interest_date DATE,
	accrual_date DATE,
	principal_payment  DECIMAL(20,2) WITH DEFAULT 0,
	accrued_interest_days INT,
	accrued_interest_payment  DECIMAL(20,2) WITH DEFAULT 0,
	unpaid_accrued_interest  DECIMAL(20,2) WITH DEFAULT 0,
	accrued_interest_payment_date DATE,
	interest_income_days INT,
	interest_income_payment  DECIMAL(20,2) WITH DEFAULT 0,
	unpaid_interest_income  DECIMAL(20,2) WITH DEFAULT 0,
	interest_income_payment_date DATE,
	liquidated_damages_days INT,
	liquidated_damages_payment  DECIMAL(20,2) WITH DEFAULT 0,
	unpaid_liquidated_damages  DECIMAL(20,2) WITH DEFAULT 0,
	liquidated_damages_payment_date DATE,
	expenses_payment  DECIMAL(20,2) WITH DEFAULT 0,
	total_payment  DECIMAL(20,2) WITH DEFAULT 0,
	last_payment_date DATE,
	ref_pn_id INT,
	delete_flag INT WITH DEFAULT 0,
	PRIMARY KEY (sap_id),
	FOREIGN KEY (ref_pn_id) REFERENCES pn_tbl(pn_id)
);

CREATE TABLE payment_pending_tbl(
 payment_pending_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
 payment_order_number VARCHAR(50),
 pn_number varchar(50),
 payment_rule_type VARCHAR(50),
 total_payment DECIMAL(20,2) WITH DEFAULT 0,
 others_payment DECIMAL(20,2) WITH DEFAULT 0,
 liquidated_damages_payment DECIMAL(20,2) WITH DEFAULT 0,
 accrued_interest_payment DECIMAL(20,2) WITH DEFAULT 0,
 interest_income_payment DECIMAL(20,2) WITH DEFAULT 0,
 principal_payment DECIMAL(20,2) WITH DEFAULT 0,
 unpaid_liquidated_damages DECIMAL(20,2) WITH DEFAULT 0,
 unpaid_accrued_interest DECIMAL(20,2) WITH DEFAULT 0,
 unpaid_interest_income DECIMAL(20,2) WITH DEFAULT 0,
 miscellaneous_income DECIMAL(20,2) WITH DEFAULT 0,
 payment_date DATE

);

CREATE TABLE lawyer_contact_tbl(
	contact_type VARCHAR(20) NOT NULL, -- EMAIL, FAX, PHONE etc.
	contact VARCHAR(50) NOT NULL, -- Actual contact value 000-000-000
	ref_lawyer_id INT NOT NULL,	-- lawyer id
	FOREIGN KEY (ref_lawyer_id) REFERENCES lawyer_tbl(lawyer_id)
);

CREATE TABLE foreclosure_status_tbl(
	status_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	status_name VARCHAR(100),
	status_description VARCHAR(100),
	PRIMARY KEY (status_id)
);

CREATE TABLE approval_status_tbl (
	approval_status_id INT UNIQUE NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	approval_status VARCHAR(50)
);

CREATE TABLE foreclosure_tbl(
	foreclosure_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	transaction_date DATE,
	foreclosure_ref_number VARCHAR(50) UNIQUE NOT NULL,
	registered_owner VARCHAR(50),
	ref_foreclosure_status_id INT,
	ref_approval_status_id INT,
	ref_lawyer_id INT,
	ref_original_lawyer_id INT,
	dts_number VARCHAR(50),
	status_filter VARCHAR(50),
	remarks VARCHAR(150),
	delete_flag INT WITH DEFAULT 0,
	last_updated_by VARCHAR(50),
	last_updated_date DATE,
	date_booked_to_ip DATE,
	publication_name VARCHAR(150),
	volume VARCHAR(50),
	issue_numbers VARCHAR(50),
	FOREIGN KEY (ref_lawyer_id) REFERENCES lawyer_tbl(lawyer_id),
	FOREIGN KEY (ref_original_lawyer_id) REFERENCES lawyer_tbl(lawyer_id),
	FOREIGN KEY (ref_foreclosure_status_id) REFERENCES foreclosure_status_tbl(status_id),
	FOREIGN KEY (ref_approval_status_id) REFERENCES approval_status_tbl(approval_status_id),
	PRIMARY KEY (foreclosure_id)
);

CREATE TABLE petition_tbl(
  petition_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  ref_foreclosure_id INT,
  promissory_note_number VARCHAR(50),
  promissory_note_face_amount DECIMAL(20,2),
  ref_pn_id INT,
  collateral_number VARCHAR(50),
  ref_collateral_id INT,
  loan_type VARCHAR(50),
  approval_status VARCHAR(50),
  bank_name VARCHAR(50),
  ref_bank_id INT,
  full_name VARCHAR(50),
  entity_name VARCHAR(50),
  ref_borrower_id INT,
  loan_application_number VARCHAR(50),
  delete_flag INT WITH DEFAULT 0,
  outstanding_balance DECIMAL(20,2) WITH DEFAULT 0,
  last_interest_payment_date DATE,
  interest_rate DECIMAL(9,6) WITH DEFAULT 0,
  penalty_rate DECIMAL(9,6) WITH DEFAULT 0,
  service_charge_rate DECIMAL(9,6) WITH DEFAULT 0,
  other_charges DECIMAL(20,2) WITH DEFAULT 0,
  FOREIGN KEY (ref_foreclosure_id) REFERENCES foreclosure_tbl(foreclosure_id),
	FOREIGN KEY (ref_bank_id) REFERENCES bank_tbl(bank_id),
	FOREIGN KEY (ref_borrower_id) REFERENCES end_user_borrower_tbl(borrower_id),
	FOREIGN KEY (ref_collateral_id) REFERENCES collateral_tbl(collateral_id),
  FOREIGN KEY (ref_pn_id) REFERENCES pn_tbl(pn_id),
	PRIMARY KEY (petition_id)
);

CREATE TABLE auction_tbl (
    auction_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
    ref_foreclosure_id INT,
    foreclosure_ref_number INT,
    auction_status VARCHAR(50),
    file_claim_amount DECIMAL(20,2) WITH DEFAULT 0,
    date_forwarded DATE,
    date_forwarded_remarks VARCHAR(150),
    date_filed DATE,
    date_filed_remarks VARCHAR(150),
    ejf_number VARCHAR(50),
    rtc VARCHAR(150),
    auction_date DATE,
    registered_owner VARCHAR(50),
    tct_number VARCHAR(50),
    area INT,
    fmv DECIMAL(20,2) WITH DEFAULT 0,
	pwd DECIMAL(9,6) WITH DEFAULT 0,
	hbu_property VARCHAR(50),
    delete_flag INT WITH DEFAULT 0,
    notice_auction_date DATE,
    time_auction VARCHAR(50),
    place_auction VARCHAR(150),
    sheriff_clerk_name VARCHAR(150),
    rtc_auction_sale VARCHAR(150),
    rtc_remarks_auction_sale VARCHAR(150),
    pdic_bank_soa VARCHAR(100),
    recommended_bid_price DECIMAL(20,2) WITH DEFAULT 0,
    total_bsp_claims DECIMAL(20,2) WITH DEFAULT 0,
    total_bank_claims DECIMAL(20,2) WITH DEFAULT 0,
    fair_market_value DECIMAL(20,2) WITH DEFAULT 0,
    approved_bid_price DECIMAL(20,2) WITH DEFAULT 0,
    manual_outstanding_principal DECIMAL(20,2) WITH DEFAULT 0,
    manual_interest_amount DECIMAL(20,2) WITH DEFAULT 0,
    manual_penalty DECIMAL(20,2) WITH DEFAULT 0,
    manual_service_charge DECIMAL(20,2) WITH DEFAULT 0,
    manual_others DECIMAL(20,2) WITH DEFAULT 0,
    approved_bid_price_remarks VARCHAR(150),
    bid_price_approval_date DATE,
	ref_bidder_id INT,
	dv_date DATE,
	cos_date DATE,
	submitted_to_rd_date DATE,
	cos_annotation_date DATE,
	memo_signed_by_md_date DATE,
	transferred_to_amd_date DATE,
    awarded_to_next_bidder_flag INT WITH DEFAULT 0,
    PRIMARY KEY(auction_id),
    FOREIGN KEY(ref_foreclosure_id) REFERENCES FORECLOSURE_TBL(foreclosure_id)
);

CREATE TABLE bid_tbl(
	bid_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	bank_principal DECIMAL(20,2) WITH DEFAULT 0,
	bank_interest DECIMAL(20,2) WITH DEFAULT 0,
	bank_penalties DECIMAL(20,2) WITH DEFAULT 0,
	bsp_principal DECIMAL(20,2) WITH DEFAULT 0,
	bsp_booked_interest DECIMAL(20,2) WITH DEFAULT 0,
	bsp_unbooked_interest_income DECIMAL(20,2) WITH DEFAULT 0,
	bsp_unbooked_liquidated_damages DECIMAL(20,2) WITH DEFAULT 0,
	filing_fee DECIMAL(20,2) WITH DEFAULT 0,
	appraisal_cost DECIMAL(20,2) WITH DEFAULT 0,
 	publication_fee DECIMAL(20,2) WITH DEFAULT 0,
	sheriffs_fee DECIMAL(20,2) WITH DEFAULT 0,
	courts_commission DECIMAL(20,2) WITH DEFAULT 0,
	entry_fee DECIMAL(20,2) WITH DEFAULT 0,
	attorneys_fee DECIMAL(20,2) WITH DEFAULT 0,
	real_estate_tax DECIMAL(20,2) WITH DEFAULT 0,
	appraised_value DECIMAL(20,2) WITH DEFAULT 0,
	applicable_adjustment_rate DECIMAL(9,6) WITH DEFAULT 0,
	total_bsp_claim DECIMAL(20,2) WITH DEFAULT 0,
	total_bank_claim DECIMAL(20,2) WITH DEFAULT 0,
  	ref_auction_id INT,
	FOREIGN KEY (ref_auction_id) REFERENCES auction_tbl (auction_id),
	PRIMARY KEY (bid_id)
);

CREATE TABLE bid_remarks_tbl(
    remarks_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
    ref_bid_id INT,
    remarks_content VARCHAR(1000),
    remarks_type VARCHAR(50),
    date_created DATE,
    PRIMARY KEY(remarks_id),
    FOREIGN KEY(ref_bid_id) REFERENCES BID_TBL(bid_id)
);

CREATE TABLE bidder_details_tbl(
	bidder_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	bidder_name VARCHAR(100),
	amount DECIMAL(20,2) WITH DEFAULT 0,
	ref_auction_id INT,
	default_flag INT,
	delete_flag INT WITH DEFAULT 0,
	PRIMARY KEY(bidder_id),
	FOREIGN KEY(ref_auction_id) REFERENCES AUCTION_TBL(auction_id)
);

CREATE TABLE winning_bidder_tbl (
	winning_bidder_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	ref_bidder_id INT,
	reason  VARCHAR(100),
	created_date TIMESTAMP WITH DEFAULT,
	delete_flag INT WITH DEFAULT 0,
	PRIMARY KEY(winning_bidder_id),
	FOREIGN KEY(ref_bidder_id) REFERENCES BIDDER_DETAILS_TBL(bidder_id)
);

CREATE TABLE transaction_remarks_tbl (
  remarks_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  ref_transaction_id INT,
  remarks_content VARCHAR(500),
  remarks_type VARCHAR (50),
  date_created TIMESTAMP WITH DEFAULT,
  PRIMARY KEY (remarks_id),
  FOREIGN KEY(ref_transaction_id) REFERENCES transaction_tbl (transaction_id)
);

CREATE TABLE collateral_documents_tbl (
	document_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	foreclosure_ref_number VARCHAR(50),
	document_name VARCHAR(50),
	document_contents VARCHAR(300),
	updated_by VARCHAR(50),
	updated_on TIMESTAMP WITH DEFAULT,
	save_flag INT WITH DEFAULT 0,
	print_flag INT WITH DEFAULT 0,
	PRIMARY KEY (document_id),
	FOREIGN KEY (updated_by) REFERENCES user_tbl(user_name),
	FOREIGN KEY (foreclosure_ref_number) REFERENCES FORECLOSURE_TBL(foreclosure_ref_number)
);

CREATE TABLE pre_foreclosure(
pre_foreclosure_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
ref_pn_id INT,
pre_foreclosure_status VARCHAR (50),
transaction_date DATE,
active_flag INT WITH DEFAULT 1,
excluded_false INT WITH DEFAULT 0,
excluded_date DATE,
FOREIGN KEY (ref_pn_id) REFERENCES pn_tbl(pn_id),
PRIMARY KEY(pre_foreclosure_id)
);

CREATE TABLE demand_letter(
demand_letter_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
date_on_demand_letter DATE,
demand_letter_date DATE,
ref_pn_id INT,
FOREIGN KEY (ref_pn_id) REFERENCES pn_tbl(pn_id),
PRIMARY KEY(demand_letter_id)
);

CREATE TABLE remarks_tbl (
  remarks_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  remarks_group VARCHAR(100),
  remarks_group_ref_id INT,
  remarks_content VARCHAR(500),
  created_date TIMESTAMP WITH DEFAULT,
  created_by VARCHAR(50),
  PRIMARY KEY (remarks_id)
);

CREATE TABLE batch_tbl(
 batch_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
 job_name varchar(100),
 cron_trigger_name varchar(100),
 cron_expression varchar(100),
 active_flag INT WITH DEFAULT 1
);

CREATE TABLE pn_collateral (
  pn_collateral_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  ref_pn_id INT,
  ref_collateral_id INT,
  PRIMARY KEY (pn_collateral_id),
  FOREIGN KEY (ref_pn_id) REFERENCES pn_tbl (pn_id),
  FOREIGN KEY (ref_collateral_id) REFERENCES collateral_tbl (collateral_id)
);

CREATE TABLE expense_tbl(
	expense_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	expense_type VARCHAR(100) NOT NULL,
	amount DECIMAL(20,2) WITH DEFAULT 0,
	or_number VARCHAR(50),
	or_date DATE,
	ref_pn_id INT,
	FOREIGN KEY (ref_pn_id) REFERENCES pn_tbl (pn_id),
	PRIMARY KEY (expense_id)
);

CREATE TABLE appraisal_report(
	appraisal_report_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	report_type VARCHAR(100) NOT NULL,
	report_date DATE,
	appraiser_name VARCHAR(50),
	ref_collateral_id INT,
	FOREIGN KEY (ref_collateral_id) REFERENCES collateral_tbl (collateral_id),
	PRIMARY KEY (appraisal_report_id)
);


CREATE TABLE tax_declaration_tbl(
	tax_declaration_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	tax_declaration_number VARCHAR(50),
	tax_declaration_type VARCHAR(50),
	classification_type VARCHAR(50),
	amount DECIMAL(20,2) WITH DEFAULT 0,
	ref_collateral_id INT,
	ref_appraisal_id INT,
	PRIMARY KEY(tax_declaration_id),
	FOREIGN KEY(ref_collateral_id) REFERENCES collateral_tbl (collateral_id),
	FOREIGN KEY(ref_appraisal_id) REFERENCES appraisal_report(appraisal_report_id)
);

CREATE TABLE insurance_details_tbl(
	insurance_details_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
	policy_number VARCHAR(50),
	insurance_company_name VARCHAR(50),
	insurance_or_number VARCHAR(50),
	expiry_date TIMESTAMP WITH DEFAULT,
	insured_amount DECIMAL(20,2) WITH DEFAULT 0,
	ref_collateral_id INT,
	ref_appraisal_id INT,
	PRIMARY KEY(insurance_details_id),
	FOREIGN KEY(ref_collateral_id) REFERENCES collateral_tbl (collateral_id)
);

CREATE SEQUENCE DACION_TRANSACTION_SEQ
      START WITH 3
      INCREMENT BY 1
      NO MAXVALUE
      NO CYCLE
      CACHE 100;



INSERT INTO balance_tbl (cwt, others, liquidated_damages, accrued_interest_receivable, interest_income, outstanding_principal, total_outstanding_balance) VALUES
( 0.00, 0.00, 540000.00, 1130000.00, 206000.00, 6800000.00, 8676000.00), --Bank of the Philippine Islands
( 0.00, 0.00, 1030000.00, 1510000.00, 229700.00, 6800000.00, 9569700.00), --Banco De Oro
( 0.00, 0.00, 890000.00, 1100000.00, 191700.00, 5600000.00, 7781700.00), --Metro Bank
( 0.00, 0.00, 540000.00, 1130000.00, 206000.00, 6800000.00, 8676000.00), --CRB of SDMD
( 0.00, 0.00, 1030000.00, 1510000.00, 229700.00, 6800000.00, 9569700.00), --Cooperative Bank of SDMD
( 0.00, 0.00, 890000.00, 1100000.00, 191700.00, 5600000.00, 7781700.00), --RB of SDMD
( 0.00, 0.00, 340000.00, 770000.00, 122000.00, 4000000.00, 5232000.00), --Loan 123456 (Bank of the Philippine Islands)
( 0.00, 0.00, 200000.00, 360000.00, 84000.00, 2800000.00, 3444000.00), --Loan 234567 (Bank of the Philippine Islands)
( 0.00, 0.00, 690000.00, 740000.00, 107700.00, 2800000.00, 4337700.00), --Loan 345678 (Banco De Oro)
( 0.00, 0.00, 340000.00, 770000.00, 122000.00, 4000000.00, 5232000.00), --Loan 456789 (Banco De Oro)
( 0.00, 0.00, 200000.00, 360000.00, 84000.00, 2800000.00, 3444000.00), --Loan 567891 (Metro Bank)
( 0.00, 0.00, 690000.00, 740000.00, 107700.00, 2800000.00, 4337700.00), --Loan 678912 (Metro Bank)
( 0.00, 0.00, 340000.00, 770000.00, 122000.00, 4000000.00, 5232000.00), --Loan 789123 (CRB of SDMD)
( 0.00, 0.00, 200000.00, 360000.00, 84000.00, 2800000.00, 3444000.00), --Loan 891234 (CRB of SDMD)
( 0.00, 0.00, 690000.00, 740000.00, 107700.00, 2800000.00, 4337700.00), --Loan 912345 (Cooperative Bank of SDMD)
( 0.00, 0.00, 340000.00, 770000.00, 122000.00, 4000000.00, 5232000.00), --Loan 565541 (Cooperative Bank of SDMD)
( 0.00, 0.00, 200000.00, 360000.00, 84000.00, 2800000.00, 3444000.00), --Loan 878999 (RB of SDMD)
( 0.00, 0.00, 690000.00, 740000.00, 107700.00, 2800000.00, 4337700.00), --Loan 741335 (RB of SDMD)
( 0.00, 20000.00, 140000.00, 450000.00, 58000.00, 1200000.00, 1848000.00), --PN 201712 [Loan 123456 (Bank of the Philippine Islands)]
( 0.00, 0.00, 200000.00, 320000.00, 64000.00, 2800000.00, 3384000.00), --PN 201723 [Loan 123456 (Bank of the Philippine Islands)]
( 0.00, 18000.00, 80000.00, 110000.00, 34000.00, 1700000.00, 1924000.00), --PN 201734 [Loan 234567 (Bank of the Philippine Islands)]
( 0.00, 21000.00, 120000.00, 250000.00, 50000.00, 1100000.00, 1520000.00), --PN 201745 [Loan 234567 (Bank of the Philippine Islands)]
( 0.00, 45000.00, 390000.00, 550000.00, 53300.00, 1300000.00, 2293300.00), --PN 201756 [Loan 345678 (Banco De Oro)]
( 0.00, 67000.00, 300000.00, 190000.00, 54400.00, 1500000.00, 2044400.00), --PN 201767 [Loan 345678 (Banco De Oro)]
( 0.00, 20000.00, 140000.00, 450000.00, 58000.00, 1200000.00, 1848000.00), --PN 201778 [Loan 456789 (Banco De Oro)]
( 0.00, 0.00, 200000.00, 320000.00, 64000.00, 2800000.00, 3384000.00), --PN 201789 [Loan 456789 (Banco De Oro)]
( 0.00, 18000.00, 80000.00, 110000.00, 34000.00, 1700000.00, 1924000.00), --PN 201791 [Loan 567891 (Metro Bank)]
( 0.00, 21000.00, 120000.00, 250000.00, 50000.00, 1100000.00, 1520000.00), --PN 201713 [Loan 567891 (Metro Bank)]
( 0.00, 45000.00, 390000.00, 550000.00, 53300.00, 1300000.00, 2293300.00), --PN 201724 [Loan 678912 (Metro Bank)]
( 0.00, 67000.00, 300000.00, 190000.00, 54400.00, 1500000.00, 2044400.00), --PN 201729 [Loan 678912 (Metro Bank)]
( 0.00, 20000.00, 140000.00, 450000.00, 58000.00, 1200000.00, 1848000.00), --PN 201735 [Loan 789123 (CRB of SDMD)]
( 0.00, 0.00, 200000.00, 320000.00, 64000.00, 2800000.00, 3384000.00), --PN 201746 [Loan 789123 (CRB of SDMD)]
( 0.00, 18000.00, 80000.00, 110000.00, 34000.00, 1700000.00, 1924000.00), --PN 201757 [Loan 891234 (CRB of SDMD)]
( 0.00, 21000.00, 120000.00, 250000.00, 50000.00, 1100000.00, 1520000.00), --PN 201768 [Loan 891234 (CRB of SDMD)]
( 0.00, 45000.00, 390000.00, 550000.00, 53300.00, 1300000.00, 2293300.00), --PN 201779 [Loan 912345 (Cooperative Bank of SDMD)]
( 0.00, 67000.00, 300000.00, 190000.00, 54400.00, 1500000.00, 2044400.00), --PN 201744 [Loan 912345 (Cooperative Bank of SDMD)]
( 0.00, 20000.00, 140000.00, 450000.00, 58000.00, 1200000.00, 1848000.00), --PN 201792 [Loan 565541 (Cooperative Bank of SDMD)]
( 0.00, 0.00, 200000.00, 320000.00, 64000.00, 2800000.00, 3384000.00), --PN 201722 [Loan 565541 (Cooperative Bank of SDMD)]
( 0.00, 18000.00, 80000.00, 110000.00, 34000.00, 1700000.00, 1924000.00), --PN 201797 [Loan 878999 (RB of SDMD)]
( 0.00, 21000.00, 120000.00, 250000.00, 50000.00, 1100000.00, 1520000.00), --PN 201787 [Loan 878999 (RB of SDMD)]
( 0.00, 45000.00, 390000.00, 550000.00, 53300.00, 1300000.00, 2293300.00), --PN 201782 [Loan 741335 (RB of SDMD)]
( 0.00, 67000.00, 300000.00, 190000.00, 54400.00, 1500000.00, 2044400.00), --PN 201733 [Loan 741335 (RB of SDMD)]
( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --Bangkong Luma
( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --Bangkong Medyo Luma
( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --Bangkong Medyo Bago
( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --Loan 999990 (Bangkong Luma)
( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --Loan 999991 (Bangkong Medyo Luma)
( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --Loan 999992 (Bangkong Medyo Bago)
( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --PN 201751 [Loan 999990 (Bangkong Luma)]
( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --PN 201752 [Loan 999991 (Bangkong Medyo Luma)]
( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --PN 201753 [Loan 999992 (Bangkong Medyo Bago)]
( 0.00, 36000.00, 66000.00, 96000.00, 156000.00, 900000.00, 12180000.00), --Bangko Mo
( 0.00, 36000.00, 66000.00, 96000.00, 156000.00, 900000.00, 1218000.00), --Loan 999993 (Bangko Mo)
( 0.00, 14000.00, 24000.00, 34000.00, 54000.00, 200000.00, 312000.00), --PN 201771 [Loan 999993 (Bangko Mo)]
( 0.00, 12000.00, 22000.00, 32000.00, 52000.00, 250000.00, 356000.00), --PN 201772 [Loan 999993 (Bangko Mo)]
( 0.00, 10000.00, 20000.00, 30000.00, 50000.00, 450000.00, 550000.00), --PN 201773 [Loan 999993 (Bangko Mo)]
( 0.00, 10000.00, 20000.00, 30000.00, 70000.00, 1000000.00, 1120000.00), --Bangko Ko
( 0.00, 10000.00, 20000.00, 30000.00, 70000.00, 1000000.00, 1120000.00), --Loan 999994 (Bangko Ko)
( 0.00, 10000.00, 20000.00, 30000.00, 70000.00, 1000000.00, 1120000.00); --PN 201774 [Loan 999994 (Bangko Ko)]


 INSERT INTO balance_tbl (ref_id, balance_type, cwt, others, liquidated_damages, accrued_interest_receivable, interest_income, outstanding_principal, total_outstanding_balance) VALUES
(32, 'PN', 0.00, 0.00, 50000.00, 150000.00, 75000.00, 225000.00, 500000.00),
(33, 'PN', 0.00, 0.00, 60000.00, 160000.00, 80000.00, 200000.00, 500000.00),
(34, 'PN', 0.00, 0.00, 50000.00, 150000.00, 75000.00, 225000.00, 500000.00),
(35, 'PN', 0.00, 0.00, 10000.00, 20000.00, 15000.00, 55000.00, 100000.00),
(36, 'PN', 0.00, 0.00, 20000.00, 40000.00, 30000.00, 110000.00, 200000.00),
(37, 'PN', 0.00, 0.00, 30000.00, 60000.00, 45000.00, 165000.00, 300000.00),
(18, 'LOAN', 0.00, 0.00, 160000.00, 460000.00, 230000.00, 650000.00, 1500000.00),
(19, 'LOAN', 0.00, 0.00, 60000.00, 120000.00, 90000.00, 330000.00, 600000.00),
(38, 'PN', 0.00, 0.00, 125000.00, 100000.00, 125000.00, 350000.00, 700000.00),
(39, 'PN', 0.00, 0.00, 250000.00, 75000.00, 100000.00, 325000.00, 750000.00),
(20, 'LOAN', 0.00, 0.00, 300000.00, 225000.00, 275000.00, 650000.00, 1450000.00),
(12, 'BANK', 0.00, 0.00, 220000.00, 580000.00, 320000.00, 980000.00, 2100000.00),
(40, 'PN', 0.00, 0.00, 40000.00, 55000.00, 30000.00, 125000.00, 250000.00),
(21, 'LOAN', 0.00, 0.00, 40000.00, 55000.00, 30000.00, 125000.00, 250000.00),
(13, 'BANK', 0.00, 0.00, 340000.00, 280000.00, 305000.00, 775000.00, 1700000.00);

INSERT INTO balance_tbl (cwt, others, liquidated_damages, accrued_interest_receivable, interest_income, outstanding_principal, total_outstanding_balance) VALUES
 ( 0.00, 0.00, 540000.00, 1130000.00, 206000.00, 1200000.00, 3076000.00), --PN 75
 ( 0.00, 0.00, 1030000.00, 1510000.00, 229700.00, 2800000.00, 5569700.00), --PN 76
 ( 0.00, 0.00, 890000.00, 1100000.00, 191700.00, 2000000.00, 4181700.00), --PN 77
 ( 0.00, 0.00, 2460000.00, 3740000.00, 627400.00, 6000000.00, 12827400.00); --LOAN 78

INSERT INTO balance_tbl (cwt, others, liquidated_damages, accrued_interest_receivable, interest_income, outstanding_principal, total_outstanding_balance) VALUES
 ( 0.00, 0.00, 540000.00, 1130000.00, 206000.00, 1200000.00, 3076000.00), --PN 79
 ( 0.00, 0.00, 1030000.00, 1510000.00, 229700.00, 2800000.00, 5569700.00), --PN 80
 ( 0.00, 0.00, 890000.00, 1100000.00, 191700.00, 2000000.00, 4181700.00), --PN 81
 ( 0.00, 0.00, 2460000.00, 3740000.00, 627400.00, 6000000.00, 12827400.00); --LOAN 82

INSERT INTO balance_tbl (ref_id, balance_type, cwt, others, liquidated_damages, accrued_interest_receivable, interest_income, outstanding_principal, total_outstanding_balance) VALUES
 (47, 'PN', 0.00, 0.00, 50000.00, 150000.00, 75000.00, 225000.00, 500000.00), -- PBB -- 83
 (48, 'PN', 0.00, 0.00, 60000.00, 160000.00, 80000.00, 200000.00, 500000.00), -- PBB -- 84
 (49, 'PN', 0.00, 0.00, 50000.00, 150000.00, 75000.00, 225000.00, 500000.00), -- PBB -- 85
 (50, 'PN', 0.00, 0.00, 10000.00, 20000.00, 15000.00, 55000.00, 100000.00), -- PBB -- 86
 (51, 'PN', 0.00, 0.00, 20000.00, 40000.00, 30000.00, 110000.00, 200000.00), -- PBB -- 87
 (52, 'PN', 0.00, 0.00, 30000.00, 60000.00, 45000.00, 165000.00, 300000.00), -- PBB -- 88
 (24, 'LOAN', 0.00, 0.00, 420000.00, 460000.00, 230000.00, 650000.00, 1500000.00),  -- PBB -- 89
 (25, 'LOAN', 0.00, 0.00, 260000.00, 690000.00, 325000.00, 975000.00, 2250000.00),  -- PBB -- 90
 (14, 'BANK', 0.00, 0.00, 680000.00, 1150000.00, 555000.00, 1625000.00, 3750000.00);  -- PBB -- 91

 INSERT INTO balance_tbl (ref_id, balance_type, cwt, others, liquidated_damages, accrued_interest_receivable, interest_income, outstanding_principal, total_outstanding_balance) VALUES
 (53, 'PN', 0.00, 0.00, 70000.00, 190000.00, 80000.00, 230000.00, 570000.00),
 (54, 'PN', 0.00, 0.00, 50000.00, 210000.00, 90000.00, 210000.00, 560000.00),
 (55, 'PN', 0.00, 0.00, 80000.00, 170000.00, 65000.00, 205000.00, 520000.00);

INSERT INTO bank_tbl ( bank_code, bank_name, bank_type, bank_status, date_joined, bank_address, date_close, ref_balance_id) VALUES
 ( 'BPI', 'Bank of the Philippine Islands', 'Rural', 'Operating', '1999-05-21', 'Gapan Nueva Ecija', NULL, 1),
 ( 'BDO', 'Banco De Oro', 'Rural', 'Operating', '1999-10-22', 'San Mateo Rizal', NULL, 2),
 ( 'MB', 'Metro Bank', 'Coop', 'Operating', '2000-05-21', 'Blumentritt', NULL, 3),
 ( 'CRBSDMD', 'CRB of SDMD', 'Coop', 'Operating', '1995-05-21', 'Quezon City', NULL, 4),
 ( 'CBSDMD', 'Cooperative Bank of SDMD', 'Coop', 'Operating', '2000-05-21', 'Makati City', NULL, 5),
 ( 'RBSDMD', 'RB of SDMD', 'Rural', 'Operating', '2000-05-21', 'Pampanga', NULL, 6),
 ( 'BL', 'Bangkong Luma', 'Rural', 'Closed', '1970-05-21', 'Cabiao Nueva Ecija', '1980-07-09', 43),
 ( 'BML', 'Bangkong Medyo Luma', 'Rural', 'Closed', '1975-06-30', 'Cabanatuan Nueva Ecija', '1980-07-09', 44),
 ( 'BMB', 'Bangkong Medyo Bago', 'Rural', 'Closed', '2005-11-01', 'Arayat Pampanga', '2015-07-09', 45),
 ( 'BM', 'Bangko Mo', 'Rural', 'Closed', '2000-10-08', 'Jaro Iloilo', '2012-10-08', 52),
 ( 'BK', 'Bangko Ko', 'Rural', 'Closed', '1980-10-08', 'Tagbak Iloilo', '1990-10-08', 57),
 ( 'RCBC', 'Rizal Commercial Banking Corporation', 'Rural', 'Operating', '1999-05-21', 'Marikina City', NULL, 71),
 ( 'UPCB', 'United Coconut Planters Bank', 'Rural', 'Operating', '1999-10-22', 'Quezon City', NULL, 74);

INSERT INTO bank_tbl ( bank_code, bank_name, bank_type, bank_status, date_joined, bank_address, date_close, ref_balance_id) VALUES
 ( 'PBB', 'Philippine Business Bank', 'Rural', 'Operating', '1999-10-22', 'Quezon City', NULL, 91); -- 14

INSERT INTO loan_tbl ( loan_application_number, ref_bank_id, loan_type, loan_status, loan_interest_rate, date_granted, maturity_date, loan_term, amount_granted, ref_balance_id) VALUES
(123456, 1, 'Rediscounting', 'Current Accounts', 0.0313, '2016-05-16', '2016-11-12', 180, 4000000.00, 7),
(234567, 1, 'Rediscounting', 'Current Accounts', 0.0313, '2016-07-08', '2017-01-04', 180, 2800000.00, 8),
(345678, 2, 'Rediscounting', 'Current Accounts', 0.0313, '2016-07-21', '2017-01-17', 180, 4000000.00,9),
(456789, 2, 'Rediscounting', 'Current Accounts', 0.03, '2017-02-22', '2017-08-21', 180, 2800000.00,10),
(567891, 3, 'Rediscounting', 'Current Accounts', 0.03, '2016-05-21', '2016-11-17', 180, 4000000.00,11),
(678912, 3, 'Rediscounting', 'Current Accounts', 0.0313, '2017-01-22', '2017-07-21', 180, 2800000.00,12),
(789123, 4, 'Rediscounting', 'Current Accounts', 0.0313, '2016-05-21', '2017-05-16', 360, 4000000.00,13),
(891234, 4, 'Rediscounting', 'Current Accounts', 0.03, '2017-02-18', '2018-02-13', 360, 2800000.00, 14),
(912345, 5, 'Rediscounting', 'Current Accounts', 0.03, '2016-01-11', '2017-01-05', 360,4000000.00,15),
(565541, 5, 'Rediscounting', 'Current Accounts', 0.03, '2016-01-03', '2016-12-28', 360, 2800000.00,16),
(878999, 6, 'Rediscounting', 'Current Accounts', 0.03, '2016-07-11', '2017-07-06', 360, 4000000.00,17),
(741335, 6, 'Rediscounting', 'Current Accounts', 0.03, '2016-09-26', '2017-09-21', 360, 2800000.00,18),
(999990, 7, 'Rediscounting', 'Past Due', 0.12, '1975-12-01', '1976-05-29', 180, 500000.00, 46),
(999991, 8, 'Rediscounting', 'Past Due', 0.12, '1977-12-01', '1978-05-30', 180, 500000.00, 47),
(999992, 9, 'Rediscounting', 'Past Due', 0.12, '2000-12-01', '2001-05-30', 180, 500000.00, 48),
(999993, 10, 'Rediscounting', 'Past Due', 0.10, '2010-06-01', '2010-11-28', 180, 900000.00, 53);

INSERT INTO loan_tbl ( loan_application_number, ref_bank_id, loan_type, loan_status, loan_interest_rate, date_granted, maturity_date, amount_granted, ref_balance_id, reprice_rate_flag) VALUES
(999994, 11, 'Rediscounting', 'Past Due', 0.10, '1988-06-01', '1988-11-28', 1000000.00, 58, 1);

INSERT INTO loan_tbl ( loan_application_number, ref_bank_id, loan_type, loan_status, loan_interest_rate, date_granted, maturity_date, loan_term, amount_granted, ref_balance_id) VALUES
(999901, 12, 'Rediscounting', 'Past Due', 0.12, '2016-05-16', '2016-11-12', 180, 1500000.00, 66),
(999902, 12, 'Rediscounting', 'Current Accounts', 0.12, '2016-05-16', '2016-11-12', 180, 600000.00, 67),
(999903, 13, 'Rediscounting', 'Current Accounts', 0.18, '2016-05-16', '2016-11-12', 180, 1450000.00, 70),
(999904, 13, 'Rediscounting', 'Current Accounts', 0.07, '2016-05-16', '2016-11-12', 180, 250000.00, 73);

INSERT INTO loan_tbl ( loan_application_number, ref_bank_id, loan_type, loan_status, loan_interest_rate, date_granted, maturity_date, loan_term, amount_granted, ref_balance_id) VALUES
(999995, 1, 'Emergency Loan', 'Current Accounts', 0.0313, '2016-05-16', '2016-11-12', 180, 6000000.00, 78);

INSERT INTO loan_tbl ( loan_application_number, ref_bank_id, loan_type, loan_status, loan_interest_rate, date_granted, maturity_date, loan_term, amount_granted, ref_balance_id) VALUES
(999996, 2, 'Emergency Loan', 'Current Accounts', 0.0313, '2016-05-16', '2016-11-12', 180, 6000000.00, 82);

INSERT INTO loan_tbl ( loan_application_number, ref_bank_id, loan_type, loan_status, loan_interest_rate, date_granted, maturity_date, loan_term, amount_granted, ref_balance_id) VALUES
 (999997, 14, 'Rediscounting', 'Past Due', 0.12, '2016-05-16', '2016-11-12', 180, 1500000.00, 89), -- 24
 (999998, 14, 'Rediscounting', 'Current Accounts', 0.12, '2016-05-16', '2016-11-12', 180, 2250000.00, 90); -- 25

INSERT INTO END_USER_BORROWER_TBL(entity_name, full_name) VALUES
 ('Mcdonalds' , 'Ray Kroc'),
 ('Jollibee' , 'Tony Tan Caktiong'),
 ('KFC' , 'Colonel Sanders'),
 ('Mang Inasal' , 'Edgar Sia'),
 ('Wendys' , 'Dave Thomas'),
 ('Coca-cola' , 'John Smith Pemberton'),
 ('Starbucks' , 'Gordon Bowker'),
 ('Reddit' , 'Steve Huffman, Alexis Ohanian'),
 ('Pizza Hut' , 'Dan Carney, Frank Carney'),
 ('Pepsi' , 'Thomas Karlton'),
 ('Bandai' , 'Juan Ted'),
 ('Nike' , 'Yeri Youngston'),
 ('Adidas' , 'Sherley Maedar'),
 ('Zesto' , 'Steve Jobs'),
 ('Microsoft' , 'Bill Gates'),
 ('Facebook' , 'Mark Zuckerberg'),
 ('KKK' , 'Jensen Huang'),
 ('Wendys' , 'Henry Sy'),
 ('JDC' , 'Juan Dela Cruz'),
 ('JOHNDC' , 'John Dela Cruz'),
 ('JODC' , 'Jo Dela Cruz'),
 ('CA Corp' , 'Corazon Aquino'),
 ('MQ' , 'Manuel Quezon'),
 ('SA Inc.' , 'Sergio Osmena'),
 ('ATon' , 'Antonio Luna'),
 ('GSM' , 'Gil More'),
 ('Empi' , 'Empoy Incheon'),
 ('Matador' , 'Mara Doria'),
 ('Tequila' , 'Teddy Quila'),
 ('Margarita' , 'Marga Rita'),
 ('Guiness' , 'Guiness Record'),
 ('Murphys' , 'Eddie Murphy'),
 ('Stout' , 'Stand Out'),
 ('Baileys' , 'Bailey Ann');

INSERT INTO END_USER_BORROWER_TBL(entity_name, full_name) VALUES
 ('Dell' , 'Mundo'),
 ('Gold' , 'Smart'),
 ('Silver' , 'Brite');

INSERT INTO pn_tbl ( pn_number, ref_loan_id, pn_status, pn_interest_rate, date_granted, maturity_date, amount_granted, ref_balance_id, ref_borrower_id) VALUES
 ( 201712, 1, 'Current Accounts', 10, '2016-01-01', '2017-01-05', 1200000.00,19,1),
 ( 201723, 1, 'Current Accounts', 10, '2016-05-11', '2017-02-20', 2800000.00, 20,2),
 ( 201734, 2, 'Current Accounts', 10, '2016-05-12', '2017-03-19', 1700000.00, 21,3),
 ( 201745, 2, 'Current Accounts', 10, '2016-05-13', '2017-04-16', 1100000.00, 22,4),
 ( 201756, 3, 'Current Accounts', 10, '2016-05-14', '2017-02-23', 1300000.00, 23,5),
 ( 201767, 3, 'Current Accounts', 10, '2016-03-14', '2017-02-05', 1500000.00, 24,6),
 ( 201778, 4, 'Current Accounts', 10, '2016-05-15', '2017-02-05', 1200000.00, 25,7),
 ( 201789, 4, 'Current Accounts', 9, '2016-07-21', '2017-02-05', 2800000.00, 26,8),
 ( 201791, 5, 'Current Accounts', 9, '2017-01-28', '2017-02-15', 1700000.00, 27,9),
 ( 201713, 5, 'Current Accounts', 9, '2017-01-29', '2018-10-25', 1100000.00, 28,10),
 ( 201724, 6, 'Current Accounts', 9, '2016-05-10', '2018-08-22', 1300000.00, 29,11),
 ( 201729, 6, 'Current Accounts', 9, '2016-03-15', '2018-04-24', 1500000.00, 30,12),
 ( 201735, 7, 'Current Accounts', 9, '2017-02-22', '2018-09-22', 1200000.00, 31,13),
 ( 201746, 7, 'Current Accounts', 12, '2015-12-16', '2018-11-03', 2800000.00,32,14),
 ( 201757, 8, 'Current Accounts', 12, '2017-12-22', '2018-11-05', 1700000.00, 33,15),
 ( 201768, 8, 'Current Accounts', 12, '2014-05-18', '2018-12-05', 1100000.00, 34,16),
 ( 201779, 9, 'Current Accounts', 12, '2015-01-22', '2018-02-15', 1300000.00, 35,17),
 ( 201744, 9, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 1500000.00,36,18),
 ( 201792, 10, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 1200000.00,37,null),
 ( 201722, 10, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 2800000.00,38,null),
 ( 201797, 11, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 1700000.00,39,null),
 ( 201787, 11, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 1100000.00,40,null),
 ( 201782, 12, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 1300000.00,41,null),
 ( 201733, 12, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 1500000.00,42,null),
 ( 201751, 13, 'Past Due', 0.12, '1975-12-01', '1976-05-29', 500000.00, 49,19),
 ( 201752, 14, 'Past Due', 0.12, '1977-12-01', '1978-05-30', 500000.00, 50,20),
 ( 201753, 15, 'Past Due', 0.12, '2000-12-01', '2001-05-30', 500000.00, 51,21),
 ( 201771, 16, 'Past Due', 0.18, '2009-12-03', '2010-11-28', 200000.00, 54,22),
 ( 201772, 16, 'Past Due', 0.18, '2009-12-03', '2010-11-28', 250000.00, 55,23),
 ( 201773, 16, 'Past Due', 0.20, '2009-12-02', '2010-11-27', 450000.00, 56,24),
 ( 201774, 17, 'Past Due', 0.18, '1988-06-03', '1988-11-28', 1000000.00, 59,25),
 ( 202000, 18, 'Past Due', 0.15, '2016-01-01', '2017-01-05', 500000.00, 60,26),
 ( 202001, 18, 'Past Due', 0.15, '2016-01-01', '2017-01-05', 500000.00, 61,27),
 ( 202002, 18, 'Past Due', 0.15, '2016-01-01', '2017-01-05', 500000.00, 62,28),
 ( 202003, 19, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 100000.00, 63,29),
 ( 202004, 19, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 200000.00, 64,30),
 ( 202005, 19, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 300000.00, 65,31),
 ( 202006, 20, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 700000.00, 68,32),
 ( 202007, 20, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 750000.00, 69,33),
 ( 202008, 21, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 250000.00, 72,34);

INSERT INTO pn_tbl ( pn_number, ref_loan_id, pn_status, pn_interest_rate, date_granted, maturity_date, amount_granted, ref_balance_id, ref_borrower_id) VALUES
 ( 202009, 22, 'Current Accounts', 10, '2016-01-01', '2017-01-05', 1200000.00,75,35),
 ( 202010, 22, 'Current Accounts', 10, '2016-05-11', '2017-02-20', 2800000.00, 76,36),
 ( 202011, 22, 'Current Accounts', 10, '2016-05-12', '2017-03-19', 2000000.00, 77,37);

INSERT INTO pn_tbl ( pn_number, ref_loan_id, pn_status, pn_interest_rate, date_granted, maturity_date, amount_granted, ref_balance_id, ref_borrower_id) VALUES
 ( 202012, 23, 'Current Accounts', 10, '2016-01-01', '2017-01-05', 1200000.00,79, 33),
 ( 202013, 23, 'Current Accounts', 10, '2016-05-11', '2017-02-20', 2800000.00, 80, 33),
 ( 202014, 23, 'Current Accounts', 10, '2016-05-12', '2017-03-19', 2000000.00, 81, 33);

INSERT INTO pn_tbl ( pn_number, ref_loan_id, pn_status, pn_interest_rate, date_granted, maturity_date, amount_granted, ref_balance_id, ref_borrower_id) VALUES
 ( 202015, 24, 'Past Due', 0.16, '2016-01-01', '2017-01-05', 500000.00, 83,26),
 ( 202016, 24, 'Past Due', 0.15, '2016-01-01', '2017-01-05', 500000.00, 84,27),
 ( 202017, 24, 'Past Due', 0.14, '2016-01-01', '2017-01-05', 500000.00, 85,28),
 ( 202018, 25, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 100000.00, 86,29),
 ( 202019, 25, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 200000.00, 87,30),
 ( 202020, 25, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 300000.00, 88,31);

 INSERT INTO pn_tbl ( pn_number, ref_loan_id, pn_status, pn_interest_rate, date_granted, maturity_date, amount_granted, ref_balance_id, ref_borrower_id) VALUES
 ( 202021, 25, 'For Foreclosure', 10, '2016-01-01', '2017-01-05', 570000.00, 92,34),
 ( 202022, 25, 'For Foreclosure', 10, '2016-01-01', '2017-01-05', 560000.00, 93,34),
 ( 202023, 25, 'For Foreclosure', 10, '2016-01-01', '2017-01-05', 520000.00, 94,34);

INSERT INTO registry_of_deeds_tbl(registry_of_deeds) VALUES
 ('RD - Manila'),
 ('RD - Quezon City'),
 ('RD - Makati City'),
 ('RD - Pasay City'),
 ('RD - Malabon / Navotas'),
 ('RD - Valenzuela'),
 ('RD - San Juan'),
 ('RD - Mandaluyong'),
 ('RD - Pasig City'),
 ('RD - Parañaque'),
 ('RD - Antipolo City'),
 ('RD - Caloocan City'),
 ('RD - Las Piñas'),
 ('RD - Marikina City'),
 ('RD - Muntinlupa City'),
 ('RD - Province Of Rizal (Binangonan)'),
 ('RD - Morong, Rizal'),
 ('RD - Taguig City');

INSERT INTO collateral_tbl(collateral_number, ref_rd_id, address, appraisal_date, fmv) VALUES
('TCT-101', 1, '6153 South Super Highway Makati, Philippines', '2017-09-12', 0),
('TCT-102', 2, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2017-09-13', 0),
('TCT-103', 3, '6153 South Super Highway Makati, Philippines', '2014-09-14', 0),
('TCT-104', 4, '14 Zaragoza St. San Lorenzo Village, Makati', '2016-10-17', 0),
('TCT-105', 5, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2015-08-07', 0),
('TCT-106', 6, '6153 South Super Highway Makati, Philippines', '2016-01-29', 0),
('TCT-107', 7, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', '2011-12-26', 0),
('TCT-108', 8, '14 Zaragoza St. San Lorenzo Village, Makati', '2012-11-08', 0),
('TCT-109', 9, '6153 South Super Highway Makati, Philippines', '2013-11-02', 0),
('TCT-110',10, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2017-07-14', 6000000.00),
('TCT-111',11, '6153 South Super Highway Makati, Philippines', '2017-07-14', 75000.00),
('TCT-112',12, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', '2017-07-14', 150000.00),
('TCT-113',13, '14 Zaragoza St. San Lorenzo Village, Makati', '2016-06-16', 0),
('TCT-114',14, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2015-05-18', 200000.00),
('TCT-115',15, '14 Zaragoza St. San Lorenzo Village, Makati', '2014-02-05', 4750000.00),
('TCT-116',16, '6153 South Super Highway Makati, Philippines', '2014-02-05', 9500000.00),
('TCT-117',17, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', NULL, 0),
('TCT-118',18, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', NULL, 0),
('TCT-119',1, '14 Zaragoza St. San Lorenzo Village, Makati', NULL, 0),
('TCT-120', 2, '6153 South Super Highway Makati, Philippines', NULL, 0),
('TCT-121', 3, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', NULL, 0),
('TCT-122', 4, '14 Zaragoza St. San Lorenzo Village, Makati', NULL, 0),
('TCT-123', 5, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', NULL, 0),
('TCT-124', 6, '6153 South Super Highway Makati, Philippines', NULL, 0),
('TCT-125', 7, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', NULL, 0),
('TCT-126', 8, '14 Zaragoza St. San Lorenzo Village, Makati', NULL, 0),
('TCT-127', 9, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', NULL, 0),
('TCT-128',10, '6153 South Super Highway Makati, Philippines', NULL, 0),
('TCT-129',11, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', NULL, 0),
('TCT-130',12, '14 Zaragoza St. San Lorenzo Village, Makati', NULL, 0),
('TCT-131',13, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', NULL, 0),
('TCT-132',14, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', NULL, 0),
('TCT-133',15, '6153 South Super Highway Makati, Philippines', NULL, 0),
('TCT-134',16, '14 Zaragoza St. San Lorenzo Village, Makati', NULL, 0),
('TCT-135',17, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', NULL, 0),
('TCT-136',18, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', NULL, 0),
('TCT-137', 1, '14 Zaragoza St. San Lorenzo Village, Makati', NULL, 0),
('TCT-138', 2, '6153 South Super Highway Makati, Philippines', NULL, 0),
('TCT-139', 3, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', NULL, 0),
('TCT-140', 4, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', NULL, 0),
('TCT-141', 5, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', NULL, 0),
('TCT-142', 6, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', NULL, 0),
('TCT-143', 7, '6153 South Super Highway Makati, Philippines', NULL, 0),
('TCT-144', 8, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', NULL, 0),
('TCT-145', 9, '14 Zaragoza St. San Lorenzo Village, Makati', NULL, 0),
('TCT-146',10, '6153 South Super Highway Makati, Philippines', NULL, 0),
('TCT-147', 3, '6154 South Super Highway Makati, Philippines', NULL, 0);

INSERT INTO role_tbl(role) VALUES ('Maker'),('Reviewer'),('Approver');

INSERT INTO user_tbl(user_name, user_role_id) VALUES
	('Jane_Maker', 1),
	('John_Reviewer', 2),
	('Josh_Approver', 3),
	('fsm.maker', 1),
	('fsm.reviewer', 2),
	('fsm.approver', 3),
	('lmm.maker', 1),
	('lmm.reviewer', 2),
	('lmm.approver', 3);

INSERT INTO chart_of_accts_tbl(chart_account_ref_code,chart_account_title,chart_account_code,chart_default_entry_type) VALUES
('PO-DLC-CASHCHK','DEPARTMENT OF LOANS AND CREDIT','172500000061000000','CREDIT');

INSERT INTO PAYMENT_ORDER_TBL(
payment_order_number, payment_order_date, bank_name, end_user_borrower, payment_order_status, or_number, or_date, user_name, receiving_office, bank_level_payment_type, total_payment_amount, payment_type, loan_type, payment_level)
VALUES('1113', '2017-07-06', 'Banco De Oro', 'Kevin Grosskreutz', 'SUBMITTED TO REVIEWER', '1', '2017-07-06', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 1000000.00, 'CASH/CHECK', 'Rediscounting', ''),
('1121', '2017-05-21', 'Bank of the Philippine Islands', 'Bastian Schweinsteiger', 'COMPLETED', '3', '2017-05-21', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 2000000.00, 'CASH/CHECK', 'Rediscounting', ''),
('1042', '2017-11-30', 'Metro Bank', 'Florian Grillitsch', 'SUBMITTED TO APPROVER', '2', '2017-11-30', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 3000000.00, 'CASH/CHECK', 'Rediscounting', ''),
('1873', '2017-02-14', 'Bank of the Philippine Islands', 'Marcel Sabitzer', 'SUBMITTED TO REVIEWER', '4', '2017-02-14', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
('2334', '2017-03-06', 'Banco De Oro', 'Lionel Messi', 'SUBMITTED TO APPROVER', '5', '2017-03-06', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
('2001', '2017-05-21', 'Bank of the Philippine Islands', 'Luis Suarez', 'RETURNED TO MAKER', '6', '2017-05-21', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 10000000.00, 'CASH/CHECK', 'Rediscounting', 'BANK'),
('1423', '2017-12-30', 'Metro Bank', 'Marcel Schmelzer', 'REJECTED', '7', '2017-12-30', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
('1356', '2017-01-14', 'Bank of the Philippine Islands', 'Arturo Vidal', 'DRAFT', '8', '2017-01-14', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
('5003', '2017-02-06', 'Banco De Oro', 'Gareth Bale', 'POSTED', '5', '2017-02-06', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
('1010', '2017-07-21', 'Metro Bank', 'Wayne Rooney', 'APPROVED', '6', '2017-07-21', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
('1169', '2017-08-30', 'Bank of the Philippine Islands', 'Manuel Neuer', 'COMPLETED', '7', '2017-08-30', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
('6934', '2017-06-14', 'Bank of the Philippine Islands', 'Keylor Navas', 'RETURNED TO MAKER', '8', '2017-06-14', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 10000000.00, 'CASH/CHECK', 'Rediscounting', 'BANK');

INSERT INTO PAYMENT_ORDER_TBL(
payment_order_number, payment_order_date, bank_name, end_user_borrower, payment_order_status, payment_level, or_number, or_date, user_name, receiving_office, bank_level_payment_type, total_payment_amount, payment_type, loan_type)
VALUES('0001', '2017-07-06', 'Rizal Commercial Banking Corporation', 'Kevin Grosskreutz', 'Approved', 'PN', '0001', '2017-07-06', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
('0002', '2017-05-21', 'Rizal Commercial Banking Corporation', 'Bastian Schweinsteiger', 'Approved', 'PN', '0003', '2017-05-21', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
('0003', '2017-02-14', 'Rizal Commercial Banking Corporation', 'Marcel Sabitzer', 'Approved', 'PN', '0004', '2017-02-14', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
('0004', '2017-03-06', 'United Coconut Planters Bank', 'Lionel Messi', 'Approved', 'PN', '0005', '2017-03-06', 'Jane_Maker', 'Cash Dept', '', 3000000.00, 'CASH/CHECK', 'Rediscounting'),
('0005', '2017-05-21', 'Rizal Commercial Banking Corporation', 'Luis Suarez', 'Approved', 'PN', '0007', '2017-05-21', 'Jane_Maker', 'Cash Dept', '', 3000000.00, 'CASH/CHECK', 'Rediscounting'),
('0006', '2017-01-14', 'Rizal Commercial Banking Corporation', 'Arturo Vidal', 'Approved', 'PN', '0008', '2017-01-14', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
('0007', '2017-02-06', 'United Coconut Planters Bank', 'Gareth Bale', 'Rejected', 'PN', '0009', '2017-02-06', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
('0008', '2017-08-30', 'Rizal Commercial Banking Corporation', 'Manuel Neuer', 'Completed', 'PN', '0010', '2017-08-30', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
('0009', '2017-06-14', 'Rizal Commercial Banking Corporation', 'Keylor Navas', 'Returned', 'PN', '0011', '2017-06-14', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
('0010', '2017-07-14', 'Bank of the Philippine Islands', 'Nyeamm Gaza', 'Approved','LOAN', '0011', '2017-07-14', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting' ),
('0011', '2017-07-14', 'Banco De Oro', 'Bill Gates', 'Approved', 'BANK', '0012', '2017-07-14', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 1000000.00, 'CASH/CHECK', 'Rediscounting');

INSERT INTO TRANSACTION_TBL(payment_number, payment_order_number, transaction_date, total_payment, year_created, payment_date, transaction_type, status) VALUES
 ('2017001234', '1113','2017-07-03', 1000000.00, 2017, '2017-03-22', 'PO', 'POSTED');

INSERT INTO TRANSACTION_TBL(payment_number, payment_order_number, transaction_date, total_payment, year_created, payment_date, transaction_type) VALUES
 ('2017002345', '1121','2017-07-03', 2000000.00, 2017, '2017-04-20', 'PO'),
 ('2017004567', '1042','2017-07-03', 3000000.00, 2017, '2017-06-20', 'PO'),
   ('2017001002', '1873', '2017-07-17', 100000.00, 2017, '2017-07-17', 'PO'),
 ('2017001003', '2334', '2017-07-17', 100000.00, 2017, '2017-07-17', 'PO'),
 ('2017001004', '2001', '2017-07-17', 10000000.00, 2017, '2017-07-17', 'PO'),
 ('2017001005', '1423', '2017-07-17', 100000.00, 2017, '2017-07-17', 'PO'),
 ('2017002001', '1356', '2017-07-17', 100000.00, 2017, '2017-07-17', 'PO'),
 ('2017002002', '5003', '2017-07-16', 100000.00, 2017, '2017-07-16', 'PO'),
 ('2017002003', '1010', '2017-07-16', 100000.00, 2017, '2017-07-16', 'PO'),
 ('2017002004', '1169', '2017-07-16', 100000.00, 2017, '2017-07-16', 'PO'),
 ('2017002005', '6934', '2017-07-16', 10000000.00, 2017, '2017-07-16', 'PO');

 INSERT INTO TRANSACTION_TBL(pn_number, payment_number, year_created, status) VALUES
 (201771, '9991', 2010, 'POSTED'),
 (201772, '9992', 2011, 'POSTED'),
 (201773, '9993', 2014, 'POSTED');

INSERT INTO rediscounting_interest_rates_tbl (effectivity_date, red_interest_rate) VALUES
 ('2017-06-01', 0.045);


UPDATE BALANCE_TBL BAL
SET (BAL.REF_ID, BAL.BALANCE_TYPE) = (
 SELECT PN.PN_ID, 'PN'
 FROM PN_TBL PN
 WHERE PN.REF_BALANCE_ID = BAL.BALANCE_ID
)
WHERE BAL.BALANCE_ID IN (
 SELECT PN.REF_BALANCE_ID FROM PN_TBL PN
);

UPDATE BALANCE_TBL BAL
SET (BAL.REF_ID, BAL.BALANCE_TYPE) = (
 SELECT LOAN.LOAN_ID, 'LOAN'
 FROM LOAN_TBL LOAN
 WHERE LOAN.REF_BALANCE_ID = BAL.BALANCE_ID
)
WHERE BAL.BALANCE_ID IN (
 SELECT LOAN.REF_BALANCE_ID FROM LOAN_TBL LOAN
);

UPDATE BALANCE_TBL BAL
SET (BAL.REF_ID, BAL.BALANCE_TYPE) = (
 SELECT BANK.BANK_ID, 'BANK'
 FROM BANK_TBL BANK
 WHERE BANK.REF_BALANCE_ID = BAL.BALANCE_ID
)
WHERE BAL.BALANCE_ID IN (
 SELECT BANK.REF_BALANCE_ID FROM BANK_TBL BANK
);

INSERT INTO SAP_TBL(sap_number, accrued_interest_payment_date, accrued_interest_payment, unpaid_accrued_interest, pn_number)
VALUES ('9991', '2010-10-08', 15000.00, 1722.22, 201771);

INSERT INTO SAP_TBL(sap_number, liquidated_damages_payment_date, liquidated_damages_payment, accrued_interest_payment_date, accrued_interest_payment, interest_income_payment_date, interest_income_payment, unpaid_interest_income, pn_number)
VALUES ('9992', '2011-03-06', 3402.78, '2011-03-06', 25000.00, '2011-03-06', 1000.00, 5805.56, 201772);

INSERT INTO SAP_TBL(sap_number, liquidated_damages_payment_date, liquidated_damages_payment, unpaid_liquidated_damages, pn_number)
VALUES ('9993', '2014-10-01', 70000.00, 17500.00, 201773);

INSERT INTO SAP_TBL(sap_number, pn_number, liquidated_damages_payment_date)
VALUES ('2017001234', 201712, '2016-05-16');


INSERT INTO payable_tbl (ref_payment_order_id,promissory_note_number,loan_application_number,
collateral_number,borrower_id,borrower_name,amount_granted,outstanding_principal_balance,
total_outstanding_balance,other_expenses,payment_amount,excess_amount,excess_payment_type, pn_outstanding_balance) VALUES
 (8,201744,999992,'TCT-118',18,'Henry Sy',1500000.00,1500000.00,2044400.00,67000.00,4000000.00,1888600.00,'Most Onerous', 0),
 (6,NULL,NULL,NULL,NULL,NULL,10000000.00,4000000.00,5232000.00,20000.00,2000000.00,1500000.00,'DLC Claims', 4000000.00),
 (12,NULL,NULL,NULL,NULL,NULL,10000000.00,4000000.00,5232000.00,20000.00,3000000.00,2000000.00,'Most Onerous', 4000000.00),
 (13,202000,999901,'TCT-126',26,'Gil More',500000.00,225000.00,500000.00,0,2000000.00,1500000.00,'DLC Claims', 0),
 (14,202001,999901,'TCT-127',27,'Empoy Incheon',500000.00,200000.00,500000.00,0,1100000.00,600000.00,'Most Onerous', 0),
 (15,202002,999901,'TCT-128',28,'Mara Doria',500000.00,225000.00,500000.00,0,1200000.00,700000.00,'DLC Claims', 0),
 (16, 202006, 999903, 'TCT-132', 32, 'Eddie Murphy', 700000.00, 350000.00, 700000.00, 0, 2000000.00, 1300000.00, 'Most Onerous', 0),
 (16, 202008, 999904, 'TCT-134', 34, 'Bailey Ann', 250000.00, 125000.00, 250000.00, 0, 1000000.00, 750000.00, 'Most Onerous', 0),
 (17, 202002, 999901, 'TCT-128', 28, 'Mara Doria', 500000.00, 225000.00, 500000.00, 0, 2000000.00, 1500000.00, 'DLC Claims', 0),
 (17, 202003, 999902, 'TCT-129', 29, 'Teddy Quila', 100000.00, 55000.00, 100000.00, 0, 1000000.00, 900000.00, 'DLC Claims', 0),
 (22, NULL, 999902, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 10000000, NULL, 'Most Onerous', 0),
   (23, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 10000000, NULL, 'Most Onerous', 0);

INSERT INTO foreclosure_status_tbl (status_name, status_description)
VALUES
 ('New', 'New'),
 ('Referred to Lawyer', 'Referred to Lawyer'),
 ('Draft Petition for Foreclosure under review', 'Draft Petition for Foreclosure under review'),
 ('Draft Petition for Foreclosure forwarded to Lawyer', 'Draft Petition for Foreclosure forwarded to Lawyer'),
 ('Petition Filed', 'Petition Filed'),
 ('With Notice of Auction', 'With Notice of Auction'),
 ('Bid Price Approved', 'Bid Price Approved'),
 ('Bid Price Forwarded to Lawyer', 'Bid Price Forwarded to Lawyer'),
 ('For Payment of Court''s Commission', 'For Payment of Court''s Commission'),
 ('Submitted to RD for COS Annotation', 'Submitted to RD for COS Annotation'),
 ('Foreclosure Booked', 'Foreclosure Booked'),
 ('Turned Over to Asset Management Department', 'Turned Over to Asset Management Department'),
 ('Awaiting Receipt of Winning Bid', 'Awaiting Receipt of Winning Bid'),
 ('Winning Bid Received', 'Winning Bid Received'),
 ('Released to Winning Bidder', 'Released to Winning Bidder');

INSERT INTO LAWYER_TBL (lawyer_name, lawyer_address, vat_registered, remarks)
VALUES ('ABELARDO P. DE JESUS '||chr(38)||' SIEGFRIED A. YBANEZ LAW FIRM', 'Unit 431 Park Avenue Mansion Park Avenue, Pasay City', 1, NULL) ,
('ATTY. FE BECINA-MACALINO '||chr(38)||' ASSOCIATES', 'Unit 7-L Westgate Tower Condominium Madrigal Business Center Ayala Alabang Muntinlupa City', 1, NULL)  ,
('BERNALDO DIRECTO '||chr(38)||' PO LAW OFFICES', 'Unit 1807 Cityland Condominium 10-Tower 1 6815 Ayala Ave. corner H. V. dela Costa St., Makati City', 1, NULL)  ,
('CRUZ MARCELO '||chr(38)||' TENEFRANCIA', '9th, 10th, 11th '||chr(38)||' 12th Floors, One Orion 11th Avenue corner University Parkway Bonifacio Global City 1634', 1, NULL)  ,
('GONZAGA LAW OFFICE', 'Unit 405 Jade Center Building 105 Shaw Boulevard, 1600 Pasig City', 0, NULL)  ,
('AGBAYANI '||chr(38)||' MENDOZA ATTORNEYS-AT-LAW', 'Unit 602 West Insula, 135 West Avenue 1105 Quezon City', 0, NULL) ,
('CUSTODIO ACORDA SICAM DE CASTRO '||chr(38)||' PANGANIBAN LAW OFFICES', '15th Floor, Citibank Tower Condominium Villar corner Valero Streets, Salcedo Village, 1227 Makati City', 1, 'formerly Ongkiko Manhit Custodio '||chr(38)||' Acorda Law Offices (OCMALAW)') ,
('R.S. REYES LAW OFFICES', 'Unit 406-407  Cornell St., South Point Town Homes Village, Brgy. Merville Leviste Avenue, Paranaque City', 1, NULL) ,
('TAGAYUNA PANOPIO ESCOBER LAW FIRM', '2nd Floor, AZPHI Bldg., 2709 Ma. Aurora St. corner E. Zobel St., Poblacion, Makati City', 1, NULL) ;

INSERT INTO LAWYER_CONTACT_TBL (contact_type, contact, ref_lawyer_id) VALUES
('EMAIL', 'abeldj_13@yahoo.com', 1),('PHONE', '832-8027', 1),('FAX', '831-9154', 1),
('EMAIL', 'macalinofe@yahoo.com', 2),('PHONE', '846-9188', 2),('EMAIL', 'legal@bdplaw.com.ph', 3),
('EMAIL', 'cherry.bernaldo@rsbernaldo.com', 3),('PHONE', '840-0535 to 38', 3),('FAX', '893-3383', 3),
('EMAIL', 'info@cruzmarcelo.com', 4),('PHONE', '810-5858', 4),('FAX', '810-3838', 4),
('EMAIL', 'gonzagalawoffice2000@yahoo.com', 5),('PHONE', '667-3479', 5),
('EMAIL', 'agbayanimendoza@yahoo.com', 6),('PHONE', '664-4778', 6),('PHONE', '783-8354', 6),
('EMAIL', 'ocmalaw@eastern.com.ph', 7),('PHONE', '819-1624', 7),('PHONE', '5051 to 53', 7),('FAX', '819-2978', 7),
('EMAIL', 'rsreyes.lawoffices@yahoo.com', 8),('PHONE', '631-3013', 8),('FAX', '631-3013', 8),
('EMAIL', 'panopio_escober@yahoo.com', 9),('FAX', '846-2014', 9);

INSERT INTO approval_status_tbl(approval_status)
VALUES
 ('Draft'),
 ('Return to Maker'),
 ('Submitted to Reviewer'),
 ('Submitted to Approver'),
 ('Approved');

INSERT INTO foreclosure_tbl (dts_number, ref_lawyer_id, ref_original_lawyer_id, ref_approval_status_id, registered_owner, foreclosure_ref_number, transaction_date, ref_foreclosure_status_id, status_filter) values
('1234567', 1, 1, 1, 'James C. Yap', '2017000234', '2017-07-25', 2, 'ONGOING'),
('3697451', 2, 1, 3, 'Paul Manahan', '2017000235', '2017-07-24',  12, 'ONGOING'),
('1321546', 3, 3, 4,  'Bryanne Marco', '2017000236', '2017-07-23',  4, 'ONGOING'),
('1234654', 4, 4, 2,  'Meri Lee', '2017000237', '2017-07-22', 4,'ONGOING'),
('7897974', NULL, NULL, 1,  'Edward Catala', '2017000238', '2017-07-21', 5,'ONGOING'),
('1354862', NULL, 5, 3, 'Anjeli Bunoan', '2017000239', '2017-07-20',  9,'ONGOING'),
('7897975', NULL, NULL, 1,  'Bryanne Catala', '2017000240', '2017-07-21', 1,'ONGOING'),
('7897977', NULL, 5, 4, 'James Bunoan', '2017000242', '2017-07-20', 7,'ONGOING'),
('7897978', 5, 5, 4, 'James Marco', '2017000243', '2017-07-21', 10,'ONGOING'), -- TEST DATA FOR LMM-FOR-RC-006. DO NOT CHANGE STATUS
('7897979', 5, 5, 4, 'James Marco jr', '2017000244', '2017-07-21', 10,'ONGOING'), -- TEST DATA FOR LMM-FOR-RC-006. DO NOT CHANGE STATUS
('99991987', 1, 1, 1, 'Tiffa Lockheart', '201719998', '2017-07-25', 14, 'ONGOING'), -- TEST DATA FOR LMM-FOR-WB-016. DO NOT CHANGE STATUS
('99991988', 2, 1, 1, 'Thomas Gilmorety', '201719890', '2017-07-17', 14, 'ONGOING'), -- TEST DATA FOR LMM-FOR-WB-016. DO NOT CHANGE STATUS
('99991989', 3, 1, 1, 'Yuan Passer', '201716998', '2017-06-25', 14, 'ONGOING'), -- TEST DATA FOR LMM-FOR-WB-016. DO NOT CHANGE STATUS
('99991911', 1, 1, 3, 'Roberto Sembrano', '201716911', '2017-06-25', 1, 'ONGOING'), -- TEST DATA FOR LMM-FOR-BOF-019.
('999919122', 2, 2, 3, 'Fhil Man', '2017169122', '2017-06-25', 1, 'ONGOING'), -- TEST DATA FOR LMM-FOR-BOF-019.
('999919111', 3, 1, 3, 'Langka Sing', '2017169111', '2017-06-25', 1, 'ONGOING'); -- TEST DATA FOR LMM-FOR-BOF-019.

INSERT INTO foreclosure_tbl (dts_number, ref_lawyer_id, ref_original_lawyer_id, ref_approval_status_id, registered_owner, foreclosure_ref_number, transaction_date, ref_foreclosure_status_id, status_filter, date_booked_to_ip, publication_name, volume, issue_numbers) values
('7897976', NULL, 3, 1,  'Bryanne Catala jr', '2017000341', '2017-07-21', 11,'ONGOING', '2017-08-24', 'Balita Pampanga', 'XIV', '2,3,4'),
('1234567', NULL, 4, 2, 'Anjeli Bunoan jr', '2017000342', '2017-07-20', 11, 'ONGOING', '2017-08-25', 'Balita Manila', 'XII', '5,6,7');

INSERT INTO PETITION_TBL (ref_foreclosure_id, ref_bank_id, bank_name, loan_type, loan_application_number, ref_pn_id, promissory_note_number, promissory_note_face_amount, ref_collateral_id, collateral_number, ref_borrower_id, entity_name, full_name, outstanding_balance, last_interest_payment_date, interest_rate, penalty_rate, service_charge_rate, other_charges)
VALUES
(1, 1, 'Bank of the Philippine Islands', 'Rediscounting', 123456, 1, 201712, 1200000.00, 1, 'TCT-101', 1, 'Mcdonalds', 'Ray Kroc', 900000.00, '1999-05-21', 0.10, 0.20, 0.30, 900001.00),
(2, 1, 'Bank of the Philippine Islands', 'Rediscounting', 123456, 2, 201723, 2800000.00, 2, 'TCT-102', 2, 'Jollibee', 'Tony Tan Caktiong', 800000.00, '1999-05-21', 0.10, 0.20, 0.30, 800001.00),
(2, 1, 'Bank of the Philippine Islands', 'Rediscounting', 123456, 2, 201723, 2800000.00, 3, 'TCT-103', 2, 'Jollibee', 'Tony Tan Caktiong', 800000.00, '1999-05-21', 0.10, 0.20, 0.30, 800001.00),
(3, 1, 'Bank of the Philippine Islands', 'Rediscounting', 234567, 3, 201734, 1700000.00, 3, 'TCT-103', 3, 'KFC', 'Colonel Sanders', 700000.00, '1999-05-21', 0.10, 0.20, 0.30, 700001.00),
(4, 1, 'Bank of the Philippine Islands', 'Rediscounting', 234567, 4, 201745, 1100000.00, 4, 'TCT-104', 4, 'Mang Inasal', 'Edgar Sia', 600000.00, '1999-05-21', 0.10, 0.20, 0.30, 600001.00),
(5, 2, 'Banco De Oro', 'Rediscounting', 345678, 5, 201756, 1300000.00, 5, 'TCT-105', 5, 'Wendys', 'Dave Thomas', 500000.00, '1999-05-21', 0.10, 0.20, 0.30, 500001.00),
(6, 2, 'Banco De Oro', 'Rediscounting', 345678, 6, 201767, 1500000.00, 6, 'TCT-106', 6, 'Coca-Cola', 'John Smith Pemberton', 400000.00, '1999-05-21', 0.10, 0.20, 0.30, 400001.00),
(7, 2, 'Banco De Oro', 'Rediscounting', 345678, 6, 201767, 1500000.00, 6, 'TCT-107', 6, 'Coca-Cola', 'John Smith Pemberton', 300000.00, '1999-05-21', 0.10, 0.20, 0.30, 300001.00),
(8, 2, 'Banco De Oro', 'Rediscounting', 345678, 6, 201767, 1500000.00, 6, 'TCT-108', 6, 'Coca-Cola', 'John Smith Pemberton', 200000.00, '1999-05-21', 0.10, 0.20, 0.30, 200001.00),
(9, 2, 'Banco De Oro', 'Rediscounting', 345678, 6, 201767, 1500000.00, 6, 'TCT-109', 6, 'Coca-Cola', 'John Smith Pemberton', 100000.00, '1999-05-21', 0.10, 0.20, 0.30, 100001.00),
(10, 2, 'Banco De Oro', 'Rediscounting', 345678, 6, 201767, 1500000.00, 6, 'TCT-110', 6, 'Coca-Cola', 'John Smith Pemberton', 100000.00, '1999-05-21', 0.10, 0.20, 0.30, 100001.00),
(11, 2, 'Banco De Oro', 'Rediscounting', 345678, 6, 201767, 1500000.00, 6, 'TCT-111', 6, 'Coca-Cola', 'John Smith Pemberton', 100000.00, '1999-05-21', 0.10, 0.20, 0.30, 100001.00),
(12, 13, 'United Coconut Planters Bank', 'Rediscounting', 999904, 39, 202007, 1990000.00, 40, 'TCT-140', 28, 'Matador', 'Mara Doria', 950000.00, '2009-05-21', 0.10, 0.20, 0.30, 900001.00), -- TEST DATA FOR LMM-FOR-WB-016.
(13, 13, 'United Coconut Planters Bank', 'Rediscounting', 999904, 39, 202007, 2000000.00, 40, 'TCT-140', 28, 'Matador', 'Mara Doria', 950000.00, '2009-05-21', 0.10, 0.20, 0.30, 900001.00), -- TEST DATA FOR LMM-FOR-WB-016.
(14, 13, 'United Coconut Planters Bank', 'Rediscounting', 999904, 39, 202007, 5000000.00, 40, 'TCT-140', 28, 'Matador', 'Mara Doria', 950000.00, '2009-05-21', 0.10, 0.20, 0.30, 900001.00), -- TEST DATA FOR LMM-FOR-WB-016.
(15, 13, 'United Coconut Planters Bank', 'Rediscounting', 999904, 39, 202007, 1990000.00, 40, 'TCT-140', 28, 'Dairy Queen', 'Krystal Del Torre', 950000.00, '2009-05-21', 0.10, 0.20, 0.30, 900001.00), -- TEST DATA FOR LMM-FOR-BOF-019.
(16, 13, 'United Coconut Planters Bank', 'Rediscounting', 999904, 39, 202007, 2000000.00, 40, 'TCT-140', 28, 'Dairy Queen', 'Krystal Del Torre', 950000.00, '2009-05-21', 0.10, 0.20, 0.30, 900001.00), -- TEST DATA FOR LMM-FOR-BOF-019.
(17, 13, 'United Coconut Planters Bank', 'Rediscounting', 999904, 39, 202007, 5000000.00, 40, 'TCT-140', 28, 'Dairy Queen', 'Krystal Del Torre', 950000.00, '2009-05-21', 0.10, 0.20, 0.30, 900001.00), -- TEST DATA FOR LMM-FOR-BOF-019.
(18, 1, 'Bank of the Philippine Islands', 'Rediscounting', 234567, 4, 201745, 1100000.00, 40, 'TCT-140', 4, 'Mang Inasal', 'Edgar Sia', 600000.00, '1999-05-21', 0.10, 0.20, 0.30, 600001.00); -- TEST DATA FOR TRC.

INSERT INTO AUCTION_TBL (ref_foreclosure_id, foreclosure_ref_number, rtc, ejf_number, hbu_property, pwd, fmv, area, tct_number, registered_owner)
VALUES
(1,2017000234,'RTC234','EJF234','Agricultural',1,2000000,10000,'TCT-101','James C. Yap'),
(2,2017000235,'RTC235','EJF235','Residential',1,2800000,2000,'TCT-102','Paul Manahan'),
(3,2017000236,'RTC236','EJF236','Commercial',1,3700000,3000,'TCT-103','Bryanne Marco'),
(4,2017000237,'RTC237','EJF237','Industrial',1,4500000,6000,'TCT-104','Meri Lee'),
(5,2017000238,'RTC238','EJF238','Seaport',1,4100000,5000,'TCT-105','Edward Catala'),
(6,2017000239,'RTC239','EJF239','Airport',1,6200000,12000,'TCT-106','Anjeli Bunoan'),
(7,2017000240,'RTC240','EJF240','Civic',1,1900000,4000,'TCT-107','Bryanne Catala'),
(17,2017000341,'RTC241','EJF241','Military',1,1400000,7000,'TCT-108','Bryanne Catala jr'),
(18,2017000342,'RTC242','EJF242','Landfill',1,1300000,8000,'TCT-109','Anjeli Bunoan jr'),
(8,2017000242,'RTC242','EJF242','Landfill',1,1300000,8000,'TCT-109','James Bunoan'),
(9,2017000243,'RTC242','EJF242','Landfill',1,1300000,8000,'TCT-110','James Bunoan'),
(10,2017000244,'RTC242','EJF242','Landfill',1,1300000,8000,'TCT-111','James Bunoan'),
(11,201719998,'RTC9981','EJF9981','Military',1,2990000,10000,'TCT-141','Tiffa Lockheart'), -- TEST DATA FOR LMM-FOR-WB-016.
(12,201719890,'RTC9934','EJF9934','Agricultural',1,2990000,10000,'TCT-141','Thomas Gilmorety'), -- TEST DATA FOR LMM-FOR-WB-016.
(13,201716998,'RTC9980','EJF9980','Landfill',1,2990000,10000,'TCT-141','Yuan Passer'), -- TEST DATA FOR LMM-FOR-WB-016.
(14,201716911,'RTC998221','EJF998221','Military',1,2990000,10000,'TCT-141','Roberto Sembrano'), -- TEST DATA FOR LMM-FOR-BOF-019.
(15,2017169122,'RTC993422','EJF993422','Agricultural',1,2990000,10000,'TCT-141','Fhil Man'), -- TEST DATA FOR LMM-FOR-BOF-019.
(16,2017169111,'RTC9980222','EJF9980222','Landfill',1,2990000,10000,'TCT-141','Langka Sing'); -- TEST DATA FOR LMM-FOR-BOF-019.

INSERT INTO BID_TBL (ref_auction_id, applicable_adjustment_rate, appraised_value, appraisal_cost, filing_fee, bank_principal, bank_interest, bank_penalties, bsp_principal, bsp_booked_interest, bsp_unbooked_interest_income, bsp_unbooked_liquidated_damages)
VALUES
('1',1,2200000,788,19790,250000,2500,2000,330000,1500,1000,3000),
('2',1,3100000,788,19790,260000,2500,2000,320000,1500,1000,3000),
('3',1,3800000,788,19790,270000,2500,2000,310000,1500,1000,3000),
('4',1,4900000,788,19790,280000,2500,2000,300000,1500,1000,3000),
('5',1,4500000,788,19790,290000,2500,2000,290000,1500,1000,3000),
('6',1,6700000,788,19790,300000,2500,2000,280000,1500,1000,3000),
('7',1,2400000,788,19790,310000,2500,2000,270000,1500,1000,3000),
('8',1,1600000,788,19790,320000,2500,2000,260000,1500,1000,3000),
('9',1,1500000,788,19790,330000,2500,2000,250000,1500,1000,3000),
('10',1,1500000,788,19790,330000,2500,2000,250000,1500,1000,3000),
('11',1,1500000,788,19790,330000,2500,2000,250000,1500,1000,3000),
('12',1,2330000,788,19790,250000,2500,20090,330000,1500,10900,3067800), -- TEST DATA FOR LMM-FOR-WB-016.
('13',1,3109900,788,19790,260000,25900,20090,3200900,15900,1000,39000), -- TEST DATA FOR LMM-FOR-WB-016.
('14',1,3809980,788,19790,2700800,2500,20900,310000,1500,1000,30900); -- TEST DATA FOR LMM-FOR-WB-016.

INSERT INTO bidder_details_tbl (bidder_name, amount, ref_auction_id, default_flag, delete_flag)
VALUES
 ('BSP', 5000000, 1, 1, 0),
 ('PTC', 100, 1, 0, 0),
 ('BPI', 7000000, 1, 0, 0),
 ('BDO', 4000000, 1, 0, 0),
 ('BSP', 45000000, 2, 1, 0),
 ('BSP', 5000000, 12, 1, 0),('PSBANK', 10000, 12, 0, 0), -- TEST DATA FOR LMM-FOR-WB-016.
 ('BSP', 2000000, 13, 1, 0),('BPI', 3110000, 13, 0, 0), -- TEST DATA FOR LMM-FOR-WB-016.
 ('BSP', 9000000, 14, 1, 0),('BDO', 1000000, 14, 0, 0); -- TEST DATA FOR LMM-FOR-WB-016.

INSERT INTO winning_bidder_tbl (ref_bidder_id, reason)
VALUES
 (1, 'bid not paid');

UPDATE AUCTION_TBL
SET ref_bidder_id = 3
WHERE auction_id = 1;

/*Testdata for FSM-PO-SAP-016*/
INSERT INTO PAYMENT_ORDER_TBL(
payment_order_number, payment_order_date, bank_name, end_user_borrower, payment_order_status, or_number, or_date, user_name, receiving_office, bank_level_payment_type, total_payment_amount, payment_type, loan_type, payment_level)
VALUES('2017123456', '2017-07-06', 'United Coconut Planters Bank', 'Dell Mundo', 'DRAFT', '01234', '2017-08-05', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 80000000.00, 'CASH/CHECK', 'Rediscounting', 'PN');

INSERT INTO PAYABLE_TBL (ref_payment_order_id,promissory_note_number,loan_application_number,
collateral_number,borrower_id,borrower_name,amount_granted,outstanding_principal_balance,
total_outstanding_balance,other_expenses,payment_amount,excess_amount,excess_payment_type) VALUES
((SELECT MAX(payment_order_id) FROM PAYMENT_ORDER_TBL),202003,999903,'TCT-129',35,'Dell Mundo',100000.00,55000.00,100000.00,0,40000000.00,39900000.00,'Most Onerous'),
((SELECT MAX(payment_order_id) FROM PAYMENT_ORDER_TBL),202004,999903,'TCT-130',35,'Dell Mundo',200000.00,110000.00,200000.00,0,40000000.00,39800000.00,'Most Onerous');

INSERT INTO TRANSACTION_TBL(payment_number, payment_order_number, transaction_date, total_payment, year_created, payment_date, transaction_type) VALUES
('2017123456', '2017123456','2017-07-03', 80000000.00, 2017, '2017-03-22', 'PO');

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017123456,  202003, 'PN', 40000000.00, 0.00, 10000.00, 20000.00, 15000.00, 0.00, 0.00, 0.00, '2017-08-10', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017123456,  202004, 'PN', 40000000.00, 0.00, 40000.00, 20000.00, 30000.00, 0.00, 0.00, 0.00, '2017-08-10', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017123456,  202005, 'Excess Most Onerous', 0.00, 0.00, 30000.00, 60000.00, 45000.00, 0.00, 0.00, 0.00, '2017-08-10', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017123456,  202006, 'Excess Most Onerous', 0.00, 0.00, 125000.00, 100000.00, 125000.00, 0.00, 0.00, 0.00, '2017-08-10', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017123456,  202007, 'Excess Most Onerous', 0.00, 0.00, 250000.00, 75000.00, 100000.00, 0.00, 0.00, 0.00, '2017-08-10', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, miscellaneous_income)
VALUES(2017123456,  '', 'Excess on Excess', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 77950000.00);

/*Testdata for FSM-PO-SAP-010*/
INSERT INTO PAYMENT_ORDER_TBL(
payment_order_number, payment_order_date, bank_name, end_user_borrower, payment_order_status, or_number, or_date, user_name, receiving_office, bank_level_payment_type, total_payment_amount, payment_type, loan_type, payment_level)
VALUES('2017123457', '2017-08-11', 'Metro Bank', 'Pepsi', 'DRAFT', '01235', '2017-08-11', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 80000000.00, 'CASH/CHECK', 'Rediscounting', 'PN');

INSERT INTO PAYABLE_TBL (ref_payment_order_id,promissory_note_number,loan_application_number,
collateral_number,borrower_id,borrower_name,amount_granted,outstanding_principal_balance,
total_outstanding_balance,other_expenses,payment_amount,excess_amount,excess_payment_type) VALUES
((SELECT MAX(payment_order_id) FROM PAYMENT_ORDER_TBL),201713,567891,'TCT-110',10,'Pepsi',1100000.00,1100000.00,1520000.00,21000.00,40000000.00,39900000.00,'Most Onerous');

INSERT INTO TRANSACTION_TBL(payment_number, payment_order_number, transaction_date, total_payment, year_created, payment_date, transaction_type) VALUES
('2017123457', '2017123457','2017-08-11', 40000000.00, 2017, '2017-08-11', 'PO');

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017123457,  201713, 'PN', 40000000.00, 20000.00, 25000.00, 30000.00, 25000.00, 0.00, 0.00, 0.00, '2017-08-11', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017123457,  201724, 'Excess Most Onerous', 39800000.00, 25000.00, 30000.00, 30000.00, 15000.00, 0.00, 0.00, 0.00, '2017-08-11', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017123457,  201729, 'Excess Most Onerous', 39900000.00, 15000.00, 45000.00, 10000.00, 30000.00, 0.00, 0.00, 0.00, '2017-08-11', 0.00);

/*Testdata for FSM-PO-SAP-012*/
INSERT INTO PAYMENT_ORDER_TBL(
payment_order_number, payment_order_date, bank_name, end_user_borrower, payment_order_status, or_number, or_date, user_name, receiving_office, bank_level_payment_type, total_payment_amount, payment_type, loan_type, payment_level)
VALUES('2017080217', '2017-08-11', 'Banco De Oro', 'Coca-cola, John Stith Pemberton', 'DRAFT', '03210', '2017-08-11', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 2338300.00, 'CASH/CHECK', 'Rediscounting', 'PN');

INSERT INTO PAYABLE_TBL (ref_payment_order_id,promissory_note_number,loan_application_number,
collateral_number,borrower_id,borrower_name,amount_granted,outstanding_principal_balance,
total_outstanding_balance,other_expenses,payment_amount,excess_amount,excess_payment_type) VALUES
((SELECT MAX(payment_order_id) FROM PAYMENT_ORDER_TBL),201767,345678,'TCT-106',6,'Coca-cola, John Stith Pemberton',1500000.00,1500000.00,2044400.00,67000.00,50000000.00,47888600.00,'Most Onerous');

INSERT INTO TRANSACTION_TBL(payment_number, payment_order_number, transaction_date, total_payment, year_created, payment_date, transaction_type) VALUES
 ('2017876543', '2017080217','2017-08-11', 1000000.00, 2017, '2017-08-11', 'PO');

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017080217,  201767, 'PN', 2111400.00, 67000.00, 300000.00, 0.00, 244400.00, 0.00, 0.00, 0.00, '2017-08-11', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017080217,  201756, 'Excess Most Onerous', 2338300.00, 45000.00, 390000.00, 0.00, 603300.00, 0.00, 0.00, 0.00, '2017-08-11', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017080217,  201778, 'Excess Most Onerous', 1868000.00, 20000.00, 140000.00, 0.00, 508000.00, 0.00, 0.00, 0.00, '2017-08-11', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017080217,  201789, 'Excess Most Onerous', 3384000.00, 0.00, 200000.00, 0.00, 384000.00, 0.00, 0.00, 0.00, '2017-08-11', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017080217,  202012, 'Excess Most Onerous', 3076000.00, 0.00, 540000.00, 0.00, 1336000.00, 0.00, 0.00, 0.00, '2017-08-11', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017080217,  202014, 'Excess Most Onerous', 4181700.00, 0.00, 890000.00, 0.00, 1291700.00, 0.00, 0.00, 0.00, '2017-08-11', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017080217,  202013, 'Excess Most Onerous', 5569700.00, 0.00, 1030000.00, 0.00, 1739700.00, 0.00, 0.00, 0.00, '2017-08-11', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
VALUES(2017080217,  '', 'Excess on Excess', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, '2017-08-11', 27470900.00);

/*Testdata for FSM-PO-SAP-020*/
INSERT INTO PAYMENT_ORDER_TBL(
payment_order_number, payment_order_date, bank_name, end_user_borrower, payment_order_status, or_number, or_date, user_name, receiving_office, bank_level_payment_type, total_payment_amount, payment_type, loan_type, payment_level)
VALUES('2017696969', '2017-08-11', 'Bangkong Luma', 'Juan Dela Cruz', 'DRAFT', '', '2017-08-11', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 5000000.00, 'CASH/CHECK', 'Rediscounting', 'LOAN');

INSERT INTO PAYABLE_TBL(ref_payment_order_id, promissory_note_number, loan_application_number, collateral_number,borrower_id,borrower_name,amount_granted,outstanding_principal_balance,
total_outstanding_balance,other_expenses,payment_amount,excess_amount,excess_payment_type) VALUES
((SELECT MAX(payment_order_id) FROM PAYMENT_ORDER_TBL),202103,999990,'TCT-119',19,'Juan Dela Cruz',5000000.00,500000.00,50000.00,0,5000000.00,4210000.00,'Most Onerous');

INSERT INTO TRANSACTION_TBL(payment_number, payment_order_number, transaction_date, total_payment, year_created, payment_date, transaction_type) VALUES
('2017123436', '2017696969','2017-08-11', 5000000.00, 2017, '2017-08-10', 'PO');

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
 interest_income_payment, principal_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
          VALUES(2017696969, '', 'LOAN', 5000000.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 0.00, 0.00, 0.00, '2017-08-10', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
 interest_income_payment, principal_payment,  unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
          VALUES(2017696969, '201797', 'BANKMO', 4210000.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 0.00, 0.00, 0.00, '2017-08-10', 0.00);

INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
 interest_income_payment,  principal_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, miscellaneous_income)
          VALUES (2017696969, '', 'MID', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 4210000.00);

INSERT INTO transaction_remarks_tbl(ref_transaction_id, remarks_content) VALUES
(1, 'remarks'),
(2, 'remarks again'),
(3, 'NEWEST REMARKS AGAIN'),
(4, 'remarks'),
(5, 'remarks again'),
(6, 'NEWEST REMARKS AGAIN'),
(7, 'remarks'),
(8, 'remarks again'),
(9, 'NEWEST REMARKS AGAIN'),
(10, 'remarks again'),
(11, 'NEWEST REMARKS AGAIN'),
(12, 'remarks'),
(13, 'remarks'),
(14, 'remarks'),
(15, 'remarks'),
(16, 'remarks'),
(17, 'remarks'),
(18, 'remarks'),
(19, 'remarks');

INSERT INTO expenses_tbl(ref_id, expense_type, miscellaneous_assets, accounts_payable)
VALUES (1, 'LOAN', 25000.00, 35000.00),
(1, 'PN', 55000.00, 85000.00),
(2, 'PN', 15000.00, 38000.00),
(3, 'PN', 25000.00, 65000.00),
(4, 'PN', 35000.00, 55000.00),
(5, 'PN', 45000.00, 25000.00),
(1, 'BANK', 55000.00, 45000.00),
(6, 'PN', 65000.00, 38000.00),
(7, 'PN', 75000.00, 32000.00),
(8, 'PN', 85000.00, 30000.00),
(9, 'PN', 35000.00, 10000.00),
(10, 'PN', 85000.00, 30000.00),
(11, 'PN', 87000.00, 30000.00),
(12, 'PN', 85000.00, 30000.00),
(13, 'PN', 82000.00, 30000.00),
(14, 'PN', 83000.00, 30000.00),
(15, 'PN', 85000.00, 30000.00),
(16, 'PN', 84000.00, 30000.00),
(17, 'PN', 81000.00, 30000.00),
(18, 'PN', 20000.00, 30000.00),
(19, 'PN', 83000.00, 10000.00),
(20, 'PN', 15000.00, 40000.00),
(21, 'PN', 25000.00, 40000.00),
(22, 'PN', 83000.00, 40000.00),
(23, 'PN', 84000.00, 40000.00),
(24, 'PN', 85000.00, 40000.00),
(25, 'PN', 86000.00, 20000.00),
(26, 'PN', 65000.00, 30000.00),
(27, 'PN', 15000.00, 55000.00),
(28, 'PN', 25000.00, 45000.00),
(29, 'PN', 35000.00, 47000.00),
(30, 'PN', 45000.00, 48000.00),
(31, 'PN', 55000.00, 49000.00),
(32, 'PN', 85000.00, 30000.00),
(33, 'PN', 85000.00, 20000.00),
(34, 'PN', 85000.00, 10000.00),
(35, 'PN', 85000.00, 90000.00),
(36, 'PN', 85000.00, 80000.00),
(37, 'PN', 85000.00, 70000.00),
(38, 'PN', 85000.00, 60000.00),
(39, 'PN', 85000.00, 40000.00),
(40, 'PN', 22700.00, 12000.00),
(41, 'PN', 23700.00, 22000.00),
(42, 'PN', 25100.00, 32000.00),
(43, 'PN', 25200.00, 42000.00),
(44, 'PN', 25400.00, 52000.00),
(45, 'PN', 25600.00, 62000.00),
(46, 'PN', 25900.00, 72000.00),
(47, 'PN', 85700.00, 82000.00),
(48, 'PN', 75700.00, 92000.00),
(49, 'PN', 55700.00, 61000.00),
(50, 'PN', 45700.00, 63000.00),
(51, 'PN', 15700.00, 64000.00),
(52, 'PN', 22700.00, 65000.00);

UPDATE pn_tbl
SET ref_expense_id = 2
WHERE pn_id = 1;

UPDATE pn_tbl
SET ref_expense_id = 3
WHERE pn_id = 2;

UPDATE pn_tbl
SET ref_expense_id = 4
WHERE pn_id = 3;

UPDATE pn_tbl
SET ref_expense_id = 5
WHERE pn_id = 4;

UPDATE pn_tbl
SET ref_expense_id = 6
WHERE pn_id = 5;

UPDATE pn_tbl
SET ref_expense_id = 8
WHERE pn_id = 6;

UPDATE pn_tbl
SET ref_expense_id = 9
WHERE pn_id = 7;

UPDATE pn_tbl
SET ref_expense_id = 10
WHERE pn_id = 8;

UPDATE pn_tbl
SET ref_expense_id = 11
WHERE pn_id = 9;

UPDATE pn_tbl
SET ref_expense_id = 12
WHERE pn_id = 10;

UPDATE pn_tbl
SET ref_expense_id = 13
WHERE pn_id = 11;

UPDATE pn_tbl
SET ref_expense_id = 14
WHERE pn_id = 12;

UPDATE pn_tbl
SET ref_expense_id = 15
WHERE pn_id = 13;

UPDATE pn_tbl
SET ref_expense_id = 16
WHERE pn_id = 14;

UPDATE pn_tbl
SET ref_expense_id = 17
WHERE pn_id = 15;

UPDATE pn_tbl
SET ref_expense_id = 18
WHERE pn_id = 16;

UPDATE pn_tbl
SET ref_expense_id = 19
WHERE pn_id = 17;

UPDATE pn_tbl
SET ref_expense_id = 20
WHERE pn_id = 18;

UPDATE pn_tbl
SET ref_expense_id = 21
WHERE pn_id = 19;

UPDATE pn_tbl
SET ref_expense_id = 22
WHERE pn_id = 20;

UPDATE pn_tbl
SET ref_expense_id = 23
WHERE pn_id = 21;

UPDATE pn_tbl
SET ref_expense_id = 24
WHERE pn_id = 22;

UPDATE pn_tbl
SET ref_expense_id = 25
WHERE pn_id = 23;

UPDATE pn_tbl
SET ref_expense_id = 26
WHERE pn_id = 24;

UPDATE pn_tbl
SET ref_expense_id = 27
WHERE pn_id = 25;

UPDATE pn_tbl
SET ref_expense_id = 28
WHERE pn_id = 26;

UPDATE pn_tbl
SET ref_expense_id = 29
WHERE pn_id = 27;

UPDATE pn_tbl
SET ref_expense_id = 30
WHERE pn_id = 28;

UPDATE pn_tbl
SET ref_expense_id = 31
WHERE pn_id = 29;

UPDATE pn_tbl
SET ref_expense_id = 32
WHERE pn_id = 30;

UPDATE pn_tbl
SET ref_expense_id = 33
WHERE pn_id = 31;

UPDATE pn_tbl
SET ref_expense_id = 34
WHERE pn_id = 32;

UPDATE pn_tbl
SET ref_expense_id = 35
WHERE pn_id = 33;

UPDATE pn_tbl
SET ref_expense_id = 36
WHERE pn_id = 34;

UPDATE pn_tbl
SET ref_expense_id = 37
WHERE pn_id = 35;

UPDATE pn_tbl
SET ref_expense_id = 38
WHERE pn_id = 36;

UPDATE pn_tbl
SET ref_expense_id = 39
WHERE pn_id = 37;

UPDATE pn_tbl
SET ref_expense_id = 40
WHERE pn_id = 38;

UPDATE pn_tbl
SET ref_expense_id = 41
WHERE pn_id = 39;

UPDATE pn_tbl
SET ref_expense_id = 42
WHERE pn_id = 40;

UPDATE pn_tbl
SET ref_expense_id = 43
WHERE pn_id = 41;

UPDATE pn_tbl
SET ref_expense_id = 44
WHERE pn_id = 42;

UPDATE pn_tbl
SET ref_expense_id = 45
WHERE pn_id = 43;

UPDATE pn_tbl
SET ref_expense_id = 46
WHERE pn_id = 44;

UPDATE pn_tbl
SET ref_expense_id = 47
WHERE pn_id = 45;

UPDATE pn_tbl
SET ref_expense_id = 48
WHERE pn_id = 46;

UPDATE pn_tbl
SET ref_expense_id = 49
WHERE pn_id = 47;

UPDATE pn_tbl
SET ref_expense_id = 50
WHERE pn_id = 48;

UPDATE pn_tbl
SET ref_expense_id = 51
WHERE pn_id = 49;

UPDATE pn_tbl
SET ref_expense_id = 52
WHERE pn_id = 50;

UPDATE pn_tbl
SET ref_expense_id = 53
WHERE pn_id = 51;

UPDATE pn_tbl
SET ref_expense_id = 54
WHERE pn_id = 52;

INSERT INTO collateral_documents_tbl(foreclosure_ref_number, document_name, document_contents, updated_by, updated_on, save_flag, print_flag)
VALUES
('2017000239','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
('2017000234','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
('2017000235','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 0, 1),
('2017000236','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 0, 1),

('2017000237','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
('2017000238','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
('2017169111','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 0, 1),
('2017000240','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),

('2017000242','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
('2017000243','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 0),
('2017000244','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 0),
('201719998','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),

('201719890','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
('201716998','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 0),
('201716911','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 0, 1),
('2017169122','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 0, 0);


--added lawyer in some PNs
UPDATE pn_tbl
SET ref_lawyer_id = 8
WHERE pn_number = 201722;

UPDATE pn_tbl
SET ref_lawyer_id = 1
WHERE pn_number = 202016;

UPDATE pn_tbl
SET ref_lawyer_id = 4
WHERE pn_number = 202006;

UPDATE pn_tbl
SET ref_lawyer_id = 2
WHERE pn_number = 201735;

UPDATE pn_tbl
SET ref_lawyer_id = 5
WHERE pn_number = 201751;

UPDATE pn_tbl
SET ref_lawyer_id = 7
WHERE pn_number = 201772;

UPDATE pn_tbl
SET ref_lawyer_id = 3
WHERE pn_number = 202002;

UPDATE pn_tbl
SET ref_lawyer_id = 6
WHERE pn_number = 201791;

UPDATE pn_tbl
SET ref_lawyer_id = 6
WHERE pn_number = 201773;

INSERT INTO demand_letter(date_on_demand_letter, demand_letter_date, ref_pn_id) VALUES
('2017-01-22', '2016-12-30', 25),
('2017-02-22', '2017-01-01', 25),
('2017-08-30', '2017-03-14', 25),
('2017-10-22', '2017-01-14', 29),
('2017-02-22', '2017-01-14', 31),
('2017-02-22', '2017-02-14', 34),
('2017-03-22', '2017-04-14', 48),
('2017-04-22', '2017-05-14', 30),
('2017-05-22', '2017-06-14', 53),
('2017-06-22', '2017-07-14', 54),
('2017-07-22', '2017-08-14', 55);

INSERT INTO PRE_FORECLOSURE(ref_pn_id, pre_foreclosure_status, transaction_date, active_flag, excluded_false, excluded_date) values
(7, 'New', '2017-07-25', 1, 0, '2017-07-25'),
(8, 'New', '2017-07-31', 1, 0, '2017-07-31'),
(9, 'Referred to Lawyer', '2017-07-28', 1, 0, '2017-07-28'),
(10, 'New', '2017-08-10', 1, 0, '2017-08-10'),
(11, 'New', '2017-08-01', 1, 0, '2017-08-01'),
(12, 'New', '2017-08-05', 1, 0, '2017-08-05'),
(13, 'Referred to Lawyer', '2017-07-05', 0, 1, '2017-07-05'),
(14, 'New', '2017-08-22', 0, 1, '2017-08-22'),
(15, 'New', '2017-08-29',  0, 1, '2017-08-29'),
(16, 'New', '2017-07-15', 0, 1, '2017-07-15');

INSERT INTO remarks_tbl(remarks_group, remarks_group_ref_id, remarks_content, created_date) values
('preforeclosure', 7, 'Current', '2017-08-29'),
('preforeclosure', 9, 'Current', '2017-08-29'),
('preforeclosure', 10, 'Current', '2017-08-29');

INSERT INTO batch_tbl(job_name,cron_trigger_name, cron_expression) VALUES
('movePnsToPreForeclosureJob', 'movePnsToPreForeclosureCronTrigger', 	'0 0 9,16 ? * *'),
('moveCurrentPnsToPreForeclosureJob', 'moveCurrentPnsToPreForeclosureCronTrigger', '0 0 9,16 ? * *');

INSERT INTO pn_collateral(ref_pn_id, ref_collateral_id) VALUES
(1,1),
(2,2),
(3,3),
(4,4),
(5,5),
(6,6),
(7,7),
(8,8),
(9,9),
(10,10),
(11,11),
(12,12),
(13,13),
(14,14),
(15,15),
(16,16),
(17,17),
(18,18),
(25,19),
(26,20),
(27,21),
(28,22),
(29,23),
(30,24),
(31,25),
(32,26),
(33,27),
(34,28),
(35,29),
(36,30),
(37,31),
(38,32),
(39,33),
(40,34),
(41,35),
(42,36),
(43,37),
(44,38),
(45,39),
(46,40),
(47,41),
(48,42),
(49,43),
(50,44),
(51,45),
(52,46),
(53,47),
(54,47),
(55,47);

INSERT INTO expense_tbl (expense_type, or_number, or_date, ref_pn_id) VALUES
('Filing Fee', '2098932 C', '2017-07-15', 39),
('Courts Commission', '5788532', '2018-02-12', 39),
('COS Annotation', '1011382990', '2019-03-21', 39),
('Sheriffs Fee', '2324567 C', '2016-12-20', 39),
('Filing Fee', '2098932 A', '2017-07-15', 4),
('Courts Commission', '3288531', '2018-02-12', 4),
('COS Annotation', '3011382910', '2019-03-21', 4),
('Sheriffs Fee', '2324567 A', '2016-12-20', 4);

INSERT INTO appraisal_report (report_type, report_date, appraiser_name, ref_collateral_id) VALUES
('DLC Appraisal Report', '2016-08-10', 'Nico Robin', 40),
('MO Appraisal Report', '2016-08-11', 'Roronoa Zoro', 4);

UPDATE pn_tbl
SET ref_lawyer_id = 4,
rem_amount = 100000,
tax_receipt = '221321'
WHERE pn_id = 39;

UPDATE pn_tbl
SET ref_lawyer_id = 3,
rem_amount = 300000,
tax_receipt = '321321'
WHERE pn_id = 4;

UPDATE auction_tbl
SET date_filed = '2016-12-25',
sheriff_clerk_name = 'Boa Hancock'
WHERE auction_id = 8;

UPDATE auction_tbl
SET date_filed = '2016-12-26',
sheriff_clerk_name = 'Charlotte Pudding'
WHERE auction_id = 9;

/*FOR SINGLE PN, MULTIPLE COLLATERAL LMM-DAC-MC-003*/
UPDATE collateral_tbl
SET collateral_value = 2500000,
collateral_classification = 'MC',
rem_amount = 500000,
registered_owner = 'Archie Andrews',
area = 28,
unit_measurement = 'hectares'
WHERE collateral_id = 19;

INSERT INTO collateral_tbl(collateral_number, collateral_classification, ref_rd_id, address, collateral_value, rem_amount, registered_owner, area, unit_measurement , appraisal_date) VALUES
('TCT-148', 'MC', 9, 'Ayala Triangle Walkways, Makati, Metro Manila', 1000000, 500000, 'Betty Cooper', 1000, 'sqm', NULL),
('TCT-149', 'MC', 10, '1101 Commonwealth Ave, Diliman, Quezon City, Philippines', 1500000, 300000, 'Jughead Jones', 25, 'hectares', NULL),
('TCT-150', 'MC', 3, 'Microtel U.P Ayala Land Technohub, Commonwealth Ave, Diliman, Quezon City', 1800000, 200000, 'Veronica Lodge', 3000, 'sqm', NULL);

INSERT INTO pn_collateral(ref_pn_id, ref_collateral_id) VALUES
(25,48),
(25,49),
(25,50);

INSERT INTO appraisal_report (report_type, report_date, appraiser_name, ref_collateral_id) VALUES
('EMC Appraisal Report', '2016-02-10', 'Harry James Potter', 48),
('EMC Appraisal Report', '2016-02-11', 'Hermione Granger', 49),
('EMC Appraisal Report', '2016-02-12', 'Ron Bilius Weasley', 50),
('EMC Appraisal Report', '2016-02-13', 'Luna Lovegood', 19);

INSERT INTO tax_declaration_tbl (tax_declaration_number, tax_declaration_type, classification_type, amount, ref_collateral_id, ref_appraisal_id) VALUES
('TAX-0501', 'Land', 'Residential', 10000, 48, 3),
('TAX-0502', 'Improvement', 'Residential', 5000, 48, 3),
('TAX-0722', 'Land', 'Residential', 15000, 49, 4),
('TAX-0723', 'Land', 'Residential', 12000, 50, 5),
('TAX-0719', 'Land', 'Residential', 28000, 19, 6);

INSERT INTO insurance_details_tbl (policy_number, insurance_company_name, insurance_or_number, expiry_date, insured_amount, ref_collateral_id, ref_appraisal_id) VALUES
('POL-1106', 'Sun Life Financial', '123', '2030-01-01', 20000000, 48, 3),
('POL-1107', 'Philam Life Insurance', '234', '2035-12-31', 10000000, 49, 4),
('POL-1108', 'Stronghold Insurance Company', '567', '2050-02-28', 500000000, 50, 5),
('POL-1109', 'Life and Health Insurance', '890', '2028-01-30', 50000000, 19, 6);

UPDATE pn_tbl
SET penalty_rate = 0.3,
rem_amount = 1500000,
tax_receipt = '123456'
WHERE pn_id = 25;

/*NEW*/
UPDATE collateral_tbl
SET collateral_value = 1100000,
collateral_classification = 'MC',
rem_amount = 100000,
registered_owner = 'Kou Mabuchi',
area = 11,
unit_measurement = 'hectares'
WHERE collateral_id = 20;

UPDATE collateral_tbl
SET collateral_value = 1200000,
collateral_classification = 'MC',
rem_amount = 200000,
registered_owner = 'Alphonse Elric',
area = 12,
unit_measurement = 'hectares'
WHERE collateral_id = 21;

UPDATE pn_tbl
SET penalty_rate = 0.1,
rem_amount = 100000,
tax_receipt = '111111'
WHERE pn_id = 26;

UPDATE pn_tbl
SET penalty_rate = 0.2,
rem_amount = 200000,
tax_receipt = '222222'
WHERE pn_id = 27;

INSERT INTO appraisal_report (report_type, report_date, appraiser_name, ref_collateral_id) VALUES
('EMC Appraisal Report', '2011-01-01', 'Yoshioka Futaba', 20),
('EMC Appraisal Report', '2012-02-02', 'Edward Elric', 21);

INSERT INTO tax_declaration_tbl (tax_declaration_number, tax_declaration_type, classification_type, amount, ref_collateral_id, ref_appraisal_id) VALUES
('TAX-0511', 'Land', 'Residential', 11000, 20, 7),
('TAX-0522', 'Land', 'Residential', 22000, 21, 8);

INSERT INTO insurance_details_tbl (policy_number, insurance_company_name, insurance_or_number, expiry_date, insured_amount, ref_collateral_id, ref_appraisal_id) VALUES
('POL-1111', 'Sun Life Financial', '111', '2021-11-11', 11000000, 20, 7),
('POL-1122', 'Philam Life Insurance', '222', '2022-12-12', 22000000, 21, 8);

INSERT INTO dacion_transaction_tbl (transaction_number, dts_number, transaction_date) VALUES
('2017000001', '1111111', '2017-09-08');

INSERT INTO dacion_tbl (dacion_id, dacion_type, udoa_number, dacion_value, ref_pn_id, ref_loan_id, ref_balance_id, ref_borrower_id, ref_bank_id, ref_collateral_id, ref_dacion_transaction_id) VALUES
(1, 'Encumbered MC', 'UDOA-LOAN-B-1111', 500000, 1, 14, 50, 20, 8, 20, 1),
(2, '', 'UDOA-LOAN-B-2222', 500000, 25, 15, 51, 21, 7, 19, null),
(3, '', 'UDOA-LOAN-B-3333', 2500000, 27, 16, 48, 21, 9, 21, null),
(4, '', 'UDOA-LOAN-B-4444', 2600000, 4, 17, 49, 21, 6, 23, null),
(5, 'ENROPA', 'UDOA-LOAN-B-1453', 600000, 31, 17, 59, 25, 8, 25, 1),
(6, '', 'UDOA-LOAN-B-1116', 500000, 25, 15, 51, 21, 7, 48, null);

UPDATE end_user_borrower_tbl
SET address = '7114 Kundiman Street, Sampaloc 1008 Manila',
tin = '999-999-999-9999',
ref_pn_id = 25
WHERE borrower_id = 19;

UPDATE end_user_borrower_tbl
SET address = '95 JayLee Street, Sofia Subdivision Del Pilar, San Fernando City',
tin = '888-888-888-8888',
ref_pn_id = 26
WHERE borrower_id = 20;

UPDATE end_user_borrower_tbl
SET address = '75 P. Domingo Street Carmona, Makati City',
tin = '777-777-777-7777',
ref_pn_id = 27
WHERE borrower_id = 21;

INSERT INTO rem_tbl (rem_name, rem_amount) VALUES
('REM 1', 100000),
('REM 2', 200000);

INSERT INTO rem_dacion_tbl (ref_rem_id, ref_dacion_id) VALUES
(1, 1),
(2, 2);

/*ENCUMBERED ROPA*/
UPDATE collateral_tbl
SET collateral_value = 2500000,
collateral_classification = 'ROPA',
rem_amount = 500000,
registered_owner = 'Bastian Schweinsteiger',
area = 28,
unit_measurement = 'hectares'
WHERE collateral_id = 25;

UPDATE end_user_borrower_tbl
SET tin = '999-999-969-6969', address = 'Manuel L. Quezon Corner Carigma Street, Antipolo City, Rizal 1870'
WHERE borrower_id = '25';

INSERT INTO appraisal_report (report_type, report_date, appraiser_name, ref_collateral_id) VALUES
('ERO Appraisal Report', '2016-02-10', 'Kevin Grosskreutz', 25);

INSERT INTO tax_declaration_tbl (tax_declaration_number, tax_declaration_type, classification_type, amount, ref_collateral_id, ref_appraisal_id) VALUES
('TAX-0514', 'Land', 'Residential', 10000, 25, 9),
('TAX-0515', 'Improvement', 'Residential', 5000, 25, 9);

INSERT INTO insurance_details_tbl (policy_number, insurance_company_name, insurance_or_number, expiry_date, insured_amount, ref_collateral_id, ref_appraisal_id) VALUES
('POL-1106', 'Sun Life Financial', '123', '2030-01-01', 20000000, 25, 9);

UPDATE pn_tbl
SET penalty_rate = 0.3,
rem_amount = 1500000,
tax_receipt = '123456'
WHERE pn_id = 31;

INSERT INTO rem_tbl (rem_name, rem_amount) VALUES
('REM 9', 400000);

INSERT INTO rem_dacion_tbl (ref_rem_id, ref_dacion_id) VALUES
(3, 5);

INSERT INTO remarks_tbl(remarks_group_ref_id, remarks_group, remarks_content) VALUES
('5','dacion', 'this is remark for encumbered ropa');

UPDATE collateral_tbl
SET collateral_value = 2500000,
collateral_classification = 'ROPA',
rem_amount = 500000,
registered_owner = 'Adriel Ortiz',
area = 28,
unit_measurement = 'hectares'
WHERE collateral_id = 21;

INSERT INTO tax_declaration_tbl (tax_declaration_number, tax_declaration_type, classification_type, amount, ref_collateral_id, ref_appraisal_id) VALUES
('TAX-0805', 'Land', 'Residential', 10000, 21, 9),
('TAX-0806', 'Improvement', 'Residential', 5000, 21, 9);

INSERT INTO insurance_details_tbl (policy_number, insurance_company_name, insurance_or_number, expiry_date, insured_amount, ref_collateral_id, ref_appraisal_id) VALUES
('POL-0805', 'Sun Life Financial', '123', '2030-01-01', 20000000, 21, 9);

INSERT INTO appraisal_report (report_type, report_date, appraiser_name, ref_collateral_id) VALUES
('ERO Appraisal Report', '2016-02-10', 'Kevin Grosskreutz', 21);