API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jan 22nd, 2020
107
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.35 KB | None | 0 0
raw download clone embed print report
  1. private String queryContentComparation() {
  2. String q = "SELECT b.ALT_ID ||'\";\"'|| \n"
  3. + " a.CONFIRM_INSTRUC_DESC ||'\";\"'|| \n"
  4. + " a.TRADE_TYP ||'\";\"'|| \n"
  5. + " a.FINSRL_TYP ||'\";\"'|| \n"
  6. + " a.CONFIRM_RECEIVER_IND ||'\";\"'|| \n"
  7. + " a.CONFIRM_SENDER_IND ||'\";\"'|| \n"
  8. + " a.INSTR_ID ||'\";\"'|| \n"
  9. + " TO_CHAR(f.VAL_DATE, 'DD-MM-YYYY') ||'\";\"'|| \n"
  10. + " TO_CHAR(g.VAL_DATE, 'DD-MM-YYYY') ||'\";\"'|| \n"
  11. + " PRODS.PRODUCT_DESC ||'\";\"'|| \n"
  12. + " BRANCH.BRANCH ||'\";\"'|| \n"
  13. + " OFFICE.OFFICE ||'\";\"'|| \n"
  14. + " g8.ACCT_ID AS CAMPOUNICO \n"
  15. + " FROM FT_T_SCIS a \n"
  16. + " JOIN FT_T_FINS a1 \n"
  17. + " ON a1.INST_MNEM = a.FINR_INST_MNEM \n"
  18. + " AND a1.DATA_STAT_TYP = 'ACTIVE' \n"
  19. + " LEFT JOIN FT_T_COI1 b \n"
  20. + " ON b.SCIS_OID = a.SCIS_OID \n"
  21. + " AND b.DATA_STAT_TYP = 'ACTIVE' \n"
  22. + " AND b.ID_CTXT_TYP = 'CONFIRMID' \n"
  23. + " LEFT JOIN FT_T_COA1 f --VALIDITY_DATE_FROM \n"
  24. + " ON f.SCIS_OID = a.SCIS_OID \n"
  25. + " AND f.DATA_STAT_TYP = 'ACTIVE' \n"
  26. + " AND f.STAT_DEF_ID = 'DATEFROM' \n"
  27. + " LEFT JOIN FT_T_COA1 g --VALIDITY_DATE_TO \n"
  28. + " ON g.SCIS_OID = a.SCIS_OID \n"
  29. + " AND g.DATA_STAT_TYP = 'ACTIVE' \n"
  30. + " AND g.STAT_DEF_ID = 'DATETO' \n"
  31. + " LEFT JOIN ( \n"
  32. + " SELECT T_PRODS.SCIS_OID, \n"
  33. + " LISTAGG(T_PRODS.PRODUCT, ';') WITHIN GROUP (ORDER BY T_PRODS.PRODUCT) AS PRODUCT, \n"
  34. + " LISTAGG(T_PRODS.PRODUCT_DESC, ';') WITHIN GROUP (ORDER BY T_PRODS.PRODUCT_DESC) AS PRODUCT_DESC \n"
  35. + " FROM ( \n"
  36. + " SELECT g4.SCIS_OID, \n"
  37. + " NVL(g4.ISS_TYP, 'ALL') AS PRODUCT, \n"
  38. + " NVL(g5.ISS_TYP_NME, 'ALL') AS PRODUCT_DESC \n"
  39. + " FROM FT_T_SCA1 g4 \n"
  40. + " LEFT JOIN FT_T_ISTY g5 \n"
  41. + " ON g4.ISS_TYP = g5.ISS_TYP \n"
  42. + " AND g5.DATA_STAT_TYP = 'ACTIVE' \n"
  43. + " WHERE g4.SCIS_OID IN ( \n"
  44. + " SELECT SCIS_OID \n"
  45. + " FROM FT_T_SCIS \n"
  46. + " WHERE FINR_INST_MNEM = '" + this.counterpartyMnem
  47. + "' \n"
  48. + " ) \n"
  49. + " AND g4.DATA_STAT_TYP = 'ACTIVE' \n"
  50. + " AND g4.PURP_TYP = 'PRODUCT' \n"
  51. + " ) T_PRODS \n"
  52. + " GROUP BY T_PRODS.SCIS_OID \n"
  53. + " ) PRODS \n"
  54. + " ON PRODS.SCIS_OID = a.SCIS_OID \n"
  55. + " --BRANCH \n"
  56. + " LEFT JOIN ( \n"
  57. + " SELECT g6.SCIS_OID, \n"
  58. + " LISTAGG(TRIM(g6.ORG_ID), ';') WITHIN GROUP (ORDER BY g6.ORG_ID) AS BRANCH \n"
  59. + " FROM FT_T_SCA1 g6 \n"
  60. + " WHERE g6.PURP_TYP = 'BRANCH' \n"
  61. + " AND g6.DATA_STAT_TYP = 'ACTIVE' \n"
  62. + " AND g6.SCIS_OID IN ( \n"
  63. + " SELECT SCIS_OID \n"
  64. + " FROM FT_T_SCIS \n"
  65. + " WHERE FINR_INST_MNEM = '" + this.counterpartyMnem
  66. + "' \n"
  67. + " ) \n"
  68. + " GROUP BY g6.SCIS_OID \n"
  69. + " ) BRANCH \n"
  70. + " ON BRANCH.SCIS_OID = a.SCIS_OID \n"
  71. + " --BRANCH \n"
  72. + " --OFFICE \n"
  73. + " LEFT JOIN ( \n"
  74. + " SELECT g7.SCIS_OID, \n"
  75. + " LISTAGG(CONCAT(TRIM(g7.ORG_ID),TRIM(g7.SUBDIV_ID)), '||') WITHIN GROUP (ORDER BY g7.SUBDIV_ID) AS OFFICE, \n"
  76. + " LISTAGG(TRIM(g7.SUBDIV_ID), '||') WITHIN GROUP (ORDER BY g7.SUBDIV_ID) AS OFFICE_DESC \n"
  77. + " FROM FT_T_SCA1 g7 \n"
  78. + " WHERE g7.PURP_TYP = 'OFFICE' \n"
  79. + " AND g7.DATA_STAT_TYP = 'ACTIVE' \n"
  80. + " GROUP BY g7.SCIS_OID \n"
  81. + " ) OFFICE \n"
  82. + " ON OFFICE.SCIS_OID = a.SCIS_OID \n"
  83. + " --OFFICE \n"
  84. + " --SECURITY ACCOUNT \n"
  85. + " LEFT JOIN FT_T_ACCT g8 \n"
  86. + " ON g8.ACCT_ID = a.ACCT_ID \n"
  87. + " AND g8.ACCT_PURP_TYP = 'SECURITY ACCOUNT' AND g8.DATA_STAT_TYP = 'ACTIVE' \n"
  88. + " WHERE a.FINR_INST_MNEM = '" + this.counterpartyMnem
  89. + "' \n"
  90. + " AND a.CONFIRM_INSTRUC_DESC = '" + this.instructionType
  91. + "' \n"
  92. + " AND a.FINSRL_TYP = '" + this.role
  93. + "' \n"
  94. + " AND a.CONFIRM_RECEIVER_IND = '" + this.receiver
  95. + "' \n"
  96. + " AND a.CONFIRM_SENDER_IND = '" + this.sender
  97. + "' \n"
  98. + " AND a.DATA_STAT_TYP = 'ACTIVE' \n";
  99. if (this.scis_oidNOTIN != null) {
  100. q += " AND a.SCIS_OID NOT IN ('" + this.scis_oidNOTIN
  101. + "') \n";
  102. }
  103.  
  104. ;
  105. return q;
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!