API tools faq
paste
Advertisement
Guest User

insertScrpitLMM

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