Guest User

Untitled

a guest
Jan 22nd, 2020
89
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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;
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×