2.21.1. bi_repuestos_inventario_stipo*
|
Nombre de la vista en Redash:
|
bi_repuestos_inventario |
|
Nombre de la vista en BD:
|
bi_repuestos_inventario_stipo |
|
Descripción del objetivo de la vista:
|
|
|
Tipo de vista
|
Maestra
|
|
Fecha versión actual:
|
18-Abril-2021
|
|
Declaración SQL:
|
SELECT `prod`.`productid` AS `id_producto`,
`rep`.`repuestosid` AS `id_repuesto`,
`repcf`.`cf_3498` AS `SubUnidad`,
`repcf`.`cf_3514` AS `imagen`,
`rep`.`proveedor` AS `proveedor`,
`rep`.`repuesto` AS `repuesto`,
`rep`.`codigobarras` AS `codigobarras`,
`rep`.`repuestosusados` AS `repuestosusados`,
`rep`.`manufacturer` AS `manufacturer`,
`rep`.`qtydisponible` AS `qtydisponible`,
`rep`.`qtytotal` AS `qtytotal`,
`rep`.`tiporepuesto` AS `tiporepuesto`,
`rep`.`modeloequipo` AS `modeloequipo`,
`repcf`.`cf_1962` AS `Repuesto_Ingles`,
`repcf`.`cf_1964` AS `Precio_USD`,
`repcf`.`cf_1966` AS `Fabricante`,
`repcf`.`cf_3092` AS `Referencia`,
`repcf`.`cf_3094` AS `cf_3094`,
`repcf`.`cf_3492` AS `Serie de Equipo`,
`repcf`.`cf_3494` AS `Tipo_Equipo`,
`repcf`.`cf_3496` AS `Importado`,
`prod`.`productname` AS `productname`,
`prod`.`productcategory` AS `productcategory`,
`inv`.`insumo` AS `insumo`,
`inv`.`tipoinsumo` AS `tipoinsumo`,
`inv`.`bodega` AS `bodega`,
`inv`.`tipobodega` AS `tipobodega`,
`inv`.`cantidadnew` AS `cantidadnew`,
`inv`.`cantidadbaja` AS `cantidadbaja`,
`inv`.`cantidadused` AS `cantidadused`,
`inv`.`cupobroken` AS `cupobroken`,
`inv`.`cupogood` AS `cupogood`,
`inv`.`cupomax` AS `cupomax`,
`inv`.`iteminventario` AS `iteminventario`,
#01 (CASE
WHEN (`inv`.`tipobodega` = 'Sedes') THEN
(SELECT `vtiger_sedes`.`sede`
FROM `vtiger_sedes`
WHERE (`vtiger_sedes`.`sedesid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'PacientesII') THEN
(SELECT `vtiger_pacientesii`.`identificador_paciente`
FROM `vtiger_pacientesii`
WHERE (`vtiger_pacientesii`.`identificador_paciente` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'Vendors') THEN
(SELECT `vtiger_vendor`.`vendorname`
FROM `vtiger_vendor`
WHERE (`vtiger_vendor`.`vendorid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'PlanillasTrabajo') THEN
(SELECT `vtiger_rutas`.`nombre_ruta`
FROM (`vtiger_planillastrabajo`
JOIN `vtiger_rutas` on((`vtiger_rutas`.`rutasid` = `vtiger_planillastrabajo`.`ruta`)))
WHERE (`vtiger_planillastrabajo`.`planillastrabajoid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'ActivosRetornables') THEN
(SELECT `vtiger_activosretornables`.`nombre_activo`
FROM `vtiger_activosretornables`
WHERE (`vtiger_activosretornables`.`activosretornablesid` = `inv`.`bodega`))
ELSE 'Otro'
END) AS `NombreUbicacion`,
#02 (CASE
WHEN (`inv`.`tipobodega` = 'Sedes') THEN
(SELECT `vtiger_sedescf`.`cf_2271`
FROM `vtiger_sedescf`
WHERE (`vtiger_sedescf`.`sedesid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'PlanillasTrabajo') THEN
(SELECT `vtiger_rutascf`.`cf_2961`
FROM `vtiger_rutascf`
WHERE (`vtiger_rutascf`.`rutasid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'PacientesII') THEN
(SELECT `acc`.`groupname`
FROM (`vtiger_accountgrouprelation` `acc`
JOIN `vtiger_pacientesii` `pac`)
WHERE ((`pac`.`pacientesiiid` = `inv`.`bodega`)
AND (`pac`.`accountid` = `acc`.`accountid`)))
WHEN (`inv`.`tipobodega` = 'Vendors') THEN 'CUNDINAMARCA'
WHEN (`inv`.`tipobodega` = 'ActivosRetornables') THEN 'EQUIPO'
ELSE '.'
END) AS `Regional`,
#03 (CASE
WHEN (`inv`.`tipobodega` = 'Sedes') THEN 'SEDE'
WHEN (`inv`.`tipobodega` = 'PlanillasTrabajo') THEN 'Planilla'
WHEN (`inv`.`tipobodega` = 'PacientesII') THEN 'Paciente'
WHEN (`inv`.`tipobodega` = 'ActivosRetornables') THEN 'Activo Retornables'
WHEN (`inv`.`tipobodega` = 'Vendors') THEN 'Vendors'
ELSE '.'
END) AS `TipoUbicacion`,
#04 (CASE
WHEN (`inv`.`tipobodega` = 'Sedes') THEN
(SELECT `vtiger_sedes`.`clasesede`
FROM `vtiger_sedes`
WHERE (`vtiger_sedes`.`sedesid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'PlanillasTrabajo') THEN
(SELECT `vtiger_rutas`.`tiporuta`
FROM `vtiger_rutas`
WHERE (`vtiger_rutas`.`rutasid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'PacientesII') THEN 'Paciente'
WHEN (`inv`.`tipobodega` = 'ActivosRetornables') THEN
(SELECT `vtiger_vendor`.`category`
FROM `vtiger_vendor`
WHERE (`vtiger_vendor`.`vendorid` = `inv`.`bodega`))
WHEN (`inv`.`tipobodega` = 'Vendors') THEN
(SELECT `vtiger_activosretornables`.`modeloequipo`
FROM `vtiger_activosretornables`
WHERE (`vtiger_activosretornables`.`activosretornablesid` = `inv`.`bodega`))
ELSE 'Otro'
END) AS `N1`,
`crminfo`.`createdtime` AS `FechaCreacion`,
count(0) AS `Cantidad`
FROM ((((`vtiger_inventarioinsumos` `inv`
JOIN `vtiger_products` `prod` on((`inv`.`insumo` = `prod`.`productid`)))
JOIN `vtiger_repuestos` `rep` on((`rep`.`repuestosid` = `prod`.`productid`)))
JOIN `vtiger_repuestoscf` `repcf` on((`repcf`.`repuestosid` = `rep`.`repuestosid`)))
JOIN `vtiger_crmentity` `crminfo` on((`prod`.`productid` = `crminfo`.`crmid`)))
WHERE (`prod`.`discontinued` = 1)
GROUP BY `inv`.`bodega`,
`id_repuesto`
ORDER BY `crminfo`.`createdtime`,
`inv`.`cantidadnew` DESC
|
|
Tablas involucradas:
|
|
|
Columnas involucradas:
|
|
|
Ubicación (URL) en Redash:
| |
|
Ubicación (RUTA) en base de datos:
|
206.189.187.192/CRM/Views/
bi_repuestos_inventario_stipo |
|
Especificaciones técnicas de los campos involucrados:
|
  |