1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87
| SELECT
C.CODEENTITY, C.CIVILITE, C.NOM, C.PRENOM, C.REFERENCE, C.COMMENTAIRE,
CTC.TELEPHONEPORT, CTC.TELEPHONEFIXE, CTC.TELEPHONE3, CTC.TELEPHONE4, CTC.TELEPHONE5, CTC.FAX, CTC.MAIL,
CA.APPT, CA.VOIE, CA.BP, CA.CP, CA.VILLE, CA.PAYS,
R.CODEBANQUE, R.CODEGUICHET, R.NUMCOMPTE, R.CLEFRIB, R.BANQUE,
I.CODEPAYS, I.CLEFCONTROLE, I.BBAN, I.BIC,
S.CODEENTITY, S.NOM, S.SIRET, S.RCS, S.APE,
SC.TELEPHONEPORT, SC.MAIL,
SA.APPT, SA.VOIE, SA.BP, SA.CP, SA.VILLE, SA.PAYS,
B.NUM, B.LONGUEUR, B.LARGEUR, B.PORTE, B.ISOLATION, B.MEZZANINE, B.LOYER, B.COMMENTAIRE,
L.CODE, LB.DATEDEBUT, LB.DATEFIN, LB.REGLEMENT, LB.ECHEANCE, LB.PRIXLOCATION, LB.CAUTION, LB.ENCAISSEMENTCAUTION,
L.CODEPORTAIL, L.COURRIERFACTURE, L.MAILFACTURE, L.NUMLOC, L.CODESITE,
F.CODE, F.NUM, F.DATEFACTURE,
LF.CODELIGNE, LF.LIBELLE, LF.MONTANTBRUT, LF.TVA, LF.PRESTATION, LF.REGLEMENT,
CX.CODEENTITY, CX.CIVILITE, CX.NOM, CX.PRENOM, CX.REFERENCE, CX.COMMENTAIRE,
CTCX.TELEPHONEPORT, CTCX.TELEPHONEFIXE, CTCX.TELEPHONE3, CTCX.TELEPHONE4, CTCX.TELEPHONE5, CTCX.FAX, CTCX.MAIL,
CAX.APPT, CAX.VOIE, CAX.BP, CAX.CP, CAX.VILLE, CAX.PAYS,
RX.CODEBANQUE, RX.CODEGUICHET, RX.NUMCOMPTE, RX.CLEFRIB, RX.BANQUE,
IX.CODEPAYS, IX.CLEFCONTROLE, IX.BBAN, IX.BIC,
SX.CODEENTITY, SX.NOM, SX.SIRET, SX.RCS, SX.APE,
SCX.TELEPHONEPORT, SCX.MAIL,
SAX.APPT, SAX.VOIE, SAX.BP, SAX.CP, SAX.VILLE, SAX.PAYS,
BX.NUM, BX.LONGUEUR, BX.LARGEUR, BX.PORTE, BX.ISOLATION, BX.MEZZANINE, BX.LOYER, BX.COMMENTAIRE,
LX.CODE, LBX.DATEDEBUT, LBX.DATEFIN, LBX.REGLEMENT, LBX.ECHEANCE, LBX.PRIXLOCATION, LBX.CAUTION, LBX.ENCAISSEMENTCAUTION,
LX.CODEPORTAIL, LX.COURRIERFACTURE, LX.MAILFACTURE, LX.NUMLOC, LX.CODESITE
FROM
LMBLOCATION L INNER JOIN LMBLOCATION_BOX LB ON LB.CODELOCATION = L.CODE
INNER JOIN LMBBOX B ON B.NUM = LB.NUMBOX
INNER JOIN LMBSITE S ON S.CODEENTITY = B.CODESITE AND S.CODEENTITY = L.CODESITE
INNER JOIN LMBENTITYINTERFACE EIS ON EIS.CODE = S.CODEENTITY
INNER JOIN LMBADRESSE SA ON SA.CODE = EIS.CODEADRESSE
INNER JOIN LMBCONTACT SC ON SC.CODE = EIS.CODECONTACT
INNER JOIN LMBCLIENT C ON C.CODEENTITY = L.CODECLIENT
INNER JOIN LMBENTITYINTERFACE EIC ON EIC.CODE = C.CODEENTITY
INNER JOIN LMBADRESSE CA ON CA.CODE = EIC.CODEADRESSE
INNER JOIN LMBCONTACT CTC ON CTC.CODE = EIC.CODECONTACT
LEFT OUTER JOIN LMBRIB R ON R.CODEENTITY = EIC.CODE
LEFT OUTER JOIN LMBIBAN I ON I.CODEENTITY = EIC.CODE
LEFT OUTER JOIN LMBFACTURERUAUDIN F INNER JOIN LMBFACTURELIGNEINTERFACE_RUAUDIN FLI ON F.CODE = FLI.CODERUAUDIN
INNER JOIN LMBLIGNEFACTURE LF ON FLI.CODE = LF.CODELIGNE
LEFT OUTER JOIN LMBLOCATION LX ON LF.CODELOCATION = LX.CODE
LEFT JOIN LMBLOCATION_BOX LBX ON LBX.CODELOCATION = LX.CODE
LEFT JOIN LMBBOX BX ON BX.NUM = LBX.NUMBOX AND BX.CODESITE = LBX.CODESITE
INNER JOIN LMBSITE SX ON SX.CODEENTITY = LBX.CODESITE
INNER JOIN LMBENTITYINTERFACE EISX ON EISX.CODE = SX.CODEENTITY
INNER JOIN LMBADRESSE SAX ON SAX.CODE = EISX.CODEADRESSE
INNER JOIN LMBCONTACT SCX ON SCX.CODE = EISX.CODECONTACT
INNER JOIN LMBCLIENT CX ON CX.CODEENTITY = F.CODECLIENT
INNER JOIN LMBENTITYINTERFACE EIC ON EIC.CODE = CX.CODEENTITY
INNER JOIN LMBADRESSE CAX ON CAX.CODE = EICX.CODEADRESSE
INNER JOIN LMBCONTACT CTCX ON CTCX.CODE = EICX.CODECONTACT
LEFT OUTER JOIN LMBRIB RX ON RX.CODEENTITY = EICX.CODE
LEFT OUTER JOIN LMBIBAN IX ON IX.CODEENTITY = EICX.CODE
WHERE
S.CODEENTITY = '11111111-1111-1111-1111-bbbbbbbbbbbb'
AND NOT EXISTS (
SELECT
*
FROM
LMBFACTURERUAUDIN FY INNER JOIN LMBFACTURELIGNEINTERFACE_RUAUDIN FLIY ON FY.CODE = FLIY.CODERUAUDIN
INNER JOIN LMBLIGNEFACTURE LFY ON FLIY.CODE = LFY.CODELIGNE
INNER JOIN LMBLOCATION LY ON LFY.CODELOCATION = LY.CODE
INNER JOIN LMBLOCATION_BOX LBY ON LBY.CODELOCATION = LY.CODE AND LBY.NUMBOX = LFY.NUMBOX
WHERE
LY.CODE = L.CODE AND LBY.NUMBOX = B.NUM AND DATEDIFF(MONTH, F.DATEFACTURE, 2012/06/20) < LB.ECHEANCE
)
AND EXISTS (
SELECT
*
FROM
LMBFACTURERUAUDIN FW INNER JOIN LMBFACTURELIGNEINTERFACE_RUAUDIN FLIW ON FW.CODE = FLIW.CODERUAUDIN
INNER JOIN LMBLIGNEFACTURE LFW ON FLIW.CODE = LFW.CODELIGNE
INNER JOIN LMBLOCATION LW ON LFW.CODELOCATION = LW.CODE
WHERE
LW.CODE = L.CODE AND FW.CODE = F.CODE
)
AND F.DATEFACTURE = (
SELECT
MAX FZ.DATEFACTURE
FROM
LMBFACTURERUAUDIN FZ INNER JOIN LMBFACTURELIGNEINTERFACE_RUAUDIN FLIZ ON FZ.CODE = FLIZ.CODERUAUDIN
INNER JOIN LMBLIGNEFACTURE LFZ ON FLIZ.CODE = LFZ.CODELIGNE
WHERE
LFZ.CODELOCATION = L.CODE
)
ORDER BY
LB.DATEDEBUT ASC, F.NUM ASC |
Partager