Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- private String queryContentComparation() {
- String q = "SELECT b.ALT_ID ||'\";\"'|| \n"
- + " a.CONFIRM_INSTRUC_DESC ||'\";\"'|| \n"
- + " a.TRADE_TYP ||'\";\"'|| \n"
- + " a.FINSRL_TYP ||'\";\"'|| \n"
- + " a.CONFIRM_RECEIVER_IND ||'\";\"'|| \n"
- + " a.CONFIRM_SENDER_IND ||'\";\"'|| \n"
- + " a.INSTR_ID ||'\";\"'|| \n"
- + " TO_CHAR(f.VAL_DATE, 'DD-MM-YYYY') ||'\";\"'|| \n"
- + " TO_CHAR(g.VAL_DATE, 'DD-MM-YYYY') ||'\";\"'|| \n"
- + " PRODS.PRODUCT_DESC ||'\";\"'|| \n"
- + " BRANCH.BRANCH ||'\";\"'|| \n"
- + " OFFICE.OFFICE ||'\";\"'|| \n"
- + " g8.ACCT_ID AS CAMPOUNICO \n"
- + " FROM FT_T_SCIS a \n"
- + " JOIN FT_T_FINS a1 \n"
- + " ON a1.INST_MNEM = a.FINR_INST_MNEM \n"
- + " AND a1.DATA_STAT_TYP = 'ACTIVE' \n"
- + " LEFT JOIN FT_T_COI1 b \n"
- + " ON b.SCIS_OID = a.SCIS_OID \n"
- + " AND b.DATA_STAT_TYP = 'ACTIVE' \n"
- + " AND b.ID_CTXT_TYP = 'CONFIRMID' \n"
- + " LEFT JOIN FT_T_COA1 f --VALIDITY_DATE_FROM \n"
- + " ON f.SCIS_OID = a.SCIS_OID \n"
- + " AND f.DATA_STAT_TYP = 'ACTIVE' \n"
- + " AND f.STAT_DEF_ID = 'DATEFROM' \n"
- + " LEFT JOIN FT_T_COA1 g --VALIDITY_DATE_TO \n"
- + " ON g.SCIS_OID = a.SCIS_OID \n"
- + " AND g.DATA_STAT_TYP = 'ACTIVE' \n"
- + " AND g.STAT_DEF_ID = 'DATETO' \n"
- + " LEFT JOIN ( \n"
- + " SELECT T_PRODS.SCIS_OID, \n"
- + " LISTAGG(T_PRODS.PRODUCT, ';') WITHIN GROUP (ORDER BY T_PRODS.PRODUCT) AS PRODUCT, \n"
- + " LISTAGG(T_PRODS.PRODUCT_DESC, ';') WITHIN GROUP (ORDER BY T_PRODS.PRODUCT_DESC) AS PRODUCT_DESC \n"
- + " FROM ( \n"
- + " SELECT g4.SCIS_OID, \n"
- + " NVL(g4.ISS_TYP, 'ALL') AS PRODUCT, \n"
- + " NVL(g5.ISS_TYP_NME, 'ALL') AS PRODUCT_DESC \n"
- + " FROM FT_T_SCA1 g4 \n"
- + " LEFT JOIN FT_T_ISTY g5 \n"
- + " ON g4.ISS_TYP = g5.ISS_TYP \n"
- + " AND g5.DATA_STAT_TYP = 'ACTIVE' \n"
- + " WHERE g4.SCIS_OID IN ( \n"
- + " SELECT SCIS_OID \n"
- + " FROM FT_T_SCIS \n"
- + " WHERE FINR_INST_MNEM = '" + this.counterpartyMnem
- + "' \n"
- + " ) \n"
- + " AND g4.DATA_STAT_TYP = 'ACTIVE' \n"
- + " AND g4.PURP_TYP = 'PRODUCT' \n"
- + " ) T_PRODS \n"
- + " GROUP BY T_PRODS.SCIS_OID \n"
- + " ) PRODS \n"
- + " ON PRODS.SCIS_OID = a.SCIS_OID \n"
- + " --BRANCH \n"
- + " LEFT JOIN ( \n"
- + " SELECT g6.SCIS_OID, \n"
- + " LISTAGG(TRIM(g6.ORG_ID), ';') WITHIN GROUP (ORDER BY g6.ORG_ID) AS BRANCH \n"
- + " FROM FT_T_SCA1 g6 \n"
- + " WHERE g6.PURP_TYP = 'BRANCH' \n"
- + " AND g6.DATA_STAT_TYP = 'ACTIVE' \n"
- + " AND g6.SCIS_OID IN ( \n"
- + " SELECT SCIS_OID \n"
- + " FROM FT_T_SCIS \n"
- + " WHERE FINR_INST_MNEM = '" + this.counterpartyMnem
- + "' \n"
- + " ) \n"
- + " GROUP BY g6.SCIS_OID \n"
- + " ) BRANCH \n"
- + " ON BRANCH.SCIS_OID = a.SCIS_OID \n"
- + " --BRANCH \n"
- + " --OFFICE \n"
- + " LEFT JOIN ( \n"
- + " SELECT g7.SCIS_OID, \n"
- + " LISTAGG(CONCAT(TRIM(g7.ORG_ID),TRIM(g7.SUBDIV_ID)), '||') WITHIN GROUP (ORDER BY g7.SUBDIV_ID) AS OFFICE, \n"
- + " LISTAGG(TRIM(g7.SUBDIV_ID), '||') WITHIN GROUP (ORDER BY g7.SUBDIV_ID) AS OFFICE_DESC \n"
- + " FROM FT_T_SCA1 g7 \n"
- + " WHERE g7.PURP_TYP = 'OFFICE' \n"
- + " AND g7.DATA_STAT_TYP = 'ACTIVE' \n"
- + " GROUP BY g7.SCIS_OID \n"
- + " ) OFFICE \n"
- + " ON OFFICE.SCIS_OID = a.SCIS_OID \n"
- + " --OFFICE \n"
- + " --SECURITY ACCOUNT \n"
- + " LEFT JOIN FT_T_ACCT g8 \n"
- + " ON g8.ACCT_ID = a.ACCT_ID \n"
- + " AND g8.ACCT_PURP_TYP = 'SECURITY ACCOUNT' AND g8.DATA_STAT_TYP = 'ACTIVE' \n"
- + " WHERE a.FINR_INST_MNEM = '" + this.counterpartyMnem
- + "' \n"
- + " AND a.CONFIRM_INSTRUC_DESC = '" + this.instructionType
- + "' \n"
- + " AND a.FINSRL_TYP = '" + this.role
- + "' \n"
- + " AND a.CONFIRM_RECEIVER_IND = '" + this.receiver
- + "' \n"
- + " AND a.CONFIRM_SENDER_IND = '" + this.sender
- + "' \n"
- + " AND a.DATA_STAT_TYP = 'ACTIVE' \n";
- if (this.scis_oidNOTIN != null) {
- q += " AND a.SCIS_OID NOT IN ('" + this.scis_oidNOTIN
- + "') \n";
- }
- ;
- return q;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement