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 88 89 90 91 92 93
| SELECT LCOMCLI.LCKTNUMERO+'/'+LCOMCLI.LCKTLIGNE+'/'+LCOMCLI.LCKTPSF,
LCOMCLI.LCKTNUMERO+'/'+LCOMCLI.LCKTLIGNE+'/'+LCOMCLI.LCKTPSF+' - '+ARTICLE.ARCTLIB01 AS EXPLORATEUR,
LCOMCLI.LCCTSOLACC,
LCOMCLI.LCKTNUMERO,
LCOMCLI.LCKTLIGNE,
LCOMCLI.LCKTPSF,
LCOMCLI.LCCTCODE,
CLIENT.CLCTNOM,
LCOMCLI.LCCTCODART,
LCOMCLI.LCCTCOMART,
RTRIM(ARTICLE.ARCTLIB01)+' '+ARTICLE.ARCTLIB02 AS DESIGNATION,
LCOMCLI.LCCNQTECDE,
LCOMCLI.LCCNQTECON,
LCOMCLI.LCCNQTECDE - LCOMCLI.LCCNQTECON AS SOLDE_PSF,
COMP.BEKTNOCOMP,
COMP.BECTCODCOM,
COMP.DESCOMP,
COMP.BECNPREVU,
COMP.BECNREALIS,
COMP.BECNPREVU - COMP.BECNREALIS AS SOLDE_COMP,
TEMPAS.QTE,
/* calcul du solde du besoin engagé en quantité*/
(LCOMCLI.LCCNQTECDE - LCOMCLI.LCCNQTECON)*COMP.BECNPREVU/BECNQTECDE AS SOLDE_COMP_NON_CONSOMME,
COMP.BECTFAMCP,
COMP.ARCTCODFAM,
COMP.ARCTCOSFAM,
COMP.BECTMACORD,
COMP.BECTOPERAT,
COMP.BECTSAT,
COMP.BECJJRPREV,
COMP.BECJJRORDO,
COMP.BECTHRORDO,
COMP.BECTTOP02,
COMP.BECTTOPPLA,
DATEPART(WEEKDAY,CAST(COMP.BECJJRPREV AS DATE)) AS NUM_J,
CONVERT(VARCHAR(10),CAST(COMP.BECJJRPREV AS DATE),103) AS DELAI,
DATEPART(ISO_WEEK,COMP.BECJJRPREV) AS SEM,
DATEPART(MONTH,COMP.BECJJRPREV) AS MOIS,
DATEPART(YEAR,COMP.BECJJRPREV) AS ANNEE
FROM LCOMCLI
INNER JOIN ARTICLE ON LCOMCLI.LCKTSOC=ARTICLE.ARKTSOC AND LCOMCLI.LCCTCODART=ARTICLE.ARKTCODART AND LCOMCLI.LCCTCOMART=ARTICLE.ARKTCOMART
INNER JOIN CLIENT ON LCOMCLI.LCKTSOC=CLIENT.CLKTSOC AND LCOMCLI.LCCTCODE=CLIENT.CLKTCODE
INNER JOIN (SELECT BESOIN.BEKTNUMERO+'/'+BESOIN.BEKTLIGNE+'/'+BESOIN.BEKTINDPSF AS ID,
BESOIN.BEKTNOCOMP,
BESOIN.BECTCODEPF,
BESOIN.BECNQTECDE,
BESOIN.BECTCODCOM,
BESOIN.BECTCCCOMP,
RTRIM(ARTICLE.ARCTLIB01)+' '+ARTICLE.ARCTLIB02 AS DESCOMP,
BESOIN.BECNPREVU,
BESOIN.BECNREALIS,
BESOIN.BECTSAT,
BESOIN.BECJJRPREV,
BESOIN.BECJJRORDO,
BESOIN.BECTHRORDO,
BESOIN.BECTFAMCP,
BESOIN.BECTTYPCPT,
BESOIN.BECTMACORD,
BESOIN.BECTOPERAT,
ARTICLE.ARCTCODFAM,
ARTICLE.ARCTCOSFAM,
BESOIN.BECTTOP02,
BESOIN.BECTTOPPLA
FROM BESOIN
INNER JOIN ARTICLE ON BESOIN.BEKTSOC=ARTICLE.ARKTSOC AND BESOIN.BECTCODCOM=ARTICLE.ARKTCODART AND BESOIN.BECTCCCOMP=ARTICLE.ARKTCOMART
WHERE BESOIN.BEKTSOC ='100'
AND BESOIN.BECTSAT NOT IN ('S','T')
AND BESOIN.BECTCODCOM LIKE '2%'
AND ARTICLE.ARCTSECART IN (@SECTION)
AND ARTICLE.ARCTCODFAM IN (@FAMILLE)
AND ARTICLE.ARCTCOSFAM IN (@SFAMILLE)
AND BESOIN.BECTCODCOM IN (@OPE)
) AS COMP ON LCOMCLI.LCKTNUMERO+'/'+LCOMCLI.LCKTLIGNE+'/'+LCOMCLI.LCKTPSF=COMP.ID
LEFT OUTER JOIN (SELECT TEMPAS.BEKTNUMERO+'/'+TEMPAS.BEKTLIGNE+'/'+TEMPAS.BEKTINDPSF AS ID,
TEMPAS.BEKTNOCOMP,
SUM(TEMPAS.BECNQTEPRO) AS QTE
FROM TEMPAS
WHERE TEMPAS.BEKTSOC='100'
GROUP BY TEMPAS.BEKTNUMERO+'/'+TEMPAS.BEKTLIGNE+'/'+TEMPAS.BEKTINDPSF, TEMPAS.BEKTNOCOMP
) AS TEMPAS ON TEMPAS.ID=LCOMCLI.LCKTNUMERO+'/'+LCOMCLI.LCKTLIGNE+'/'+LCOMCLI.LCKTPSF AND TEMPAS.BEKTNOCOMP=COMP.BEKTNOCOMP
WHERE LCOMCLI.LCKTSOC='100'
AND LCOMCLI.LCCTNATURE NOT IN ('7')
AND LCOMCLI.LCCTTYPE <> 'S'
AND LCOMCLI.LCCTSOLACC NOT IN ('S','T')
AND LCOMCLI.LCCTSOLACE NOT IN ('S','T')
ORDER BY COMP.BECJJRPREV,LCOMCLI.LCKTNUMERO,LCOMCLI.LCKTLIGNE,LCOMCLI.LCKTPSF |
Partager