select 
DATE_STAT
||CHR(9)||CIE_SAP
||CHR(9)||CIE
||CHR(9)||ACTIVITE
||CHR(9)||ACTIVITE||POSTE||CIE
||CHR(9)||TYPE_CONTRAT
||CHR(9)||NATURE_CONTRAT
||CHR(9)||ETAT_CONTRAT
||CHR(9)||CONTRAT
||CHR(9)||CODE_FAMILLE
||CHR(9)||FAMILLE_COMPTABLE
||CHR(9)||REGROUPEMENT_TDB
||CHR(9)||STRUCTURE
||CHR(9)||decode(STRUCTURE,'NAT', 'HORS GLOBAL', 'GLOBAL')
||CHR(9)||CONTRAT || decode(STRUCTURE, 'NAT', 'HORS GLOBAL', 'GLOBAL') || POSTE
||CHR(9)||CIE_SAP || ACTIVITE || decode(STRUCTURE, 'NAT', 'HORS GLOBAL', 'GLOBAL')
||CHR(9)||RETRO_FAC
||CHR(9)||RETRO_TRAITE
||CHR(9)||CONTRAT || POSTE
||CHR(9)||DEVISE
||CHR(9)||TERRITOIRE_SAP
||CHR(9)||TERRITOIRE
||CHR(9)||CBO_SAP
||CHR(9)||CBO
||CHR(9)||CSD_SAP
||CHR(9)||DATE_SURVENANCE
||CHR(9)||DATE_EFFET
||CHR(9)||EXE
||CHR(9)||NATURE_COMPTABLE
||CHR(9)||POSTE
||CHR(9)||POSTE_LIBELLE
||CHR(9)||POSTE_COPA
||CHR(9)||TAUX_CONV
||CHR(9)||REPLACE(to_char(sum(MT),'FM9999999990.09'),'.',',')
||CHR(9)||REPLACE(to_char(sum(MT_EUR),'FM9999999990.09'),'.',',')
from (
select 
   to_char(tba.tbafind,'dd/mm/yyyy') as DATE_STAT,
   cie.sap_ciecodc as CIE_SAP,
   cie.ciecodc as CIE,
   mtp.acrcodc as ACTIVITE,
   decode(str.strseqx,null,'F','T') as TYPE_CONTRAT,
   decode(str.strseqx,null,cfa.cfanarc,str.strnarc) as NATURE_CONTRAT,
   decode(str.strseqx,null,null,decode(R1.MAX_EXE,2007,'EN_COURS','RUN_OFF')) as ETAT_CONTRAT,
   decode(str.strseqx,null,decode(cfa.acrcodc,'CES','Facultatives '||bef.beflonl,'Fac n° '||cfa.cfanumx||' - '||nvl(pol.polnoml,cai.cainoml)),
   to_char(trt.trtnumx)||'.'||to_char(str.strnumx)||' '||str.strlibl) as CONTRAT,
   decode(str.strseqx,null,fco_cfa.fcocodc,fco_str.fcocodc) as CODE_FAMILLE,
   decode(str.strseqx,null,fco_cfa.fcolibl,fco_str.fcolibl)  as FAMILLE_COMPTABLE,
   decode(str.strseqx,null,cfa.cfartbl, str.strrtbl)  as REGROUPEMENT_TDB,
   decode(str.strseqx,null,decode(pol.polseqx,null,cai.caioric,pol.poloric),decode(str.oricodc,null,'NAT',str.oricodc)) as STRUCTURE,
   decode(str.strseqx,null,decode(cfa.acrcodc,'ACC',cfa.cfarfac,'N'),'N') as RETRO_FAC,
   decode(str.strseqx,null,decode(cfa.acrcodc,'ACC',decode(cfa.cfatdsc,'P','Schema '||to_char(cfa.srpseqx),'S','Standard','Pas de Retro'),'N'),
   decode(trt.acrcodc,'ACC',decode(str.strtdsc,'P','Schema '||to_char(str.srpseqx),'S','Standard','Pas de Retro'),'N')) as RETRO_TRAITE,
   mtp.devcodc as DEVISE,
   decode(mtp.acrcodc,'CES',ter.ter_tercodc,'97') as TERRITOIRE_SAP,
   ter.tercodc as TERRITOIRE,
   cbo.cmicodc as CBO_SAP,
   cbo.cbocodc as CBO,
   decode(mtp.acrcodc,'CES',csd.bpccodc,'B004') as CSD_SAP,
   null as DATE_SURVENANCE,
   mtp.mtpannn as DATE_EFFET,
   decode(str.strseqx,null, CFAEXEN, TRTREFN) as EXE,
   mtp.acrcodc||ncl.ncenatx as NATURE_COMPTABLE,
   ncl.ncenatx as POSTE,
   ncl.ncllibl as POSTE_LIBELLE,
   '' as POSTE_COPA,
   mtp.mtpmmtm*power(10,dev.devprcn-dev.devdecn) as MT, 
   rskcdv.rspcdv02(mtp.mtpmmtm,mtp.devcodc,'500',05,2007, '500') / 100 as MT_EUR,
   rskcdv.rspcdv05(mtp.devcodc, 05, 2007) as TAUX_CONV
FROM
  rsbmtp mtp,
  rsbcfa cfa,
  rsbpol pol,
  rsbcai cai,
  rsbbef bef,
  rsbtrt trt,
  rsbstr str,
  rsbfco fco_cfa,
  rsbfco fco_str,
  rsbcbo cbo,
  rsbcsd csd,
  rsbter ter,
  rsbcie cie,
  rsbdev dev,
  rsbtba tba,
  rsbncl ncl,
  (select rsbtrt.trtnumx, max(rsbtrt.trtrefn) as MAX_EXE 
  from rsbtrt where rsbtrt.supcodc = 'N'
  group by rsbtrt.trtnumx) R1
  WHERE  
  mtp.cfaseqx = cfa.cfaseqx (+)
  and cfa.polseqx = pol.polseqx (+)
  and cfa.caiseqx = cai.caiseqx (+)
  and cfa.befcodc = bef.befcodc (+)
  and cfa.fcocodc = fco_cfa.fcocodc (+)
  and mtp.cbocodc = cbo.cbocodc
  and mtp.ciecodc = cbo.ciecodc 
  and mtp.csdcodc = csd.csdcodc (+)
  and mtp.ciecodc = csd.ciecodc (+)
  and mtp.strseqx = str.strseqx (+)
  and str.trtseqx = trt.trtseqx (+)
  and trt.trtnumx = R1.trtnumx (+)
  and str.fcocodc = fco_str.fcocodc (+)
  and mtp.ciecodc = cie.ciecodc
  and mtp.tercodc = ter.tercodc
  and mtp.ncenatx = ncl.ncenatx
  and mtp.devcodc = dev.devcodc
  and mtp.tbaseqx = tba.tbaseqx
  and tba.tdtcodc = 'CAL'
  and to_char(tba.tbafind,'mm') = 05
  and to_char(tba.tbafind,'yyyy') = 2007
  and (('' is null and mtp.ciecodc in (select distinct ciecodc from rsbcie where ltrim(rtrim(to_char(sap_ciecodc)))='G001')) or ('' is not null and mtp.ciecodc=''))
  and (('ACC' is null and mtp.acrcodc in (select acrcodc from rsbacr)) or ('ACC' is not null and mtp.acrcodc='ACC'))
  and (('' is null and mtp.ncenatx in (3,6,18,35,80,83,89,90,91,94,95,105,112,161,177,178,179,180,181,182,183)) or ('' is not null and ltrim(rtrim(to_char(mtp.ncenatx)))=''))
  and mtp.mtpmmtm <> 0
union all
select 
   to_char(tba.tbafind,'dd/mm/yyyy') as DATE_STAT,
   cie.sap_ciecodc as CIE_SAP,
   cie.ciecodc as CIE_CODE,
   mts.acrcodc as ACTIVITE,
   decode(str.strseqx,null,'F','T') as TYPE_CONTRAT,
   decode(str.strseqx,null,cfa.cfanarc,str.strnarc) as NATURE_CONTRAT,
   decode(str.strseqx,null,null,decode(R1.MAX_EXE,2007,'EN_COURS','RUN_OFF')) as ETAT_CONTRAT,
   decode(str.strseqx,null,decode(cfa.acrcodc,'CES','Facultatives '||bef.beflonl,'Fac n° '||cfa.cfanumx||' - '||nvl(pol.polnoml,cai.cainoml)),
   to_char(trt.trtnumx)||'.'||to_char(str.strnumx)||' '||str.strlibl) as CONTRAT,
   decode(str.strseqx,null,fco_cfa.fcocodc,fco_str.fcocodc) as CODE_FAMILLE,
   decode(str.strseqx,null,fco_cfa.fcolibl,fco_str.fcolibl)  as FAMILLE_COMPTABLE,
   decode(str.strseqx,null,cfa.cfartbl, str.strrtbl)  as REGROUPEMENT_TDB,
   decode(str.strseqx,null,decode(pol.polseqx,null,cai.caioric,pol.poloric),decode(str.oricodc,null,'NAT',str.oricodc)) as STRUCTURE,
   decode(str.strseqx,null,decode(cfa.acrcodc,'ACC',cfa.cfarfac,'N'),'N') as RETRO_FAC,
   decode(str.strseqx,null,decode(cfa.acrcodc,'ACC',decode(cfa.cfatdsc,'P','Schema '||to_char(cfa.srpseqx),'S','Standard','Pas de Retro'),'N'),
   decode(trt.acrcodc,'ACC',decode(str.strtdsc,'P','Schema '||to_char(str.srpseqx),'S','Standard','Pas de Retro'),'N')) as RETRO_TRAITE,
   mts.devcodc as DEVISE,
   decode(mts.acrcodc,'CES',ter.ter_tercodc,'97') as TERRITOIRE_SAP,
   ter.tercodc as TERRITOIRE,
   cbo.cmicodc as CBO_SAP,
   cbo.cbocodc as CBO,
   decode(mts.acrcodc,'CES',csd.bpccodc,'B004') as CSD_SAP,
   decode(mts.ncenatx,20,null,30,null,31,null,mts.mtsannn) as DATE_SURVENANCE,
   decode(mts.ncenatx,20,mts.mtsannn,30,mts.mtsannn,31,mts.mtsannn,0) as DATE_EFFET,
   decode(str.strseqx,null, CFAEXEN, TRTREFN) as EXE,
   mts.acrcodc||ncl.ncenatx as NATURE_COMPTABLE,
   ncl.ncenatx as POSTE,
   ncl.ncllibl as POSTE_LIBELLE,
   '' as POSTE_COPA,
   mts.mtsmmtm*power(10,dev.devprcn-dev.devdecn) as MT, 
   rskcdv.rspcdv02(mts.mtsmmtm,mts.devcodc,'500',05,2007, '500') / 100 as MT_EUR,
   rskcdv.rspcdv05(mts.devcodc, 05, 2007) as TAUX_CONV
FROM
  rsbmts mts,
  rsbcfa cfa,
  rsbpol pol,
  rsbcai cai,
  rsbbef bef,
  rsbtrt trt,
  rsbstr str,
  rsbfco fco_cfa,
  rsbfco fco_str,
  rsbcbo cbo,
  rsbcsd csd,
  rsbter ter,
  rsbcie cie,
  rsbdev dev,
  rsbtba tba,
  rsbncl ncl,
  (select rsbtrt.trtnumx, max(rsbtrt.trtrefn) as MAX_EXE 
  from rsbtrt where rsbtrt.supcodc = 'N'
  group by rsbtrt.trtnumx) R1
WHERE  
  mts.cfaseqx = cfa.cfaseqx (+)
  and cfa.polseqx = pol.polseqx (+)
  and cfa.caiseqx = cai.caiseqx (+)
  and cfa.befcodc = bef.befcodc (+)
  and cfa.fcocodc = fco_cfa.fcocodc (+)
  and mts.cbocodc = cbo.cbocodc
  and mts.ciecodc = cbo.ciecodc 
  and mts.csdcodc = csd.csdcodc (+)
  and mts.ciecodc = csd.ciecodc (+)
  and mts.strseqx = str.strseqx (+)
  and str.trtseqx = trt.trtseqx (+)
  and trt.trtnumx = R1.trtnumx (+)
  and str.fcocodc = fco_str.fcocodc (+)
  and mts.ciecodc = cie.ciecodc
  and mts.tercodc = ter.tercodc
  and mts.ncenatx = ncl.ncenatx
  and mts.devcodc = dev.devcodc
  and mts.tbaseqx = tba.tbaseqx
  and tba.tdtcodc = 'CAL'
  and to_char(tba.tbafind,'mm') = 05
  and to_char(tba.tbafind,'yyyy') = 2007
  and (('' is null and mts.ciecodc in (select distinct ciecodc from rsbcie where ltrim(rtrim(to_char(sap_ciecodc)))='G001')) or ('' is not null and mts.ciecodc=''))
  and (('ACC' is null and mts.acrcodc in (select acrcodc from rsbacr)) or ('ACC' is not null and mts.acrcodc='ACC'))
  and (('' is null and mts.ncenatx in (15,20,26,30,31,66,87,92,93,113,184,185)) or ('' is not null and ltrim(rtrim(to_char(mts.ncenatx)))=''))
  and mts.mtsmmtm <> 0
) group by DATE_STAT, CIE_SAP, CIE, ACTIVITE, TYPE_CONTRAT, NATURE_CONTRAT, ETAT_CONTRAT, CONTRAT,
CODE_FAMILLE, FAMILLE_COMPTABLE, REGROUPEMENT_TDB, STRUCTURE, RETRO_FAC, RETRO_TRAITE, DEVISE, 
TERRITOIRE_SAP, TERRITOIRE,  CBO_SAP, CBO, CSD_SAP, DATE_SURVENANCE, DATE_EFFET, EXE,
NATURE_COMPTABLE, POSTE, POSTE_LIBELLE, POSTE_COPA, TAUX_CONV