API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 26th, 2017
98
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 30.53 KB | None | 0 0
raw download clone embed print report
  1. package gov.bsp.lcmis.payment.rules.constants;
  2.  
  3. public class Constants {
  4. // PN QUERIES
  5. public static final String QUERY_GET_PN_PAYABLE_DETAILS =
  6. "SELECT PN.PN_ID, PN.PN_NUMBER, PN.PN_STATUS, PN.REF_LOAN_ID, PN.PN_INTEREST_RATE, PN.MATURITY_DATE, "
  7. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, "
  8. + "BAL.BALANCE_ID, BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  9. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE,"
  10. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS,BAL.MISCELLANEOUS_INCOME_OTHERS "
  11. + "FROM PN_TBL PN "
  12. + "JOIN BALANCE_TBL BAL "
  13. + "ON PN.REF_BALANCE_ID = BAL.BALANCE_ID "
  14. + "WHERE PN.DELETE_FLAG = 0";
  15.  
  16. public static final String QUERY_GET_PN_PAYABLE_DETAILS_BY_PN_ID =
  17. "SELECT PN.PN_ID, PN.PN_NUMBER, PN.PN_STATUS, PN.REF_LOAN_ID, PN.PN_INTEREST_RATE, PN.MATURITY_DATE, "
  18. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, BAL.OTHERS, "
  19. + "BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  20. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE, "
  21. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS,BAL.MISCELLANEOUS_INCOME_OTHERS "
  22. + "FROM PN_TBL PN "
  23. + "JOIN BALANCE_TBL BAL "
  24. + "ON PN.REF_BALANCE_ID = BAL.BALANCE_ID "
  25. + "WHERE PN.PN_ID = :PN_ID "
  26. + "AND PN.DELETE_FLAG = 0";
  27.  
  28. public static final String QUERY_GET_PN_PAYABLE_DETAILS_BY_PN_NUMBER =
  29. "SELECT PN.PN_ID, PN.PN_NUMBER, PN.PN_STATUS, PN.REF_LOAN_ID, PN.PN_INTEREST_RATE, PN.MATURITY_DATE, "
  30. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, "
  31. + "BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  32. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE, "
  33. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS,BAL.MISCELLANEOUS_INCOME_OTHERS "
  34. + "FROM PN_TBL PN "
  35. + "JOIN BALANCE_TBL BAL "
  36. + "ON PN.REF_BALANCE_ID = BAL.BALANCE_ID "
  37. + "WHERE PN.PN_NUMBER = :PN_NUMBER "
  38. + "AND PN.DELETE_FLAG = 0";
  39.  
  40. public static final String QUERY_GET_PN_PAYABLE_DETAILS_BY_LOAN_ID =
  41. "SELECT PN.PN_ID, PN.PN_NUMBER, PN.PN_STATUS, PN.REF_LOAN_ID, PN.PN_INTEREST_RATE, PN.MATURITY_DATE, "
  42. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, "
  43. + "BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  44. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE, "
  45. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS,BAL.MISCELLANEOUS_INCOME_OTHERS "
  46. + "FROM PN_TBL PN "
  47. + "JOIN BALANCE_TBL BAL "
  48. + "ON PN.REF_BALANCE_ID = BAL.BALANCE_ID "
  49. + "WHERE PN.REF_LOAN_ID = :LOAN_ID "
  50. + "AND PN.DELETE_FLAG = 0";
  51.  
  52. public static final String QUERY_GET_PN_PAYABLE_DETAILS_BY_PN_NUMBER_LIST =
  53. "SELECT PN.PN_ID, PN.PN_NUMBER, PN.PN_STATUS, PN.REF_LOAN_ID, PN.PN_INTEREST_RATE, PN.MATURITY_DATE, "
  54. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, "
  55. + "BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  56. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE ,"
  57. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS,BAL.MISCELLANEOUS_INCOME_OTHERS "
  58. + "FROM PN_TBL PN "
  59. + "JOIN BALANCE_TBL BAL "
  60. + "ON PN.REF_BALANCE_ID = BAL.BALANCE_ID "
  61. + "WHERE PN.PN_NUMBER IN (:PN_NUMBER) "
  62. + "AND PN.DELETE_FLAG = 0";
  63.  
  64. public static final String QUERY_GET_PN_PAYABLE_DETAILS_BY_LOAN_APPLICATION_NUMBER =
  65. "SELECT PN.PN_ID, PN.PN_NUMBER, PN.PN_STATUS, PN.REF_LOAN_ID, PN.PN_INTEREST_RATE, PN.MATURITY_DATE, "
  66. + "BAL.BALANCE_ID, BAL.BALANCE_TYPE, BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  67. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE, BAL.BALANCE_TYPE, BAL.REF_ID, "
  68. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS,BAL.MISCELLANEOUS_INCOME_OTHERS "
  69. + "FROM PN_TBL PN "
  70. + "JOIN BALANCE_TBL BAL "
  71. + "ON PN.REF_BALANCE_ID = BAL.BALANCE_ID "
  72. + "JOIN LOAN_TBL LOAN "
  73. + "ON LOAN.LOAN_ID = PN.REF_LOAN_ID "
  74. + "WHERE LOAN.LOAN_APPLICATION_NUMBER = :LOAN_APPLICATION_NUMBER "
  75. + "AND PN.DELETE_FLAG = 0";
  76.  
  77. public static final String UPDATE_PN_REF_BALANCE = "UPDATE PN_TBL SET REF_BALANCE_ID = :REF_BALANCE_ID WHERE PN_ID = :PN_ID";
  78. // END - PN QUERIES
  79.  
  80. // LOAN QUERIES
  81. public static final String QUERY_GET_LOAN_PAYABLE_DETAILS =
  82. "SELECT LOAN.LOAN_ID, LOAN.LOAN_APPLICATION_NUMBER, LOAN.AMOUNT_GRANTED, LOAN.REF_BANK_ID, LOAN.LOAN_INTEREST_RATE, LOAN.MATURITY_DATE, "
  83. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, "
  84. + "BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  85. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE ,"
  86. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS, BAL.MISCELLANEOUS_INCOME_OTHERS "
  87. + "FROM LOAN_TBL LOAN "
  88. + "JOIN BALANCE_TBL BAL "
  89. + "ON LOAN.REF_BALANCE_ID = BAL.BALANCE_ID "
  90. + "AND LOAN.DELETE_FLAG = 0";
  91.  
  92. public static final String QUERY_GET_LOAN_PAYABLE_DETAILS_BY_LOAN_ID =
  93. "SELECT LOAN.LOAN_ID, LOAN.LOAN_APPLICATION_NUMBER, LOAN.AMOUNT_GRANTED, LOAN.REF_BANK_ID, LOAN.LOAN_INTEREST_RATE, LOAN.MATURITY_DATE, "
  94. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, "
  95. + "BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  96. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE,"
  97. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS, BAL.MISCELLANEOUS_INCOME_OTHERS "
  98. + "FROM LOAN_TBL LOAN "
  99. + "JOIN BALANCE_TBL BAL "
  100. + "ON LOAN.REF_BALANCE_ID = BAL.BALANCE_ID "
  101. + "WHERE LOAN.LOAN_ID = :LOAN_ID "
  102. + "AND LOAN.DELETE_FLAG = 0";
  103.  
  104. public static final String QUERY_GET_LOAN_PAYABLE_DETAILS_BY_LOAN_APPLICATION_NUMBER =
  105. "SELECT LOAN.LOAN_ID, LOAN.LOAN_APPLICATION_NUMBER, LOAN.AMOUNT_GRANTED, LOAN.REF_BANK_ID,LOAN.LOAN_INTEREST_RATE, LOAN.MATURITY_DATE, "
  106. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, "
  107. + "BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  108. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE ,"
  109. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS, BAL.MISCELLANEOUS_INCOME_OTHERS "
  110. + "FROM LOAN_TBL LOAN "
  111. + "JOIN BALANCE_TBL BAL "
  112. + "ON LOAN.REF_BALANCE_ID = BAL.BALANCE_ID "
  113. + "WHERE LOAN.LOAN_APPLICATION_NUMBER = :LOAN_APPLICATION_NUMBER "
  114. + "AND LOAN.DELETE_FLAG = 0";
  115.  
  116. public static final String QUERY_GET_LOAN_PAYABLE_DETAILS_BANK_ID =
  117. "SELECT LOAN.LOAN_ID, LOAN.LOAN_APPLICATION_NUMBER, LOAN.AMOUNT_GRANTED, LOAN.REF_BANK_ID, LOAN.LOAN_INTEREST_RATE, LOAN.MATURITY_DATE, "
  118. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, BAL.OTHERS, "
  119. + "BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  120. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE ,"
  121. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS, BAL.MISCELLANEOUS_INCOME_OTHERS "
  122. + "FROM LOAN_TBL LOAN "
  123. + "JOIN BALANCE_TBL BAL "
  124. + "ON LOAN.REF_BALANCE_ID = BAL.BALANCE_ID "
  125. + "WHERE LOAN.REF_BANK_ID = :BANK_ID "
  126. + "AND LOAN.DELETE_FLAG = 0";
  127.  
  128. public static final String QUERY_GET_LOAN_PAYABLE_DETAILS_BANK_NAME =
  129. "SELECT LOAN.LOAN_ID, LOAN.LOAN_APPLICATION_NUMBER, LOAN.AMOUNT_GRANTED, LOAN.REF_BANK_ID, LOAN.LOAN_INTEREST_RATE, LOAN.MATURITY_DATE, "
  130. + "BAL.BALANCE_ID, BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  131. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE, BAL.BALANCE_TYPE, BAL.REF_ID ,"
  132. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS, BAL.MISCELLANEOUS_INCOME_OTHERS "
  133. + "FROM LOAN_TBL LOAN "
  134. + "JOIN BALANCE_TBL BAL "
  135. + "ON LOAN.REF_BALANCE_ID = BAL.BALANCE_ID "
  136. + "JOIN BANK_TBL BANK "
  137. + "ON LOAN.REF_BANK_ID = BANK.BANK_ID "
  138. + "WHERE BANK.BANK_NAME = :BANK_NAME AND LOAN.LOAN_TYPE = :LOAN_TYPE "
  139. + "AND LOAN.DELETE_FLAG = 0";
  140.  
  141. public static final String UPDATE_LOAN_REF_BALANCE =
  142. "UPDATE LOAN_TBL SET REF_BALANCE_ID = :REF_BALANCE_ID WHERE LOAN_ID = :LOAN_ID";
  143. // END - LOAN QUERIES
  144.  
  145. // BANK QUERIES
  146. public static final String QUERY_GET_BANK_PAYABLE_DETAILS =
  147. "SELECT BANK.BANK_ID, BANK.BANK_CODE, BANK.BANK_NAME, BANK.BANK_TYPE, "
  148. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, "
  149. + "BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  150. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE, "
  151. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS,BAL.MISCELLANEOUS_INCOME_OTHERS "
  152. + "FROM BANK_TBL BANK "
  153. + "JOIN BALANCE_TBL BAL "
  154. + "ON BANK.REF_BALANCE_ID = BAL.BALANCE_ID "
  155. + "AND BANK.DELETE_FLAG = 0";
  156.  
  157. public static final String QUERY_GET_BANK_PAYABLE_DETAILS_BY_BANK_ID =
  158. "SELECT BANK.BANK_ID, BANK.BANK_CODE, BANK.BANK_NAME, BANK.BANK_TYPE, "
  159. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, "
  160. + "BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  161. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE , "
  162. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS,BAL.MISCELLANEOUS_INCOME_OTHERS "
  163. + "FROM BANK_TBL BANK "
  164. + "JOIN BALANCE_TBL BAL "
  165. + "ON BANK.REF_BALANCE_ID = BAL.BALANCE_ID "
  166. + "WHERE BANK.BANK_ID = :BANK_ID "
  167. + "AND BANK.DELETE_FLAG = 0";
  168.  
  169. public static final String QUERY_GET_BANK_PAYABLE_DETAILS_BY_BANK_CODE =
  170. "SELECT BANK.BANK_ID, BANK.BANK_CODE, BANK.BANK_NAME, BANK.BANK_TYPE, "
  171. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, "
  172. + "BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  173. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE , "
  174. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS,BAL.MISCELLANEOUS_INCOME_OTHERS "
  175. + "FROM BANK_TBL BANK "
  176. + "JOIN BALANCE_TBL BAL "
  177. + "ON BANK.REF_BALANCE_ID = BAL.BALANCE_ID "
  178. + "WHERE BANK.BANK_CODE = :BANK_CODE "
  179. + "AND BANK.DELETE_FLAG = 0";
  180.  
  181. public static final String QUERY_GET_BANK_PAYABLE_DETAILS_BY_BANK_NAME =
  182. "SELECT BANK.BANK_ID, BANK.BANK_CODE, BANK.BANK_NAME, BANK.BANK_TYPE,"
  183. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, "
  184. + "BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  185. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE , "
  186. + "BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS,BAL.MISCELLANEOUS_INCOME_OTHERS "
  187. + "FROM BANK_TBL BANK "
  188. + "JOIN BALANCE_TBL BAL "
  189. + "ON BANK.REF_BALANCE_ID = BAL.BALANCE_ID "
  190. + "WHERE BANK.BANK_NAME = :BANK_NAME "
  191. + "AND BANK.DELETE_FLAG = 0";
  192.  
  193. public static final String UPDATE_BANK_REF_BALANCE =
  194. "UPDATE BANK_TBL SET REF_BALANCE_ID = :REF_BALANCE_ID WHERE BANK_ID = :BANK_ID";
  195.  
  196. // QUERY FOR RETRIEVE_BANK_BY_BANK_NAME_AND_LOAN_TYPE
  197. public static final String QUERY_RETRIEVE_BANK_BY_BANK_NAME_AND_LOAN_TYPE =
  198. "SELECT BANK.BANK_ID, BANK.BANK_CODE, BANK.BANK_NAME, BANK.BANK_TYPE, BAL.OTHERS, BANK.BANK_STATUS,"
  199. + " sum(BAL.LIQUIDATED_DAMAGES) as LIQUIDATED_DAMAGES, sum(BAL.ACCRUED_INTEREST_RECEIVABLE)"
  200. + " as ACCRUED_INTEREST_RECEIVABLE,sum(BAL.INTEREST_INCOME) as INTEREST_INCOME, sum(BAL.OUTSTANDING_PRINCIPAL)"
  201. + " as OUTSTANDING_PRINCIPAL, sum(BAL.TOTAL_OUTSTANDING_BALANCE) as TOTAL_OUTSTANDING_BALANCE"
  202. + " FROM BALANCE_TBL BAL LEFT JOIN LOAN_TBL LOAN ON BAL.balance_id = LOAN.ref_balance_id"
  203. + " LEFT JOIN BANK_TBL BANK ON BANK.bank_id = LOAN.ref_bank_id"
  204. + " WHERE BANK.BANK_ID = ? and LOAN.LOAN_TYPE = ? and LOAN.DELETE_FLAG = 0"
  205. + " GROUP BY BANK.BANK_ID, BANK.BANK_CODE, BANK.BANK_NAME, BANK.BANK_TYPE, BAL.OTHERS, BANK.BANK_STATUS";
  206.  
  207. // QUERY FOR UPDATE_BANK_BY_BANK_ID
  208. public static final String QUERY_UPDATE_BANK_BY_BANK_ID = "UPDATE bank_tbl SET ref_balance_id = ? WHERE bank_id = ?";
  209.  
  210. // END - BANK QUERIES
  211.  
  212. // BALANCE QUERIES
  213.  
  214. // QUERY FOR INSERT_BALANCE
  215. public static final String QUERY_INSERT_BALANCE =
  216. "INSERT INTO balance_tbl (ref_id, balance_type, others, accrued_interest_receivable, liquidated_damages, interest_income,"
  217. + " outstanding_principal, total_outstanding_balance, delete_flag) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
  218.  
  219. public static final String QUERY_UPDATE_BALANCE =
  220. "INSERT INTO BALANCE_TBL (REF_ID, BALANCE_TYPE, OTHERS, LIQUIDATED_DAMAGES, ACCRUED_INTEREST_RECEIVABLE, INTEREST_INCOME, OUTSTANDING_PRINCIPAL, TOTAL_OUTSTANDING_BALANCE) "
  221. + "VALUES (:REF_ID, :BALANCE_TYPE, :OTHERS, :LIQUIDATED_DAMAGES, :ACCRUED_INTEREST_RECEIVABLE, :INTEREST_INCOME, :OUTSTANDING_PRINCIPAL, :TOTAL_OUTSTANDING_BALANCE)";
  222.  
  223. public static final String QUERY_COMPUTE_TOTAL_LOAN_BALANCE_BY_LOAN_ID = "SELECT " + "SUM(BAL.OTHERS) AS OTHERS, "
  224. + "SUM(BAL.LIQUIDATED_DAMAGES) AS LIQUIDATED_DAMAGES, "
  225. + "SUM(BAL.INTEREST_INCOME) AS INTEREST_INCOME, "
  226. + "SUM(BAL.ACCRUED_INTEREST_RECEIVABLE) AS ACCRUED_INTEREST_RECEIVABLE, "
  227. + "SUM(BAL.OUTSTANDING_PRINCIPAL) AS OUTSTANDING_PRINCIPAL, "
  228. + "SUM(BAL.TOTAL_OUTSTANDING_BALANCE) AS TOTAL_OUTSTANDING_BALANCE "
  229. + "FROM BALANCE_TBL AS BAL "
  230. + "JOIN PN_TBL AS PN "
  231. + "ON BAL.BALANCE_ID = PN.REF_BALANCE_ID "
  232. + "WHERE PN.REF_LOAN_ID = :LOAN_ID "
  233. + "AND PN.DELETE_FLAG = 0 "
  234. + "AND BAL.DELETE_FLAG = 0";
  235.  
  236. public static final String QUERY_COMPUTE_TOTAL_BANK_BALANCE_BY_BANK_ID = "SELECT "
  237. + "SUM(BAL.OTHERS) AS OTHERS, "
  238. + "SUM(BAL.LIQUIDATED_DAMAGES) AS LIQUIDATED_DAMAGES, "
  239. + "SUM(BAL.INTEREST_INCOME) AS INTEREST_INCOME, "
  240. + "SUM(BAL.ACCRUED_INTEREST_RECEIVABLE) AS ACCRUED_INTEREST_RECEIVABLE, "
  241. + "SUM(BAL.OUTSTANDING_PRINCIPAL) AS OUTSTANDING_PRINCIPAL, "
  242. + "SUM(BAL.TOTAL_OUTSTANDING_BALANCE) AS TOTAL_OUTSTANDING_BALANCE "
  243. + "FROM BALANCE_TBL AS BAL "
  244. + "JOIN LOAN_TBL AS LOAN "
  245. + "ON LOAN.REF_BALANCE_ID = BAL.BALANCE_ID "
  246. + "WHERE LOAN.REF_BANK_ID = :BANK_ID "
  247. + "AND LOAN.DELETE_FLAG = 0 "
  248. + "AND BAL.DELETE_FLAG = 0";
  249.  
  250. // END - BALANCE QUERIES
  251.  
  252. // PAYABLE QUERIES
  253. public static final String QUERY_GET_PAYABLE_BY_ID = "SELECT "
  254. + "PAYABLE.PAYABLE_ID, PAYABLE.LOAN_APPLICATION_NUMBER, PAYABLE.PROMISSORY_NOTE_NUMBER, PAYABLE.PAYMENT_AMOUNT, PAYABLE.REF_PAYMENT_ORDER_ID "
  255. + "FROM PAYABLE_TBL PAYABLE "
  256. + "WHERE PAYABLE.PAYABLE_ID = :PAYABLE_ID";
  257.  
  258. public static final String QUERY_GET_PAYABLE_BY_PAYMENT_ORDER_ID = "SELECT "
  259. + "PAYABLE.PAYABLE_ID, PAYABLE.LOAN_APPLICATION_NUMBER, PAYABLE.PROMISSORY_NOTE_NUMBER, PAYABLE.PAYMENT_AMOUNT, PAYABLE.REF_PAYMENT_ORDER_ID, "
  260. + "PAYABLE.EXCESS_PAYMENT_TYPE, PAYABLE.EXCESS_AMOUNT "
  261. + "FROM PAYABLE_TBL PAYABLE "
  262. + "WHERE REF_PAYMENT_ORDER_ID = :PAYMENT_ORDER_ID";
  263.  
  264. public static final String QUERY_GET_PAYABLE_BY_PAYMENT_ORDER_STATUS = "SELECT "
  265. + "PAYABLE.PAYABLE_ID, PAYABLE.LOAN_APPLICATION_NUMBER, PAYABLE.PROMISSORY_NOTE_NUMBER, PAYABLE.PAYMENT_AMOUNT, PAYABLE.REF_PAYMENT_ORDER_ID, PO.BANK_NAME, PO.LOAN_TYPE "
  266. + "FROM PAYABLE_TBL PAYABLE "
  267. + "JOIN PAYMENT_ORDER_TBL PO "
  268. + "ON PO.PAYMENT_ORDER_ID = PAYABLE.REF_PAYMENT_ORDER_ID "
  269. + "WHERE PO.PAYMENT_ORDER_STATUS = :PAYMENT_ORDER_STATUS";
  270.  
  271. public static final String QUERY_GET_PAYABLE_FOR_PAYMENT_RULE_PN_LEVEL = "SELECT "
  272. + "PAYABLE.PAYABLE_ID, PAYABLE.LOAN_APPLICATION_NUMBER, PAYABLE.PROMISSORY_NOTE_NUMBER, PAYABLE.PAYMENT_AMOUNT, PAYABLE.REF_PAYMENT_ORDER_ID, "
  273. + "PN.PN_ID, PN.PN_NUMBER, PN.PN_STATUS, PN.REF_LOAN_ID, PN.PN_INTEREST_RATE, PN.MATURITY_DATE, "
  274. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, "
  275. + "BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  276. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE, PO.BANK_NAME, PO.LOAN_TYPE, "
  277. + "PAYABLE.EXCESS_PAYMENT_TYPE "
  278. + "FROM PAYABLE_TBL PAYABLE "
  279. + "JOIN PAYMENT_ORDER_TBL PO "
  280. + "ON PO.PAYMENT_ORDER_ID = PAYABLE.REF_PAYMENT_ORDER_ID "
  281. + "JOIN PN_TBL PN "
  282. + "ON PN.PN_NUMBER = PAYABLE.PROMISSORY_NOTE_NUMBER "
  283. + "JOIN BALANCE_TBL BAL "
  284. + "ON BAL.BALANCE_ID = PN.REF_BALANCE_ID "
  285. + "WHERE PO.PAYMENT_ORDER_STATUS = 'Approved' "
  286. + "AND PO.OR_NUMBER IS NOT NULL "
  287. + "AND PO.DELETE_FLAG = 0 "
  288. + "AND PN.DELETE_FLAG = 0 "
  289. + "AND BAL.DELETE_FLAG = 0";
  290.  
  291. public static final String QUERY_GET_PAYABLE_FOR_PAYMENT_RULE_ALL_LEVELS = "SELECT "
  292. + "PAYABLE.PAYABLE_ID, PAYABLE.LOAN_APPLICATION_NUMBER, PAYABLE.PROMISSORY_NOTE_NUMBER, PAYABLE.PAYMENT_AMOUNT, PAYABLE.REF_PAYMENT_ORDER_ID, "
  293. + "PN.PN_ID, PN.PN_NUMBER, PN.PN_STATUS, PN.REF_LOAN_ID, PN.PN_INTEREST_RATE, PN.MATURITY_DATE, "
  294. + "BAL.REF_ID, BAL.BALANCE_TYPE, BAL.CREATED_DATE, BAL.BALANCE_ID, "
  295. + "BAL.OTHERS, BAL.LIQUIDATED_DAMAGES, BAL.ACCRUED_INTEREST_RECEIVABLE, "
  296. + "BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL, BAL.TOTAL_OUTSTANDING_BALANCE, PO.BANK_NAME, PO.LOAN_TYPE, "
  297. + "PAYABLE.EXCESS_PAYMENT_TYPE "
  298. + "FROM PAYABLE_TBL PAYABLE "
  299. + "JOIN PAYMENT_ORDER_TBL PO "
  300. + "ON PO.PAYMENT_ORDER_ID = PAYABLE.REF_PAYMENT_ORDER_ID "
  301. + "JOIN PN_TBL PN "
  302. + "ON PN.PN_NUMBER = PAYABLE.PROMISSORY_NOTE_NUMBER "
  303. + "JOIN BALANCE_TBL BAL "
  304. + "ON BAL.BALANCE_ID = PN.REF_BALANCE_ID "
  305. + "WHERE PO.PAYMENT_ORDER_STATUS = 'Approved' "
  306. + "AND PO.BANK_LEVEL_PAYMENT_TYPE = :BANK_LEVEL_PAYMENT_TYPE "
  307. + "AND PO.PAYMENT_LEVEL = :PAYMENT_LEVEL "
  308. + "AND PO.OR_NUMBER IS NOT NULL "
  309. + "AND PO.DELETE_FLAG = 0 "
  310. + "AND PN.DELETE_FLAG = 0 "
  311. + "AND BAL.DELETE_FLAG = 0";
  312.  
  313. // END - PAYABLE QUERIES
  314.  
  315. // PAYMENT ORDER QUERIES
  316. public static final String QUERY_GET_PAYMENT_ORDER_BY_STATUS = "SELECT PO.PAYMENT_ORDER_ID, PO.PAYMENT_ORDER_NUMBER, "
  317. + "PO.PAYMENT_ORDER_STATUS, PO.BANK_NAME, PO.OR_NUMBER, PO.LOAN_TYPE "
  318. + "FROM PAYMENT_ORDER_TBL PO "
  319. + "WHERE PO.PAYMENT_ORDER_STATUS = :PAYMENT_ORDER_STATUS "
  320. + "AND PO.DELETE_FLAG = 0";
  321.  
  322. public static final String QUERY_GET_PAYMENT_ORDER_BY_ID =
  323. "SELECT PO.PAYMENT_ORDER_ID, PO.PAYMENT_ORDER_NUMBER, " + "PO.PAYMENT_ORDER_STATUS, PO.BANK_NAME, PO.OR_NUMBER "
  324. + "FROM PAYMENT_ORDER_TBL PO "
  325. + "WHERE PO.PAYMENT_ORDER_ID = :PAYMENT_ORDER_ID "
  326. + "AND PO.DELETE_FLAG = 0";
  327.  
  328. public static final String QUERY_GET_PAYMENT_ORDER_BY_ID_LIST =
  329. "SELECT PO.PAYMENT_ORDER_ID, PO.PAYMENT_ORDER_NUMBER, " + "PO.PAYMENT_ORDER_STATUS, PO.BANK_NAME, PO.OR_NUMBER "
  330. + "FROM PAYMENT_ORDER_TBL PO "
  331. + "WHERE PO.PAYMENT_ORDER_ID IN (:PAYMENT_ORDER_ID) "
  332. + "AND PO.DELETE_FLAG = 0";
  333.  
  334. public static final String QUERY_GET_PAYMENT_ORDER_FOR_PAYMENT_RULES_PN_LEVEL =
  335. "SELECT PO.PAYMENT_ORDER_ID, PO.PAYMENT_ORDER_NUMBER, " + "PO.PAYMENT_ORDER_STATUS, PO.BANK_NAME, PO.OR_NUMBER "
  336. + "FROM PAYMENT_ORDER_TBL PO "
  337. + "WHERE PO.PAYMENT_ORDER_STATUS = 'Approved' "
  338. + "AND PO.OR_NUMBER IS NOT NULL "
  339. + "AND PO.DELETE_FLAG = 0";
  340.  
  341. public static final String QUERY_UPDATE_PAYMENT_ORDER_STATUS = "UPDATE PAYMENT_ORDER_TBL PO "
  342. + "SET PO.PAYMENT_ORDER_STATUS = :PAYMENT_ORDER_STATUS WHERE PO.PAYMENT_ORDER_ID = :PAYMENT_ORDER_ID";
  343.  
  344. public static final String QUERY_UPDATE_PAYMENT_ORDER_STATUS_BY_ID_LIST = "UPDATE PAYMENT_ORDER_TBL PO "
  345. + "SET PO.PAYMENT_ORDER_STATUS = :PAYMENT_ORDER_STATUS WHERE PO.PAYMENT_ORDER_ID IN (:PAYMENT_ORDER_ID)";
  346. // END - PAYMENT ORDER QUERIES
  347.  
  348. //QUERY FOR RETRIEVING BANK LEVEL - LOAN TYPE USING BANK ID
  349. public static final String QUERY_RETRIEVE_BANK_LEVEL_LOAN_TYPE_USING_BANK_ID =
  350. "SELECT BANK.BANK_ID, BANK.BANK_CODE, BANK.BANK_NAME, BANK.BANK_TYPE, BAL.OTHERS,"
  351. + " BANK.REF_BALANCE_ID as BALANCE_ID, BALANCE_TYPE, REF_ID, BAL.LIQUIDATED_DAMAGES,"
  352. + " BAL.ACCRUED_INTEREST_RECEIVABLE, BAL.INTEREST_INCOME, BAL.OUTSTANDING_PRINCIPAL,"
  353. + " BAL.TOTAL_OUTSTANDING_BALANCE, BAL.MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS,BAL.MISCELLANEOUS_INCOME_OTHERS"
  354. + " FROM BALANCE_TBL BAL"
  355. + " LEFT JOIN BANK_TBL BANK ON BAL.balance_id = BANK.ref_balance_id"
  356. + " WHERE BANK.BANK_ID = :BANK_ID and BAL.BALANCE_ID = (SELECT REF_BALANCE_ID FROM BANK_TBL WHERE BANK_ID = :BANK_ID)";
  357. //END - QUERY FOR RETRIEVING BANK LEVEL - LOAN TYPE USING BANK ID
  358.  
  359. //QUERY FOR RETRIEVING BALANCE OF A PN
  360. public static final String QUERY_RETRIEVE_BALANCE_OF_A_PN =
  361. "SELECT bal.accrued_interest_receivable," + " bal.liquidated_damages, bal.interest_income, bal.outstanding_principal,"
  362. + " bal.total_outstanding_balance FROM pn_tbl pn"
  363. + " LEFT JOIN balance_tbl bal ON pn.ref_balance_id = bal.balance_id"
  364. + " WHERE pn_number = ?";
  365. //END - QUERY FOR RETRIEVING BALANCE OF A PN
  366.  
  367. //QUERY FOR INSERT IN BALANCE TABLE
  368. public static final String QUERY_FOR_INSERT_BALANCE_TBL = "SELECT BALANCE_ID FROM NEW TABLE (INSERT INTO BALANCE_TBL"
  369. + " (ACCRUED_INTEREST_RECEIVABLE, LIQUIDATED_DAMAGES, INTEREST_INCOME, OUTSTANDING_PRINCIPAL,"
  370. + " TOTAL_OUTSTANDING_BALANCE, REF_ID, BALANCE_TYPE, CREATED_DATE)"
  371. + " VALUES (?, ?, ?, ?, ?, ?, ?, ?))";
  372. //END - QUERY FOR INSERT IN BALANCE TABLE
  373.  
  374. //UPDATE QUERY FOR BANK TBL'S BALANCE_ID
  375. public static final String QUERY_FOR_UPDATING_BALANCE_ID_BANK_TBL = "UPDATE BANK_TBL SET REF_BALANCE_ID = ? WHERE BANK_ID = ?";
  376. //END
  377.  
  378. //FOR BANK MISCELLANEOUS INCOME
  379. public static final String QUERY_UPDATE_MISC_INCOME_LD =
  380. "UPDATE BALANCE_TBL SET MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS = ? WHERE BALANCE_ID IN (SELECT REF_BALANCE_ID FROM BANK_TBL WHERE BANK_ID = ?)";
  381.  
  382. public static final String QUERY_SELECT_MISC_INCOME_LD =
  383. "SELECT MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS FROM BALANCE_TBL WHERE BALANCE_ID IN (SELECT REF_BALANCE_ID FROM BANK_TBL WHERE BANK_ID = ?)";
  384.  
  385. public static final String QUERY_UPDATE_MISC_INCOME_OTHERS =
  386. "UPDATE BALANCE_TBL SET MISCELLANEOUS_INCOME_OTHERS = ? WHERE BALANCE_ID IN (SELECT REF_BALANCE_ID FROM BANK_TBL WHERE BANK_ID = ?)";
  387.  
  388. public static final String QUERY_SELECT_MISC_INCOME_OTHERS =
  389. "SELECT MISCELLANEOUS_INCOME_OTHERS FROM BALANCE_TBL WHERE BALANCE_ID IN (SELECT REF_BALANCE_ID FROM BANK_TBL WHERE BANK_ID = ?)";
  390.  
  391. //END
  392.  
  393. // PN COLUMNS
  394. public static final String COLUMN_PN_ID = "PN_ID";
  395.  
  396. public static final String COLUMN_PN_NUMBER = "PN_NUMBER";
  397.  
  398. public static final String COLUMN_PN_STATUS = "PN_STATUS";
  399.  
  400. public static final String COLUMN_REF_LOAN_ID = "REF_LOAN_ID";
  401.  
  402. public static final String COLUMN_PN_INTEREST_RATE = "PN_INTEREST_RATE";
  403.  
  404. public static final String COLUMN_MATURITY_DATE = "MATURITY_DATE";
  405. // END - PN COLUMNS
  406.  
  407. // LOAN COLUMNS
  408. public static final String COLUMN_LOAN_ID = "LOAN_ID";
  409.  
  410. public static final String COLUMN_LOAN_TYPE = "LOAN_TYPE";
  411.  
  412. public static final String COLUMN_LOAN_APPLICATION_NUMBER = "LOAN_APPLICATION_NUMBER";
  413.  
  414. public static final String COLUMN_AMOUNT_GRANTED = "AMOUNT_GRANTED";
  415.  
  416. public static final String COLUMN_REF_BANK_ID = "REF_BANK_ID";
  417.  
  418. public static final String COLUMN_LOAN_INTEREST_RATE = "LOAN_INTEREST_RATE";
  419.  
  420. // END - LOAN COLUMNS
  421.  
  422. // BANK COLUMNS
  423. public static final String COLUMN_BANK_ID = "BANK_ID";
  424.  
  425. public static final String COLUMN_BANK_CODE = "BANK_CODE";
  426.  
  427. public static final String COLUMN_BANK_NAME = "BANK_NAME";
  428.  
  429. public static final String COLUMN_BANK_TYPE = "BANK_TYPE";
  430.  
  431. public static final String COLUMN_BANK_STATUS = "BANK_STATUS";
  432.  
  433. // END - BANK COLUMNS
  434.  
  435. // BALANCE COLUMNS
  436. public static final String COLUMN_BALANCE_ID = "BALANCE_ID";
  437.  
  438. public static final String COLUMN_BALANCE_TYPE = "BALANCE_TYPE";
  439.  
  440. public static final String COLUMN_REF_ID = "REF_ID";
  441.  
  442. public static final String COLUMN_OTHERS = "OTHERS";
  443.  
  444. public static final String COLUMN_LIQUIDATED_DAMAGES = "LIQUIDATED_DAMAGES";
  445.  
  446. public static final String COLUMN_ACCRUED_INTEREST_RECEIVABLE = "ACCRUED_INTEREST_RECEIVABLE";
  447.  
  448. public static final String COLUMN_INTEREST_INCOME = "INTEREST_INCOME";
  449.  
  450. public static final String COLUMN_OUTSTANDING_PRINCIPAL = "OUTSTANDING_PRINCIPAL";
  451.  
  452. public static final String COLUMN_TOTAL_OUTSTANDING_BALANCE = "TOTAL_OUTSTANDING_BALANCE";
  453.  
  454. public static final String COLUMN_REF_BALANCE_ID = "REF_BALANCE_ID";
  455.  
  456. public static final String COLUMN_MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS = "MISCELLANEOUS_INCOME_LIQUIDATING_DIVIDENDS";
  457.  
  458. public static final String COLUMN_MISCELLANEOUS_INCOME_OTHERS = "MISCELLANEOUS_INCOME_OTHERS";
  459.  
  460. // END - BALANCE COLUMNS
  461.  
  462. // PAYABLE COLUMNS
  463. public static final String COLUMN_PAYABLE_ID = "PAYABLE_ID";
  464.  
  465. public static final String COLUMN_PAYMENT_AMOUNT = "PAYMENT_AMOUNT";
  466.  
  467. public static final String COLUMN_PROMISSORY_NOTE_NUMBER = "PROMISSORY_NOTE_NUMBER";
  468.  
  469. public static final String COLUMN_REF_PAYMENT_ORDER_ID = "REF_PAYMENT_ORDER_ID";
  470.  
  471. public static final String COLUMN_EXCESS_PAYMENT_TYPE = "EXCESS_PAYMENT_TYPE";
  472. // END - PAYABLE COLUMNS
  473.  
  474. // PAYMENT ORDER COLUMNS
  475.  
  476. public static final String COLUMN_PAYMENT_ORDER_ID = "PAYMENT_ORDER_ID";
  477.  
  478. public static final String COLUMN_PAYMENT_ORDER_NUMBER = "PAYMENT_ORDER_NUMBER";
  479.  
  480. public static final String COLUMN_PAYMENT_ORDER_STATUS = "PAYMENT_ORDER_STATUS";
  481.  
  482. public static final String COLUMN_PAYMENT_LEVEL = "PAYMENT_LEVEL";
  483.  
  484. public static final String COLUMN_BANK_LEVEL_PAYMENT_TYPE = "BANK_LEVEL_PAYMENT_TYPE";
  485.  
  486. // END - PAYMENT ORDER COLUMNS
  487.  
  488. // CONSTATNS
  489. public static final String PAYMENT_ORDER_STATUS_APPROVED = "Approved";
  490.  
  491. public static final String PAYMENT_ORDER_STATUS_COMPLETED = "Completed";
  492.  
  493. public static final String PAYMENT_ORDER_BANK_LEVEL_PAYMENT_TYPE_MOST_ONEROUS = "MOST ONEROUS";
  494.  
  495. public static final String PAYMENT_ORDER_BANK_LEVEL_PAYMENT_TYPE_DLC_CLAIMS = "DLC CLAIMS";
  496.  
  497. public static final String PAYMENT_ORDER_PAYMENT_LEVEL_BANK = "BANK";
  498.  
  499. public static final String PAYMENT_ORDER_PAYMENT_LEVEL_LOAN = "LOAN";
  500.  
  501. public static final String PAYMENT_ORDER_PAYMENT_LEVEL_PN = "PN";
  502.  
  503. public static final String QUERY_RETRIEVE_BALANCE_BY_BANK_ID = "select * from balance_tbl " + "where balance_id in "
  504. +"(select ref_balance_id from loan_tbl where ref_bank_id = "
  505. +"(select bank_id from bank_tbl where bank_name = ?) and loan_type=?)";
  506.  
  507. public static final String QUERY_RETRIEVE_BANK_BALANCE_BY_BANK_ID =
  508. "select * from balance_tbl " + "where balance_id = " + "(select ref_balance_id from bank_tbl where bank_id =?) ";
  509.  
  510. public static final String QUERY_INSERT_BALANCE_MARK ="insert into balance_tbl "
  511. + "(OTHERS,ACCRUED_INTEREST_RECEIVABLE,LIQUIDATED_DAMAGES, "
  512. + "INTEREST_INCOME,OUTSTANDING_PRINCIPAL,TOTAL_OUTSTANDING_BALANCE,ref_id,balance_type) "
  513. + "values(?,?,?,?,?,?,?,?)";
  514.  
  515. public static final String UPDATE_BALANCE_ID_IN_LOAN_TBL =
  516. "update loan_tbl " + "set ref_balance_id = ? " + "where ref_balance_id = ?";
  517.  
  518. public static final String UPDATE_BALANCE_ID_IN_BANK_TBL =
  519. "update bank_tbl " + "set ref_balance_id = ? " + "where ref_balance_id = ?";
  520. }
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!