---
# System prepended metadata

title: REPORTES DataStudio

---

# 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;
```

