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
|
SELECT
tcPresupuesto.Emp as "n societe",
tcVeh.Chasis as "vin",
tgMarca.Descrip as "marque",
Replace(tgModelo.Descrip, ',', '.') as "modele",
Replace(tgVersion.Descrip, ',', '.') as "version",
Replace(tcVeh.Version , ',', '.') as "code_version",
'' as "carrosserie",
tcVeh.Combustible "Carburant",
tcVeh.Puertas as "Porte",
CONVERT(varchar,tcVeh.FecPedido,103) as "Date_Commande",
CONVERT(varchar,tcVeh.FecEntradaFisica,103) as "date_entree_physique",
CONVERT(varchar,tcPresupuesto.FechaFactura,103) as "datefacture",
REPLACE(REPLACE(REPLACE(Replace(LTRIM(RTRIM(REPLACE(REPLACE(tcvehOpcion.Descrip, CHAR(13), ' '), CHAR(10), ' '))), ',', ' '),'=',' '),'-',' '),'"','') as "Libelles_Options",
LTRIM(RTRIM(tcvehOpcion.Opcion)) as "code_option",
(case when tcveh.esdemo = 1 then 'VD'
when tcveh.eskm0 = 1 then 'V0km'
else 'VN'
end) as typevehicule,
case
LTRIM(RTRIM(tgCliente.PerJuridica)) WHEN '1' then 'Societe' else 'Particulier' end as "type_client",
tcPresupuesto.FinanImporte as "Mt_financement",
(COALESCE(tcPresupuesto.Subtotal16,0)) As "Mt_reprise",
'' as "marque_veh_vo",'' as "modele_veh_vo",
(COALESCE(tcPresupuesto.Subtotal1, 0)) AS "Prix_vente_depart_HT",
(COALESCE(tcPresupuesto.Subtotal3, 0)) AS "Option2_HT",
(COALESCE(tcPresupuesto.Subtotal7, 0) * (-1)) AS "Remise_HT",
'' as "transfertdemargevo",
(COALESCE(tcPresupuesto.Subtotal8, 0)) as "totalfactureclient_HT"
FROM tcPresupuesto
LEFT OUTER JOIN tgVendedor ON tcPresupuesto.Emp = tgVendedor.Emp AND tcPresupuesto.Vendedor = tgVendedor.Vendedor
LEFT OUTER JOIN tgCliente ON tcPresupuesto.ClienteFac = tgCliente.Codigo
LEFT OUTER JOIN tcVeh ON tcPresupuesto.Emp = tcVeh.Emp AND tcPresupuesto.Vehiculo = tcVeh.NumInterno
LEFT OUTER JOIN tgMarca ON tcPresupuesto.Marca = tgMarca.Marca
LEFT OUTER JOIN tgModelo ON tcPresupuesto.Modelo = tgModelo.Modelo AND tcPresupuesto.Marca = tgModelo.Marca
LEFT OUTER JOIN tgVersion ON tcPresupuesto.Marca = tgVersion.Marca AND tcPresupuesto.Modelo = tgVersion.Modelo AND tcPresupuesto.Version = tgVersion.Version
LEFT OUTER JOIN tcTapiz ON tcPresupuesto.Marca = tcTapiz.Marca AND tcPresupuesto.Tapiz = tcTapiz.Tapiz
LEFT OUTER JOIN tcColor ON tcPresupuesto.Marca = tcColor.Marca AND tcPresupuesto.Color = tcColor.Color
LEFT OUTER JOIN tcGarantia ON tcGarantia.Marca = '*' AND tcGarantia.Garantia = tcPresupuesto.Garantia
LEFT OUTER JOIN tgCategoria ON tcPresupuesto.Categoria = tgCategoria.Categoria
LEFT OUTER JOIN tcVehOpcion ON tcPresupuesto.Emp = tcVehOpcion.Emp AND tcPresupuesto.Vehiculo = tcVehOpcion.NumInterno AND tcVehOpcion.TipoLinea <> 'A'
LEFT OUTER JOIN tgClienteFac ON tcPresupuesto.Emp = tgClienteFac.Emp AND tcPresupuesto.ClienteFac = tgClienteFac.Codigo
LEFT OUTER JOIN tgEmpresa ON tcPresupuesto.Emp = tgEmpresa.Emp
WHERE
( tcPresupuesto.EsVO = 0) AND
( tcPresupuesto.Status <> 50 ) AND
((tcPresupuesto.PasoFasePresupuesto = 9) OR (tcPresupuesto.PasoFaseExpediente = 9) OR (tcPresupuesto.PasoFaseVenta = 1) OR (tcPresupuesto.PasoFaseCierre = 9)) AND
(tcPresupuesto.FechaFactura Between '07-01-2013 00:00:00.000' AND '07-01-2013 23:59:59.000')
ORDER BY tcvehopcion.tipolinea
UNION
SELECT
tcVeh.Emp as "n societe",
case when tcVeh.Chasis = '' then tcveh.numpedidofab else tcVeh.Chasis end as "vin",
tgMarca.Descrip as "marque",
Replace(tgModelo.Descrip, ',', '.') as "modele",
Replace(tgVersion.Descrip, ',', '.') as "version",
Replace(tcVeh.Version , ',', '.') as "code_version",
'' as "carrosserie",
tcVeh.Combustible "Carburant",
tcVeh.Puertas as "Porte",
CONVERT(varchar,tcVeh.FecPedido,103) as "Date_Commande",
CONVERT(varchar,tcVeh.FecEntradaFisica,103) as "date_entree_physique",
'' as "datefacture",
REPLACE(REPLACE(REPLACE(Replace(LTRIM(RTRIM(REPLACE(REPLACE(tcvehOpcion.Descrip, CHAR(13), ' '), CHAR(10), ' '))), ',', ' '),'=',' '),'-',' '),'"','') as "Libelles_Options",
LTRIM(RTRIM(tcvehOpcion.Opcion)) as "code_option",
(case when tcveh.esdemo = 1 then 'VD'
when tcveh.eskm0 = 1 then 'V0km'
else 'VN'
end) as typevehicule,
case
LTRIM(RTRIM(tgCliente.PerJuridica)) WHEN '1' then 'Societe' else 'Particulier' end as "type_client",
'0' as "Mt_financement",
'0' As "Mt_reprise",
'' as "marque_veh_vo",'' as "modele_veh_vo",
tcvehOpcion.VentaPrecio AS "Prix_vente_depart_HT",
'0' AS "Option2_HT",
'0' AS "Remise_HT",
'0' as "transfertdemargevo",
'0' as "totalfactureclient_HT"
FROM tcVeh
left outer join tgEmpresa on tcVeh.Emp = tgEmpresa.Emp
left outer join TgMarca on tcveh.Marca = tgMarca.Marca
left outer join tgModelo on tcveh.Marca = tgModelo.Marca and tcveh.Modelo = tgModelo.Modelo
left outer join tgVersion on tcveh.Marca = tgVersion.Marca and tcveh.Modelo = tgVersion.Modelo and tcveh.Version = tgVersion.Version
left outer join tgPtoVenta on tcVeh.Emp = tgPtoVenta.Emp and tcVeh.PuntoVenta = tgPtoVenta.PuntoVenta
left outer join tccolor on tcveh.marca = tccolor.marca and tcveh.color = tccolor.color
left outer join tctapiz on tcveh.marca = tctapiz.marca and tcveh.tapiz = tctapiz.tapiz
left outer join tgVendedor on tcveh.Emp = tgVendedor.Emp and tcveh.reservaVendedor = tgVendedor.Vendedor
left outer join tgcliente on tcveh.reservaCliente = tgCliente.Codigo
left outer join tcvehopcion on tcveh.emp = tcvehopcion.emp and tcveh.numinterno = tcvehopcion.numinterno
left outer join tcvehsubstatus on tcveh.status=tcvehsubstatus.status and tcveh.substatus=tcvehsubstatus.substatus
left outer join tcubicacion on tcveh.emp = tcubicacion.emp and tcveh.ubicacion = tcubicacion.ubicacion
left outer join tgcategoria on tcveh.categoria = tgcategoria.categoria
LEFT OUTER JOIN ttCortesiaCategoria ON ttCortesiaCategoria.CortesiaCategoria = tcVeh.CortesiaCategoria
WHERE
tcveh.EsVO = 0 and
tcveh.status < 30 and
tcveh.status > 0 and
tcvehsubstatus.substatus in ('11','21')
and (Replace(tgModelo.Descrip, ',', '.')='B-MAX')
ORDER BY tcvehopcion.tipolinea |
Explication union et order by
Répondre avec citation
Partager