Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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, 25, 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);
- 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 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
- (5, 'ENROPA', 'UDOA-LOAN-B-1453', 600000, 31, 17, 59, 25, 8, 25, 1);
- 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');
- INSERT INTO ada_tbl (outstanding_balance, booked_interest, unbooked_interest, unbooked_liquidated_damages, fair_market_value, appraisal_date, ref_collateral_id) VALUES
- (1500000, 50000, 450000, 350000, 6000000, '2017-07-14', 10),
- (750000, 35000, 250000, 210000, 75000, '2017-07-14', 11),
- (750000, 35000, 250000, 210000, 150000, '2017-07-14', 12),
- (500000, 25000, 150000, 125000, 0, '2016-06-16', 13),
- (1000000, 50000, 300000, 250000, 200000, '2015-05-18', 14),
- (1125000, 45000, 287500, 325000, 4750000, '2014-02-05', 15),
- (2250000, 90000, 575000, 650000, 9500000, '2014-02-05', 16);
- 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);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement