# REPORTES DataStudio
### Total Fabricado x Periodo
___
**QUERY's**
```sql=
SELECT
stock_move.name as mo,
(stock_move.date AT TIME ZONE 'UTC-6')::timestamp::date AS date_,
pt.default_code AS clave,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
pt.name,
'\t',
''
),
'\r',
''
),
'\n',
''
),
'char(13)',
''),
'char(10)','') AS descripcion,
pu.name AS uom,
trunc(stock_move.product_qty,2) AS unidades,
trunc(stock_move.product_qty * pt.udv, 4) AS litros,
CASE
-- WHEN aaa.id in ('2048','2049') THEN 'N1'
WHEN
aaa.id in ('2048','2049','2053','2054','2055','2056','2057','2058','2059','2079','143208')
OR rr.name = 'Envasado GRAN2-DIFERENCIAL'
OR rr.name = 'Envasado GRAN2-FERRO'
OR rr.name = 'Envasado GRAN2-HIDRAULICO'
OR rr.name = 'Envasado GRAN2-ANTICONGELANTE'
OR rr.name = 'Envasado GRAN2-TRACTORES'
OR rr.name = 'Envasado GRAN2-TRANSMISION' THEN 'N2'
WHEN
aaa.id in ('2062','2063','2064','2065','2066','2070')
OR rr.name = 'Envasado GR3-15W40GR'
OR rr.name = 'Envasado GR3-5W30GR'
OR rr.name = 'Envasado GR3-BASGR'
OR rr.name = 'Envasado GR3-5W40'
OR rr.name = 'Envasado PLASTGR' THEN 'N3'
WHEN aaa.id in ('2043','2045') THEN 'GRASAS'
WHEN aaa.id in ('2082') THEN 'ASFALTOS'
ELSE aaa.name
END AS NAVE,
CASE
WHEN
rr.name = 'Envasado GRAN2-DIFERENCIAL'
OR rr.name = 'Envasado GRAN2-FERRO'
OR rr.name = 'Envasado GRAN2-HIDRAULICO'
OR rr.name = 'Envasado GRAN2-ANTICONGELANTE'
OR rr.name = 'Envasado GRAN2-TRACTORES'
OR rr.name = 'Envasado GRAN2-TRANSMISION' THEN 'GRAN-2'
WHEN
rr.name = 'Envasado GR3-15W40GR'
OR rr.name = 'Envasado GR3-5W30GR'
OR rr.name = 'Envasado GR3-BASGR'
OR rr.name = 'Envasado GR3-5W40'
OR rr.name = 'Envasado PLASTGR' THEN 'GRAN-3'
WHEN aaa.id = '2043' THEN 'RG 402'
WHEN aaa.id = '2045' THEN 'RG 404'
WHEN aaa.id = '2048' THEN 'PRESTONE'
WHEN aaa.id = '2049' THEN 'LIQ/FREN'
WHEN aaa.id = '2053' THEN 'MOD1'
WHEN aaa.id = '2054' THEN 'WP-2'
WHEN aaa.id = '2055' THEN 'PP-2'
WHEN aaa.id = '2056' THEN '8EST'
WHEN aaa.id = '2057' THEN 'CUB-2'
WHEN aaa.id = '2058' THEN 'TA-2'
WHEN aaa.id = '2059' THEN 'ANTFR'
WHEN aaa.id = '2062' THEN 'WP-3'
WHEN aaa.id = '2063' THEN 'PP-3'
WHEN aaa.id = '2064' THEN 'EQ-3'
WHEN aaa.id = '2065' THEN 'CUB-3'
WHEN aaa.id = '2066' THEN 'TA-3'
WHEN aaa.id = '2070' THEN 'WPS-3'
WHEN aaa.id = '2079' THEN 'TP/ANTF'
WHEN aaa.id = '2082' THEN 'ASFALTOS'
WHEN aaa.id = '143208' THEN 'COMER'
ELSE 'OTROS'
END AS Modulo,
rr.name AS mod,
aaa.name,
xtp.x_name AS t_basico,
xs.x_name AS t_servicio
FROM
stock_move
INNER JOIN product_product ON stock_move.product_id = product_product.id
INNER JOIN product_template pt on product_product.product_tmpl_id = pt.id AND pt.default_code like 'P%'
INNER JOIN product_uom pu on pt.uom_id = pu.id
INNER JOIN x_servicio xs on pt.x_servicio = xs.id
INNER JOIN x_tipo_material xtp ON pt.x_nivel_calidad = xtp.id
LEFT JOIN mrp_production mrp on stock_move.production_id = mrp.id and stock_move.product_id = mrp.product_id
LEFT JOIN mrp_routing mr ON mrp.routing_id = mr.id
LEFT JOIN mrp_routing_workcenter mrw on mr.id = mrw.routing_id
LEFT JOIN mrp_workcenter wc on mrw.workcenter_id = wc.id
LEFT JOIN resource_resource rr on wc.resource_id = rr.id
LEFT JOIN account_analytic_account aaa on wc.costs_hour_account_id = aaa.id
WHERE
--Ubicaciones origen 7:produccion en proceso, 1149:APT/Calidad
stock_move.location_id IN ('7')
AND
--Ubicacion destino
stock_move.location_dest_id IN ('15','1149')
AND
is_done
AND
stock_move.state = 'done'
AND
stock_move.origin LIKE 'MO/%'
-- AND stock_move.product_id = 46461
-- AND stock_move.production_id = 24145
ORDER BY (stock_move.date AT TIME ZONE 'UTC-6')::timestamp::date desc;
```
_____
**Scrap**
#### Scrap x Periodo
```sql=
select
(sm.date AT TIME ZONE 'UTC-6')::timestamp::date AS date,
sm.product_id,
sm.origin,
pp.default_code,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
pt.name,
'\t',
''
),
'\r',
''
),
'\n',
''
),
'char(13)',
''),
'char(10)','') AS descripcion,
/*sl.complete_name as location_completename_origin,
* sm.scrapped,
* sm.location_id as location_id_origin,
* sl.name as location_key_origin,
* */
sm.location_dest_id as location_id_dest,
sld.name as location_key_dest,
sld.complete_name as location_completename_dest,
pu."name"AS uom,
trunc(sm.product_qty,2) AS unidades,
trunc(sm.product_qty * pt.udv, 4) AS litros,
CASE
WHEN
aaa.id in ('2048','2049','2053','2054','2055','2056','2057','2058','2059','2079','143208')
OR rr.name = 'Envasado GRAN2-DIFERENCIAL'
OR rr.name = 'Envasado GRAN2-FERRO'
OR rr.name = 'Envasado GRAN2-HIDRAULICO'
OR rr.name = 'Envasado GRAN2-ANTICONGELANTE'
OR rr.name = 'Envasado GRAN2-TRACTORES'
OR rr.name = 'Envasado GRAN2-TRANSMISION' THEN 'N2'
WHEN
aaa.id in ('2062','2063','2064','2065','2066','2070')
OR rr.name = 'Envasado GR3-15W40GR'
OR rr.name = 'Envasado GR3-5W30GR'
OR rr.name = 'Envasado GR3-BASGR'
OR rr.name = 'Envasado GR3-5W40'
OR rr.name = 'Envasado PLASTGR' THEN 'N3'
WHEN aaa.id in ('2043','2045') THEN 'GRASAS'
WHEN aaa.id in ('2082') THEN 'ASFALTOS'
ELSE aaa.name
END AS NAVE,
CASE
WHEN
rr.name = 'Envasado GRAN2-DIFERENCIAL'
OR rr.name = 'Envasado GRAN2-FERRO'
OR rr.name = 'Envasado GRAN2-HIDRAULICO'
OR rr.name = 'Envasado GRAN2-ANTICONGELANTE'
OR rr.name = 'Envasado GRAN2-TRACTORES'
OR rr.name = 'Envasado GRAN2-TRANSMISION' THEN 'GRAN-2'
WHEN
rr.name = 'Envasado GR3-15W40GR'
OR rr.name = 'Envasado GR3-5W30GR'
OR rr.name = 'Envasado GR3-BASGR'
OR rr.name = 'Envasado GR3-5W40'
OR rr.name = 'Envasado PLASTGR' THEN 'GRAN-3'
WHEN aaa.id = '2043' THEN 'RG 402'
WHEN aaa.id = '2045' THEN 'RG 404'
WHEN aaa.id = '2048' THEN 'PRESTONE'
WHEN aaa.id = '2049' THEN 'LIQ/FREN'
WHEN aaa.id = '2053' THEN 'MOD1'
WHEN aaa.id = '2054' THEN 'WP-2'
WHEN aaa.id = '2055' THEN 'PP-2'
WHEN aaa.id = '2056' THEN '8EST'
WHEN aaa.id = '2057' THEN 'CUB-2'
WHEN aaa.id = '2058' THEN 'TA-2'
WHEN aaa.id = '2059' THEN 'ANTFR'
WHEN aaa.id = '2062' THEN 'WP-3'
WHEN aaa.id = '2063' THEN 'PP-3'
WHEN aaa.id = '2064' THEN 'EQ-3'
WHEN aaa.id = '2065' THEN 'CUB-3'
WHEN aaa.id = '2066' THEN 'TA-3'
WHEN aaa.id = '2070' THEN 'WPS-3'
WHEN aaa.id = '2079' THEN 'TP/ANTF'
WHEN aaa.id = '2082' THEN 'ASFALTOS'
WHEN aaa.id = '143208' THEN 'COMER'
ELSE 'OTROS'
END AS Modulo,
rr.name AS mod,
aaa.name,
mw.qty_produced as Cant_prod_final
from
stock_move sm
inner join product_product pp on
sm.product_id = pp.id
inner join product_template pt on
pp.product_tmpl_id = pt.id
inner join
stock_location sl
on
sm.location_id = sl.id
inner join
stock_location sld on
sm.location_dest_id = sld.id
inner join
product_uom pu
on
pu.id = pt.uom_id
inner join
mrp_workorder mw
on
sm.workorder_id = mw.id
inner join
mrp_workcenter wc
on
mw.workcenter_id = wc.id
inner join
account_analytic_account aaa
on
wc.costs_hour_account_id = aaa.id
left join
resource_resource rr
on
wc.resource_id = rr.id
where
sm.scrapped = true
and sm.origin like 'MO%'
order by
(sm.date at time zone 'UTC-6')::timestamp::date desc;
```
___
#### Catalogo PT x Clasificación
```sql=
SELECT
pt.default_code AS clave,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
pt.name,
'\t',
''
),
'\r',
''
),
'\n',
''
),
'char(13)',
''),
'char(10)', '') AS descripcion,
pu.name AS uom,
pt.udv AS udv,
pp2.default_code AS ept,
pt.package_type AS presentacion,
pc3.name AS familia,
pc2.name AS p_envasado,
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
pc1.name,
'\t',
''
),
'\r',
''
),
'\n',
''
),
'char(13)',
''),
'char(10)', '') AS aplicación,
basico.x_name AS tipo_basico,
servicio.x_name AS servicio
FROM
product_product pp
INNER JOIN product_template pt on pp.product_tmpl_id = pt.id AND pt.active
INNER JOIN product_uom pu ON pt.uom_id = pu.id
LEFT JOIN mrp_bom mb ON pp.id = mb.product_id
LEFT JOIN mrp_bom_line mbl ON mb.id = mbl.bom_id AND mbl.product_uom_id IN ('3', '11') AND mbl.product_id <> '84562'
LEFT JOIN product_product pp2 ON mbl.product_id = pp2.id AND pp2.default_code <> 'M10090'
INNER JOIN x_tipo_material basico ON pt.x_nivel_calidad = basico.id
INNER JOIN x_servicio servicio ON pt.x_servicio = servicio.id
LEFT JOIN product_category pc ON pt.categ_id = pc.id
LEFT JOIN product_category pc1 ON pc.parent_id = pc1.id
LEFT JOIN product_category pc2 ON pc1.parent_id = pc2.id
LEFT JOIN product_category pc3 ON pc2.parent_id = pc3.id
WHERE
pp.default_code LIKE 'P%'
ORDER BY pp.default_code asc;
```
Sign in with Wallet
Connect another wallet