# Reporte Lento Movimiento PT
###### tags: `DataStudio`

:::info
Query Actual 21/01/2020 (Este presenta repote de lento movimiento pero no cumple con los requerimientos totales por el usuario)
:::
---

## ACCESO A LA BD

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

---
- Reporte DataStudio ➜ [Lento Movimiento PT](https://datastudio.google.com/u/2/reporting/1p2R--BGr_vyNl1zprzCbGv3eEz6k4YfG/page/4H3t/edit)
---
:::spoiler PUEBAS

:::

select * from procurement_order  ;

select * from product_product pp ; 

select * from product_template pt where default_code in ('P7986','P8822');

:::warning
ACTUAL
:::

SELECT *
FROM (
         SELECT pp.id,
                pc3.name        AS categoria,
                CASE
                    WHEN
                        pp2.default_code ISNULL THEN ' --- '
                    ELSE
                        pp2.default_code
                    END         AS ept,
                pt.default_code AS clave,
                pt.name         AS descripcion,
                pu.name         AS uom,
                CASE
                    WHEN
                        (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date ISNULL THEN '2018-11-01'::date
                    ELSE
                        (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date
                    END         AS fecha,
                CASE
                    WHEN
                            (now() AT TIME ZONE 'UTC-6')::timestamp::date -
                            (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date ISNULL
                        THEN (now() AT TIME ZONE 'UTC-6')::timestamp::date - '2018-11-01'::date
                    ELSE
                            (now() AT TIME ZONE 'UTC-6')::timestamp::date - (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date
                    END         AS s_mov,
                CASE
                    WHEN
                            (now() AT TIME ZONE 'UTC-6')::timestamp::date -
                            (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date ISNULL THEN TRUNC(
                                ((now() AT TIME ZONE 'UTC-6')::timestamp::date - '2018-11-1'::date)::numeric / 30.41, 2)
                    ELSE
                        TRUNC(((now() AT TIME ZONE 'UTC-6')::timestamp::date -
                               (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date)::numeric / 30.41, 2)
                    END         AS s_mov_mes,
                CASE
                    WHEN
                            (now() AT TIME ZONE 'UTC-6')::timestamp::date -
                            (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date ISNULL THEN 'Obsoleto'
                    ELSE
                        '-----'
                    END         AS estado,
                CASE
                    WHEN t_qty.qty_ ISNULL THEN 0
                    ELSE t_qty.qty_
                    END         AS inv
--     CASE
--         WHEN t_qty.costo ISNULL THEN 0
--         ELSE t_qty.costo
--     END
         FROM product_template pt
                  INNER JOIN product_product pp ON pt.id = pp.product_tmpl_id AND pp.default_code LIKE 'P%'
                  INNER JOIN product_uom pu ON pt.uom_id = pu.id
                  INNER 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 ('11', '3')
                  LEFT JOIN product_product pp2 ON mbl.product_id = pp2.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
                  INNER JOIN (SELECT stock_pack_operation.product_id AS pid,
                                    MAX(sp.date_done)               AS fecha
                             FROM stock_pack_operation
                                      INNER JOIN stock_picking sp ON stock_pack_operation.picking_id = sp.id
                                 AND sp.location_dest_id = 9
                                 --AND sp.picking_type_id IN (4, 125)
                                 AND sp.state IN ('done', 'documentado')
                                      INNER JOIN stock_picking_type spt
                                                 ON sp.picking_type_id = spt.id AND code = 'outgoing'
                             GROUP BY stock_pack_operation.product_id
                             ORDER BY fecha DESC) AS last_date ON pp.id = last_date.pid
                  LEFT JOIN (SELECT sq.product_id AS pid,
                                    sum(sq.qty)   AS qty_,
                                    sum(sq.cost)  AS costo
                             FROM stock_quant sq
                             WHERE sq.location_id = '15'
                             GROUP BY product_id) AS t_qty ON pp.id = t_qty.pid
-- WHERE pt.default_code = 'P4797'
         ORDER BY pc3.name ASC) moves
WHERE s_mov > 120 AND inv > 0.0;

### MOVIMIENTO
:::info
23/01/2020
:::

SELECT *
FROM (
         SELECT pp.id,
                pc3.name        AS categoria,
                CASE
                    WHEN
                        pp2.default_code ISNULL THEN ' --- '
                    ELSE
                        pp2.default_code
                    END         AS ept,
                pt.default_code AS clave,
                pt.name         AS descripcion,
                pu.name         AS uom,
                CASE
                    WHEN
                        (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date ISNULL THEN '2018-11-01'::date
                    ELSE
                        (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date
                    END         AS fecha,
                CASE
                    WHEN
                            (now() AT TIME ZONE 'UTC-6')::timestamp::date -
                            (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date ISNULL
                        THEN (now() AT TIME ZONE 'UTC-6')::timestamp::date - '2018-11-01'::date
                    ELSE
                            (now() AT TIME ZONE 'UTC-6')::timestamp::date - (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date
                    END         AS s_mov,
                CASE
                    WHEN
                            (now() AT TIME ZONE 'UTC-6')::timestamp::date -
                            (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date ISNULL THEN TRUNC(
                                ((now() AT TIME ZONE 'UTC-6')::timestamp::date - '2018-11-1'::date)::numeric / 30.41, 2)
                    ELSE
                        TRUNC(((now() AT TIME ZONE 'UTC-6')::timestamp::date -
                               (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date)::numeric / 30.41, 2)
                    END         AS s_mov_mes,
                CASE
                    WHEN
                            (now() AT TIME ZONE 'UTC-6')::timestamp::date -
                            (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date ISNULL THEN 'Obsoleto'
                    ELSE
                        '-----'
                    END         AS estado,
                CASE
                    WHEN t_qty.qty_ ISNULL THEN 0
                    ELSE t_qty.qty_
                    END         AS inv
--     CASE
--         WHEN t_qty.costo ISNULL THEN 0
--         ELSE t_qty.costo
--     END
         FROM product_template pt
                  INNER JOIN product_product pp ON pt.id = pp.product_tmpl_id AND pp.default_code LIKE 'P%'
                  INNER JOIN product_uom pu ON pt.uom_id = pu.id
                  INNER 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 ('11', '3')
                  LEFT JOIN product_product pp2 ON mbl.product_id = pp2.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
                  INNER JOIN (SELECT stock_pack_operation.product_id AS pid,
                                    MAX(sp.date_done)               AS fecha
                             FROM stock_pack_operation
                                      INNER JOIN stock_picking sp ON stock_pack_operation.picking_id = sp.id
                                 AND sp.location_dest_id = 9
                                 --AND sp.picking_type_id IN (4, 125)
                                  --AND sp.state IN ('done', 'documentado')
                                      INNER JOIN stock_picking_type spt
                                                 ON sp.picking_type_id = spt.id --AND code = 'outgoing'
                             GROUP BY stock_pack_operation.product_id
                             ORDER BY fecha DESC) AS last_date ON pp.id = last_date.pid
                  LEFT JOIN (SELECT sq.product_id AS pid,
                                    sum(sq.qty)   AS qty_,
                                    sum(sq.cost)  AS costo
                             FROM stock_quant sq
                             WHERE sq.location_id = '15'
                             GROUP BY product_id) AS t_qty ON pp.id = t_qty.pid
-- WHERE pt.default_code = 'P4797'
         ORDER BY pc3.name ASC) moves
WHERE s_mov > 120 AND inv > 0.0;

:::info
EN PRUEBA
:::

SELECT *
FROM (
         SELECT pp.id,
                pc3.name        AS categoria,
                CASE
                    WHEN
                        pp2.default_code ISNULL THEN ' --- '
                    ELSE
                        pp2.default_code
                    END         AS ept,
                pt.default_code AS clave,
                pt.name         AS descripcion,
                pu.name         AS uom,
                CASE
                    WHEN
                        (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date ISNULL THEN '2018-11-01'::date
                    ELSE
                        (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date
                    END         AS fecha,
                CASE
                    WHEN
                            (now() AT TIME ZONE 'UTC-6')::timestamp::date -
                            (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date ISNULL
                        THEN (now() AT TIME ZONE 'UTC-6')::timestamp::date - '2018-11-01'::date
                    ELSE
                            (now() AT TIME ZONE 'UTC-6')::timestamp::date - (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date
                    END         AS s_mov,
                CASE
                    WHEN
                            (now() AT TIME ZONE 'UTC-6')::timestamp::date -
                            (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date ISNULL THEN TRUNC(
                                ((now() AT TIME ZONE 'UTC-6')::timestamp::date - '2018-11-1'::date)::numeric / 30.41, 2)
                    ELSE
                        TRUNC(((now() AT TIME ZONE 'UTC-6')::timestamp::date -
                               (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date)::numeric / 30.41, 2)
                    END         AS s_mov_mes,
                CASE
                    WHEN
                            (now() AT TIME ZONE 'UTC-6')::timestamp::date -
                            (last_date.fecha AT TIME ZONE 'UTC-6')::timestamp::date ISNULL THEN 'Obsoleto'
                    ELSE
                        '-----'
                    END         AS estado,
                CASE
                    WHEN t_qty.qty_ ISNULL THEN 0
                    ELSE t_qty.qty_
                    END         AS inv
--     CASE
--         WHEN t_qty.costo ISNULL THEN 0
--         ELSE t_qty.costo
--     END
         FROM product_template pt
                  INNER JOIN product_product pp ON pt.id = pp.product_tmpl_id AND pp.default_code LIKE 'P%'
                  INNER JOIN product_uom pu ON pt.uom_id = pu.id
                  INNER 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 ('11', '3')
                  LEFT JOIN product_product pp2 ON mbl.product_id = pp2.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
                  INNER JOIN (SELECT stock_pack_operation.product_id AS pid,
                                    MAX(sp.date_done)               AS fecha
                             FROM stock_pack_operation
                                      INNER JOIN stock_picking sp ON stock_pack_operation.picking_id = sp.id
                                 AND sp.location_dest_id in (9,15)
                                 --AND sp.picking_type_id IN (4, 125)
                                 AND sp.state IN ('done', 'documentado')
                                      INNER JOIN stock_picking_type spt
                                                 ON sp.picking_type_id = spt.id AND code = 'outgoing'
                             GROUP BY stock_pack_operation.product_id
                             ORDER BY fecha DESC) AS last_date ON pp.id = last_date.pid
                  LEFT JOIN (SELECT sq.product_id AS pid,
                                    sum(sq.qty)   AS qty_,
                                    sum(sq.cost)  AS costo
                             FROM stock_quant sq
                             WHERE sq.location_id in (9,15)
                             GROUP BY product_id) AS t_qty ON pp.id = t_qty.pid
-- WHERE pt.default_code = 'P4797'
         ORDER BY pc3.name ASC) moves
WHERE s_mov > 120 AND inv > 0.0;

select 
a.id,
a.location_stock_id ,
a.familia_producto ,
a.nave_fabricacion ,
a.stock_qty_location AS Cantidad_sistema,
a.product_id ,
a.name,
a.product_tmpl_name ,
a.year_month ,
a.year_month_2 ,
a.year_month_3 ,
a.year_month_4 ,
a.platform_pieces ,
a.product_ept_id ,
a.location_stock_id_name ,
a.udv ,
a.product_ept_id ,
a.product_ept_id_name ,
a.categ_id ,
a.package_type ,
a.product_name ,
a.tipo_abastecimiento,
a.abc_log_error
from analisis_productos_abc a
where a.stock_qty_location is not null;

:::success
ultimo
:::

select 
a.id,
a.location_stock_id ,
a.familia_producto ,
a.nave_fabricacion ,
a.stock_qty_location AS Cantidad_sistema,
a.product_id ,
a.name,
a.product_tmpl_name ,
a.year_month ,
a.year_month_2 ,
a.year_month_3 ,
a.year_month_4 ,
a.platform_pieces ,
a.product_ept_id ,
a.location_stock_id_name ,
a.udv ,
a.product_ept_id ,
a.product_ept_id_name ,
a.categ_id ,
a.package_type ,
a.product_name ,
a.tipo_abastecimiento,
a.abc_log_error
from analisis_productos_abc a
where a.stock_qty_location is not null
and current_count_sales_by_month = 0;