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
|
SELECT LTRIM(RTRIM(TGPTOVENTA.DESCRIPCORTO)) AS 'Soc/Lieu',
TCVEH.NUMSTOCK AS 'No Stock',
(CAST(CONVERT(CHAR(12), '1-1-2050', 103) AS DATETIME) -
CONVERT(CHAR(12), TCVEH.FECCOMPRA, 103)) AS 'Nb Jrs de Stock',
TCVEH.VENDEDORRECOMPRA AS 'Acheteur',
(SELECT TGMARCA.DESCRIP
FROM TGMARCA
WHERE TGMARCA.MARCA = TCVEH.MARCA) AS 'Marque',
(SELECT LTRIM(RTRIM(TGVERSION.DESCRIP))
FROM TGVERSION
WHERE TGVERSION.MARCA = TCVEH.MARCA
AND TGVERSION.MODELO = TCVEH.MODELO
AND TGVERSION.VERSION = TCVEH.VERSION) AS 'Modele/Version',
REPLACE(TCVEH.COMBUSTIBLE, 'NULL', '') AS 'Energie',
CONVERT(CHAR(12), TCVEH.FECMATRICPRIMERA, 103) AS 'Date Mec',
TCVEH.KM AS 'Kms',
(SELECT SUM(COMPRAPRECIO * CDAD)
FROM TCVEHOPCION
WHERE TCVEHOPCION.EMP = TCVEH.EMP
AND TCVEHOPCION.NUMINTERNO = TCVEH.NUMINTERNO
AND TCVEHOPCION.TIPOLINEA = 'A') AS "Tot Achat",
(SELECT RTRIM(LTRIM(TCCOLOR.DESCRIP))
FROM TCCOLOR
WHERE TCCOLOR.MARCA = TCVEH.MARCA
AND TCCOLOR.COLOR = TCVEH.COLOR) AS 'Couleur',
REPLACE(TCVEH.POTENCIAFISCAL, 'NULL', '') AS 'Pu Fisc',
LTRIM(RTRIM(REPLACE(TCVEH.MATRIC, 'NULL', ''))) AS 'Immat',
TCVEH.AUTCIRCULACION AS 'Type Mine',
TCVEH.CHASIS AS 'N° Chassis',
(SELECT TCVODESTINO.DESCRIP
FROM TCVODESTINO
WHERE TCVODESTINO.CODIGO = TCVEH.VODESTINO) AS 'Destination VO',
--'Vendu' = CASE tcVeh.Status WHEN 30 THEN 'OUI' ELSE 'NON' END,
CASE TCVEH.RESERVADO
WHEN 1 THEN
'OUI'
ELSE
'NON'
END AS 'Reservé',
CASE
WHEN TCVEH.REBU <> 1 THEN
TCVEHOPCION.VENTAPRECIO *
(1 + ((SELECT PORCEN
FROM TGIVAPOR
WHERE TCVEHOPCION.IVAVENTA = TGIVAPOR.CODIGO) / 100))
ELSE
TCVEHOPCION.VENTAPRECIO
END TCVEHOPCIONGENERIC.DESCRIP AS 'Option',
REPLACE(REPLACE(REPLACE(TCVEH.OBSER, CHAR(13), ''), CHAR(10), ''),
CHAR(9),
' ') AS 'Remarque'
FROM TCVEH, TGEMPRESA, TGPTOVENTA, TCVEHOPCION, TCVEHOPCIONGENERIC
>>> WHERE VEH.NUM_INTERNE = OPT.NUM_INTERNE
AND EXISTS
(SELECT OPT2.CODE
FROM TCVEHOPCIONGENERIC AS OPT2
WHERE OPT2.NUM_INTERNE = OPT.NUM_INTERNE
AND OPT2.CODE = OPT.CODE) <<<
AND TCVEHOPCION.TIPOLINEA = 'A'
AND TCVEH.EMP = TCVEHOPCION.EMP
AND TCVEH.NUMINTERNO = TCVEHOPCION.NUMINTERNO
AND TGEMPRESA.EMP = TCVEH.EMP
AND TGPTOVENTA.PUNTOVENTA = TCVEH.PUNTOVENTA
--AND ((tcveh.baja = 0) or (tcveh.baja is null))
AND (((TCVEH.BAJA = 0) OR (TCVEH.BAJA IS NULL)) OR
(CAST(CONVERT(CHAR(12), TCVEH.BAJAFEC, 103) AS DATETIME) <=
CAST(CONVERT(CHAR(12), '1-1-2050', 103) AS DATETIME))) |
Partager