1 2 3
| SELECT Nz(E.ED,"") AS ED, Nz(E.LOC,"") AS LOC, Nz(E.CIE,"") AS CIE, Nz(E.PAYS,"") AS PAYS, Nz(E.MONNAIE,"") AS MONNAIE, Nz(E.BANQUE,"") AS BANQUE, Nz(E.GUICHET,"") AS GUICHET, IIf(Left(Nz(E.COMPTE,""),2)="ZT",Nz(E.COMPTE,""),"ZT" & Nz(E.COMPTE,"")) AS COMPTE, Nz(E.IDENTIFIANT,"") AS IDENTIFIANT, Nz(E.ORIGINE,"") AS ORIGINE, Nz(E.DEBIT,0) AS DEBIT, Nz(E.CREDIT,0) AS CREDIT, Nz(E.DEBIT,0)-Nz(E.CREDIT,0) AS SOLDE, Nz(CLE_RAPPRO.FLAG,"") AS EMARGEMENT, "" AS OBSERVATIONS, "" AS ECART, "" AS [ECART POSITIF/NEGATIF], IIf([AV] & [C] & [D] & [F] & [O] & [V]="","N/D",IIf([AV] & [C] & [D] & [F] & [O] & [V] Not In ('AV','C','D','F','O','V'),"DBL: " & [AV] & [C] & [D] & [F] & [O] & [V],[AV] & [C] & [D] & [F] & [O] & [V] & Left(E.ORIGINE,1))) AS [TYPE DE SUSPENS], "" AS REGULARISATION, Abs(Nz(E.DEBIT,0)-Nz(E.CREDIT,0)) AS [VALEUR ABSOLUE], Left(Nz(E.DtValeur,""),4) AS ANNEE, Nz(E.CODE_OPERATION,"") AS CODE_OPERATION, Nz(E.LIB_OPERATION,"") AS LIB_OPERATION, Mid(Nz(E.DtValeur,""),3,2) & Mid(Nz(E.DtValeur,""),6,2) & Mid(Nz(E.DtValeur,""),9,2) AS [DATE VALEUR], Mid(Nz(E.DtOperation,""),3,2) & Mid(Nz(E.DtOperation,""),6,2) & Mid(Nz(E.DtOperation,""),9,2) AS [DATE OPERATION], Mid(Nz(E.DtCompta,""),3,2) & Mid(Nz(E.DtCompta,""),6,2) & Mid(Nz(E.DtCompta,""),9,2) AS [DATE COMPTABLE], Nz(E.ECRITURE,"") AS ECRITURE, Nz(E.LIGNE,"") AS LIGNE, Nz(E.ARRANGEMENT,"") AS ARRANGEMENT, Nz(E.EXON,"") AS EXON, IIf((E.LIB_OPERATION Like "*AC*" And E.LIB_OPERATION Not Like "*[a-zA-Z;/]AC*") Or E.LIB_OPERATION Like "*AC[0-9]*" Or E.LIB_OPERATION Like "*RACH*" Or (E.LIB_OPERATION Like "*AT*" And E.LIB_OPERATION Not Like "*[a-zA-Z;/]AT*") Or E.LIB_OPERATION Like "*AT[0-9]*" Or E.LIB_OPERATION Like "*VT*" Or E.LIB_OPERATION Like "*VC*" Or E.LIB_OPERATION Like "*E01*" Or E.LIB_OPERATION Like "*AHM*" Or E.LIB_OPERATION Like "*VENTE*" Or E.LIB_OPERATION Like "*R*SICAV*" Or E.LIB_OPERATION Like "*S*SICAV*" Or E.LIB_OPERATION Like "*SOUS[c;C; ]*" Or E.LIB_OPERATION Like "*SOU *" Or E.LIB_OPERATION Like "*GRE A GRE*" Or E.LIB_OPERATION Like "*NEGO*" Or E.LIB_OPERATION Like "*COMPTANT*" Or (E.LIB_OPERATION Like "*CPT*" And E.LIB_OPERATION Like "*CPT[a-zA-Z]*") Or E.LIB_OPERATION Like "*RETRAIT*" And E.LIB_OPERATION Not Like "*FRAIS*" And E.LIB_OPERATION Not Like "*VIR*","AV","") AS AV, IIf((E.LIB_OPERATION Like "*ERE*" And E.LIB_OPERATION Not Like "*[a-zA-Z0-9]ERE*") Or E.LIB_OPERATION Like "*I01*" Or E.LIB_OPERATION Like "*I02*" Or E.LIB_OPERATION Like "*I04*" Or E.LIB_OPERATION Like "*I05*" Or E.LIB_OPERATION Like "*[a;e;A;E]PH*" Or E.LIB_OPERATION Like "*REVENU*" Or E.LIB_OPERATION Like "*SOULTE*" Or E.LIB_OPERATION Like "*COUPON*" Or E.LIB_OPERATION Like "*CPS*" Or E.LIB_OPERATION Like "*CPON*" Or E.LIB_OPERATION Like "*COUP *" Or E.LIB_OPERATION Like "*DIVIDENDE*" And E.LIB_OPERATION Not Like "*OST*","C","") AS C, IIf((E.LIB_OPERATION Like "*COMM*" Or E.LIB_OPERATION Like "*COM *" Or E.LIB_OPERATION Like "*OP*CASH*" Or E.LIB_OPERATION Like "*CDT*" Or (E.LIB_OPERATION Like "*FRAIS*" And E.LIB_OPERATION Not Like "*FRAIS DE*") Or (E.LIB_OPERATION Like "*FRS*" And E.LIB_OPERATION Not Like "*GEST*") Or (E.LIB_OPERATION Like "*DG*" And E.LIB_OPERATION Not Like "*[a-c;e-zA-C;E-Z]DG*" And E.LIB_OPERATION Not Like "*DG[a-zA-Z]*") Or (E.LIB_OPERATION Like "*GARDE*" And E.LIB_OPERATION Not Like "*[a-zA-Z]GARDE*" And E.LIB_OPERATION Not Like "*GARDE[a-zA-Z]*") Or E.LIB_OPERATION Like "*RISTOU*" Or E.LIB_OPERATION Like "*INTERET*" Or E.LIB_OPERATION Like "*FACTU*" Or E.LIB_OPERATION Like "*AGIO*" Or E.LIB_OPERATION Like "*REFERE*") And E.LIB_OPERATION Not Like "*[a-zA-Z]COM*" And E.LIB_OPERATION Not Like "*COMM[a-h;j-zA-H;J-Z]*" And E.LIB_OPERATION Not Like "*OD[M;T]*" And E.LIB_OPERATION Not Like "*VIR*","D","") AS D, IIf(E.LIB_OPERATION Like "*FDG*" Or E.LIB_OPERATION Like "*FHG*" Or E.LIB_OPERATION Like "*FRAIS*GEST*" Or E.LIB_OPERATION Like "*FRAIS DE*" Or E.LIB_OPERATION Like "*FGE*" Or E.LIB_OPERATION Like "*FG *" Or E.LIB_OPERATION Like "*FRS*GEST*" And E.LIB_OPERATION Not Like "*MANDAT*","F","") AS F, IIf((E.LIB_OPERATION Like "*CHM*" Or E.LIB_OPERATION Like "*RTC*" Or E.LIB_OPERATION Like "*RCT*" Or E.LIB_OPERATION Like "*RO[0-9]*" Or E.LIB_OPERATION Like "* RO/*" Or (E.LIB_OPERATION Like "*OST*" And E.LIB_OPERATION Not Like "*OST[a-zA-Z]*" And E.LIB_OPERATION Not Like "*[a-zA-Z]OST*") Or E.LIB_OPERATION Like "*OPA*" Or E.LIB_OPERATION Like "* DA[0-9_]*" Or E.LIB_OPERATION Like "* DS[0-9_]*" Or E.LIB_OPERATION Like "*PRIM*DIV*") And E.LIB_OPERATION Not Like "*VENTE*","O","") AS O, IIf(E.LIB_OPERATION Like "*OD[M;T]*" Or E.LIB_OPERATION Like "*TDF*" Or E.LIB_OPERATION Like "*ADV*" Or E.LIB_OPERATION Like "*VDC*" Or E.LIB_OPERATION Like "*INVEST*" Or E.LIB_OPERATION Like "*DESINV*" Or E.LIB_OPERATION Like "*I/D*" Or (E.LIB_OPERATION Like "*VIR*" And E.LIB_OPERATION Not Like "*[a-zA-Z]VIR*" And E.LIB_OPERATION Not Like "*VIR[a-d;f-s;u-zA-D;F-S;U-Z]*") Or E.LIB_OPERATION Like "*VRT*" Or E.LIB_OPERATION Like "*FPJ*" Or E.LIB_OPERATION Like "*PENSION*" Or E.LIB_OPERATION Like "*RECU*" Or E.LIB_OPERATION Like "*TRESO*" And E.LIB_OPERATION Not Like "*ERE/*" And E.LIB_OPERATION Not Like "*EREU/*" And E.LIB_OPERATION Not Like "*FRAIS*" And E.LIB_OPERATION Not Like "*FDG*" And E.LIB_OPERATION Not Like "*FG*","V","") AS V, S.ID_NOSTRO
FROM (T_EXTRCB AS E INNER JOIN T_SUSPENS AS S ON E.ID_EXTRCB = S.ID_EXTRCB) LEFT JOIN RCB_EMARGEMENT_SUSPENS AS CLE_RAPPRO ON E.ID_EXTRCB = CLE_RAPPRO.ID_EXTRCB
WHERE (((S.ID_NOSTRO)=[@ID_NOSTRO]) AND ((S.ID_SUSPENS)>=[@LimiteMin] And (S.ID_SUSPENS)<[@LimiteMax])); |
Partager