SELECT z.A_CD_CLIENTE ,z.A_CD_ASSESSOR ,z.A_VL_NEGOCIO ,z.A_QT_NEGOCIO ,z.A_CD_NEGOCIO ,z.A_DT_PREGAO ,z.A_CD_NATOPE ,z.A_CD_CONTRAPARTE ,z.A_NM_CORRET ,z.A_CD_CPFCGC ,z.A_DT_NASC_FUND ,z.A_NM_CLIENTE ,z.A_IN_PESS_VINC ,z.A_IN_POLITICO_EXP ,z.A_NM_ASSESSOR ,z.A_TP_MERCADO ,z.A_HH_NEGOCIO ,z.B_CD_CLIENTE ,z.B_CD_ASSESSOR ,z.B_VL_NEGOCIO ,z.B_QT_NEGOCIO ,z.B_CD_NEGOCIO ,z.B_DT_PREGAO ,z.B_CD_NATOPE ,z.B_CD_CONTRAPARTE ,z.B_NM_CORRET ,z.B_CD_CPFCGC ,z.B_DT_NASC_FUND ,z.B_NM_CLIENTE ,z.B_IN_PESS_VINC ,z.B_IN_POLITICO_EXP ,z.B_NM_ASSESSOR ,z.B_TP_MERCADO ,z.B_HH_NEGOCIO from ( SELECT a.CD_CLIENTE as A_CD_CLIENTE ,c.CODASS as A_CD_ASSESSOR ,f.VL_PREPAP as A_VL_NEGOCIO ,a.QT_ORDEXEC as A_QT_NEGOCIO ,a.CD_CODCAS as A_CD_NEGOCIO ,to_char(a.DT_DATORD,'dd/mm/yyyy') as A_DT_PREGAO ,a.CD_NATOPE as A_CD_NATOPE ,f.CD_CONTRAPARTE as A_CD_CONTRAPARTE ,b.NM_CORRET as A_NM_CORRET ,to_char(c.CD_CPFCGC,'000000000000000') as A_CD_CPFCGC ,to_char(c.DT_NASC_FUND,'dd/mm/yyyy') as A_DT_NASC_FUND ,d.NM_CLIENTE as A_NM_CLIENTE ,DECODE(d.IN_PESS_VINC,'S','S','N' ) as A_IN_PESS_VINC ,DECODE(d.IN_POLITICO_EXP,'S','S','N' ) as A_IN_POLITICO_EXP ,e.NM_ASSESSOR as A_NM_ASSESSOR ,a.CD_MERCAD as A_TP_MERCADO ,to_char(a.DT_HORORD,'dd/mm/yyyy HH24:MI:SS') as A_HH_NEGOCIO ,z9.CD_CLIENTE as B_CD_CLIENTE ,z9.CD_ASSESSOR as B_CD_ASSESSOR ,z9.VL_NEGOCIO as B_VL_NEGOCIO ,z9.QT_NEGOCIO as B_QT_NEGOCIO ,z9.CD_NEGOCIO as B_CD_NEGOCIO ,z9.DT_PREGAO as B_DT_PREGAO ,z9.CD_NATOPE as B_CD_NATOPE ,z9.CD_CONTRAPARTE as B_CD_CONTRAPARTE ,z9.NM_CORRET as B_NM_CORRET ,z9.CD_CPFCGC as B_CD_CPFCGC ,z9.DT_NASC_FUND as B_DT_NASC_FUND ,z9.NM_CLIENTE as B_NM_CLIENTE ,z9.IN_PESS_VINC as B_IN_PESS_VINC ,z9.IN_POLITICO_EXP as B_IN_POLITICO_EXP ,z9.NM_ASSESSOR as B_NM_ASSESSOR ,z9.TP_MERCADO as B_TP_MERCADO ,z9.HH_NEGOCIO as B_HH_NEGOCIO from corrwin.VMFMOV a, corrwin.TMFCORRET b, corrwin.TSCCLIBMF c, corrwin.TSCCLIGER d, corrwin.TSCASSES e, corrwin.VMFDETD_H f, ( SELECT a.CD_CLIENTE as CD_CLIENTE ,c.CODASS as CD_ASSESSOR ,f.VL_PREPAP as VL_NEGOCIO ,a.QT_ORDEXEC as QT_NEGOCIO ,a.CD_CODCAS as CD_NEGOCIO ,to_char(a.DT_DATORD,'dd/mm/yyyy') as DT_PREGAO ,a.CD_NATOPE as CD_NATOPE ,f.CD_CONTRAPARTE as CD_CONTRAPARTE ,b.NM_CORRET as NM_CORRET ,to_char(c.CD_CPFCGC,'000000000000000') as CD_CPFCGC ,to_char(c.DT_NASC_FUND,'dd/mm/yyyy') as DT_NASC_FUND ,d.NM_CLIENTE as NM_CLIENTE ,DECODE(d.IN_PESS_VINC,'S','S','N' ) as IN_PESS_VINC ,DECODE(d.IN_POLITICO_EXP,'S','S','N' ) as IN_POLITICO_EXP ,e.NM_ASSESSOR as NM_ASSESSOR ,a.CD_MERCAD as TP_MERCADO ,to_char(a.DT_HORORD,'dd/mm/yyyy HH24:MI:SS') as HH_NEGOCIO from corrwin.VMFMOV a, corrwin.TMFCORRET b, corrwin.TSCCLIBMF c, corrwin.TSCCLIGER d, corrwin.TSCASSES e, corrwin.VMFDETD_H f WHERE a.DT_DATORD = f.DT_DATORD and a.NR_SEQORD = f.NR_SEQORD and a.NR_SUBSEQ = f.NR_SUBSEQ and f.CD_CONTRAPARTE = b.CD_CORRET and a.CD_CLIENTE = c.CODCLI and c.CD_CPFCGC = d.CD_CPFCGC and c.DT_NASC_FUND = d.DT_NASC_FUND and c.CD_CON_DEP = d.CD_CON_DEP and c.CODASS = e.CD_ASSESSOR and f.CD_CONTRAPARTE = 74 AND a.DT_DATORD >= to_date('11/01/2018','dd/mm/yyyy') AND a.DT_DATORD <= to_date('11/01/2018','dd/mm/yyyy') AND (c.CODASS IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,17,18,20,21,22,23,25,30,31,32,33,34,35,36,37,40,41,42,43,45,47,48,49,50,51,52,54,55,56,58,59,60,62,63,64,65,66,67,68,69,70,71,72,73,75,76,78,82,83,84,86,87,88,89,90,92,94,96,100,101,102,103,104,106,107,108,109,110,111,112,113,114,115,116,117,118,120,121,122,123,124,125,126,127,128,129) OR c.CODASS IN (129,130,131,132,133,134,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230) OR c.CODASS IN (230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,260,261,262,263,264,265,266,267,276,500,660,661,662,664,666,667,668,669,712,731,732,733,800,1000,1001,1007,1010,1025,1035,1040,1060,1061,1200,1440,1445,1450,1460,1490,1491,1510,1540,1560,6620,6623,6659,6671,7023) OR a.CD_CLIENTE IN (0)) ) z9 WHERE a.DT_DATORD = f.DT_DATORD and a.NR_SEQORD = f.NR_SEQORD and a.NR_SUBSEQ = f.NR_SUBSEQ and f.CD_CONTRAPARTE = b.CD_CORRET and a.CD_CLIENTE = c.CODCLI and c.CD_CPFCGC = d.CD_CPFCGC and c.DT_NASC_FUND = d.DT_NASC_FUND and c.CD_CON_DEP = d.CD_CON_DEP and c.CODASS = e.CD_ASSESSOR and f.CD_CONTRAPARTE = 74 AND a.DT_DATORD >= to_date('11/01/2018','dd/mm/yyyy') AND a.DT_DATORD <= to_date('11/01/2018','dd/mm/yyyy') AND (c.CODASS IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,17,18,20,21,22,23,25,30,31,32,33,34,35,36,37,40,41,42,43,45,47,48,49,50,51,52,54,55,56,58,59,60,62,63,64,65,66,67,68,69,70,71,72,73,75,76,78,82,83,84,86,87,88,89,90,92,94,96,100,101,102,103,104,106,107,108,109,110,111,112,113,114,115,116,117,118,120,121,122,123,124,125,126,127,128,129) OR c.CODASS IN (129,130,131,132,133,134,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230) OR c.CODASS IN (230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,260,261,262,263,264,265,266,267,276,500,660,661,662,664,666,667,668,669,712,731,732,733,800,1000,1001,1007,1010,1025,1035,1040,1060,1061,1200,1440,1445,1450,1460,1490,1491,1510,1540,1560,6620,6623,6659,6671,7023) OR a.CD_CLIENTE IN (0)) ) z WHERE z.A_CD_CLIENTE = z.B_CD_CLIENTE and z.A_CD_NATOPE<>z.B_CD_NATOPE and z.A_CD_NEGOCIO=z.B_CD_NEGOCIO and z.A_DT_PREGAO=z.B_DT_PREGAO