Guest User

Untitled

a guest
Jan 22nd, 2020
84
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