===== Repertoire MOA  =====

\\wpcci400\cfttrans\AG2R_Entreprise\PR\J0P0\LC\CRE

U:\Retraite\Reunica_Entreprise\PR\T0P0\LC\CRE

===== JCL emetteur: =====

J2CFIX08

T8CFIX08

===== fichier : =====

20170819_1087000-J0P0.J2CFIX08.REJETGEN.CTRLTCTTANO.csv

20170819_1086999-J0P0.J2CFIX08.REJETGEN.csv

<code>
with tempTableAno as
(
SELECT
   SUBSTR(ANO.IDULFREJ, 1, 12) as IDREFRGP ,
   ANO.IDPGMAPT,
   ANO.IDPGMERR,
   COALESCE(MSG.LBMSGLON, ERR.LB_LNG) as LBANO,
   ANO.NOMSG,
   VARCHAR(ANO.CORETPRI) as CORETPRI,
   ANO.IDPGM   ,
   ANO.IDJOB   ,
   ANO.LBERRCOM,
   ANO.TYACT   ,
   ANO.LBTAB001,
   SUBSTR(ANO.LBCRI , 1 , 100) as LBCRI
FROM T0P0.TCTTANO ANO
LEFT JOIN T0P0.TCTTMSG MSG
   ON MSG.NOMSG=ANO.NOMSG
LEFT JOIN T0P0.TZ00ERR ERR
   ON ERR.CO_MSG=ANO.NOMSG
where ANO.IDPGM in ('PCFIMB0S','PCFIMB0T')
)
, tempTableFluxFPF as
(
SELECT
   CURRENT DATE as DTCUR,
   FPF.IDFLXITS,
   FPF.COTRITAB,
   FPF.IDENTGES,
   CHAR(DATE(FPF.DTREFFLXITS)) as DTREFFLXITS,
   CHAR(DATE(FPF.TSCRE)) as TSCREDATE ,
   CHAR(TIME(FPF.TSCRE)) as TSCRETIME ,
   COALESCE(FPF2.COFAMEVTGES, '-') as COFAMEVTGES,
   COALESCE(FPF2.COEVTGES, '-') as COEVTGES,
   '' as COCTX,
   tta.IDPGMAPT,
   tta.IDPGMERR,
   tta.LBANO,
   tta.NOMSG,
   tta.CORETPRI,
   FPF.IDUTICRE,
   FPF.IDPGMCRE,
   tta.IDPGM   ,
   tta.IDJOB   ,
   FPF.IDREFRGP,
   tta.LBERRCOM,
   tta.TYACT   ,
   tta.LBTAB001,
   tta.LBCRI ,
   '' as COPARCRE
FROM T0P0.TC9RFPF FPF
INNER JOIN T0P0.TCMRFPF FPF2
   ON FPF.IDFLXITS = FPF2.IDFLXITS
LEFT JOIN tempTableAno tta
   ON FPF.IDREFRGP=tta.IDREFRGP
WHERE FPF2.COFAMEVTGES =''
   AND ( FPF2.DTTRTFLX  IS NULL
         OR FPF2.DTTRTFLX  ^= '31.12.9999' )
)
, tempTableFluxFPT as
(
SELECT
   CURRENT DATE as DTCUR,
   FPT.IDFLXITS,
   FPT.COTRITAB,
   FPT.IDENTGES,
   CHAR(DATE(FPT.DTREFFLXITS)) as DTREFFLXITS,
   CHAR(DATE(FPT.TSCRE)) as TSCREDATE ,
   CHAR(TIME(FPT.TSCRE)) as TSCRETIME ,
   COALESCE(FPT2.COFAMEVTGES, '-') as COFAMEVTGES,
   COALESCE(FPT2.COEVTGES, '-') as COEVTGES,
   FPT.COCTX,
   tta.IDPGMAPT,
   tta.IDPGMERR,
   tta.LBANO,
   tta.NOMSG,
   tta.CORETPRI,
   FPT.IDUTICRE,
   FPT.IDPGMCRE,
   tta.IDPGM   ,
   tta.IDJOB   ,
   FPT.IDREFRGP,
   tta.LBERRCOM,
   tta.TYACT   ,
   tta.LBTAB001,
   tta.LBCRI ,
   FPT.COPARCRE
FROM T0P0.TC9RFPT FPT
inner join T0P0.TCMRFPT FPT2
on FPT.IDFLXITS = FPT2.IDFLXITS
and FPT.COPARCRE = FPT2.COPARCRE
LEFT JOIN tempTableAno tta
   ON FPT.IDREFRGP=tta.IDREFRGP
WHERE FPT2.COFAMEVTGES =''
   AND ( FPT2.DTTRTFLX  IS NULL
         OR FPT2.DTTRTFLX  ^= '31.12.9999' )
)
 SELECT
  CURRENT DATE        ,
  SUBSTR('TYPE REJET     ', 1, 15),
  SUBSTR('IDENTIFIANT CRE', 1, 15),
  SUBSTR('TYPE DE CRE', 1, 11),
  SUBSTR('ID ENTITE GESTION', 1, 17),
  SUBSTR('DATE REFERENCE', 1, 14),
  SUBSTR('DATE CRéATION', 1, 13),
  SUBSTR('HEURE CREATION', 1, 14),
  SUBSTR('CODE FAMILLE EVT GESTION', 1,24),
  SUBSTR('CODE EVENEMENT', 1, 14),
  SUBSTR('ID UE COMPTE GRECCO', 1, 19),
  SUBSTR('ID TECH ADHERANT', 1, 16),
  SUBSTR('ID FONCTIONNEL ADHESION', 1,23),
  SUBSTR('NUM ORDRE ADHESION', 1, 17),
  SUBSTR('NUM ORDRE UEC', 1, 13),
  SUBSTR('TYPE INSTITUTION', 1, 16),
  SUBSTR('NUM INSTITUTION', 1, 15),
  SUBSTR('CODE ORIGINE FINANCIER', 1,22),
  SUBSTR('CODE OBJECTIF FINANCIER', 1,23),
  SUBSTR('TYPE CRéANCE', 1, 12),
  SUBSTR('CODE MODE CALCUL CRéANCE', 1,24),
  SUBSTR('CODE PéRIODE RéFéRENCE', 1,22),
  SUBSTR('CODE GARANTIE', 1, 13),
  SUBSTR('CRE.INATTPOI', 1, 12),
  SUBSTR('CODE CATéGORIE PROF NORM', 1,24),
  SUBSTR('MONTANT', 1,07),
  SUBSTR('CODE CONTENTIEUX ANCIEN', 1,23),
  SUBSTR('CODE CONTENTIEUX', 1, 16),
  SUBSTR('ID ENTITE GESTION BST', 1,21),
  SUBSTR('ID ENTITE GESTION CIBLE', 1,23),
  SUBSTR('-', 1,01),
  SUBSTR('ID DOMAINE ACTIVITE BST', 1,23),
  SUBSTR('ID PAIEMENT', 1, 11),
  SUBSTR('MONTANT PAIEMENT', 1, 16),
  SUBSTR('SENS PAIEMENT', 1, 13),
  SUBSTR('MODE PAIEMENT', 1, 13),
  SUBSTR('ID BORDREAU', 1, 11),
  SUBSTR('PGM APPELLANT', 1, 13),
  SUBSTR('PGM ERREUR', 1, 10),
  SUBSTR('LIBELLE ANOMALIE', 1, 16),
  SUBSTR('NUM MESSAGE', 1, 11),
  SUBSTR('CODE RETOUR ANO', 1, 15),
  SUBSTR('UTIL CREA', 1,09),
  SUBSTR('PGM CREA', 1,08),
  SUBSTR('IDPGM ANO ', 1, 10),
  SUBSTR('IDJOB ANO ', 1, 10),
  SUBSTR('IDULFREJ ANO ', 1, 13),
  SUBSTR('LBERRCOM ANO ', 1, 13),
  SUBSTR('TYACT ANO ', 1, 10),
  SUBSTR('LBTAB001 ANO ', 1, 13),
  SUBSTR('LBCRI ANO ', 1, 10)
  FROM SYSIBM.SYSDUMMY1
  UNION ALL
  SELECT DISTINCT ttf.dtcur ,
                  'RTCMEJET PAIEMENT               ',
   ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
   ttf.TSCREDATE ,
   ttf.TSCRETIME ,
   ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CUE.IDUECRET,
   VARCHAR(UEC.ID_TECH_UR_ADHR),
   ADH.ID_FONC_ADHR ,
   VARCHAR(UEC.NO_ORD_ADHS),
   VARCHAR(UEC.NO_ORD_UEC),
    '-',
    '-',
   FCU.COORI,
   FCU.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    VARCHAR(FCU.MTLIGFINUEC),
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   PAI.IDPAIRET,
    VARCHAR(PAI.MTPAI),
   PAI.COSENPAI,
   PAI.COMODPAI,
   PAI.IDBOR,
    ttf.IDPGMAPT,
    ttf.IDPGMERR,
    ttf.LBANO,
   ttf.NOMSG,
    ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM  tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRPAI PAI ON PAI.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRFCU FCU ON FCU.IDPAIRET=PAI.IDPAIRET
  INNER JOIN T0P0.TCMRCUE CUE ON CUE.IDCPTUECRET = FCU.IDCPTUECRET
  INNER JOIN T0P0.TAUEUEC UEC
    ON UEC.ID_TECH_PUBL_UEC = INT(CUE.IDUECRET)
  INNER JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE ttf.COTRITAB='PAI'
  UNION ALL
  SELECT DISTINCT ttf.dtcur,
                  'IMPAYES EN REJET                ',
   ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
   ttf.TSCREDATE  ,
  ttf.TSCRETIME ,
   ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CAI.IDCAI,
   CAI.IDTECTIE,
    VARCHAR(CAI.NOORDSOUENS),
    '-',
    '-',
   CAI.IDENTGES,
    '-',
   FCA.COORI,
   FCA.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   VARCHAR(FCA.MTFCA),
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
   ttf.LBANO,
   ttf.NOMSG,
    ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM   tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRPAI IMP ON IMP.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRPAI PAI ON PAI.IDIMP=IMP.IDPAIRET
  INNER JOIN T0P0.TCMRFCA FCA ON FCA.IDPAIRET=PAI.IDPAIRET
  INNER JOIN T0P0.TCMRCAI CAI ON CAI.IDCAI = FCA.IDCAI
  WHERE ttf.COTRITAB='PAI'
  UNION ALL
   SELECT DISTINCT ttf.dtcur,
                  ' PAIEMENT SUR COMPTE DE PASSAGE ',
   ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
  ttf.TSCRETIME ,
  ttf.TSCRETIME ,
   ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CAI.IDCAI,
   CAI.IDTECTIE,
    VARCHAR(CAI.NOORDSOUENS),
    '-',
    '-',
   CAI.IDENTGES,
    '-',
   FCA.COORI,
   FCA.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   VARCHAR(FCA.MTFCA),
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
   ttf.LBANO,
   ttf.NOMSG,
    ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE ,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM   tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRPAI PAI ON PAI.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRFCA FCA ON FCA.IDPAIRET=PAI.IDPAIRET
  INNER JOIN T0P0.TCMRCAI CAI ON CAI.IDCAI = FCA.IDCAI
  WHERE ttf.COTRITAB='PAI'
  UNION ALL
   SELECT DISTINCT ttf.dtcur,
                   'REJET PAIEMENT ANNULI          ',
   ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
   ttf.TSCREDATE  ,
    ttf.TSCRETIME  ,
   ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CUE.IDUECRET,
    VARCHAR(UEC.ID_TECH_UR_ADHR),
   ADH.ID_FONC_ADHR ,
    VARCHAR(UEC.NO_ORD_ADHS),
    VARCHAR(UEC.NO_ORD_UEC),
    '-',
    '-',
   FCU.COORI,
   FCU.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    VARCHAR(FCU.MTLIGFINUEC),
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   PAI.IDPAIRET,
    VARCHAR(PAI.MTPAI),
   PAI.COSENPAI,
   PAI.COMODPAI,
   PAI.IDBOR,
    ttf.IDPGMAPT,
    ttf.IDPGMERR,
    ttf.LBANO,
   ttf.NOMSG,
    ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE ,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM  tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRPAA PAA ON PAA.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRPAI PAI ON PAI.IDPAIRET=PAA.IDPAIRET
  INNER JOIN T0P0.TCMRFCU FCU ON FCU.IDPAIRET=PAI.IDPAIRET
  INNER JOIN T0P0.TCMRCUE CUE ON CUE.IDCPTUECRET = FCU.IDCPTUECRET
  INNER JOIN T0P0.TAUEUEC UEC
        ON UEC.ID_TECH_PUBL_UEC = INT(CUE.IDUECRET)
  INNER JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
   WHERE ttf.COTRITAB='PAA'
   UNION ALL
    SELECT DISTINCT ttf.dtcur,
                   'PAIMENT COMPTE PASSAGE ANNULI  ' ,
    ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
   ttf.TSCREDATE  ,
  ttf.TSCRETIME ,
   ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CAI.IDCAI,
   CAI.IDTECTIE,
    VARCHAR(CAI.NOORDSOUENS),
    '-',
    '-',
   CAI.IDENTGES,
    '-',
   FCA.COORI,
   FCA.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   VARCHAR(FCA.MTFCA),
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
   ttf.LBANO,
   ttf.NOMSG,
    ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM   tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRPAA PAA ON PAA.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRPAI PAI ON PAI.IDPAIRET=PAA.IDPAIRET
  INNER JOIN T0P0.TCMRFCA FCA ON FCA.IDPAIRET=PAI.IDPAIRET
  INNER JOIN T0P0.TCMRCAI CAI ON CAI.IDCAI = FCA.IDCAI
   WHERE ttf.COTRITAB='PAA'
   UNION ALL
    SELECT DISTINCT ttf.dtcur,
          'AFFECTATIONS                            ',
   ttf.IDFLXITS ,
   ttf.COTRITAB ,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
   ttf.TSCREDATE  ,
  ttf.TSCRETIME  ,
   ttf.COFAMEVTGES,
    ttf.COEVTGES,
    CUE.IDUECRET,
   VARCHAR(UEC.ID_TECH_UR_ADHR),
    ADH.ID_FONC_ADHR ,
   VARCHAR(UEC.NO_ORD_ADHS),
   VARCHAR(UEC.NO_ORD_UEC),
    SCT.TYINS,
    SCT.NOINS,
    FSC.COORI,
    FSC.COOBJ,
   CRE.TYLIGCRE  ,
   CRE.COMODCALCRE  ,
   CRE.ANPER !!'-'!!
   CRE.COPERREF  ,
   CRE.COGAR,
   CRE.INATTPOI,
   CRE.COCPN,
   VARCHAR(AFD.MTAFFDET) ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
    ttf.LBANO,
   ttf.NOMSG,
    ttf.CORETPRI ,
   ttf.IDUTICRE,
   ttf.IDPGMCRE,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM
   tempTableFluxFPT ttf
  INNER JOIN T0P0.TCMRAFD AFD
   ON ((AFD.IDFLXITSCRE = ttf.IDFLXITS AND ttf.COTRITAB='AFF')
     OR (AFD.IDFLXITSANN = ttf.IDFLXITS AND ttf.COTRITAB='AFA'))
   AND AFD.COPARCRE = ttf.COPARCRE
  INNER JOIN T0P0.TCMRCRE CRE
   ON CRE.IDLIGCRE = AFD.IDLIGCRE
  INNER JOIN T0P0.TCMRFSC FSC
    ON FSC.IDFINSOUCPT=AFD.IDFINSOUCPT
  INNER JOIN T0P0.TCMRSCT SCT
   ON SCT.IDSOUCPT = CRE.IDSOUCPT
  INNER JOIN T0P0.TCMRCUE CUE
    ON CUE.IDCPTUECRET = SCT.IDCPTUECRET
  INNER JOIN T0P0.TAUEUEC UEC
   ON UEC.ID_TECH_PUBL_UEC = INT(CUE.IDUECRET)
   INNER JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE ttf.COTRITAB IN ('AFF','AFA')
  UNION ALL
   SELECT DISTINCT ttf.dtcur,
                  'CHGT NIV DTX LTX                ',
    ttf.IDFLXITS ,
   ttf.COTRITAB ,
   ttf.IDENTGES ,
   ttf.DTREFFLXITS ,
  ttf.TSCREDATE  ,
  ttf.TSCRETIME  ,
   ttf.COFAMEVTGES,
   ttf.COEVTGES ,
   CUE.IDUECRET,
   VARCHAR(UEC.ID_TECH_UR_ADHR) ,
  ADH.ID_FONC_ADHR ,
   VARCHAR(UEC.NO_ORD_ADHS) ,
   VARCHAR(UEC.NO_ORD_UEC) ,
   SCT.TYINS ,
   SCT.NOINS ,
    '-',
    '-',
   CRE.TYLIGCRE ,
   CRE.COMODCALCRE ,
   CRE.ANPER !!'-'!!
   CRE.COPERREF  ,
   CRE.COGAR,
   CRE.INATTPOI,
   CRE.COCPN,
   VARCHAR(CDL.MTSOLCRD) ,
   CDL.COCTXANC ,
   ttf.COCTX ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
  ttf.LBANO,
  ttf.NOMSG,
   ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE ,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM tempTableFluxFPT ttf
  INNER JOIN T0P0.TCMRCDL CDL
   ON CDL.IDFLXITS = ttf.IDFLXITS
   AND CDL.COPARCRE = ttf.COPARCRE
  INNER JOIN T0P0.TCMRCRE CRE
   ON CRE.IDLIGCRE = CDL.IDLIGCRE
  INNER JOIN T0P0.TCMRSCT SCT
   ON SCT.IDSOUCPT = CRE.IDSOUCPT
  INNER JOIN T0P0.TCMRCUE CUE
    ON CUE.IDCPTUECRET = SCT.IDCPTUECRET
  INNER JOIN T0P0.TAUEUEC UEC
   ON UEC.ID_TECH_PUBL_UEC = INT(CUE.IDUECRET)
  INNER JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE  ttf.COTRITAB ='CDL'
  UNION ALL
   SELECT DISTINCT ttf.dtcur,
                  'CREANCES                        ',
    ttf.IDFLXITS ,
   ttf.COTRITAB ,
   ttf.IDENTGES ,
   ttf.DTREFFLXITS ,
  ttf.TSCREDATE  ,
  ttf.TSCRETIME  ,
   ttf.COFAMEVTGES,
   ttf.COEVTGES ,
   CUE.IDUECRET,
    VARCHAR(UEC.ID_TECH_UR_ADHR) ,
   ADH.ID_FONC_ADHR,
    VARCHAR(UEC.NO_ORD_ADHS) ,
    VARCHAR(UEC.NO_ORD_UEC) ,
   SCT.TYINS ,
   SCT.NOINS ,
    '-',
    '-',
   CRE.TYLIGCRE ,
   CRE.COMODCALCRE ,
   CRE.ANPER !!'-'!!
   CRE.COPERREF  ,
   CRE.COGAR,
   CRE.INATTPOI,
   CRE.COCPN,
    VARCHAR(CRD.MTCREDET) ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
  ttf.LBANO,
  ttf.NOMSG,
   ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE ,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM tempTableFluxFPT ttf
  INNER JOIN T0P0.TCMRCRD CRD
   ON ((CRD.IDFLXITSCRE = ttf.IDFLXITS AND ttf.COTRITAB='CRE')
     OR (CRD.IDFLXITSANN = ttf.IDFLXITS AND ttf.COTRITAB='CRA'))
   AND CRD.COPARCRE = ttf.COPARCRE
  INNER JOIN T0P0.TCMRCRE CRE
   ON CRE.IDLIGCRE = CRD.IDLIGCRE
  INNER JOIN T0P0.TCMRSCT SCT
   ON SCT.IDSOUCPT = CRE.IDSOUCPT
  INNER JOIN T0P0.TCMRCUE CUE
    ON CUE.IDCPTUECRET = SCT.IDCPTUECRET
  INNER JOIN T0P0.TAUEUEC UEC
   ON UEC.ID_TECH_PUBL_UEC = INT(CUE.IDUECRET)
   INNER JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE ttf.COTRITAB IN ('CRE','CRA')
  UNION ALL
   SELECT DISTINCT ttf.dtcur,
                   'CHGT EG NIV. CREA              ',
   ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES ,
   ttf.DTREFFLXITS,
  ttf.TSCREDATE  ,
  ttf.TSCRETIME  ,
   ttf.COFAMEVTGES,
   ttf.COEVTGES ,
   CUE.IDUECRET,
   VARCHAR(UEC.ID_TECH_UR_ADHR) ,
  ADH.ID_FONC_ADHR,
    VARCHAR(UEC.NO_ORD_ADHS) ,
   VARCHAR(UEC.NO_ORD_UEC) ,
   SCT.TYINS ,
   SCT.NOINS ,
    '-',
    '-',
   CRE.TYLIGCRE ,
   CRE.COMODCALCRE ,
   CRE.ANPER !!'-'!!
   CRE.COPERREF  ,
   CRE.COGAR,
   CRE.INATTPOI,
   CRE.COCPN,
   VARCHAR(CTR.MTSOLCRD) ,
    '-',
    '-',
   TPU.IDENTGESORI ,
   TPU.IDENTGESCIB ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
  ttf.LBANO,
  ttf.NOMSG,
   ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM  tempTableFluxFPT ttf
  INNER JOIN T0P0.TCMRCTR CTR
   ON CTR.IDFLXITS = ttf.IDFLXITS
   AND CTR.COPARCRE = ttf.COPARCRE
  INNER JOIN T0P0.TCMRTPU TPU
      ON TPU.IDTPURET=CTR.IDTPURET
  INNER JOIN T0P0.TCMRCRE CRE
   ON CRE.IDLIGCRE = CTR.IDLIGCRE
  INNER JOIN T0P0.TCMRSCT SCT
   ON SCT.IDSOUCPT = CRE.IDSOUCPT
  INNER JOIN T0P0.TCMRCUE CUE
    ON CUE.IDCPTUECRET = SCT.IDCPTUECRET
  INNER JOIN T0P0.TAUEUEC UEC
   ON UEC.ID_TECH_PUBL_UEC = INT(CUE.IDUECRET)
  INNER JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE ttf.COTRITAB IN ('CTO','CTC')
  UNION ALL
   SELECT DISTINCT ttf.dtcur,
                  'LF COMPTE PASSAGE               ',
    ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
  ttf.TSCREDATE ,
  ttf.TSCRETIME ,
   ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CAI.IDCAI,
   CAI.IDTECTIE,
    VARCHAR(CAI.NOORDSOUENS),
    '-',
    '-',
   CAI.IDENTGES,
    '-',
   FCA.COORI,
   FCA.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    VARCHAR(FCA.MTFCA),
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
   ttf.LBANO,
   ttf.NOMSG,
    ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE ,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM  tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRFCA FCA ON FCA.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRCAI CAI ON CAI.IDCAI = FCA.IDCAI
  WHERE ttf.COTRITAB IN ('FCA','FCM','FCP','FCR')
  UNION ALL
   SELECT DISTINCT ttf.dtcur,
                  'LF COMPTE PASSAGE ANNULES       ',
    ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
  ttf.TSCREDATE ,
  ttf.TSCRETIME ,
   ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CAI.IDCAI,
   CAI.IDTECTIE,
    VARCHAR(CAI.NOORDSOUENS),
    '-',
    '-',
   CAI.IDENTGES,
    '-',
   FCA.COORI,
   FCA.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    VARCHAR(FCA.MTFCA),
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
   ttf.LBANO,
   ttf.NOMSG,
    ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRCAA CAA ON CAA.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRFCA FCA ON FCA.IDFCA=CAA.IDFCA
  INNER JOIN T0P0.TCMRCAI CAI ON CAI.IDCAI = FCA.IDCAI
  WHERE ttf.COTRITAB IN ('CAA','CMA','CPA','ACR')
  UNION ALL
       SELECT DISTINCT ttf.dtcur,
                      'LF COMPTE UE                   ',
    ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
  ttf.TSCREDATE ,
  ttf.TSCRETIME ,
     ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CUE.IDUECRET,
    VARCHAR(UEC.ID_TECH_UR_ADHR),
   ADH.ID_FONC_ADHR,
    VARCHAR(UEC.NO_ORD_ADHS),
    VARCHAR(UEC.NO_ORD_UEC),
    '-',
    '-',
   FCU.COORI,
   FCU.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    VARCHAR(FCU.MTLIGFINUEC),
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
  ttf.LBANO,
   ttf.NOMSG,
    ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE ,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRFCU FCU ON FCU.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRCUE CUE ON CUE.IDCPTUECRET = FCU.IDCPTUECRET
  INNER JOIN T0P0.TAUEUEC UEC
            ON UEC.ID_TECH_PUBL_UEC = INT(CUE.IDUECRET)
  INNER JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE ttf.COTRITAB='FCU'
      UNION ALL
   SELECT DISTINCT ttf.dtcur,
                    'LF COMPTE UE ANNULES          ',
   ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
  ttf.TSCREDATE ,
  ttf.TSCRETIME ,
     ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CUE.IDUECRET,
    VARCHAR(UEC.ID_TECH_UR_ADHR),
    ADH.ID_FONC_ADHR,
    VARCHAR(UEC.NO_ORD_ADHS),
    VARCHAR(UEC.NO_ORD_UEC),
    '-',
    '-',
   FCU.COORI,
   FCU.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    VARCHAR(FCU.MTLIGFINUEC),
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
  ttf.LBANO,
   ttf.NOMSG,
    ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM   tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRCUA CUA ON CUA.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRFCU FCU ON FCU.IDFINUECRET=CUA.IDFINUECRET
  INNER JOIN T0P0.TCMRCUE CUE ON CUE.IDCPTUECRET = FCU.IDCPTUECRET
  INNER JOIN T0P0.TAUEUEC UEC
            ON UEC.ID_TECH_PUBL_UEC = INT(CUE.IDUECRET)
      INNER JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE ttf.COTRITAB='CUA'
  UNION ALL
   SELECT DISTINCT ttf.dtcur,
                  'LF SOUS COMPTE                  ',
    ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
  ttf.TSCREDATE ,
  ttf.TSCRETIME ,
     ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CUE.IDUECRET,
    VARCHAR(UEC.ID_TECH_UR_ADHR),
   ADH.ID_FONC_ADHR,
    VARCHAR(UEC.NO_ORD_ADHS),
    VARCHAR(UEC.NO_ORD_UEC),
   SCT.TYINS,
   SCT.NOINS,
   FSC.COORI,
   FSC.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    VARCHAR(FSC.MTFINSOUCPT),
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
  ttf.LBANO,
   ttf.NOMSG,
    ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE ,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM  tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRFSC FSC ON FSC.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRSCT SCT ON SCT.IDSOUCPT=FSC.IDSOUCPT
  INNER JOIN T0P0.TCMRCUE CUE ON CUE.IDCPTUECRET = SCT.IDCPTUECRET
  INNER JOIN T0P0.TAUEUEC UEC
            ON UEC.ID_TECH_PUBL_UEC = INT(CUE.IDUECRET)
  INNER JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE ttf.COTRITAB='FSC'
      UNION ALL
   SELECT DISTINCT ttf.dtcur,
                  'LF SOUS COMPTE ANNULES          ',
    ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
  ttf.TSCREDATE ,
  ttf.TSCRETIME ,
     ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CUE.IDUECRET,
    VARCHAR(UEC.ID_TECH_UR_ADHR),
   ADH.ID_FONC_ADHR,
    VARCHAR(UEC.NO_ORD_ADHS),
    VARCHAR(UEC.NO_ORD_UEC),
   SCT.TYINS,
   SCT.NOINS,
   FSC.COORI,
   FSC.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    VARCHAR(FSC.MTFINSOUCPT),
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
  ttf.LBANO,
   ttf.NOMSG,
    ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE ,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM   tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRFSA FSA ON FSA.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRFSC FSC ON FSC.IDFINSOUCPT=FSA.IDFINSOUCPT
  INNER JOIN T0P0.TCMRSCT SCT ON SCT.IDSOUCPT=FSC.IDSOUCPT
  INNER JOIN T0P0.TCMRCUE CUE ON CUE.IDCPTUECRET = SCT.IDCPTUECRET
  INNER JOIN T0P0.TAUEUEC UEC
            ON UEC.ID_TECH_PUBL_UEC = INT(CUE.IDUECRET)
  INNER JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE ttf.COTRITAB='FSA'
  UNION ALL
       SELECT DISTINCT ttf.dtcur,
                       'CHGT EG NIV. COMPT            ',
    ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
  ttf.TSCREDATE ,
  ttf.TSCRETIME ,
     ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CUE.IDUECRET,
    VARCHAR(UEC.ID_TECH_UR_ADHR),
   ADH.ID_FONC_ADHR,
    VARCHAR(UEC.NO_ORD_ADHS),
    VARCHAR(UEC.NO_ORD_UEC),
    '-',
    '-',
    '-',
   MSC.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    VARCHAR(MSC.MTMVTSOL),
    '-',
    '-',
   TPU.IDENTGESORI,
   TPU.IDENTGESCIB,
   'T',
   TPU.IDDOMACT,
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
  ttf.LBANO,
    ttf.NOMSG,
   ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE ,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM   tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRMSC MSC ON MSC.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRTPU TPU ON TPU.IDTPURET=MSC.IDTPURET
  INNER JOIN T0P0.TCMRCUE CUE ON CUE.IDCPTUECRET = MSC.IDCPTUECRET
  INNER JOIN T0P0.TAUEUEC UEC
    ON UEC.ID_TECH_PUBL_UEC = INT(CUE.IDUECRET)
  INNER JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE ttf.COTRITAB='MSC'
  UNION ALL
       SELECT DISTINCT ttf.dtcur,
                      'CHGT EG NIV. SS COMP           ',
    ttf.IDFLXITS,
   ttf.COTRITAB,
   ttf.IDENTGES,
   ttf.DTREFFLXITS,
  ttf.TSCREDATE ,
  ttf.TSCRETIME ,
     ttf.COFAMEVTGES,
   ttf.COEVTGES,
   CUE.IDUECRET,
    VARCHAR(UEC.ID_TECH_UR_ADHR),
   ADH.ID_FONC_ADHR,
    VARCHAR(UEC.NO_ORD_ADHS),
    VARCHAR(UEC.NO_ORD_UEC),
   SCT.TYINS,
   SCT.NOINS,
    '-',
   MSS.COOBJ,
    '-',
    '-',
    '-',
    '-',
    '-',
    '-',
    VARCHAR(MSS.MTMVTSOL),
    '-',
    '-',
   TPU.IDENTGESORI,
   TPU.IDENTGESCIB,
   'T',
   TPU.IDDOMACT,
    '-',
    '-',
    '-',
    '-',
    '-',
   ttf.IDPGMAPT,
   ttf.IDPGMERR,
  ttf.LBANO,
    ttf.NOMSG,
   ttf.CORETPRI,
   ttf.IDUTICRE,
   ttf.IDPGMCRE ,
   ttf.IDPGM   ,
   ttf.IDJOB   ,
   ttf.IDREFRGP,
   ttf.LBERRCOM,
   ttf.TYACT   ,
   ttf.LBTAB001,
   ttf.lbcri
  FROM tempTableFluxFPF ttf
  INNER JOIN T0P0.TCMRMSS MSS ON MSS.IDFLXITS=ttf.IDFLXITS
  INNER JOIN T0P0.TCMRTPU TPU ON TPU.IDTPURET=MSS.IDTPURET
  INNER JOIN T0P0.TCMRSCT SCT ON SCT.IDSOUCPT=MSS.IDSOUCPT
  INNER JOIN T0P0.TCMRCUE CUE ON CUE.IDCPTUECRET = SCT.IDCPTUECRET
  INNER JOIN T0P0.TAUEUEC UEC
      ON UEC.ID_TECH_PUBL_UEC = INT(CUE.IDUECRET)
  INNER JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE ttf.COTRITAB='MSS'
      WITH UR ;
