---
# System prepended metadata

title: Analisi de Proveedor
tags: [DataStudio]

---

# Analisi de Proveedor

###### tags: `DataStudio`

```sql=
SELECT
       rp.ref                                                                                                                       AS ref_partner,
       rp.name                                                                                                                      AS partner,
       po.name                                                                                                                      AS oc,
       pt.default_code                                                                                                              AS producto,
       REPLACE(
               REPLACE(
                       REPLACE(
                               REPLACE(
                                       REPLACE(
                                               pt.name,
                                               '\t',
                                               ''
                                           ),
                                       '\r',
                                       ''
                                   ),
                               '\n',
                               ''
                           ),
                       'char(13)',
                       ''),
               'char(10)','')                                                                                                       AS descripcion,
       pu.name                                                                                                                      AS uom,
       COALESCE(pol.product_qty, 0)                                                                                                 AS qty_pedido,
       pol.qty_received                                                                                                             AS qty_recivida,
       (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date                                                                        AS fech_pedido,
       (po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date                                                                      AS fech_entrega,
       (pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date                                                                       AS fech_recibo,
       ps.delay                                                                                                                     AS delay_std,
       CASE
           WHEN
               ((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)::numeric = 0 THEN 1
           ELSE
               ((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)::numeric
        END             AS delay_oc,
       (((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)         AS delay_real,
       CASE
           WHEN
                (((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) <=
                ((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) THEN 0.0
           ELSE
                (((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) -
                ((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)
       END                                                                                                                           AS dias_retraso,

       CASE
            WHEN
                CASE
                    WHEN
                         (((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) <=
                         ((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) THEN 25
                    ELSE
                       25 - trunc((((((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) -
                         ((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date))::numeric *
                         (25 / CASE
                                   WHEN
                                       ((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)::numeric = 0.0 THEN 1
                                   ELSE
                                       ((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)::numeric
                                END
                             )),2)
                 END < 0.0
            THEN 0
            ELSE
                CASE
                    WHEN
                         (((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) <=
                         ((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) THEN 25
                    ELSE
                       25 - trunc((((((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) -
                         ((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date))::numeric *
                         (25 / CASE
                                   WHEN
                                       ((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)::numeric = 0.0 THEN 1
                                   ELSE
                                       ((po.date_planned AT TIME ZONE 'UTC-6')::timestamp::date -(po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)::numeric
                                END
                             )),2)
                END
       END                                                                                                                            AS pts,

      --EVALUACION GLOBAL DEL PROVEEDOR
        ps.delay                                                                                                                      AS delay_prov,
       (((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date)         AS delay_real_prov,
       CASE
           WHEN
                (((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) <= ps.delay THEN 0.0
           ELSE
                (((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) - ps.delay
       END                                                                                                                           AS dias_retraso_prov,
       CASE
           WHEN
                CASE
                    WHEN
                         (((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) <= ps.delay THEN 25
                    ELSE
                       25 - trunc((((((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) - (ps.delay))::numeric *(25 /
                         CASE
                             WHEN ps.delay = 0 THEN 1
                         ELSE
                             ps.delay
                         END)),2)
                END   < 0.0
            THEN  0
           ELSE
                CASE
                    WHEN
                         (((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) <= ps.delay THEN 25
                    ELSE
                       25 - trunc((((((pol.write_date AT TIME ZONE 'UTC-6')::timestamp::date - 1) - (po.date_order AT TIME ZONE 'UTC-6')::timestamp::date) - (ps.delay))::numeric *(25 /
                         CASE
                             WHEN ps.delay = 0 THEN 1
                         ELSE
                             ps.delay
                         END)),2)
                END
       END                                                                                                                              AS pts_prov,
       CASE
           WHEN
                 pol.qty_received >= pol.product_qty THEN 25
           ELSE
                25 - ((pol.product_qty - pol.qty_received) * (25/pol.product_qty))
             
           END                                                                                                                         AS cantidad_pts,

       rp2.name                                                                                                                         AS comprador
FROM
     purchase_order po
       INNER JOIN purchase_order_line pol ON po.id = pol.order_id AND pol.qty_received > 0 AND pol.product_uom <> 95
       INNER JOIN product_product pp ON pol.product_id = pp.id
       INNER JOIN product_template pt ON pp.product_tmpl_id = pt.id
       INNER JOIN product_supplierinfo ps ON pp.product_tmpl_id = ps.product_tmpl_id
       INNER JOIN product_uom pu ON pt.uom_id = pu.id
--        INNER JOIN (SELECT
--                           pickings.id as ids,
--                           pickings.origin as origen,
--                           max(pickings.date_done) AS fecha_listo
--                    FROM
--                          stock_picking pickings
--                    WHERE pickings.state = 'done'
--                    GROUP BY pickings.origin,
--                             pickings.id,
--                             pickings.date_done) AS sp ON sp.origen = po.name

       INNER JOIN res_partner rp ON po.partner_id = rp.id
       INNER JOIN res_users ru ON rp.x_user_id = ru.id
       INNER JOIN res_partner rp2 ON ru.partner_id = rp2.id
WHERE
	po.state = 'done'
    --and po.name = 'PO05100'
ORDER BY
	pol.product_qty DESC
```