# Reporte Scrap x Periodo

Conexion

    HOST : odoo.raloy.com.mx
    USER : odoo
    PASS : R4l0y.DB
    BD   : raloy_productivo

---
DEFINICIÓN

La ubicación del scrap es en la ubicación Virtual Locations/Daño en Producción


  NECESITAMOS OBTENBER EL SCRAP A PARTIR DE UNA TABLA QUE SE LLAMA stocks.move ,
  tiene nun campo es boleano realacionado con la palabra scrap, y un campo de proiducto(id , default_code)
  (ese campo de producto se liga a product.product pero este no contiene la descripcion la
  descripción la encuentras en product.template (default_code, name). El pŕoduct.protduct tiene un campo id_product*templatre)

---

SQL

:::info
Actualizacion 09/01/2020 (Este presenta el Scrap de los productos)
:::

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,
    sl.complete_name                      AS ubicacion
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
    INNER JOIN stock_location sl ON stock_move.location_dest_id = sl.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

  stock_move.scrapped
 AND
-- --Ubicacion destino
stock_move.location_dest_id IN ('4')
  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

###### tags: `DataStudio`