\\wpcci400\cfttrans\AG2R_Entreprise\PR\J0P0\LC\CRE
U:\Retraite\Reunica_Entreprise\PR\T0P0\LC\CRE
J2CFIX08
T8CFIX08
20170819_1087000-J0P0.J2CFIX08.REJETGEN.CTRLTCTTANO.csv
20170819_1086999-J0P0.J2CFIX08.REJETGEN.csv
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'