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 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109
|
WITH TABLE_MAITREPYLOS( vhcod, vhcateg, vhptac,peimmat, vhimmat,trnom2,trcod,vhordre,penum,pecprod,
penom, peprenom, pezont, ztcod,ztpxsr, pepdsb, petare, pepdsn,PNetpaye,Tarif,Schge,pedat,peetc,
peagri,peparc, petrsp,trcp, trvil, petyp, trnom,tradr1,tradr2,trsap, prnom,pytxtva1,pytxtva2,
pevalid,petrans,trtva,NumComm, Montant_HT, Montant_TVA, Montant_TTC) AS
(
SELECT
VEHICULE.vhcod, VEHICULE.vhcateg, VEHICULE.vhptac, MENSUTRA.peimmat, VEHICULE.vhimmat,
TRANSPORTEUR.trnom2, TRANSPORTEUR.trcod,VEHICULE.vhordre, MENSUTRA.penum, MENSUTRA.pecprod,
MENSUTRA.penom, MENSUTRA.peprenom, MENSUTRA.pezont, ZONETAR.ztcod,
ZONETAR.ztpxsr, MENSUTRA.pepdsb, MENSUTRA.petare, MENSUTRA.pepdsn,
(
CASE
WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100))
THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare
ELSE MENSUTRA.pepdsn
END) AS PNetpaye,
(
CASE
WHEN (VEHICULE.vhcateg = 'CR')
THEN ZONETAR.ztpxcr
ELSE ZONETAR.ztpxsr
END) AS TarifOld, MENSUTRA.peprix AS Tarif,
(
CASE
WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100))
THEN (MENSUTRA.pepdsb - (2 + VEHICULE.vhptac) * 100)
ELSE 0
END) AS Schge,
MENSUTRA.pedat, MENSUTRA.peetc, MENSUTRA.peagri, MENSUTRA.peparc, MENSUTRA.petrsp,
TRANSPORTEUR.trcp, TRANSPORTEUR.trvil, MENSUTRA.petyp, TRANSPORTEUR.trnom, TRANSPORTEUR.tradr1,
TRANSPORTEUR.tradr2, TRANSPORTEUR.trsap, PRODUIT.prnom,PAYS.pytxtva1, PAYS.pytxtva2,
MENSUTRA.pevalid, MENSUTRA.petrans,TRANSPORTEUR.trtva,
(
CASE
WHEN ([petyp] = 'ENTREE')
THEN trNumBCA
ELSE trNumBCE
END) AS NumComm,
(
(
(
CASE
WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100))
THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare
ELSE MENSUTRA.pepdsn
END)/100)* MENSUTRA.peprix) AS Montant_HT,
(
(
(
(
CASE
WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100))
THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare
ELSE MENSUTRA.pepdsn
END)/100)* MENSUTRA.peprix)*[pytxtva2]/100) AS Montant_TVA,
(
(
(
(
CASE
WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100))
THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare
ELSE MENSUTRA.pepdsn END)/100)* MENSUTRA.peprix) +
(
(
(
CASE
WHEN (MENSUTRA.pepdsb > ((VEHICULE.vhptac + 2) * 100))
THEN ((VEHICULE.vhptac + 2) * 100) - MENSUTRA.petare
ELSE MENSUTRA.pepdsn
END)/100)* MENSUTRA.peprix)*[pytxtva2]/100) AS Montant_TTC
FROM
(
(
(
MENSUTRA
LEFT JOIN
(VEHICULE LEFT JOIN TRANSPORTEUR
ON VEHICULE.vhcod = TRANSPORTEUR.trcod)
ON MENSUTRA.peimmat = VEHICULE.vhimmat)
LEFT JOIN ZONETAR
ON MENSUTRA.pezont = ZONETAR.ztcod)
LEFT JOIN PRODUIT
ON MENSUTRA.pecprod = PRODUIT.prcod)
LEFT JOIN PAYS
ON TRANSPORTEUR.trpays = PAYS.pycod
WHERE (
(
(MENSUTRA.petrsp) <> '00000099'
AND (MENSUTRA.petrsp) <> '00000098')
AND ((MENSUTRA.pevalid) <> 0)
);
)
SELECT vhcod, vhcateg, vhptac,peimmat, vhimmat,trnom2,trcod,vhordre,penum,pecprod,
penom, peprenom, pezont, ztcod,ztpxsr, pepdsb, petare, pepdsn,PNetpaye,Tarif,Schge,pedat,peetc,
peagri,peparc, petrsp,trcp, trvil, petyp, trnom,tradr1,tradr2,trsap, prnom,pytxtva1,pytxtva2,
pevalid,petrans,trtva,NumComm, Montant_HT, Montant_TVA, Montant_TTC,
(Montant_HT - Montant_TVA) AS Montant_HT_TVA
FROM TABLE_MAITREPYLOS |
Partager