with tempTableAno as
(
SELECT
   ANO.IDULFREJ as IDCODINS ,
   ANO.IDPGMAPT,
   ANO.IDPGMERR,
   COALESCE(MSG.LBMSGLON, ERR.LB_LNG) as LBANO,
   ANO.NOMSG,
   VARCHAR(ANO.CORETPRI) as CORETPRI,
   ANO.IDPGM   ,
   ANO.IDJOB   ,
   ANO.LBERRCOM,
   ANO.TYACT   ,
   ANO.LBTAB001,
   SUBSTR(ANO.LBCRI , 1 , 100) as LBCRI
FROM T0P0.TCTTANO ANO
LEFT JOIN T0P0.TCTTMSG MSG
   ON MSG.NOMSG=ANO.NOMSG
LEFT JOIN T0P0.TZ00ERR ERR
   ON ERR.CO_MSG=ANO.NOMSG
where ANO.IDPGM in ('PCFIMB09','PCFIMB10')
)
  SELECT
  CURRENT DATE ,
  'FLUX RICHE TECHNIQUE NON ENVOYE' as "TYPE REJET",
   FRT.IDFLXITS,
   FRT.COTRITAB,
   FRT.IDENTGES,
   FRT.DTREFFLXITS,
   DATE (FRT.TSCRE) AS "DATE CREATION",
   TIME (FRT.TSCRE) AS "HEURE CREATION",
   COALESCE(FRT.COFAMEVTGES, '-') as COFAMEVTGES,
   FRT.COEVTGES,
   FRT.IDUECRET,
    VARCHAR(UEC.ID_TECH_UR_ADHR),
   ADH.ID_FONC_ADHR,
    VARCHAR(UEC.NO_ORD_ADHS),
    VARCHAR(UEC.NO_ORD_UEC),
   FRT.TYINS,
   FRT.NOINS,
   '' as COORI,
   '' as COOBJ,
   FRT.TYLIGCRE,
   FRT.COMODCALCRE,
   FRT.ANPER !!'-'!!
   FRT.COPERREF  ,
   FRT.COGAR,
   FRT.INATTPOI,
   FRT.COCPN,
   VARCHAR(FRT.MTFLXITS)  as MTFLXITS,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   tta.IDPGMAPT,
   tta.IDPGMERR,
   tta.LBANO,
   tta.NOMSG,
   tta.CORETPRI,
   FRT.IDUTICRE,
   FRT.IDPGMCRE,
   tta.IDPGM   ,
   tta.IDJOB   ,
   tta.IDCODINS,
   tta.LBERRCOM,
   tta.TYACT   ,
   tta.LBTAB001,
   tta.lbcri
  FROM T0P0.TCMREFT EFT
  INNER JOIN T0P0.TC9RFRT FRT ON FRT.IDFLXITS = EFT.IDFLXITS
  AND FRT.INTRTCRE = 'O'
  LEFT JOIN tempTableAno tta
     ON FRT.IDCODINS=TTA.IDCODINS
  LEFT JOIN T0P0.TAUEUEC UEC
      ON UEC.ID_TECH_PUBL_UEC = INT(FRT.IDUECRET)
  LEFT JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE EFT.DTENVFLX = '01.01.0001'
  UNION ALL
  SELECT
  CURRENT DATE,
  'FLUX RICHE FINANCIER NON ENVOYE' as "TYPE REJET",
   frf.IDFLXITS,
   frf.COTRITAB,
   frf.IDENTGES,
   frf.DTREFFLXITS,
   DATE (frf.TSCRE) AS "DATE CREATION",
   TIME (frf.TSCRE) AS "HEURE CREATION",
   COALESCE(frf.COFAMEVTGES, '-') as COFAMEVTGES,
   frf.COEVTGES,
   frf.IDUECRET,
    VARCHAR(UEC.ID_TECH_UR_ADHR),
   ADH.ID_FONC_ADHR,
    VARCHAR(UEC.NO_ORD_ADHS),
    VARCHAR(UEC.NO_ORD_UEC),
   frf.TYINS,
   frf.NOINS,
   FRF.COORI,
   FRF.COOBJ,
   '-' as TYLIGCRE,
   '-' as COMODCALCRE,
   frf.ANPER !!'-'!!
   frf.COPERREF  ,
   '-' as COGAR,
   '-' as INATTPOI,
   '-' as COCPN,
   VARCHAR(frf.MTFLXITS)  as MTFLXITS,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   '-' ,
   tta.IDPGMAPT,
   tta.IDPGMERR,
   tta.LBANO,
   tta.NOMSG,
   tta.CORETPRI,
   frf.IDUTICRE,
   frf.IDPGMCRE,
   tta.IDPGM   ,
   tta.IDJOB   ,
   tta.IDCODINS,
   tta.LBERRCOM,
   tta.TYACT   ,
   tta.LBTAB001,
   tta.lbcri
  FROM T0P0.TCMREFF EFF
  INNER JOIN T0P0.TC9RFRF FRF ON FRF.IDFLXITS = EFF.IDFLXITS
  AND FRF.INTRTCRE = 'O'
  LEFT JOIN tempTableAno tta
     ON FRF.IDCODINS=tta.IDCODINS
  LEFT JOIN T0P0.TAUEUEC UEC
      ON UEC.ID_TECH_PUBL_UEC = INT('0'||FRF.IDUECRET)
  LEFT JOIN T0P0.TRENADH ADH
    ON UEC.ID_TECH_UR_ADHR = ADH.ID_TECH_UR_ADHR
  WHERE EFF.DTENVFLX = '01.01.0001'
</code>