API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Sep 28th, 2017
541
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 95.20 KB | None | 0 0
raw download clone embed print report
  1.  
  2. INSERT INTO balance_tbl (cwt, others, liquidated_damages, accrued_interest_receivable, interest_income, outstanding_principal, total_outstanding_balance) VALUES
  3. ( 0.00, 0.00, 540000.00, 1130000.00, 206000.00, 6800000.00, 8676000.00), --Bank of the Philippine Islands
  4. ( 0.00, 0.00, 1030000.00, 1510000.00, 229700.00, 6800000.00, 9569700.00), --Banco De Oro
  5. ( 0.00, 0.00, 890000.00, 1100000.00, 191700.00, 5600000.00, 7781700.00), --Metro Bank
  6. ( 0.00, 0.00, 540000.00, 1130000.00, 206000.00, 6800000.00, 8676000.00), --CRB of SDMD
  7. ( 0.00, 0.00, 1030000.00, 1510000.00, 229700.00, 6800000.00, 9569700.00), --Cooperative Bank of SDMD
  8. ( 0.00, 0.00, 890000.00, 1100000.00, 191700.00, 5600000.00, 7781700.00), --RB of SDMD
  9. ( 0.00, 0.00, 340000.00, 770000.00, 122000.00, 4000000.00, 5232000.00), --Loan 123456 (Bank of the Philippine Islands)
  10. ( 0.00, 0.00, 200000.00, 360000.00, 84000.00, 2800000.00, 3444000.00), --Loan 234567 (Bank of the Philippine Islands)
  11. ( 0.00, 0.00, 690000.00, 740000.00, 107700.00, 2800000.00, 4337700.00), --Loan 345678 (Banco De Oro)
  12. ( 0.00, 0.00, 340000.00, 770000.00, 122000.00, 4000000.00, 5232000.00), --Loan 456789 (Banco De Oro)
  13. ( 0.00, 0.00, 200000.00, 360000.00, 84000.00, 2800000.00, 3444000.00), --Loan 567891 (Metro Bank)
  14. ( 0.00, 0.00, 690000.00, 740000.00, 107700.00, 2800000.00, 4337700.00), --Loan 678912 (Metro Bank)
  15. ( 0.00, 0.00, 340000.00, 770000.00, 122000.00, 4000000.00, 5232000.00), --Loan 789123 (CRB of SDMD)
  16. ( 0.00, 0.00, 200000.00, 360000.00, 84000.00, 2800000.00, 3444000.00), --Loan 891234 (CRB of SDMD)
  17. ( 0.00, 0.00, 690000.00, 740000.00, 107700.00, 2800000.00, 4337700.00), --Loan 912345 (Cooperative Bank of SDMD)
  18. ( 0.00, 0.00, 340000.00, 770000.00, 122000.00, 4000000.00, 5232000.00), --Loan 565541 (Cooperative Bank of SDMD)
  19. ( 0.00, 0.00, 200000.00, 360000.00, 84000.00, 2800000.00, 3444000.00), --Loan 878999 (RB of SDMD)
  20. ( 0.00, 0.00, 690000.00, 740000.00, 107700.00, 2800000.00, 4337700.00), --Loan 741335 (RB of SDMD)
  21. ( 0.00, 20000.00, 140000.00, 450000.00, 58000.00, 1200000.00, 1848000.00), --PN 201712 [Loan 123456 (Bank of the Philippine Islands)]
  22. ( 0.00, 0.00, 200000.00, 320000.00, 64000.00, 2800000.00, 3384000.00), --PN 201723 [Loan 123456 (Bank of the Philippine Islands)]
  23. ( 0.00, 18000.00, 80000.00, 110000.00, 34000.00, 1700000.00, 1924000.00), --PN 201734 [Loan 234567 (Bank of the Philippine Islands)]
  24. ( 0.00, 21000.00, 120000.00, 250000.00, 50000.00, 1100000.00, 1520000.00), --PN 201745 [Loan 234567 (Bank of the Philippine Islands)]
  25. ( 0.00, 45000.00, 390000.00, 550000.00, 53300.00, 1300000.00, 2293300.00), --PN 201756 [Loan 345678 (Banco De Oro)]
  26. ( 0.00, 67000.00, 300000.00, 190000.00, 54400.00, 1500000.00, 2044400.00), --PN 201767 [Loan 345678 (Banco De Oro)]
  27. ( 0.00, 20000.00, 140000.00, 450000.00, 58000.00, 1200000.00, 1848000.00), --PN 201778 [Loan 456789 (Banco De Oro)]
  28. ( 0.00, 0.00, 200000.00, 320000.00, 64000.00, 2800000.00, 3384000.00), --PN 201789 [Loan 456789 (Banco De Oro)]
  29. ( 0.00, 18000.00, 80000.00, 110000.00, 34000.00, 1700000.00, 1924000.00), --PN 201791 [Loan 567891 (Metro Bank)]
  30. ( 0.00, 21000.00, 120000.00, 250000.00, 50000.00, 1100000.00, 1520000.00), --PN 201713 [Loan 567891 (Metro Bank)]
  31. ( 0.00, 45000.00, 390000.00, 550000.00, 53300.00, 1300000.00, 2293300.00), --PN 201724 [Loan 678912 (Metro Bank)]
  32. ( 0.00, 67000.00, 300000.00, 190000.00, 54400.00, 1500000.00, 2044400.00), --PN 201729 [Loan 678912 (Metro Bank)]
  33. ( 0.00, 20000.00, 140000.00, 450000.00, 58000.00, 1200000.00, 1848000.00), --PN 201735 [Loan 789123 (CRB of SDMD)]
  34. ( 0.00, 0.00, 200000.00, 320000.00, 64000.00, 2800000.00, 3384000.00), --PN 201746 [Loan 789123 (CRB of SDMD)]
  35. ( 0.00, 18000.00, 80000.00, 110000.00, 34000.00, 1700000.00, 1924000.00), --PN 201757 [Loan 891234 (CRB of SDMD)]
  36. ( 0.00, 21000.00, 120000.00, 250000.00, 50000.00, 1100000.00, 1520000.00), --PN 201768 [Loan 891234 (CRB of SDMD)]
  37. ( 0.00, 45000.00, 390000.00, 550000.00, 53300.00, 1300000.00, 2293300.00), --PN 201779 [Loan 912345 (Cooperative Bank of SDMD)]
  38. ( 0.00, 67000.00, 300000.00, 190000.00, 54400.00, 1500000.00, 2044400.00), --PN 201744 [Loan 912345 (Cooperative Bank of SDMD)]
  39. ( 0.00, 20000.00, 140000.00, 450000.00, 58000.00, 1200000.00, 1848000.00), --PN 201792 [Loan 565541 (Cooperative Bank of SDMD)]
  40. ( 0.00, 0.00, 200000.00, 320000.00, 64000.00, 2800000.00, 3384000.00), --PN 201722 [Loan 565541 (Cooperative Bank of SDMD)]
  41. ( 0.00, 18000.00, 80000.00, 110000.00, 34000.00, 1700000.00, 1924000.00), --PN 201797 [Loan 878999 (RB of SDMD)]
  42. ( 0.00, 21000.00, 120000.00, 250000.00, 50000.00, 1100000.00, 1520000.00), --PN 201787 [Loan 878999 (RB of SDMD)]
  43. ( 0.00, 45000.00, 390000.00, 550000.00, 53300.00, 1300000.00, 2293300.00), --PN 201782 [Loan 741335 (RB of SDMD)]
  44. ( 0.00, 67000.00, 300000.00, 190000.00, 54400.00, 1500000.00, 2044400.00), --PN 201733 [Loan 741335 (RB of SDMD)]
  45. ( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --Bangkong Luma
  46. ( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --Bangkong Medyo Luma
  47. ( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --Bangkong Medyo Bago
  48. ( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --Loan 999990 (Bangkong Luma)
  49. ( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --Loan 999991 (Bangkong Medyo Luma)
  50. ( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --Loan 999992 (Bangkong Medyo Bago)
  51. ( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --PN 201751 [Loan 999990 (Bangkong Luma)]
  52. ( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --PN 201752 [Loan 999991 (Bangkong Medyo Luma)]
  53. ( 0.00, 50000.00, 60000.00, 80000.00, 100000.00, 500000.00, 740000.00), --PN 201753 [Loan 999992 (Bangkong Medyo Bago)]
  54. ( 0.00, 36000.00, 66000.00, 96000.00, 156000.00, 900000.00, 12180000.00), --Bangko Mo
  55. ( 0.00, 36000.00, 66000.00, 96000.00, 156000.00, 900000.00, 1218000.00), --Loan 999993 (Bangko Mo)
  56. ( 0.00, 14000.00, 24000.00, 34000.00, 54000.00, 200000.00, 312000.00), --PN 201771 [Loan 999993 (Bangko Mo)]
  57. ( 0.00, 12000.00, 22000.00, 32000.00, 52000.00, 250000.00, 356000.00), --PN 201772 [Loan 999993 (Bangko Mo)]
  58. ( 0.00, 10000.00, 20000.00, 30000.00, 50000.00, 450000.00, 550000.00), --PN 201773 [Loan 999993 (Bangko Mo)]
  59. ( 0.00, 10000.00, 20000.00, 30000.00, 70000.00, 1000000.00, 1120000.00), --Bangko Ko
  60. ( 0.00, 10000.00, 20000.00, 30000.00, 70000.00, 1000000.00, 1120000.00), --Loan 999994 (Bangko Ko)
  61. ( 0.00, 10000.00, 20000.00, 30000.00, 70000.00, 1000000.00, 1120000.00); --PN 201774 [Loan 999994 (Bangko Ko)]
  62.  
  63.  
  64. INSERT INTO balance_tbl (ref_id, balance_type, cwt, others, liquidated_damages, accrued_interest_receivable, interest_income, outstanding_principal, total_outstanding_balance) VALUES
  65. (32, 'PN', 0.00, 0.00, 50000.00, 150000.00, 75000.00, 225000.00, 500000.00),
  66. (33, 'PN', 0.00, 0.00, 60000.00, 160000.00, 80000.00, 200000.00, 500000.00),
  67. (34, 'PN', 0.00, 0.00, 50000.00, 150000.00, 75000.00, 225000.00, 500000.00),
  68. (35, 'PN', 0.00, 0.00, 10000.00, 20000.00, 15000.00, 55000.00, 100000.00),
  69. (36, 'PN', 0.00, 0.00, 20000.00, 40000.00, 30000.00, 110000.00, 200000.00),
  70. (37, 'PN', 0.00, 0.00, 30000.00, 60000.00, 45000.00, 165000.00, 300000.00),
  71. (18, 'LOAN', 0.00, 0.00, 160000.00, 460000.00, 230000.00, 650000.00, 1500000.00),
  72. (19, 'LOAN', 0.00, 0.00, 60000.00, 120000.00, 90000.00, 330000.00, 600000.00),
  73. (38, 'PN', 0.00, 0.00, 125000.00, 100000.00, 125000.00, 350000.00, 700000.00),
  74. (39, 'PN', 0.00, 0.00, 250000.00, 75000.00, 100000.00, 325000.00, 750000.00),
  75. (20, 'LOAN', 0.00, 0.00, 300000.00, 225000.00, 275000.00, 650000.00, 1450000.00),
  76. (12, 'BANK', 0.00, 0.00, 220000.00, 580000.00, 320000.00, 980000.00, 2100000.00),
  77. (40, 'PN', 0.00, 0.00, 40000.00, 55000.00, 30000.00, 125000.00, 250000.00),
  78. (21, 'LOAN', 0.00, 0.00, 40000.00, 55000.00, 30000.00, 125000.00, 250000.00),
  79. (13, 'BANK', 0.00, 0.00, 340000.00, 280000.00, 305000.00, 775000.00, 1700000.00);
  80.  
  81. INSERT INTO balance_tbl (cwt, others, liquidated_damages, accrued_interest_receivable, interest_income, outstanding_principal, total_outstanding_balance) VALUES
  82. ( 0.00, 0.00, 540000.00, 1130000.00, 206000.00, 1200000.00, 3076000.00), --PN 75
  83. ( 0.00, 0.00, 1030000.00, 1510000.00, 229700.00, 2800000.00, 5569700.00), --PN 76
  84. ( 0.00, 0.00, 890000.00, 1100000.00, 191700.00, 2000000.00, 4181700.00), --PN 77
  85. ( 0.00, 0.00, 2460000.00, 3740000.00, 627400.00, 6000000.00, 12827400.00); --LOAN 78
  86.  
  87. INSERT INTO balance_tbl (cwt, others, liquidated_damages, accrued_interest_receivable, interest_income, outstanding_principal, total_outstanding_balance) VALUES
  88. ( 0.00, 0.00, 540000.00, 1130000.00, 206000.00, 1200000.00, 3076000.00), --PN 79
  89. ( 0.00, 0.00, 1030000.00, 1510000.00, 229700.00, 2800000.00, 5569700.00), --PN 80
  90. ( 0.00, 0.00, 890000.00, 1100000.00, 191700.00, 2000000.00, 4181700.00), --PN 81
  91. ( 0.00, 0.00, 2460000.00, 3740000.00, 627400.00, 6000000.00, 12827400.00); --LOAN 82
  92.  
  93. INSERT INTO balance_tbl (ref_id, balance_type, cwt, others, liquidated_damages, accrued_interest_receivable, interest_income, outstanding_principal, total_outstanding_balance) VALUES
  94. (47, 'PN', 0.00, 0.00, 50000.00, 150000.00, 75000.00, 225000.00, 500000.00), -- PBB -- 83
  95. (48, 'PN', 0.00, 0.00, 60000.00, 160000.00, 80000.00, 200000.00, 500000.00), -- PBB -- 84
  96. (49, 'PN', 0.00, 0.00, 50000.00, 150000.00, 75000.00, 225000.00, 500000.00), -- PBB -- 85
  97. (50, 'PN', 0.00, 0.00, 10000.00, 20000.00, 15000.00, 55000.00, 100000.00), -- PBB -- 86
  98. (51, 'PN', 0.00, 0.00, 20000.00, 40000.00, 30000.00, 110000.00, 200000.00), -- PBB -- 87
  99. (52, 'PN', 0.00, 0.00, 30000.00, 60000.00, 45000.00, 165000.00, 300000.00), -- PBB -- 88
  100. (24, 'LOAN', 0.00, 0.00, 420000.00, 460000.00, 230000.00, 650000.00, 1500000.00), -- PBB -- 89
  101. (25, 'LOAN', 0.00, 0.00, 260000.00, 690000.00, 325000.00, 975000.00, 2250000.00), -- PBB -- 90
  102. (14, 'BANK', 0.00, 0.00, 680000.00, 1150000.00, 555000.00, 1625000.00, 3750000.00); -- PBB -- 91
  103.  
  104. INSERT INTO balance_tbl (ref_id, balance_type, cwt, others, liquidated_damages, accrued_interest_receivable, interest_income, outstanding_principal, total_outstanding_balance) VALUES
  105. (53, 'PN', 0.00, 0.00, 70000.00, 190000.00, 80000.00, 230000.00, 570000.00),
  106. (54, 'PN', 0.00, 0.00, 50000.00, 210000.00, 90000.00, 210000.00, 560000.00),
  107. (55, 'PN', 0.00, 0.00, 80000.00, 170000.00, 65000.00, 205000.00, 520000.00);
  108.  
  109. INSERT INTO bank_tbl ( bank_code, bank_name, bank_type, bank_status, date_joined, bank_address, date_close, ref_balance_id) VALUES
  110. ( 'BPI', 'Bank of the Philippine Islands', 'Rural', 'Operating', '1999-05-21', 'Gapan Nueva Ecija', NULL, 1),
  111. ( 'BDO', 'Banco De Oro', 'Rural', 'Operating', '1999-10-22', 'San Mateo Rizal', NULL, 2),
  112. ( 'MB', 'Metro Bank', 'Coop', 'Operating', '2000-05-21', 'Blumentritt', NULL, 3),
  113. ( 'CRBSDMD', 'CRB of SDMD', 'Coop', 'Operating', '1995-05-21', 'Quezon City', NULL, 4),
  114. ( 'CBSDMD', 'Cooperative Bank of SDMD', 'Coop', 'Operating', '2000-05-21', 'Makati City', NULL, 5),
  115. ( 'RBSDMD', 'RB of SDMD', 'Rural', 'Operating', '2000-05-21', 'Pampanga', NULL, 6),
  116. ( 'BL', 'Bangkong Luma', 'Rural', 'Closed', '1970-05-21', 'Cabiao Nueva Ecija', '1980-07-09', 43),
  117. ( 'BML', 'Bangkong Medyo Luma', 'Rural', 'Closed', '1975-06-30', 'Cabanatuan Nueva Ecija', '1980-07-09', 44),
  118. ( 'BMB', 'Bangkong Medyo Bago', 'Rural', 'Closed', '2005-11-01', 'Arayat Pampanga', '2015-07-09', 45),
  119. ( 'BM', 'Bangko Mo', 'Rural', 'Closed', '2000-10-08', 'Jaro Iloilo', '2012-10-08', 52),
  120. ( 'BK', 'Bangko Ko', 'Rural', 'Closed', '1980-10-08', 'Tagbak Iloilo', '1990-10-08', 57),
  121. ( 'RCBC', 'Rizal Commercial Banking Corporation', 'Rural', 'Operating', '1999-05-21', 'Marikina City', NULL, 71),
  122. ( 'UPCB', 'United Coconut Planters Bank', 'Rural', 'Operating', '1999-10-22', 'Quezon City', NULL, 74);
  123.  
  124. INSERT INTO bank_tbl ( bank_code, bank_name, bank_type, bank_status, date_joined, bank_address, date_close, ref_balance_id) VALUES
  125. ( 'PBB', 'Philippine Business Bank', 'Rural', 'Operating', '1999-10-22', 'Quezon City', NULL, 91); -- 14
  126.  
  127. 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
  128. (123456, 1, 'Rediscounting', 'Current Accounts', 0.0313, '2016-05-16', '2016-11-12', 180, 4000000.00, 7),
  129. (234567, 1, 'Rediscounting', 'Current Accounts', 0.0313, '2016-07-08', '2017-01-04', 180, 2800000.00, 8),
  130. (345678, 2, 'Rediscounting', 'Current Accounts', 0.0313, '2016-07-21', '2017-01-17', 180, 4000000.00,9),
  131. (456789, 2, 'Rediscounting', 'Current Accounts', 0.03, '2017-02-22', '2017-08-21', 180, 2800000.00,10),
  132. (567891, 3, 'Rediscounting', 'Current Accounts', 0.03, '2016-05-21', '2016-11-17', 180, 4000000.00,11),
  133. (678912, 3, 'Rediscounting', 'Current Accounts', 0.0313, '2017-01-22', '2017-07-21', 180, 2800000.00,12),
  134. (789123, 4, 'Rediscounting', 'Current Accounts', 0.0313, '2016-05-21', '2017-05-16', 360, 4000000.00,13),
  135. (891234, 4, 'Rediscounting', 'Current Accounts', 0.03, '2017-02-18', '2018-02-13', 360, 2800000.00, 14),
  136. (912345, 5, 'Rediscounting', 'Current Accounts', 0.03, '2016-01-11', '2017-01-05', 360,4000000.00,15),
  137. (565541, 5, 'Rediscounting', 'Current Accounts', 0.03, '2016-01-03', '2016-12-28', 360, 2800000.00,16),
  138. (878999, 6, 'Rediscounting', 'Current Accounts', 0.03, '2016-07-11', '2017-07-06', 360, 4000000.00,17),
  139. (741335, 6, 'Rediscounting', 'Current Accounts', 0.03, '2016-09-26', '2017-09-21', 360, 2800000.00,18),
  140. (999990, 7, 'Rediscounting', 'Past Due', 0.12, '1975-12-01', '1976-05-29', 180, 500000.00, 46),
  141. (999991, 8, 'Rediscounting', 'Past Due', 0.12, '1977-12-01', '1978-05-30', 180, 500000.00, 47),
  142. (999992, 9, 'Rediscounting', 'Past Due', 0.12, '2000-12-01', '2001-05-30', 180, 500000.00, 48),
  143. (999993, 10, 'Rediscounting', 'Past Due', 0.10, '2010-06-01', '2010-11-28', 180, 900000.00, 53);
  144.  
  145. 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
  146. (999994, 11, 'Rediscounting', 'Past Due', 0.10, '1988-06-01', '1988-11-28', 1000000.00, 58, 1);
  147.  
  148. 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
  149. (999901, 12, 'Rediscounting', 'Past Due', 0.12, '2016-05-16', '2016-11-12', 180, 1500000.00, 66),
  150. (999902, 12, 'Rediscounting', 'Current Accounts', 0.12, '2016-05-16', '2016-11-12', 180, 600000.00, 67),
  151. (999903, 13, 'Rediscounting', 'Current Accounts', 0.18, '2016-05-16', '2016-11-12', 180, 1450000.00, 70),
  152. (999904, 13, 'Rediscounting', 'Current Accounts', 0.07, '2016-05-16', '2016-11-12', 180, 250000.00, 73);
  153.  
  154. 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
  155. (999995, 1, 'Emergency Loan', 'Current Accounts', 0.0313, '2016-05-16', '2016-11-12', 180, 6000000.00, 78);
  156.  
  157. 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
  158. (999996, 2, 'Emergency Loan', 'Current Accounts', 0.0313, '2016-05-16', '2016-11-12', 180, 6000000.00, 82);
  159.  
  160. 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
  161. (999997, 14, 'Rediscounting', 'Past Due', 0.12, '2016-05-16', '2016-11-12', 180, 1500000.00, 89), -- 24
  162. (999998, 14, 'Rediscounting', 'Current Accounts', 0.12, '2016-05-16', '2016-11-12', 180, 2250000.00, 90); -- 25
  163.  
  164. INSERT INTO approval_status_tbl(approval_status)
  165. VALUES
  166. ('DRAFT'),
  167. ('RETURNED TO MAKER'),
  168. ('SUBMITTED TO REVIEWER'),
  169. ('SUBMITTER TO APPROVER'),
  170. ('APPROVED'),
  171. ('REJECTED'),
  172. ('DELETED'),
  173. ('RETURNED'),
  174. ('COMPLETED'),
  175. ('BOOKED'),
  176. ('POSTED');
  177.  
  178.  
  179. INSERT INTO END_USER_BORROWER_TBL(entity_name, full_name) VALUES
  180. ('Mcdonalds' , 'Ray Kroc'),
  181. ('Jollibee' , 'Tony Tan Caktiong'),
  182. ('KFC' , 'Colonel Sanders'),
  183. ('Mang Inasal' , 'Edgar Sia'),
  184. ('Wendys' , 'Dave Thomas'),
  185. ('Coca-cola' , 'John Smith Pemberton'),
  186. ('Starbucks' , 'Gordon Bowker'),
  187. ('Reddit' , 'Steve Huffman, Alexis Ohanian'),
  188. ('Pizza Hut' , 'Dan Carney, Frank Carney'),
  189. ('Pepsi' , 'Thomas Karlton'),
  190. ('Bandai' , 'Juan Ted'),
  191. ('Nike' , 'Yeri Youngston'),
  192. ('Adidas' , 'Sherley Maedar'),
  193. ('Zesto' , 'Steve Jobs'),
  194. ('Microsoft' , 'Bill Gates'),
  195. ('Facebook' , 'Mark Zuckerberg'),
  196. ('KKK' , 'Jensen Huang'),
  197. ('Wendys' , 'Henry Sy'),
  198. ('JDC' , 'Juan Dela Cruz'),
  199. ('JOHNDC' , 'John Dela Cruz'),
  200. ('JODC' , 'Jo Dela Cruz'),
  201. ('CA Corp' , 'Corazon Aquino'),
  202. ('MQ' , 'Manuel Quezon'),
  203. ('SA Inc.' , 'Sergio Osmena'),
  204. ('ATon' , 'Antonio Luna'),
  205. ('GSM' , 'Gil More'),
  206. ('Empi' , 'Empoy Incheon'),
  207. ('Matador' , 'Mara Doria'),
  208. ('Tequila' , 'Teddy Quila'),
  209. ('Margarita' , 'Marga Rita'),
  210. ('Guiness' , 'Guiness Record'),
  211. ('Murphys' , 'Eddie Murphy'),
  212. ('Stout' , 'Stand Out'),
  213. ('Baileys' , 'Bailey Ann');
  214.  
  215. INSERT INTO END_USER_BORROWER_TBL(entity_name, full_name) VALUES
  216. ('Dell' , 'Mundo'),
  217. ('Gold' , 'Smart'),
  218. ('Silver' , 'Brite');
  219.  
  220. 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, beginning_ada_principal, beginning_ada_booked_interest) VALUES
  221. ( 201712, 1, 'Current Accounts', 10, '2016-01-01', '2017-01-05', 1200000.00,19,1, 677343.85, 76671.83),
  222. ( 201723, 1, 'Current Accounts', 10, '2016-05-11', '2017-02-20', 2800000.00, 20,2, 350869.79, 41619.01),
  223. ( 201734, 2, 'Current Accounts', 10, '2016-05-12', '2017-03-19', 1700000.00, 21,3, 305688.31, 111500.05),
  224. ( 201745, 2, 'Current Accounts', 10, '2016-05-13', '2017-04-16', 1100000.00, 22,4, 305688.31, 109944.48),
  225. ( 201756, 3, 'Current Accounts', 10, '2016-05-14', '2017-02-23', 1300000.00, 23,5, 999533.97, 33459.43),
  226. ( 201767, 3, 'Current Accounts', 10, '2016-03-14', '2017-02-05', 1500000.00, 24,6, 755325.46, 53249.98),
  227. ( 201778, 4, 'Current Accounts', 10, '2016-05-15', '2017-02-05', 1200000.00, 25,7, 428994.19, 51505.83),
  228. ( 201789, 4, 'Current Accounts', 9, '2016-07-21', '2017-02-05', 2800000.00, 26,8, 428559.10, 73103.48),
  229. ( 201791, 5, 'Current Accounts', 9, '2017-01-28', '2017-02-15', 1700000.00, 27,9, 1126765.90, 77540.52),
  230. ( 201713, 5, 'Current Accounts', 9, '2017-01-29', '2018-10-25', 1100000.00, 28,10, 749647.69, 107106.25),
  231. ( 201724, 6, 'Current Accounts', 9, '2016-05-10', '2018-08-22', 1300000.00, 29,11, 1377886.21, 57192.36),
  232. ( 201729, 6, 'Current Accounts', 9, '2016-03-15', '2018-04-24', 1500000.00, 30,12, 472181.25, 62437.04),
  233. ( 201735, 7, 'Current Accounts', 9, '2017-02-22', '2018-09-22', 1200000.00, 31,13, 533748.39, 80096.21),
  234. ( 201746, 7, 'Current Accounts', 12, '2015-12-16', '2018-11-03', 2800000.00,32,14, 564326.26, 36581.56),
  235. ( 201757, 8, 'Current Accounts', 12, '2017-12-22', '2018-11-05', 1700000.00, 33,15, 899674.85, 95236.67),
  236. ( 201768, 8, 'Current Accounts', 12, '2014-05-18', '2018-12-05', 1100000.00, 34,16, 947121.26, 97958.70),
  237. ( 201779, 9, 'Current Accounts', 12, '2015-01-22', '2018-02-15', 1300000.00, 35,17, 1357628.47, 22896.51),
  238. ( 201744, 9, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 1500000.00,36,18, 574112.02, 103489.76),
  239. ( 201792, 10, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 1200000.00,37,null, 459345.48, 79872.89),
  240. ( 201722, 10, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 2800000.00,38,null, 1156782.91, 37596.23),
  241. ( 201797, 11, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 1700000.00,39,null, 1205521.11, 34877.41),
  242. ( 201787, 11, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 1100000.00,40,null, 1138743.05, 23621.93),
  243. ( 201782, 12, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 1300000.00,41,null, 514703.72, 52512.55),
  244. ( 201733, 12, 'Current Accounts', 12, '2013-05-16', '2018-01-05', 1500000.00,42,null, 753535.08, 25369.34),
  245. ( 201751, 13, 'Past Due', 0.12, '1975-12-01', '1976-05-29', 500000.00, 49,19, 401398.73, 106557.03),
  246. ( 201752, 14, 'Past Due', 0.12, '1977-12-01', '1978-05-30', 500000.00, 50,20, 1288300.30, 47211.78),
  247. ( 201753, 15, 'Past Due', 0.12, '2000-12-01', '2001-05-30', 500000.00, 51,21, 1136697.77, 55623.95),
  248. ( 201771, 16, 'Past Due', 0.18, '2009-12-03', '2010-11-28', 200000.00, 54,22, 988038.49, 29136.04),
  249. ( 201772, 16, 'Past Due', 0.18, '2009-12-03', '2010-11-28', 250000.00, 55,23, 378515.11, 91427.23),
  250. ( 201773, 16, 'Past Due', 0.20, '2009-12-02', '2010-11-27', 450000.00, 56,24, 1026097.78, 60388.98),
  251. ( 201774, 17, 'Past Due', 0.18, '1988-06-03', '1988-11-28', 1000000.00, 59,25, 782661.22, 60629.30),
  252. ( 202000, 18, 'Past Due', 0.15, '2016-01-01', '2017-01-05', 500000.00, 60,26, 893821.98, 70591.98),
  253. ( 202001, 18, 'Past Due', 0.15, '2016-01-01', '2017-01-05', 500000.00, 61,27, 407786.11, 52206.88),
  254. ( 202002, 18, 'Past Due', 0.15, '2016-01-01', '2017-01-05', 500000.00, 62,28, 855137.73, 78726.99),
  255. ( 202003, 19, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 100000.00, 63,29, 1384312.86, 53753.38),
  256. ( 202004, 19, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 200000.00, 64,30, 1435595.26, 111496.58),
  257. ( 202005, 19, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 300000.00, 65,31, 959797.97, 70737.38),
  258. ( 202006, 20, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 700000.00, 68,32, 725206.12, 30761.30),
  259. ( 202007, 20, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 750000.00, 69,33, 1309439.90, 20156.66),
  260. ( 202008, 21, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 250000.00, 72,34, 1309439.90, 20156.66);
  261.  
  262. 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, beginning_ada_principal, beginning_ada_booked_interest) VALUES
  263. ( 202009, 22, 'Current Accounts', 10, '2016-01-01', '2017-01-05', 1200000.00,75,35, 747001.62, 70608.49),
  264. ( 202010, 22, 'Current Accounts', 10, '2016-05-11', '2017-02-20', 2800000.00, 76,36, 1431404.95, 41508.20),
  265. ( 202011, 22, 'Current Accounts', 10, '2016-05-12', '2017-03-19', 2000000.00, 77,37, 1453694.33, 51547.59);
  266.  
  267. 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, beginning_ada_principal, beginning_ada_booked_interest) VALUES
  268. ( 202012, 23, 'Current Accounts', 10, '2016-01-01', '2017-01-05', 1200000.00,79, 33, 1186264.07, 84831.51),
  269. ( 202013, 23, 'Current Accounts', 10, '2016-05-11', '2017-02-20', 2800000.00, 80, 33, 557962.07, 76403.95),
  270. ( 202014, 23, 'Current Accounts', 10, '2016-05-12', '2017-03-19', 2000000.00, 81, 33, 748421.86, 70792.68);
  271.  
  272. 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, beginning_ada_principal, beginning_ada_booked_interest) VALUES
  273. ( 202015, 24, 'Past Due', 0.16, '2016-01-01', '2017-01-05', 500000.00, 83,26, 1102757.16, 80931.26),
  274. ( 202016, 24, 'Past Due', 0.15, '2016-01-01', '2017-01-05', 500000.00, 84,27, 855137.73, 78726.99),
  275. ( 202017, 24, 'Past Due', 0.14, '2016-01-01', '2017-01-05', 500000.00, 85,28, 1384312.86, 53753.38),
  276. ( 202018, 25, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 100000.00, 86,29, 1435595.26, 70792.68),
  277. ( 202019, 25, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 200000.00, 87,30, 1102757.16, 76403.95),
  278. ( 202020, 25, 'Current Accounts', 0.12, '2016-01-01', '2017-01-05', 300000.00, 88,31, 1186264.07, 51547.59);
  279.  
  280. 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, beginning_ada_principal, beginning_ada_booked_interest) VALUES
  281. ( 202021, 25, 'For Foreclosure', 10, '2016-01-01', '2017-01-05', 570000.00, 92,34, 1309439.90, 52206.88),
  282. ( 202022, 25, 'For Foreclosure', 10, '2016-01-01', '2017-01-05', 560000.00, 93,34, 855137.73, 60629.30),
  283. ( 202023, 25, 'For Foreclosure', 10, '2016-01-01', '2017-01-05', 520000.00, 94,34, 378515.11, 111496.58);
  284.  
  285. INSERT INTO registry_of_deeds_tbl(registry_of_deeds) VALUES
  286. ('RD - Manila'),
  287. ('RD - Quezon City'),
  288. ('RD - Makati City'),
  289. ('RD - Pasay City'),
  290. ('RD - Malabon / Navotas'),
  291. ('RD - Valenzuela'),
  292. ('RD - San Juan'),
  293. ('RD - Mandaluyong'),
  294. ('RD - Pasig City'),
  295. ('RD - Parañaque'),
  296. ('RD - Antipolo City'),
  297. ('RD - Caloocan City'),
  298. ('RD - Las Piñas'),
  299. ('RD - Marikina City'),
  300. ('RD - Muntinlupa City'),
  301. ('RD - Province Of Rizal (Binangonan)'),
  302. ('RD - Morong, Rizal'),
  303. ('RD - Taguig City');
  304.  
  305. INSERT INTO collateral_tbl(collateral_number, ref_rd_id, address, appraisal_date, fmv) VALUES
  306. ('TCT-101', 1, '6153 South Super Highway Makati, Philippines', '2017-09-12', 2000000.00),
  307. ('TCT-102', 2, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2017-09-13', 3400000.00),
  308. ('TCT-103', 3, '6153 South Super Highway Makati, Philippines', '2014-09-14', 1520000.00),
  309. ('TCT-104', 4, '14 Zaragoza St. San Lorenzo Village, Makati', '2016-10-17', 5670000.00),
  310. ('TCT-105', 5, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2015-08-07', 9500000.00),
  311. ('TCT-106', 6, '6153 South Super Highway Makati, Philippines', '2016-01-29', 6021000.00),
  312. ('TCT-107', 7, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', '2011-12-26', 1200000.00),
  313. ('TCT-108', 8, '14 Zaragoza St. San Lorenzo Village, Makati', '2012-11-08', 4900000.00),
  314. ('TCT-109', 9, '6153 South Super Highway Makati, Philippines', '2013-11-02', 4600000.00),
  315. ('TCT-110',10, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2017-07-14', 1900000.00),
  316. ('TCT-111',11, '6153 South Super Highway Makati, Philippines', '2017-07-14', 75000.00),
  317. ('TCT-112',12, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', '2017-07-14', 150000.00),
  318. ('TCT-113',13, '14 Zaragoza St. San Lorenzo Village, Makati', '2016-06-16', 5900000.00),
  319. ('TCT-114',14, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2015-05-18', 200000.00),
  320. ('TCT-115',15, '14 Zaragoza St. San Lorenzo Village, Makati', '2014-02-05', 4750000.00),
  321. ('TCT-116',16, '6153 South Super Highway Makati, Philippines', '2014-02-05', 9500000.00),
  322. ('TCT-117',17, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City','2016-03-17', 768000.00),
  323. ('TCT-118',18, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2012-09-08', 1000000.00),
  324. ('TCT-119',1, '14 Zaragoza St. San Lorenzo Village, Makati', '2017-09-14', 1800000.00),
  325. ('TCT-120', 2, '6153 South Super Highway Makati, Philippines', '2016-09-17', 2600000.00),
  326. ('TCT-121', 3, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', '2017-05-23', 9600000.00),
  327. ('TCT-122', 4, '14 Zaragoza St. San Lorenzo Village, Makati', '2012-02-18', 5860000.00),
  328. ('TCT-123', 5, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2014-02-14', 4200000.00),
  329. ('TCT-124', 6, '6153 South Super Highway Makati, Philippines', '2010-05-18', 9635000.00),
  330. ('TCT-125', 7, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', '2011-02-03', 2360000.00),
  331. ('TCT-126', 8, '14 Zaragoza St. San Lorenzo Village, Makati', '2017-12-14', 1230000.00),
  332. ('TCT-127', 9, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2017-11-02', 4200000.00),
  333. ('TCT-128',10, '6153 South Super Highway Makati, Philippines', '2010-12-26', 7800000.00),
  334. ('TCT-129',11, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', '2011-10-17', 6300000.00),
  335. ('TCT-130',12, '14 Zaragoza St. San Lorenzo Village, Makati', '2013-05-18', 2500000.00),
  336. ('TCT-131',13, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2014-06-14', 6300000.00),
  337. ('TCT-132',14, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2012-04-17', 7700000.00),
  338. ('TCT-133',15, '6153 South Super Highway Makati, Philippines', '2010-06-15', 3690000.00),
  339. ('TCT-134',16, '14 Zaragoza St. San Lorenzo Village, Makati', '2017-09-16', 3800000.00),
  340. ('TCT-135',17, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2015-10-17', 2500000.00),
  341. ('TCT-136',18, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', '2017-02-14', 1000000.00),
  342. ('TCT-137', 1, '14 Zaragoza St. San Lorenzo Village, Makati', '2011-05-18', 3000000.00),
  343. ('TCT-138', 2, '6153 South Super Highway Makati, Philippines', '2011-05-18', 3250000.00),
  344. ('TCT-139', 3, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2017-09-12', 3560000.00),
  345. ('TCT-140', 4, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', '2014-10-17', 3400000.00),
  346. ('TCT-141', 5, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2016-12-14', 1100000.00),
  347. ('TCT-142', 6, 'Kennedy Drive Pleasant View Subd. Tandang Sora, Queson City', '2017-02-14', 1400000.00),
  348. ('TCT-143', 7, '6153 South Super Highway Makati, Philippines', '2011-01-26', 5200000.00),
  349. ('TCT-144', 8, 'Block 5H Lot 27A Jade Heights Victoria Homes, Tunasan Muntinlupa City, Metro Manila', '2011-08-21', 8000000.00),
  350. ('TCT-145', 9, '14 Zaragoza St. San Lorenzo Village, Makati', '2014-09-29', 3200000.00),
  351. ('TCT-146',10, '6153 South Super Highway Makati, Philippines', '2015-07-14', 7800000.00),
  352. ('TCT-147', 3, '6154 South Super Highway Makati, Philippines', '2017-09-12', 3600000.00);
  353.  
  354. INSERT INTO role_tbl (role) VALUES
  355. ('FSM_Maker'), ('FSM_Reviewer'), ('FSM_Approver'),
  356. ('LMM_Maker'), ('LMM_Reviewer'), ('LMM_Approver'),
  357. ('ROLE_FSM'), ('ROLE_LMM');
  358.  
  359. INSERT INTO user_status_tbl (user_status_name, user_status_type) VALUES
  360. ('Logged off', 'Access Status'),
  361. ('Logged on', 'Access Status'),
  362. ('Inactive', 'Access Status'),
  363. ('New', 'Account Status'),
  364. ('Active', 'Account Status'),
  365. ('Deactivated', 'Account Status');
  366.  
  367. INSERT INTO user_tbl (user_name, ref_user_account_status_id) VALUES
  368. ('Jane_Maker', 4),
  369. ('John_Reviewer', 4),
  370. ('Josh_Approver', 4),
  371. ('fsm.maker', 4),
  372. ('fsm.reviewer', 4),
  373. ('fsm.approver', 4),
  374. ('lmm.maker', 4),
  375. ('lmm.reviewer', 4),
  376. ('lmm.approver', 4),
  377. ('lcuser1', 4),
  378. ('lcuser2', 4),
  379. ('lcuser3', 4),
  380. ('Mark.Nipes', 4),
  381. ('ana.rangasajo', 4),
  382. ('Kenneth.Laforteza', 4);
  383.  
  384. INSERT INTO user_role (ref_user_id, ref_role_id) VALUES
  385. (1, 1),
  386. (1, 7),
  387. (2, 2),
  388. (2, 7),
  389. (3, 3),
  390. (3, 7),
  391. (4, 1),
  392. (4, 7),
  393. (5, 2),
  394. (5, 7),
  395. (6, 3),
  396. (6, 7),
  397. (7, 4),
  398. (7, 8),
  399. (8, 5),
  400. (8, 8),
  401. (9, 6),
  402. (9, 8),
  403. (10, 1),
  404. (10, 7),
  405. (10, 4),
  406. (10, 8),
  407. (11, 2),
  408. (11, 7),
  409. (11, 5),
  410. (11, 8),
  411. (12, 3),
  412. (12, 7),
  413. (12, 6),
  414. (12, 8),
  415. (13, 1),
  416. (13, 7),
  417. (13, 4),
  418. (13, 8),
  419. (14, 2),
  420. (14, 7),
  421. (14, 5),
  422. (14, 8),
  423. (15, 3),
  424. (15, 7),
  425. (15, 6),
  426. (15, 8);
  427.  
  428. INSERT INTO chart_of_accts_tbl(chart_account_ref_code,chart_account_title,chart_account_code,chart_default_entry_type) VALUES
  429. ('PO-DLC-CASHCHK','DEPARTMENT OF LOANS AND CREDIT','172500000061000000','CREDIT');
  430.  
  431. INSERT INTO PAYMENT_ORDER_TBL(
  432. payment_order_number, payment_order_date, bank_name, end_user_borrower, ref_approval_status_id, or_number, or_date, user_name, receiving_office, bank_level_payment_type, total_payment_amount, payment_type, loan_type, payment_level)
  433. VALUES('1113', '2017-07-06', 'Banco De Oro', 'Kevin Grosskreutz', 3, '1', '2017-07-06', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 1000000.00, 'CASH/CHECK', 'Rediscounting', ''),
  434. ('1121', '2017-05-21', 'Bank of the Philippine Islands', 'Bastian Schweinsteiger', 9, '3', '2017-05-21', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 2000000.00, 'CASH/CHECK', 'Rediscounting', ''),
  435. ('1042', '2017-11-30', 'Metro Bank', 'Florian Grillitsch', 4, '2', '2017-11-30', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 3000000.00, 'CASH/CHECK', 'Rediscounting', ''),
  436. ('1873', '2017-02-14', 'Bank of the Philippine Islands', 'Marcel Sabitzer', 3, '4', '2017-02-14', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
  437. ('2334', '2017-03-06', 'Banco De Oro', 'Lionel Messi', 4, '5', '2017-03-06', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
  438. ('2001', '2017-05-21', 'Bank of the Philippine Islands', 'Luis Suarez', 2, '6', '2017-05-21', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 10000000.00, 'CASH/CHECK', 'Rediscounting', 'BANK'),
  439. ('1423', '2017-12-30', 'Metro Bank', 'Marcel Schmelzer', 6, '7', '2017-12-30', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
  440. ('1356', '2017-01-14', 'Bank of the Philippine Islands', 'Arturo Vidal', 1, '8', '2017-01-14', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
  441. ('5003', '2017-02-06', 'Banco De Oro', 'Gareth Bale',11, '5', '2017-02-06', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
  442. ('1010', '2017-07-21', 'Metro Bank', 'Wayne Rooney', 5, '6', '2017-07-21', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
  443. ('1169', '2017-08-30', 'Bank of the Philippine Islands', 'Manuel Neuer', 9, '7', '2017-08-30', 'Jane_Maker', 'Cash Dept', 'DLC CLAIMS', 100000.00, 'CASH/CHECK', 'Rediscounting', ''),
  444. ('6934', '2017-06-14', 'Bank of the Philippine Islands', 'Keylor Navas', 2, '8', '2017-06-14', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 10000000.00, 'CASH/CHECK', 'Rediscounting', 'BANK');
  445.  
  446. INSERT INTO PAYMENT_ORDER_TBL(
  447. payment_order_number, payment_order_date, bank_name, end_user_borrower, ref_approval_status_id, payment_level, or_number, or_date, user_name, receiving_office, bank_level_payment_type, total_payment_amount, payment_type, loan_type)
  448. VALUES('0001', '2017-07-06', 'Rizal Commercial Banking Corporation', 'Kevin Grosskreutz', 5, 'PN', '0001', '2017-07-06', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
  449. ('0002', '2017-05-21', 'Rizal Commercial Banking Corporation', 'Bastian Schweinsteiger', 5, 'PN', '0003', '2017-05-21', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
  450. ('0003', '2017-02-14', 'Rizal Commercial Banking Corporation', 'Marcel Sabitzer', 5, 'PN', '0004', '2017-02-14', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
  451. ('0004', '2017-03-06', 'United Coconut Planters Bank', 'Lionel Messi', 5, 'PN', '0005', '2017-03-06', 'Jane_Maker', 'Cash Dept', '', 3000000.00, 'CASH/CHECK', 'Rediscounting'),
  452. ('0005', '2017-05-21', 'Rizal Commercial Banking Corporation', 'Luis Suarez', 5, 'PN', '0007', '2017-05-21', 'Jane_Maker', 'Cash Dept', '', 3000000.00, 'CASH/CHECK', 'Rediscounting'),
  453. ('0006', '2017-01-14', 'Rizal Commercial Banking Corporation', 'Arturo Vidal', 5, 'PN', '0008', '2017-01-14', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
  454. ('0007', '2017-02-06', 'United Coconut Planters Bank', 'Gareth Bale', 6, 'PN', '0009', '2017-02-06', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
  455. ('0008', '2017-08-30', 'Rizal Commercial Banking Corporation', 'Manuel Neuer', 9, 'PN', '0010', '2017-08-30', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
  456. ('0009', '2017-06-14', 'Rizal Commercial Banking Corporation', 'Keylor Navas', 8,'PN', '0011', '2017-06-14', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting'),
  457. ('0010', '2017-07-14', 'Bank of the Philippine Islands', 'Nyeamm Gaza', 5,'LOAN', '0011', '2017-07-14', 'Jane_Maker', 'Cash Dept', '', 1000000.00, 'CASH/CHECK', 'Rediscounting' ),
  458. ('0011', '2017-07-14', 'Banco De Oro', 'Bill Gates', 5, 'BANK', '0012', '2017-07-14', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 1000000.00, 'CASH/CHECK', 'Rediscounting');
  459.  
  460. INSERT INTO TRANSACTION_TBL(payment_number, payment_order_number, transaction_date, total_payment, year_created, payment_date, transaction_type, status) VALUES
  461. ('2017001234', '1113','2017-07-03', 1000000.00, 2017, '2017-03-22', 'PO', 'POSTED');
  462.  
  463. INSERT INTO TRANSACTION_TBL(payment_number, payment_order_number, transaction_date, total_payment, year_created, payment_date, transaction_type) VALUES
  464. ('2017002345', '1121','2017-07-03', 2000000.00, 2017, '2017-04-20', 'PO'),
  465. ('2017004567', '1042','2017-07-03', 3000000.00, 2017, '2017-06-20', 'PO'),
  466. ('2017001002', '1873', '2017-07-17', 100000.00, 2017, '2017-07-17', 'PO'),
  467. ('2017001003', '2334', '2017-07-17', 100000.00, 2017, '2017-07-17', 'PO'),
  468. ('2017001004', '2001', '2017-07-17', 10000000.00, 2017, '2017-07-17', 'PO'),
  469. ('2017001005', '1423', '2017-07-17', 100000.00, 2017, '2017-07-17', 'PO'),
  470. ('2017002001', '1356', '2017-07-17', 100000.00, 2017, '2017-07-17', 'PO'),
  471. ('2017002002', '5003', '2017-07-16', 100000.00, 2017, '2017-07-16', 'PO'),
  472. ('2017002003', '1010', '2017-07-16', 100000.00, 2017, '2017-07-16', 'PO'),
  473. ('2017002004', '1169', '2017-07-16', 100000.00, 2017, '2017-07-16', 'PO'),
  474. ('2017002005', '6934', '2017-07-16', 10000000.00, 2017, '2017-07-16', 'PO');
  475.  
  476. INSERT INTO TRANSACTION_TBL(pn_number, payment_number, year_created, status) VALUES
  477. (201771, '9991', 2010, 'POSTED'),
  478. (201772, '9992', 2011, 'POSTED'),
  479. (201773, '9993', 2014, 'POSTED');
  480.  
  481. INSERT INTO rediscounting_interest_rates_tbl (effectivity_date, red_interest_rate) VALUES
  482. ('2017-06-01', 0.045);
  483.  
  484.  
  485. UPDATE BALANCE_TBL BAL
  486. SET (BAL.REF_ID, BAL.BALANCE_TYPE) = (
  487. SELECT PN.PN_ID, 'PN'
  488. FROM PN_TBL PN
  489. WHERE PN.REF_BALANCE_ID = BAL.BALANCE_ID
  490. )
  491. WHERE BAL.BALANCE_ID IN (
  492. SELECT PN.REF_BALANCE_ID FROM PN_TBL PN
  493. );
  494.  
  495. UPDATE BALANCE_TBL BAL
  496. SET (BAL.REF_ID, BAL.BALANCE_TYPE) = (
  497. SELECT LOAN.LOAN_ID, 'LOAN'
  498. FROM LOAN_TBL LOAN
  499. WHERE LOAN.REF_BALANCE_ID = BAL.BALANCE_ID
  500. )
  501. WHERE BAL.BALANCE_ID IN (
  502. SELECT LOAN.REF_BALANCE_ID FROM LOAN_TBL LOAN
  503. );
  504.  
  505. UPDATE BALANCE_TBL BAL
  506. SET (BAL.REF_ID, BAL.BALANCE_TYPE) = (
  507. SELECT BANK.BANK_ID, 'BANK'
  508. FROM BANK_TBL BANK
  509. WHERE BANK.REF_BALANCE_ID = BAL.BALANCE_ID
  510. )
  511. WHERE BAL.BALANCE_ID IN (
  512. SELECT BANK.REF_BALANCE_ID FROM BANK_TBL BANK
  513. );
  514.  
  515. INSERT INTO SAP_TBL(sap_number, accrued_interest_payment_date, accrued_interest_payment, unpaid_accrued_interest, pn_number)
  516. VALUES ('9991', '2010-10-08', 15000.00, 1722.22, 201771);
  517.  
  518. 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)
  519. VALUES ('9992', '2011-03-06', 3402.78, '2011-03-06', 25000.00, '2011-03-06', 1000.00, 5805.56, 201772);
  520.  
  521. INSERT INTO SAP_TBL(sap_number, liquidated_damages_payment_date, liquidated_damages_payment, unpaid_liquidated_damages, pn_number)
  522. VALUES ('9993', '2014-10-01', 70000.00, 17500.00, 201773);
  523.  
  524. INSERT INTO SAP_TBL(sap_number, pn_number, liquidated_damages_payment_date)
  525. VALUES ('2017001234', 201712, '2016-05-16');
  526.  
  527.  
  528. INSERT INTO payable_tbl (ref_payment_order_id,promissory_note_number,loan_application_number,
  529. collateral_number,borrower_id,borrower_name,amount_granted,outstanding_principal_balance,
  530. total_outstanding_balance,other_expenses,payment_amount,excess_amount,excess_payment_type, pn_outstanding_balance) VALUES
  531. (8,201744,999992,'TCT-118',18,'Henry Sy',1500000.00,1500000.00,2044400.00,67000.00,4000000.00,1888600.00,'Most Onerous', 0),
  532. (6,NULL,NULL,NULL,NULL,NULL,10000000.00,4000000.00,5232000.00,20000.00,2000000.00,1500000.00,'DLC Claims', 4000000.00),
  533. (12,NULL,NULL,NULL,NULL,NULL,10000000.00,4000000.00,5232000.00,20000.00,3000000.00,2000000.00,'Most Onerous', 4000000.00),
  534. (13,202000,999901,'TCT-126',26,'Gil More',500000.00,225000.00,500000.00,0,2000000.00,1500000.00,'DLC Claims', 0),
  535. (14,202001,999901,'TCT-127',27,'Empoy Incheon',500000.00,200000.00,500000.00,0,1100000.00,600000.00,'Most Onerous', 0),
  536. (15,202002,999901,'TCT-128',28,'Mara Doria',500000.00,225000.00,500000.00,0,1200000.00,700000.00,'DLC Claims', 0),
  537. (16, 202006, 999903, 'TCT-132', 32, 'Eddie Murphy', 700000.00, 350000.00, 700000.00, 0, 2000000.00, 1300000.00, 'Most Onerous', 0),
  538. (16, 202008, 999904, 'TCT-134', 34, 'Bailey Ann', 250000.00, 125000.00, 250000.00, 0, 1000000.00, 750000.00, 'Most Onerous', 0),
  539. (17, 202002, 999901, 'TCT-128', 28, 'Mara Doria', 500000.00, 225000.00, 500000.00, 0, 2000000.00, 1500000.00, 'DLC Claims', 0),
  540. (17, 202003, 999902, 'TCT-129', 29, 'Teddy Quila', 100000.00, 55000.00, 100000.00, 0, 1000000.00, 900000.00, 'DLC Claims', 0),
  541. (22, NULL, 999902, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 10000000, NULL, 'Most Onerous', 0),
  542. (23, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 10000000, NULL, 'Most Onerous', 0);
  543.  
  544. INSERT INTO foreclosure_status_tbl (status_name, status_description)
  545. VALUES
  546. ('New', 'New'),
  547. ('Referred to Lawyer', 'Referred to Lawyer'),
  548. ('Draft Petition for Foreclosure under review', 'Draft Petition for Foreclosure under review'),
  549. ('Draft Petition for Foreclosure forwarded to Lawyer', 'Draft Petition for Foreclosure forwarded to Lawyer'),
  550. ('Petition Filed', 'Petition Filed'),
  551. ('With Notice of Auction', 'With Notice of Auction'),
  552. ('Bid Price Approved', 'Bid Price Approved'),
  553. ('Bid Price Forwarded to Lawyer', 'Bid Price Forwarded to Lawyer'),
  554. ('For Payment of Court''s Commission', 'For Payment of Court''s Commission'),
  555. ('Submitted to RD for COS Annotation', 'Submitted to RD for COS Annotation'),
  556. ('Foreclosure Booked', 'Foreclosure Booked'),
  557. ('Turned Over to Asset Management Department', 'Turned Over to Asset Management Department'),
  558. ('Awaiting Receipt of Winning Bid', 'Awaiting Receipt of Winning Bid'),
  559. ('Winning Bid Received', 'Winning Bid Received'),
  560. ('Released to Winning Bidder', 'Released to Winning Bidder');
  561.  
  562. INSERT INTO LAWYER_TBL (lawyer_name, lawyer_address, vat_registered, remarks)
  563. VALUES ('ABELARDO P. DE JESUS '||chr(38)||' SIEGFRIED A. YBANEZ LAW FIRM', 'Unit 431 Park Avenue Mansion Park Avenue, Pasay City', 1, NULL) ,
  564. ('ATTY. FE BECINA-MACALINO '||chr(38)||' ASSOCIATES', 'Unit 7-L Westgate Tower Condominium Madrigal Business Center Ayala Alabang Muntinlupa City', 1, NULL) ,
  565. ('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) ,
  566. ('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) ,
  567. ('GONZAGA LAW OFFICE', 'Unit 405 Jade Center Building 105 Shaw Boulevard, 1600 Pasig City', 0, NULL) ,
  568. ('AGBAYANI '||chr(38)||' MENDOZA ATTORNEYS-AT-LAW', 'Unit 602 West Insula, 135 West Avenue 1105 Quezon City', 0, NULL) ,
  569. ('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)') ,
  570. ('R.S. REYES LAW OFFICES', 'Unit 406-407 Cornell St., South Point Town Homes Village, Brgy. Merville Leviste Avenue, Paranaque City', 1, NULL) ,
  571. ('TAGAYUNA PANOPIO ESCOBER LAW FIRM', '2nd Floor, AZPHI Bldg., 2709 Ma. Aurora St. corner E. Zobel St., Poblacion, Makati City', 1, NULL) ;
  572.  
  573. INSERT INTO LAWYER_CONTACT_TBL (contact_type, contact, ref_lawyer_id) VALUES
  574. ('EMAIL', 'abeldj_13@yahoo.com', 1),('PHONE', '832-8027', 1),('FAX', '831-9154', 1),
  575. ('EMAIL', 'macalinofe@yahoo.com', 2),('PHONE', '846-9188', 2),('EMAIL', 'legal@bdplaw.com.ph', 3),
  576. ('EMAIL', 'cherry.bernaldo@rsbernaldo.com', 3),('PHONE', '840-0535 to 38', 3),('FAX', '893-3383', 3),
  577. ('EMAIL', 'info@cruzmarcelo.com', 4),('PHONE', '810-5858', 4),('FAX', '810-3838', 4),
  578. ('EMAIL', 'gonzagalawoffice2000@yahoo.com', 5),('PHONE', '667-3479', 5),
  579. ('EMAIL', 'agbayanimendoza@yahoo.com', 6),('PHONE', '664-4778', 6),('PHONE', '783-8354', 6),
  580. ('EMAIL', 'ocmalaw@eastern.com.ph', 7),('PHONE', '819-1624', 7),('PHONE', '5051 to 53', 7),('FAX', '819-2978', 7),
  581. ('EMAIL', 'rsreyes.lawoffices@yahoo.com', 8),('PHONE', '631-3013', 8),('FAX', '631-3013', 8),
  582. ('EMAIL', 'panopio_escober@yahoo.com', 9),('FAX', '846-2014', 9);
  583.  
  584.  
  585. 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
  586. ('1234567', 1, 1, 1, 'James C. Yap', '2017000234', '2017-07-25', 2, 'ONGOING'),
  587. ('3697451', 2, 1, 3, 'Paul Manahan', '2017000235', '2017-07-24', 12, 'ONGOING'),
  588. ('1321546', 3, 3, 4, 'Bryanne Marco', '2017000236', '2017-07-23', 4, 'ONGOING'),
  589. ('1234654', 4, 4, 2, 'Meri Lee', '2017000237', '2017-07-22', 4,'ONGOING'),
  590. ('7897974', NULL, NULL, 1, 'Edward Catala', '2017000238', '2017-07-21', 5,'ONGOING'),
  591. ('1354862', NULL, 5, 3, 'Anjeli Bunoan', '2017000239', '2017-07-20', 9,'ONGOING'),
  592. ('7897975', NULL, NULL, 1, 'Bryanne Catala', '2017000240', '2017-07-21', 1,'ONGOING'),
  593. ('7897977', NULL, 5, 4, 'James Bunoan', '2017000242', '2017-07-20', 7,'ONGOING'),
  594. ('7897978', 5, 5, 4, 'James Marco', '2017000243', '2017-07-21', 10,'ONGOING'), -- TEST DATA FOR LMM-FOR-RC-006. DO NOT CHANGE STATUS
  595. ('7897979', 5, 5, 4, 'James Marco jr', '2017000244', '2017-07-21', 10,'ONGOING'), -- TEST DATA FOR LMM-FOR-RC-006. DO NOT CHANGE STATUS
  596. ('99991987', 1, 1, 1, 'Tiffa Lockheart', '201719998', '2017-07-25', 14, 'ONGOING'), -- TEST DATA FOR LMM-FOR-WB-016. DO NOT CHANGE STATUS
  597. ('99991988', 2, 1, 1, 'Thomas Gilmorety', '201719890', '2017-07-17', 14, 'ONGOING'), -- TEST DATA FOR LMM-FOR-WB-016. DO NOT CHANGE STATUS
  598. ('99991989', 3, 1, 1, 'Yuan Passer', '201716998', '2017-06-25', 14, 'ONGOING'), -- TEST DATA FOR LMM-FOR-WB-016. DO NOT CHANGE STATUS
  599. ('99991911', 1, 1, 3, 'Roberto Sembrano', '201716911', '2017-06-25', 1, 'ONGOING'), -- TEST DATA FOR LMM-FOR-BOF-019.
  600. ('999919122', 2, 2, 3, 'Fhil Man', '2017169122', '2017-06-25', 1, 'ONGOING'), -- TEST DATA FOR LMM-FOR-BOF-019.
  601. ('999919111', 3, 1, 3, 'Langka Sing', '2017169111', '2017-06-25', 1, 'ONGOING'); -- TEST DATA FOR LMM-FOR-BOF-019.
  602.  
  603. 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
  604. ('7897976', NULL, 3, 1, 'Bryanne Catala jr', '2017000341', '2017-07-21', 11,'ONGOING', '2017-08-24', 'Balita Pampanga', 'XIV', '2,3,4'),
  605. ('1234567', NULL, 4, 2, 'Anjeli Bunoan jr', '2017000342', '2017-07-20', 11, 'ONGOING', '2017-08-25', 'Balita Manila', 'XII', '5,6,7');
  606.  
  607. 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)
  608. VALUES
  609. (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),
  610. (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),
  611. (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),
  612. (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),
  613. (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),
  614. (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),
  615. (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),
  616. (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),
  617. (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),
  618. (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),
  619. (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),
  620. (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),
  621. (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.
  622. (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.
  623. (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.
  624. (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.
  625. (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.
  626. (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.
  627. (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.
  628.  
  629. INSERT INTO AUCTION_TBL (ref_foreclosure_id, foreclosure_ref_number, rtc, ejf_number, hbu_property, pwd, fmv, area, tct_number, registered_owner)
  630. VALUES
  631. (1,2017000234,'RTC234','EJF234','Agricultural',1,2000000,10000,'TCT-101','James C. Yap'),
  632. (2,2017000235,'RTC235','EJF235','Residential',1,2800000,2000,'TCT-102','Paul Manahan'),
  633. (3,2017000236,'RTC236','EJF236','Commercial',1,3700000,3000,'TCT-103','Bryanne Marco'),
  634. (4,2017000237,'RTC237','EJF237','Industrial',1,4500000,6000,'TCT-104','Meri Lee'),
  635. (5,2017000238,'RTC238','EJF238','Seaport',1,4100000,5000,'TCT-105','Edward Catala'),
  636. (6,2017000239,'RTC239','EJF239','Airport',1,6200000,12000,'TCT-106','Anjeli Bunoan'),
  637. (7,2017000240,'RTC240','EJF240','Civic',1,1900000,4000,'TCT-107','Bryanne Catala'),
  638. (17,2017000341,'RTC241','EJF241','Military',1,1400000,7000,'TCT-108','Bryanne Catala jr'),
  639. (18,2017000342,'RTC242','EJF242','Landfill',1,1300000,8000,'TCT-109','Anjeli Bunoan jr'),
  640. (8,2017000242,'RTC242','EJF242','Landfill',1,1300000,8000,'TCT-109','James Bunoan'),
  641. (9,2017000243,'RTC242','EJF242','Landfill',1,1300000,8000,'TCT-110','James Bunoan'),
  642. (10,2017000244,'RTC242','EJF242','Landfill',1,1300000,8000,'TCT-111','James Bunoan'),
  643. (11,201719998,'RTC9981','EJF9981','Military',1,2990000,10000,'TCT-141','Tiffa Lockheart'), -- TEST DATA FOR LMM-FOR-WB-016.
  644. (12,201719890,'RTC9934','EJF9934','Agricultural',1,2990000,10000,'TCT-141','Thomas Gilmorety'), -- TEST DATA FOR LMM-FOR-WB-016.
  645. (13,201716998,'RTC9980','EJF9980','Landfill',1,2990000,10000,'TCT-141','Yuan Passer'), -- TEST DATA FOR LMM-FOR-WB-016.
  646. (14,201716911,'RTC998221','EJF998221','Military',1,2990000,10000,'TCT-141','Roberto Sembrano'), -- TEST DATA FOR LMM-FOR-BOF-019.
  647. (15,2017169122,'RTC993422','EJF993422','Agricultural',1,2990000,10000,'TCT-141','Fhil Man'), -- TEST DATA FOR LMM-FOR-BOF-019.
  648. (16,2017169111,'RTC9980222','EJF9980222','Landfill',1,2990000,10000,'TCT-141','Langka Sing'); -- TEST DATA FOR LMM-FOR-BOF-019.
  649.  
  650. 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)
  651. VALUES
  652. ('1',1,2200000,788,19790,250000,2500,2000,330000,1500,1000,3000),
  653. ('2',1,3100000,788,19790,260000,2500,2000,320000,1500,1000,3000),
  654. ('3',1,3800000,788,19790,270000,2500,2000,310000,1500,1000,3000),
  655. ('4',1,4900000,788,19790,280000,2500,2000,300000,1500,1000,3000),
  656. ('5',1,4500000,788,19790,290000,2500,2000,290000,1500,1000,3000),
  657. ('6',1,6700000,788,19790,300000,2500,2000,280000,1500,1000,3000),
  658. ('7',1,2400000,788,19790,310000,2500,2000,270000,1500,1000,3000),
  659. ('8',1,1600000,788,19790,320000,2500,2000,260000,1500,1000,3000),
  660. ('9',1,1500000,788,19790,330000,2500,2000,250000,1500,1000,3000),
  661. ('10',1,1500000,788,19790,330000,2500,2000,250000,1500,1000,3000),
  662. ('11',1,1500000,788,19790,330000,2500,2000,250000,1500,1000,3000),
  663. ('12',1,2330000,788,19790,250000,2500,20090,330000,1500,10900,3067800), -- TEST DATA FOR LMM-FOR-WB-016.
  664. ('13',1,3109900,788,19790,260000,25900,20090,3200900,15900,1000,39000), -- TEST DATA FOR LMM-FOR-WB-016.
  665. ('14',1,3809980,788,19790,2700800,2500,20900,310000,1500,1000,30900); -- TEST DATA FOR LMM-FOR-WB-016.
  666.  
  667. INSERT INTO bidder_details_tbl (bidder_name, amount, ref_auction_id, default_flag, delete_flag)
  668. VALUES
  669. ('BSP', 5000000, 1, 1, 0),
  670. ('PTC', 100, 1, 0, 0),
  671. ('BPI', 7000000, 1, 0, 0),
  672. ('BDO', 4000000, 1, 0, 0),
  673. ('BSP', 45000000, 2, 1, 0),
  674. ('BSP', 5000000, 12, 1, 0),('PSBANK', 10000, 12, 0, 0), -- TEST DATA FOR LMM-FOR-WB-016.
  675. ('BSP', 2000000, 13, 1, 0),('BPI', 3110000, 13, 0, 0), -- TEST DATA FOR LMM-FOR-WB-016.
  676. ('BSP', 9000000, 14, 1, 0),('BDO', 1000000, 14, 0, 0); -- TEST DATA FOR LMM-FOR-WB-016.
  677.  
  678. INSERT INTO winning_bidder_tbl (ref_bidder_id, reason)
  679. VALUES
  680. (1, 'bid not paid');
  681.  
  682. UPDATE AUCTION_TBL
  683. SET ref_bidder_id = 3
  684. WHERE auction_id = 1;
  685. /*Testdata for FSM-PO-SAP-016*/
  686. INSERT INTO PAYMENT_ORDER_TBL(
  687. payment_order_number, payment_order_date, bank_name, end_user_borrower, ref_approval_status_id, or_number, or_date, user_name, receiving_office, bank_level_payment_type, total_payment_amount, payment_type, loan_type, payment_level)
  688. VALUES('2017123456', '2017-07-06', 'United Coconut Planters Bank', 'Dell Mundo', 1, '01234', '2017-08-05', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 80000000.00, 'CASH/CHECK', 'Rediscounting', 'PN');
  689.  
  690. INSERT INTO PAYABLE_TBL (ref_payment_order_id,promissory_note_number,loan_application_number,
  691. collateral_number,borrower_id,borrower_name,amount_granted,outstanding_principal_balance,
  692. total_outstanding_balance,other_expenses,payment_amount,excess_amount,excess_payment_type) VALUES
  693. ((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'),
  694. ((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');
  695.  
  696. INSERT INTO TRANSACTION_TBL(payment_number, payment_order_number, transaction_date, total_payment, year_created, payment_date, transaction_type) VALUES
  697. ('2017123456', '2017123456','2017-07-03', 80000000.00, 2017, '2017-03-22', 'PO');
  698.  
  699. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  700. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  701. 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);
  702.  
  703. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  704. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  705. 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);
  706.  
  707. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  708. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  709. 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);
  710.  
  711. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  712. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  713. 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);
  714.  
  715. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  716. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  717. 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);
  718.  
  719. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  720. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, miscellaneous_income)
  721. VALUES(2017123456, '', 'Excess on Excess', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 77950000.00);
  722.  
  723. /*Testdata for FSM-PO-SAP-010*/
  724. INSERT INTO PAYMENT_ORDER_TBL(
  725. payment_order_number, payment_order_date, bank_name, end_user_borrower, ref_approval_status_id, or_number, or_date, user_name, receiving_office, bank_level_payment_type, total_payment_amount, payment_type, loan_type, payment_level)
  726. VALUES('2017123457', '2017-08-11', 'Metro Bank', 'Pepsi', 1, '01235', '2017-08-11', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 80000000.00, 'CASH/CHECK', 'Rediscounting', 'PN');
  727.  
  728. INSERT INTO PAYABLE_TBL (ref_payment_order_id,promissory_note_number,loan_application_number,
  729. collateral_number,borrower_id,borrower_name,amount_granted,outstanding_principal_balance,
  730. total_outstanding_balance,other_expenses,payment_amount,excess_amount,excess_payment_type) VALUES
  731. ((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');
  732.  
  733. INSERT INTO TRANSACTION_TBL(payment_number, payment_order_number, transaction_date, total_payment, year_created, payment_date, transaction_type) VALUES
  734. ('2017123457', '2017123457','2017-08-11', 40000000.00, 2017, '2017-08-11', 'PO');
  735.  
  736. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  737. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  738. 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);
  739.  
  740. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  741. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  742. 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);
  743.  
  744. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  745. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  746. 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);
  747.  
  748. /*Testdata for FSM-PO-SAP-012*/
  749. INSERT INTO PAYMENT_ORDER_TBL(
  750. payment_order_number, payment_order_date, bank_name, end_user_borrower, ref_approval_status_id, or_number, or_date, user_name, receiving_office, bank_level_payment_type, total_payment_amount, payment_type, loan_type, payment_level)
  751. VALUES('2017080217', '2017-08-11', 'Banco De Oro', 'Coca-cola, John Stith Pemberton', 1, '03210', '2017-08-11', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 2338300.00, 'CASH/CHECK', 'Rediscounting', 'PN');
  752.  
  753. INSERT INTO PAYABLE_TBL (ref_payment_order_id,promissory_note_number,loan_application_number,
  754. collateral_number,borrower_id,borrower_name,amount_granted,outstanding_principal_balance,
  755. total_outstanding_balance,other_expenses,payment_amount,excess_amount,excess_payment_type) VALUES
  756. ((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');
  757.  
  758. INSERT INTO TRANSACTION_TBL(payment_number, payment_order_number, transaction_date, total_payment, year_created, payment_date, transaction_type) VALUES
  759. ('2017876543', '2017080217','2017-08-11', 1000000.00, 2017, '2017-08-11', 'PO');
  760.  
  761. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  762. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  763. 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);
  764.  
  765. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  766. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  767. 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);
  768.  
  769. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  770. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  771. 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);
  772.  
  773. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  774. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  775. 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);
  776.  
  777. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  778. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  779. 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);
  780.  
  781. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  782. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  783. 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);
  784.  
  785. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  786. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  787. 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);
  788.  
  789. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  790. interest_income_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  791. 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);
  792.  
  793. /*Testdata for FSM-PO-SAP-020*/
  794. INSERT INTO PAYMENT_ORDER_TBL(
  795. payment_order_number, payment_order_date, bank_name, end_user_borrower, ref_approval_status_id, or_number, or_date, user_name, receiving_office, bank_level_payment_type, total_payment_amount, payment_type, loan_type, payment_level)
  796. VALUES('2017696969', '2017-08-11', 'Bangkong Luma', 'Juan Dela Cruz', 1, '', '2017-08-11', 'Jane_Maker', 'Cash Dept', 'MOST ONEROUS', 5000000.00, 'CASH/CHECK', 'Rediscounting', 'LOAN');
  797.  
  798. 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,
  799. total_outstanding_balance,other_expenses,payment_amount,excess_amount,excess_payment_type) VALUES
  800. ((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');
  801.  
  802. INSERT INTO TRANSACTION_TBL(payment_number, payment_order_number, transaction_date, total_payment, year_created, payment_date, transaction_type) VALUES
  803. ('2017123436', '2017696969','2017-08-11', 5000000.00, 2017, '2017-08-10', 'PO');
  804.  
  805. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  806. interest_income_payment, principal_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  807. 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);
  808.  
  809. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  810. interest_income_payment, principal_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, payment_date, miscellaneous_income)
  811. 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);
  812.  
  813. INSERT INTO PAYMENT_PENDING_TBL (payment_order_number, pn_number, payment_rule_type, total_payment, others_payment, liquidated_damages_payment, accrued_interest_payment,
  814. interest_income_payment, principal_payment, unpaid_liquidated_damages, unpaid_accrued_interest, unpaid_interest_income, miscellaneous_income)
  815. VALUES (2017696969, '', 'MID', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 4210000.00);
  816.  
  817. INSERT INTO transaction_remarks_tbl(ref_transaction_id, remarks_content) VALUES
  818. (1, 'remarks'),
  819. (2, 'remarks again'),
  820. (3, 'NEWEST REMARKS AGAIN'),
  821. (4, 'remarks'),
  822. (5, 'remarks again'),
  823. (6, 'NEWEST REMARKS AGAIN'),
  824. (7, 'remarks'),
  825. (8, 'remarks again'),
  826. (9, 'NEWEST REMARKS AGAIN'),
  827. (10, 'remarks again'),
  828. (11, 'NEWEST REMARKS AGAIN'),
  829. (12, 'remarks'),
  830. (13, 'remarks'),
  831. (14, 'remarks'),
  832. (15, 'remarks'),
  833. (16, 'remarks'),
  834. (17, 'remarks'),
  835. (18, 'remarks'),
  836. (19, 'remarks');
  837.  
  838. INSERT INTO expenses_tbl(ref_id, expense_type, miscellaneous_assets, accounts_payable)
  839. VALUES (1, 'LOAN', 25000.00, 35000.00),
  840. (1, 'PN', 55000.00, 85000.00),
  841. (2, 'PN', 15000.00, 38000.00),
  842. (3, 'PN', 25000.00, 65000.00),
  843. (4, 'PN', 35000.00, 55000.00),
  844. (5, 'PN', 45000.00, 25000.00),
  845. (1, 'BANK', 55000.00, 45000.00),
  846. (6, 'PN', 65000.00, 38000.00),
  847. (7, 'PN', 75000.00, 32000.00),
  848. (8, 'PN', 85000.00, 30000.00),
  849. (9, 'PN', 35000.00, 10000.00),
  850. (10, 'PN', 85000.00, 30000.00),
  851. (11, 'PN', 87000.00, 30000.00),
  852. (12, 'PN', 85000.00, 30000.00),
  853. (13, 'PN', 82000.00, 30000.00),
  854. (14, 'PN', 83000.00, 30000.00),
  855. (15, 'PN', 85000.00, 30000.00),
  856. (16, 'PN', 84000.00, 30000.00),
  857. (17, 'PN', 81000.00, 30000.00),
  858. (18, 'PN', 20000.00, 30000.00),
  859. (19, 'PN', 83000.00, 10000.00),
  860. (20, 'PN', 15000.00, 40000.00),
  861. (21, 'PN', 25000.00, 40000.00),
  862. (22, 'PN', 83000.00, 40000.00),
  863. (23, 'PN', 84000.00, 40000.00),
  864. (24, 'PN', 85000.00, 40000.00),
  865. (25, 'PN', 86000.00, 20000.00),
  866. (26, 'PN', 65000.00, 30000.00),
  867. (27, 'PN', 15000.00, 55000.00),
  868. (28, 'PN', 25000.00, 45000.00),
  869. (29, 'PN', 35000.00, 47000.00),
  870. (30, 'PN', 45000.00, 48000.00),
  871. (31, 'PN', 55000.00, 49000.00),
  872. (32, 'PN', 85000.00, 30000.00),
  873. (33, 'PN', 85000.00, 20000.00),
  874. (34, 'PN', 85000.00, 10000.00),
  875. (35, 'PN', 85000.00, 90000.00),
  876. (36, 'PN', 85000.00, 80000.00),
  877. (37, 'PN', 85000.00, 70000.00),
  878. (38, 'PN', 85000.00, 60000.00),
  879. (39, 'PN', 85000.00, 40000.00),
  880. (40, 'PN', 22700.00, 12000.00),
  881. (41, 'PN', 23700.00, 22000.00),
  882. (42, 'PN', 25100.00, 32000.00),
  883. (43, 'PN', 25200.00, 42000.00),
  884. (44, 'PN', 25400.00, 52000.00),
  885. (45, 'PN', 25600.00, 62000.00),
  886. (46, 'PN', 25900.00, 72000.00),
  887. (47, 'PN', 85700.00, 82000.00),
  888. (48, 'PN', 75700.00, 92000.00),
  889. (49, 'PN', 55700.00, 61000.00),
  890. (50, 'PN', 45700.00, 63000.00),
  891. (51, 'PN', 15700.00, 64000.00),
  892. (52, 'PN', 22700.00, 65000.00);
  893.  
  894. UPDATE pn_tbl
  895. SET ref_expense_id = 2
  896. WHERE pn_id = 1;
  897.  
  898. UPDATE pn_tbl
  899. SET ref_expense_id = 3
  900. WHERE pn_id = 2;
  901.  
  902. UPDATE pn_tbl
  903. SET ref_expense_id = 4
  904. WHERE pn_id = 3;
  905.  
  906. UPDATE pn_tbl
  907. SET ref_expense_id = 5
  908. WHERE pn_id = 4;
  909.  
  910. UPDATE pn_tbl
  911. SET ref_expense_id = 6
  912. WHERE pn_id = 5;
  913.  
  914. UPDATE pn_tbl
  915. SET ref_expense_id = 8
  916. WHERE pn_id = 6;
  917.  
  918. UPDATE pn_tbl
  919. SET ref_expense_id = 9
  920. WHERE pn_id = 7;
  921.  
  922. UPDATE pn_tbl
  923. SET ref_expense_id = 10
  924. WHERE pn_id = 8;
  925.  
  926. UPDATE pn_tbl
  927. SET ref_expense_id = 11
  928. WHERE pn_id = 9;
  929.  
  930. UPDATE pn_tbl
  931. SET ref_expense_id = 12
  932. WHERE pn_id = 10;
  933.  
  934. UPDATE pn_tbl
  935. SET ref_expense_id = 13
  936. WHERE pn_id = 11;
  937.  
  938. UPDATE pn_tbl
  939. SET ref_expense_id = 14
  940. WHERE pn_id = 12;
  941.  
  942. UPDATE pn_tbl
  943. SET ref_expense_id = 15
  944. WHERE pn_id = 13;
  945.  
  946. UPDATE pn_tbl
  947. SET ref_expense_id = 16
  948. WHERE pn_id = 14;
  949.  
  950. UPDATE pn_tbl
  951. SET ref_expense_id = 17
  952. WHERE pn_id = 15;
  953.  
  954. UPDATE pn_tbl
  955. SET ref_expense_id = 18
  956. WHERE pn_id = 16;
  957.  
  958. UPDATE pn_tbl
  959. SET ref_expense_id = 19
  960. WHERE pn_id = 17;
  961.  
  962. UPDATE pn_tbl
  963. SET ref_expense_id = 20
  964. WHERE pn_id = 18;
  965.  
  966. UPDATE pn_tbl
  967. SET ref_expense_id = 21
  968. WHERE pn_id = 19;
  969.  
  970. UPDATE pn_tbl
  971. SET ref_expense_id = 22
  972. WHERE pn_id = 20;
  973.  
  974. UPDATE pn_tbl
  975. SET ref_expense_id = 23
  976. WHERE pn_id = 21;
  977.  
  978. UPDATE pn_tbl
  979. SET ref_expense_id = 24
  980. WHERE pn_id = 22;
  981.  
  982. UPDATE pn_tbl
  983. SET ref_expense_id = 25
  984. WHERE pn_id = 23;
  985.  
  986. UPDATE pn_tbl
  987. SET ref_expense_id = 26
  988. WHERE pn_id = 24;
  989.  
  990. UPDATE pn_tbl
  991. SET ref_expense_id = 27
  992. WHERE pn_id = 25;
  993.  
  994. UPDATE pn_tbl
  995. SET ref_expense_id = 28
  996. WHERE pn_id = 26;
  997.  
  998. UPDATE pn_tbl
  999. SET ref_expense_id = 29
  1000. WHERE pn_id = 27;
  1001.  
  1002. UPDATE pn_tbl
  1003. SET ref_expense_id = 30
  1004. WHERE pn_id = 28;
  1005.  
  1006. UPDATE pn_tbl
  1007. SET ref_expense_id = 31
  1008. WHERE pn_id = 29;
  1009.  
  1010. UPDATE pn_tbl
  1011. SET ref_expense_id = 32
  1012. WHERE pn_id = 30;
  1013.  
  1014. UPDATE pn_tbl
  1015. SET ref_expense_id = 33
  1016. WHERE pn_id = 31;
  1017.  
  1018. UPDATE pn_tbl
  1019. SET ref_expense_id = 34
  1020. WHERE pn_id = 32;
  1021.  
  1022. UPDATE pn_tbl
  1023. SET ref_expense_id = 35
  1024. WHERE pn_id = 33;
  1025.  
  1026. UPDATE pn_tbl
  1027. SET ref_expense_id = 36
  1028. WHERE pn_id = 34;
  1029.  
  1030. UPDATE pn_tbl
  1031. SET ref_expense_id = 37
  1032. WHERE pn_id = 35;
  1033.  
  1034. UPDATE pn_tbl
  1035. SET ref_expense_id = 38
  1036. WHERE pn_id = 36;
  1037.  
  1038. UPDATE pn_tbl
  1039. SET ref_expense_id = 39
  1040. WHERE pn_id = 37;
  1041.  
  1042. UPDATE pn_tbl
  1043. SET ref_expense_id = 40
  1044. WHERE pn_id = 38;
  1045.  
  1046. UPDATE pn_tbl
  1047. SET ref_expense_id = 41
  1048. WHERE pn_id = 39;
  1049.  
  1050. UPDATE pn_tbl
  1051. SET ref_expense_id = 42
  1052. WHERE pn_id = 40;
  1053.  
  1054. UPDATE pn_tbl
  1055. SET ref_expense_id = 43
  1056. WHERE pn_id = 41;
  1057.  
  1058. UPDATE pn_tbl
  1059. SET ref_expense_id = 44
  1060. WHERE pn_id = 42;
  1061.  
  1062. UPDATE pn_tbl
  1063. SET ref_expense_id = 45
  1064. WHERE pn_id = 43;
  1065.  
  1066. UPDATE pn_tbl
  1067. SET ref_expense_id = 46
  1068. WHERE pn_id = 44;
  1069.  
  1070. UPDATE pn_tbl
  1071. SET ref_expense_id = 47
  1072. WHERE pn_id = 45;
  1073.  
  1074. UPDATE pn_tbl
  1075. SET ref_expense_id = 48
  1076. WHERE pn_id = 46;
  1077.  
  1078. UPDATE pn_tbl
  1079. SET ref_expense_id = 49
  1080. WHERE pn_id = 47;
  1081.  
  1082. UPDATE pn_tbl
  1083. SET ref_expense_id = 50
  1084. WHERE pn_id = 48;
  1085.  
  1086. UPDATE pn_tbl
  1087. SET ref_expense_id = 51
  1088. WHERE pn_id = 49;
  1089.  
  1090. UPDATE pn_tbl
  1091. SET ref_expense_id = 52
  1092. WHERE pn_id = 50;
  1093.  
  1094. UPDATE pn_tbl
  1095. SET ref_expense_id = 53
  1096. WHERE pn_id = 51;
  1097.  
  1098. UPDATE pn_tbl
  1099. SET ref_expense_id = 54
  1100. WHERE pn_id = 52;
  1101.  
  1102. INSERT INTO collateral_documents_tbl(foreclosure_ref_number, document_name, document_contents, updated_by, updated_on, save_flag, print_flag)
  1103. VALUES
  1104. ('2017000239','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
  1105. ('2017000234','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
  1106. ('2017000235','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 0, 1),
  1107. ('2017000236','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 0, 1),
  1108.  
  1109. ('2017000237','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
  1110. ('2017000238','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
  1111. ('2017169111','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 0, 1),
  1112. ('2017000240','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
  1113.  
  1114. ('2017000242','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
  1115. ('2017000243','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 0),
  1116. ('2017000244','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 0),
  1117. ('201719998','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
  1118.  
  1119. ('201719890','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 1),
  1120. ('201716998','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 1, 0),
  1121. ('201716911','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 0, 1),
  1122. ('2017169122','Birth Certificate','Birth Certificate','Jane_Maker','2017-08-08 13:58:34', 0, 0);
  1123.  
  1124.  
  1125. --added lawyer in some PNs
  1126. UPDATE pn_tbl
  1127. SET ref_lawyer_id = 8
  1128. WHERE pn_number = 201722;
  1129.  
  1130. UPDATE pn_tbl
  1131. SET ref_lawyer_id = 1
  1132. WHERE pn_number = 202016;
  1133.  
  1134. UPDATE pn_tbl
  1135. SET ref_lawyer_id = 4
  1136. WHERE pn_number = 202006;
  1137.  
  1138. UPDATE pn_tbl
  1139. SET ref_lawyer_id = 2
  1140. WHERE pn_number = 201735;
  1141.  
  1142. UPDATE pn_tbl
  1143. SET ref_lawyer_id = 5
  1144. WHERE pn_number = 201751;
  1145.  
  1146. UPDATE pn_tbl
  1147. SET ref_lawyer_id = 7
  1148. WHERE pn_number = 201772;
  1149.  
  1150. UPDATE pn_tbl
  1151. SET ref_lawyer_id = 3
  1152. WHERE pn_number = 202002;
  1153.  
  1154. UPDATE pn_tbl
  1155. SET ref_lawyer_id = 6
  1156. WHERE pn_number = 201791;
  1157.  
  1158. UPDATE pn_tbl
  1159. SET ref_lawyer_id = 6
  1160. WHERE pn_number = 201773;
  1161.  
  1162. INSERT INTO demand_letter(date_on_demand_letter, demand_letter_date, ref_pn_id) VALUES
  1163. ('2017-01-22', '2016-12-30', 25),
  1164. ('2017-02-22', '2017-01-01', 25),
  1165. ('2017-08-30', '2017-03-14', 25),
  1166. ('2017-10-22', '2017-01-14', 29),
  1167. ('2017-02-22', '2017-01-14', 31),
  1168. ('2017-02-22', '2017-02-14', 34),
  1169. ('2017-03-22', '2017-04-14', 48),
  1170. ('2017-04-22', '2017-05-14', 30),
  1171. ('2017-05-22', '2017-06-14', 53),
  1172. ('2017-06-22', '2017-07-14', 54),
  1173. ('2017-07-22', '2017-08-14', 55);
  1174.  
  1175. INSERT INTO PRE_FORECLOSURE(ref_pn_id, pre_foreclosure_status, transaction_date, active_flag, excluded_false, excluded_date) values
  1176. (7, 'New', '2017-07-25', 1, 0, '2017-07-25'),
  1177. (8, 'New', '2017-07-31', 1, 0, '2017-07-31'),
  1178. (9, 'Referred to Lawyer', '2017-07-28', 1, 0, '2017-07-28'),
  1179. (10, 'New', '2017-08-10', 1, 0, '2017-08-10'),
  1180. (11, 'New', '2017-08-01', 1, 0, '2017-08-01'),
  1181. (12, 'New', '2017-08-05', 1, 0, '2017-08-05'),
  1182. (13, 'Referred to Lawyer', '2017-07-05', 0, 1, '2017-07-05'),
  1183. (14, 'New', '2017-08-22', 0, 1, '2017-08-22'),
  1184. (15, 'New', '2017-08-29', 0, 1, '2017-08-29'),
  1185. (16, 'New', '2017-07-15', 0, 1, '2017-07-15');
  1186.  
  1187. INSERT INTO remarks_tbl(remarks_group, remarks_group_ref_id, remarks_content, created_date) values
  1188. ('preforeclosure', 7, 'Current', '2017-08-29'),
  1189. ('preforeclosure', 9, 'Current', '2017-08-29'),
  1190. ('preforeclosure', 10, 'Current', '2017-08-29');
  1191.  
  1192. INSERT INTO batch_tbl(job_name,cron_trigger_name, cron_expression) VALUES
  1193. ('movePnsToPreForeclosureJob', 'movePnsToPreForeclosureCronTrigger', '0 0 9,16 ? * *'),
  1194. ('moveCurrentPnsToPreForeclosureJob', 'moveCurrentPnsToPreForeclosureCronTrigger', '0 0 9,16 ? * *');
  1195.  
  1196. INSERT INTO pn_collateral(ref_pn_id, ref_collateral_id) VALUES
  1197. (1,1),
  1198. (2,2),
  1199. (3,3),
  1200. (4,4),
  1201. (5,5),
  1202. (6,6),
  1203. (7,7),
  1204. (8,8),
  1205. (9,9),
  1206. (10,10),
  1207. (11,11),
  1208. (12,12),
  1209. (13,13),
  1210. (14,14),
  1211. (15,15),
  1212. (16,16),
  1213. (17,17),
  1214. (18,18),
  1215. (25,19),
  1216. (26,20),
  1217. (27,21),
  1218. (28,22),
  1219. (29,23),
  1220. (30,24),
  1221. (31,25),
  1222. (32,26),
  1223. (33,27),
  1224. (34,28),
  1225. (35,29),
  1226. (36,30),
  1227. (37,31),
  1228. (38,32),
  1229. (39,33),
  1230. (40,34),
  1231. (41,35),
  1232. (42,36),
  1233. (43,37),
  1234. (44,38),
  1235. (45,39),
  1236. (46,40),
  1237. (47,41),
  1238. (48,42),
  1239. (49,43),
  1240. (50,44),
  1241. (51,45),
  1242. (52,46),
  1243. (53,47),
  1244. (54,47),
  1245. (55,47);
  1246.  
  1247. INSERT INTO expense_tbl (expense_type, or_number, or_date, ref_pn_id) VALUES
  1248. ('Filing Fee', '2098932 C', '2017-07-15', 39),
  1249. ('Courts Commission', '5788532', '2018-02-12', 39),
  1250. ('COS Annotation', '1011382990', '2019-03-21', 39),
  1251. ('Sheriffs Fee', '2324567 C', '2016-12-20', 39),
  1252. ('Filing Fee', '2098932 A', '2017-07-15', 4),
  1253. ('Courts Commission', '3288531', '2018-02-12', 4),
  1254. ('COS Annotation', '3011382910', '2019-03-21', 4),
  1255. ('Sheriffs Fee', '2324567 A', '2016-12-20', 4);
  1256.  
  1257. INSERT INTO appraisal_report (report_type, report_date, appraiser_name, ref_collateral_id) VALUES
  1258. ('DLC Appraisal Report', '2016-08-10', 'Nico Robin', 40),
  1259. ('MO Appraisal Report', '2016-08-11', 'Roronoa Zoro', 4);
  1260.  
  1261. UPDATE pn_tbl
  1262. SET ref_lawyer_id = 4,
  1263. rem_amount = 100000
  1264. WHERE pn_id = 46;
  1265.  
  1266. UPDATE pn_tbl
  1267. SET ref_lawyer_id = 3,
  1268. rem_amount = 300000
  1269. WHERE pn_id = 4;
  1270.  
  1271. UPDATE auction_tbl
  1272. SET date_filed = '2016-12-25',
  1273. sheriff_clerk_name = 'Boa Hancock'
  1274. WHERE auction_id = 8;
  1275.  
  1276. UPDATE auction_tbl
  1277. SET date_filed = '2016-12-26',
  1278. sheriff_clerk_name = 'Charlotte Pudding'
  1279. WHERE auction_id = 9;
  1280.  
  1281. /*FOR SINGLE PN, MULTIPLE COLLATERAL LMM-DAC-MC-003*/
  1282. UPDATE collateral_tbl
  1283. SET collateral_value = 2500000,
  1284. collateral_classification = 'MC',
  1285. rem_amount = 500000,
  1286. registered_owner = 'Archie Andrews',
  1287. area = 28,
  1288. unit_measurement = 'hectares'
  1289. WHERE collateral_id = 19;
  1290.  
  1291. UPDATE PN_COLLATERAL
  1292. SET collateral_priority = 4
  1293. WHERE pn_collateral_id = 19;
  1294.  
  1295. INSERT INTO collateral_tbl(collateral_number, collateral_classification, ref_rd_id, address, collateral_value, rem_amount, registered_owner, area, unit_measurement , appraisal_date, fmv) VALUES
  1296. ('TCT-148', 'MC', 9, 'Ayala Triangle Walkways, Makati, Metro Manila', 1000000, 500000, 'Betty Cooper', 1000, 'sqm', '2017-05-04', 2300000.00),
  1297. ('TCT-149', 'MC', 10, '1101 Commonwealth Ave, Diliman, Quezon City, Philippines', 1500000, 300000, 'Jughead Jones', 25, 'hectares', '2016-02-04', 1800000.00),
  1298. ('TCT-150', 'MC', 3, 'Microtel U.P Ayala Land Technohub, Commonwealth Ave, Diliman, Quezon City', 1800000, 200000, 'Veronica Lodge', 3000, 'sqm', '2016-07-04', 5200000.00);
  1299.  
  1300. INSERT INTO pn_collateral(ref_pn_id, ref_collateral_id, collateral_priority) VALUES
  1301. (25,48, 1),
  1302. (25,49, 2),
  1303. (25,50, 3);
  1304.  
  1305. INSERT INTO appraisal_report (report_type, report_date, appraiser_name, ref_collateral_id) VALUES
  1306. ('EMC Appraisal Report', '2016-02-10', 'Harry James Potter', 48),
  1307. ('EMC Appraisal Report', '2016-02-11', 'Hermione Granger', 49),
  1308. ('EMC Appraisal Report', '2016-02-12', 'Ron Bilius Weasley', 50),
  1309. ('EMC Appraisal Report', '2016-02-13', 'Luna Lovegood', 19);
  1310.  
  1311. INSERT INTO tax_declaration_tbl (tax_declaration_number, tax_declaration_type, classification_type, amount, ref_collateral_id, ref_appraisal_id, tax_receipt) VALUES
  1312. ('TAX-0501', 'Land', 'Residential', 10000, 48, 3, null),
  1313. ('TAX-0502', 'Improvement', 'Residential', 5000, 48, 3, null),
  1314. ('TAX-0722', 'Land', 'Residential', 15000, 49, 4, null),
  1315. ('TAX-0723', 'Land', 'Residential', 12000, 50, 5, null),
  1316. ('TAX-0719', 'Land', 'Residential', 28000, 19, 6, '123456'),
  1317. ('TAX-0735', 'Land', 'Residential', 38000, 40, 1, '221321'),
  1318. ('TAX-0736', 'Land', 'Residential', 18000, 4, 2, '331321');
  1319.  
  1320. INSERT INTO insurance_details_tbl (policy_number, insurance_company_name, insurance_or_number, expiry_date, insured_amount, ref_collateral_id, ref_appraisal_id) VALUES
  1321. ('POL-1106', 'Sun Life Financial', '123', '2030-01-01', 20000000, 48, 3),
  1322. ('POL-1107', 'Philam Life Insurance', '234', '2035-12-31', 10000000, 49, 4),
  1323. ('POL-1108', 'Stronghold Insurance Company', '567', '2050-02-28', 500000000, 50, 5),
  1324. ('POL-1109', 'Life and Health Insurance', '890', '2028-01-30', 50000000, 19, 6),
  1325. ('POL-1110', 'Stronghold Insurance Company', '568', '2028-12-30', 30000000, 40, 1),
  1326. ('POL-1111', 'Philam Life Insurance', '566', '2028-02-28', 20000000, 4, 2);
  1327.  
  1328. UPDATE pn_tbl
  1329. SET penalty_rate = 0.3,
  1330. rem_amount = 1500000
  1331. WHERE pn_id = 25;
  1332.  
  1333. /*NEW*/
  1334. UPDATE collateral_tbl
  1335. SET collateral_value = 1100000,
  1336. collateral_classification = 'MC',
  1337. rem_amount = 100000,
  1338. registered_owner = 'Kou Mabuchi',
  1339. area = 11,
  1340. unit_measurement = 'hectares'
  1341. WHERE collateral_id = 20;
  1342.  
  1343. UPDATE collateral_tbl
  1344. SET collateral_value = 1200000,
  1345. collateral_classification = 'MC',
  1346. rem_amount = 200000,
  1347. registered_owner = 'Alphonse Elric',
  1348. area = 12,
  1349. unit_measurement = 'hectares'
  1350. WHERE collateral_id = 21;
  1351.  
  1352. UPDATE pn_tbl
  1353. SET penalty_rate = 0.1,
  1354. rem_amount = 100000
  1355. WHERE pn_id = 26;
  1356.  
  1357. UPDATE pn_tbl
  1358. SET penalty_rate = 0.2,
  1359. rem_amount = 200000
  1360. WHERE pn_id = 27;
  1361.  
  1362. INSERT INTO pn_tbl ( pn_number, ref_loan_id, ref_balance_id, ref_borrower_id, delete_flag) VALUES
  1363. ('202024', 25, 93, 34, 2);
  1364.  
  1365. INSERT INTO appraisal_report (report_type, report_date, appraiser_name, ref_collateral_id) VALUES
  1366. ('EMC Appraisal Report', '2011-01-01', 'Yoshioka Futaba', 20),
  1367. ('EMC Appraisal Report', '2012-02-02', 'Edward Elric', 21);
  1368.  
  1369. INSERT INTO tax_declaration_tbl (tax_declaration_number, tax_declaration_type, classification_type, amount, ref_collateral_id, ref_appraisal_id, tax_receipt) VALUES
  1370. ('TAX-0511', 'Land', 'Residential', 11000, 20, 7, '123450'),
  1371. ('TAX-0522', 'Land', 'Residential', 22000, 21, 8, '987654');
  1372.  
  1373. INSERT INTO insurance_details_tbl (policy_number, insurance_company_name, insurance_or_number, expiry_date, insured_amount, ref_collateral_id, ref_appraisal_id) VALUES
  1374. ('POL-1111', 'Sun Life Financial', '111', '2021-11-11', 11000000, 20, 7),
  1375. ('POL-1122', 'Philam Life Insurance', '222', '2022-12-12', 22000000, 21, 8);
  1376.  
  1377. INSERT INTO dacion_transaction_tbl (transaction_number, dts_number, transaction_date) VALUES
  1378. ('2017000001', '1111111', '2017-09-08');
  1379.  
  1380. INSERT INTO dacion_tbl (dacion_type, udoa_number, dacion_value, ref_pn_id, ref_dacion_transaction_id) VALUES
  1381. ('Encumbered MC', 'UDOA-LOAN-B-1111', 500000, 1, 1), --Temporarily removed manual insertion of dacion_id
  1382. ('', 'UDOA-LOAN-B-2222', 500000, 25, null),
  1383. ('', 'UDOA-LOAN-B-3333', 2500000, 27, null),
  1384. ('', 'UDOA-LOAN-B-4444', 2600000, 4, null),
  1385. ('ENROPA', 'UDOA-LOAN-B-1453', 600000, 31, null),
  1386. ('ENROPA', 'UDOA-LOAN-B-1349', 200000, 56, null);
  1387.  
  1388. UPDATE end_user_borrower_tbl
  1389. SET address = '7114 Kundiman Street, Sampaloc 1008 Manila',
  1390. tin = '999-999-999-9999'
  1391. WHERE borrower_id = 19;
  1392.  
  1393. UPDATE end_user_borrower_tbl
  1394. SET address = '95 JayLee Street, Sofia Subdivision Del Pilar, San Fernando City',
  1395. tin = '888-888-888-8888'
  1396. WHERE borrower_id = 20;
  1397.  
  1398. UPDATE end_user_borrower_tbl
  1399. SET address = '75 P. Domingo Street Carmona, Makati City',
  1400. tin = '777-777-777-7777'
  1401. WHERE borrower_id = 21;
  1402.  
  1403. INSERT INTO rem_tbl (rem_name, rem_amount) VALUES
  1404. ('REM 1', 100000),
  1405. ('REM 2', 200000);
  1406.  
  1407. INSERT INTO rem_dacion_tbl (ref_rem_id, ref_dacion_id) VALUES
  1408. (1, 1),
  1409. (2, 2);
  1410.  
  1411. /*ENCUMBERED ROPA*/
  1412. UPDATE collateral_tbl
  1413. SET collateral_value = 2500000,
  1414. collateral_classification = 'ROPA',
  1415. rem_amount = 500000,
  1416. registered_owner = 'Bastian Schweinsteiger',
  1417. area = 28,
  1418. unit_measurement = 'hectares'
  1419. WHERE collateral_id = 25;
  1420.  
  1421. UPDATE end_user_borrower_tbl
  1422. SET tin = '999-999-969-6969', address = 'Manuel L. Quezon Corner Carigma Street, Antipolo City, Rizal 1870'
  1423. WHERE borrower_id = '25';
  1424.  
  1425. INSERT INTO appraisal_report (report_type, report_date, appraiser_name, ref_collateral_id) VALUES
  1426. ('ERO Appraisal Report', '2016-02-10', 'Kevin Grosskreutz', 25);
  1427.  
  1428. INSERT INTO tax_declaration_tbl (tax_declaration_number, tax_declaration_type, classification_type, amount, ref_collateral_id, ref_appraisal_id, tax_receipt) VALUES
  1429. ('TAX-0514', 'Land', 'Residential', 10000, 25, 9, '123456'),
  1430. ('TAX-0515', 'Improvement', 'Residential', 5000, 25, 9, '789012');
  1431.  
  1432. INSERT INTO insurance_details_tbl (policy_number, insurance_company_name, insurance_or_number, expiry_date, insured_amount, ref_collateral_id, ref_appraisal_id) VALUES
  1433. ('POL-1106', 'Sun Life Financial', '123', '2030-01-01', 20000000, 25, 9);
  1434.  
  1435. UPDATE pn_tbl
  1436. SET penalty_rate = 0.3,
  1437. rem_amount = 1500000
  1438. WHERE pn_id = 31;
  1439.  
  1440. INSERT INTO rem_tbl (rem_name, rem_amount) VALUES
  1441. ('REM 9', 400000);
  1442.  
  1443. INSERT INTO rem_dacion_tbl (ref_rem_id, ref_dacion_id) VALUES
  1444. (3, 5);
  1445.  
  1446. INSERT INTO remarks_tbl(remarks_group_ref_id, remarks_group, remarks_content) VALUES
  1447. ('5','dacion', 'this is remark for encumbered ropa');
  1448.  
  1449. UPDATE collateral_tbl
  1450. SET collateral_value = 2500000,
  1451. collateral_classification = 'ROPA',
  1452. rem_amount = 500000,
  1453. registered_owner = 'Adriel Ortiz',
  1454. area = 28,
  1455. unit_measurement = 'hectares'
  1456. WHERE collateral_id = 21;
  1457.  
  1458. INSERT INTO tax_declaration_tbl (tax_declaration_number, tax_declaration_type, classification_type, amount, ref_collateral_id, ref_appraisal_id, tax_receipt) VALUES
  1459. ('TAX-0805', 'Land', 'Residential', 10000, 21, 9, '345212'),
  1460. ('TAX-0806', 'Improvement', 'Residential', 5000, 21, 9, '243121');
  1461.  
  1462. INSERT INTO insurance_details_tbl (policy_number, insurance_company_name, insurance_or_number, expiry_date, insured_amount, ref_collateral_id, ref_appraisal_id) VALUES
  1463. ('POL-0805', 'Sun Life Financial', '123', '2030-01-01', 20000000, 21, 9);
  1464.  
  1465. INSERT INTO appraisal_report (report_type, report_date, appraiser_name, ref_collateral_id) VALUES
  1466. ('ERO Appraisal Report', '2016-02-10', 'Kevin Grosskreutz', 21);
  1467.  
  1468. INSERT INTO pn_collateral(ref_pn_id, ref_collateral_id) VALUES
  1469. (56,21);
  1470.  
  1471. INSERT INTO foreclosure_expenses_tbl (expense_type, particular, expense_amount, dv_date, dv_number, dv_amount, date_credited, ref_foreclosure_id) VALUES
  1472. ('Filing Fees', 'JDF', 15000.00, '1990-09-10', 'ABC123', 15000.00, '2017-09-10', 13),
  1473. ('Filing Fees', 'SAJ', 15000.00, '1990-09-10', 'ABC123', 15000.00, '2017-09-10', 13),
  1474. ('Publication Fees', 'Mitzy''s Publication', 15000.00, '1990-09-10', 'ABC123', 15000.00, '2017-09-10', 13),
  1475. ('Sheriff''s Fees', 'Mandaluyong Regional Trial Court', 15000.00, '1990-09-10', 'ABC123', 15000.00, '2017-09-10', 13),
  1476. ('Court''s Commission', 'JDF', 15000.00, '1990-09-10', 'ABC123', 15000.00, '2017-09-10', 13),
  1477. ('Court''s Commission', 'SAJ', 15000.00, '1990-09-10', 'ABC123', 15000.00, '2017-09-10', 13),
  1478. ('Registration of COS', 'Registry of Deeds, Mandaluyong Branch', 15000.00, '1990-09-10', 'ABC123', 15000.00, '2017-09-10', 13),
  1479. ('Registration of COS', 'IT Service Fee', 15000.00, '1990-09-10', 'ABC123', 15000.00, '2017-09-10', 13),
  1480. ('Attorney''s Fees', 'Foreclosure Fee', 15000.00, '1990-09-10', 'ABC123', 15000.00, '2017-09-10', 13),
  1481. ('Filing Fees', 'JDF', 151515.00, '2012-07-15', 'ABC123', 1515.00, '2014-04-01', 14),
  1482. ('Filing Fees', 'SAJ', 151515.00, '2012-07-15', 'ABC123', 1515.00, '2014-04-01', 14),
  1483. ('Publication Fees', 'Mitzy''s Publication', 151515.00, '2012-07-15', 'ABC123', 1515.00, '2014-04-01', 14),
  1484. ('Sheriff''s Fees', 'Mandaluyong Regional Trial Court', 151515.00, '2012-07-15', 'ABC123', 1515.00, '2014-04-01', 14),
  1485. ('Court''s Commission', 'JDF', 151515.00, '2012-07-15', 'ABC123', 1515.00, '2014-04-01', 14),
  1486. ('Court''s Commission', 'SAJ', 151515.00, '2012-07-15', 'ABC123', 1515.00, '2014-04-01', 14),
  1487. ('Registration of COS', 'Registry of Deeds, Mandaluyong Branch', 151515.00, '2012-07-15', 'ABC123', 1515.00, '2014-04-01', 14),
  1488. ('Registration of COS', 'IT Service Fee', 151515.00, '2012-07-15', 'ABC123', 1515.00, '2014-04-01', 14),
  1489. ('Attorney''s Fees', 'Foreclosure Fee', 151515.00, '2012-07-15', 'ABC123', 1515.00, '2014-04-01', 14),
  1490. ('Filing Fees', 'JDF', 379000.00, '1962-04-19', 'ABC123', 250000.00, '1974-08-20', 15),
  1491. ('Filing Fees', 'SAJ', 379000.00, '1962-04-19', 'ABC123', 250000.00, '1974-08-20', 15),
  1492. ('Publication Fees', 'Mitzy''s Publication', 379000.00, '1962-04-19', 'ABC123', 250000.00, '1974-08-20', 15),
  1493. ('Sheriff''s Fees', 'Mandaluyong Regional Trial Court', 379000.00, '1962-04-19', 'ABC123', 250000.00, '1974-08-20', 15),
  1494. ('Court''s Commission', 'JDF', 379000.00, '1962-04-19', 'ABC123', 250000.00, '1974-08-20', 15),
  1495. ('Court''s Commission', 'SAJ', 379000.00, '1962-04-19', 'ABC123', 250000.00, '1974-08-20', 15),
  1496. ('Registration of COS', 'Registry of Deeds, Mandaluyong Branch', 379000.00, '1962-04-19', 'ABC123', 250000.00, '1974-08-20', 15),
  1497. ('Registration of COS', 'IT Service Fee', 379000.00, '1962-04-19', 'ABC123', 250000.00, '1974-08-20', 15),
  1498. ('Attorney''s Fees', 'Foreclosure Fee', 379000.00, '1962-04-19', 'ABC123', 250000.00, '1974-08-20', 15),
  1499. ('Others', 'Best Data', 379000.00, '1962-04-19', 'ABC123', 250000.00, '1974-08-20', 15),
  1500. ('Others', 'Sample Information', 379000.00, '1962-04-19', 'ABC123', 250000.00, '1974-08-20', 15);
  1501.  
  1502.  
  1503. /* Testdata for CMN-SVC-APV-010-002 */
  1504. INSERT INTO PAYMENT_ORDER_TBL (PAYMENT_ORDER_NUMBER, PAYMENT_ORDER_DATE, REF_APPROVAL_STATUS_ID, PAYMENT_LEVEL, PAYMENT_TYPE, BANK_NAME, LOAN_TYPE, AMOUNT, RECEIVING_OFFICE, PARTICULARS, DTS_NUMBER, PAYOR, TOTAL_CASH_AMOUNT, TOTAL_CHECK_AMOUNT, TOTAL_PMO_AMOUNT, TOTAL_AMOUNT, TOTAL_PAYMENT_AMOUNT, PMO_AMOUNT, CHART_ACCOUNT_TITLE, CHART_ACCOUNT_CODE, LAST_UPDATED_DATE, USER_NAME, REF_USER_ID, DELETE_FLAG, YEAR_CREATED, BANK_LEVEL_PAYMENT_TYPE)
  1505. VALUES (2017007771, '2017-09-19', 4, 'PN', 'CASH/CHECK', 'Philippine Business Bank', 'Rediscounting', 0.00, '200 CASH DEPARTMENT', 'particular', 'dts1', 'payor', 295000.00, 0.00, 0.00, 295000.00, 295000.00, 0.00, 'DEPARTMENT OF LOANS AND CREDIT', '172500000061000000', '2017-09-19 00:00:00', 'Jane_Maker', 1, 0, 2017, ''),
  1506. (2017007772, '2017-09-19', 4, 'LOAN', 'CASH/CHECK', 'Metro Bank', 'Rediscounting' , 0.00, '200 CASH DEPARTMENT', 'particular', 'dts', 'pay', 15000000.00, 0.00, 0.00, 15000000.00, 15000000.00, 0.00,'DEPARTMENT OF LOANS AND CREDIT', '172500000061000000', '2017-09-19 00:00:00', 'Jane_Maker', 1, 0, 2017, ''),
  1507. (2017007773, '2017-09-19', 4, 'BANK', 'CASH/CHECK', 'Cooperative Bank of SDMD', 'Rediscounting', 0.00, '200 CASH DEPARTMENT', 'part', 'dts1', 'pay', 16000000.00, 0.00, 0.00, 16000000.00, 16000000.00, 0.00, 'DEPARTMENT OF LOANS AND CREDIT', '172500000061000000', '2017-09-19 00:00:00', 'Jane_Maker', 1, 0, 2017, 'DLC CLAIMS');
  1508.  
  1509. INSERT INTO PAYABLE_TBL (REF_PAYMENT_ORDER_ID, PROMISSORY_NOTE_NUMBER, LOAN_APPLICATION_NUMBER, COLLATERAL_NUMBER, BORROWER_NAME, AMOUNT_GRANTED, PN_OUTSTANDING_BALANCE, OUTSTANDING_PRINCIPAL_BALANCE, TOTAL_OUTSTANDING_BALANCE, OTHER_EXPENSES, PAYMENT_AMOUNT, EXCESS_AMOUNT, EXCESS_PAYMENT_TYPE) VALUES
  1510. (28, '202020', '999998', 'TCT-146', 'Guiness, Guiness Record', 300000.00, 165000.00, 0.00, 300000.00, 0.00, 295000.00, 0.00, ''),
  1511. (29, '', '678912', '', '', 2800000.00, 2800000.00, 0.00, 4337700.00, 0.00, 15000000.00, 10662300.00, 'DLC Claims'),
  1512. (30, '', '', '', '', 6800000.00, 6800000.00, 0.00, 9569700.00 ,null, 16000000.00, 6430300.00, '');
  1513.  
  1514. INSERT INTO PAYMENT_TBL (PAYMENT_TYPE, PAYMENT_AMOUNT, DELETE_FLAG, REF_PAYMENT_ORDER_ID)
  1515. VALUES ('CASH', 295000.00, 0, 28),
  1516. ('CASH', 15000000.00, 0, 29),
  1517. ('CASH', 16000000.00, 0, 30);
  1518.  
  1519. 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)
  1520. VALUES
  1521. (2017007771, '202020', 'PN', 295000.00, 0.00, 30000.00, 60000.00, 45000.00, 160000.00, 0.00, 0.00, 0.00, '2017-09-19'),
  1522. (2017007772, '201724', 'LOAN', 2293300.00, null, 390000.00, 550000.00, 53300.00, 1300000.00, 0.00, 0.00, 0.00, '2017-09-19'),
  1523. (2017007772, '201729', 'LOAN', 2044400.00, null, 300000.00, 190000.00, 54400.00, 1500000.00, 0.00, 0.00, 0.00, '2017-09-19'),
  1524. (2017007772, null, 'BANKDLC', 3444000.00, null, 200000.00, 360000.00, 84000.00, 2800000.00, null, null, null, '2017-09-19'),
  1525. (2017007772, null, 'MID', null, null, null, null, null, 7218300.00, null, null, null, '2017-09-19'),
  1526. (2017007773, null, 'BANKDLC', 9569700.00, null, 1030000.00, 1510000.00, 229700.00, 6800000.00, null, null, null, '2017-09-19'),
  1527. (2017007773, null, 'MID', null, null, null, null, null, 6430300.00, null, null, null, '2017-09-19');
  1528.  
  1529. INSERT INTO TRANSACTION_TBL (TRANSACTION_DATE, PAYMENT_DATE, PAYMENT_ORDER_NUMBER, PAYMENT_NUMBER, TOTAL_PAYMENT, DELETE_FLAG, YEAR_CREATED, TRANSACTION_TYPE)
  1530. VALUES ('2017-09-19', '2017-09-19' ,2017007771, 2017008881, 295000.00, 0, 2017, 'PO'),
  1531. ('2017-09-19', '2017-09-19', 2017007772, 2017008882, 15000000.00, 0, 2017,'PO'),
  1532. ('2017-09-19', '2017-09-19', 2017007773, 2017008883, 16000000.00, 0, 2017,'PO');
  1533.  
  1534. /** RECEIVING OFFICES / INITIATING OFFICES **/
  1535. INSERT INTO office_tbl (office_name, office_type, npo, po) VALUES
  1536. ('930 CASG-CLOSED BANKS/CONTINGENT ACCOUNTS','Initiating Office','YES',''),
  1537. ('730 CASG-COMMERCIAL BANKS','Initiating Office','YES',''),
  1538. ('750 CASG-RURAL BANKS-REGULAR','Initiating Office','YES',''),
  1539. ('830 CASG-THRIFT BANKS/NBFIS/FX BANKS','Initiating Office','YES',''),
  1540. ('200 CASH DEPARTMENT','Receiving Office','','YES'),
  1541. ('610 DEPARTMENT OF LOANS AND CREDIT','*NOt included','',''),
  1542. ('217 EO-SAN FERNANDO','Receiving Office','','YES'),
  1543. ('100 FINANCIAL ACCOUNTING DEPARTMENT','Initiating Office','YES',''),
  1544. ('900 PAYMENTS AND SETTLEMENTS OFFICE','Initiating Office','YES',''),
  1545. ('307 RCU-CAGAYAN DE ORO','Receiving Office','','YES'),
  1546. ('203 RO-CEBU','Receiving Office','','YES'),
  1547. ('206 RO-DAVAO','Receiving Office','','YES'),
  1548. ('210 RO-LA UNION','Receiving Office','','YES'),
  1549. ('215 RU-BACOLOD','Receiving Office','','YES'),
  1550. ('219 RU-BATAC','Receiving Office','','YES'),
  1551. ('227 RU-BUTUAN','Receiving Office','','YES'),
  1552. ('201 RU-CABANATUAN','Receiving Office','','YES'),
  1553. ('202 RU-CAGAYAN DE ORO','Receiving Office','','YES'),
  1554. ('204 RU-COTABATO','Receiving Office','','YES'),
  1555. ('205 RU-DAGUPAN','Receiving Office','','YES'),
  1556. ('207 RU-DUMAGUETE','Receiving Office','','YES'),
  1557. ('220 RU-GENERAL SANTOS','Receiving Office','','YES'),
  1558. ('208 RU-ILOILO','Receiving Office','','YES'),
  1559. ('211 RU-LEGAZPI','Receiving Office','','YES'),
  1560. ('212 RU-LUCENA','Receiving Office','','YES'),
  1561. ('216 RU-NAGA','Receiving Office','','YES'),
  1562. ('221 RU-OZAMIS','Receiving Office','','YES'),
  1563. ('209 RU-ROXAS','Receiving Office','','YES'),
  1564. ('213 RU-TACLOBAN','Receiving Office','','YES'),
  1565. ('218 RU-TUGUEGARAO','Receiving Office','','YES'),
  1566. ('214 RU-ZAMBOANGA','Receiving Office','','YES'),
  1567. ('131 TREASURY-DOMESTIC OPERATIONS','Initiating Office','YES',''),
  1568. ('132 TREASURY-INTERNATIONAL OPERATIONS','Initiating Office','YES','');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!