report_stock_quantity

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

id

int4

10

√

null

product_id

int4

10

√

null

product_tmpl_id

int4

10

√

null

state

text

2147483647

√

null

date

13

√

null

product_qty

numeric

0

√

null

company_id

int4

10

√

null

warehouse_id

int4

10

√

null

Relationships

Close relationships within degrees of separation

One

View Definition

WITH warehouse_cte AS (
         SELECT sl.id AS sl_id,
            w.id AS w_id
           FROM (stock_location sl
             LEFT JOIN stock_warehouse w ON (((sl.parent_path)::text ~~ concat('%/', w.view_location_id, '/%'))))
        ), existing_sm(id, product_id, tmpl_id, product_qty, date, state, company_id, whs_id, whd_id) AS (
         SELECT m.id,
            m.product_id,
            pt.id,
            m.product_qty,
            m.date,
            m.state,
            m.company_id,
            source.w_id,
            dest.w_id
           FROM ((((stock_move m
             LEFT JOIN warehouse_cte source ON ((source.sl_id = m.location_id)))
             LEFT JOIN warehouse_cte dest ON ((dest.sl_id = m.location_dest_id)))
             LEFT JOIN product_product pp ON ((pp.id = m.product_id)))
             LEFT JOIN product_template pt ON ((pt.id = pp.product_tmpl_id)))
          WHERE (((pt.type)::text = 'product'::text) AND ((source.w_id IS NOT NULL) OR (dest.w_id IS NOT NULL)) AND ((source.w_id IS NULL) OR (dest.w_id IS NULL) OR (source.w_id <> dest.w_id)) AND (m.product_qty <> (0)::numeric) AND ((m.state)::text <> ALL ((ARRAY['draft'::character varying, 'cancel'::character varying])::text[])) AND (((m.state)::text = ANY ((ARRAY['draft'::character varying, 'waiting'::character varying, 'confirmed'::character varying, 'partially_available'::character varying, 'assigned'::character varying])::text[])) OR (m.date >= (((now() AT TIME ZONE 'utc'::text))::date - '3 mons'::interval))))
        ), all_sm(id, product_id, tmpl_id, product_qty, date, state, company_id, whs_id, whd_id) AS (
         SELECT sm.id,
            sm.product_id,
            sm.tmpl_id,
                CASE
                    WHEN (is_duplicated.is_duplicated = 0) THEN sm.product_qty
                    WHEN ((sm.whs_id IS NOT NULL) AND (sm.whd_id IS NOT NULL) AND (sm.whs_id <> sm.whd_id)) THEN sm.product_qty
                    ELSE (0)::numeric
                END AS "case",
            sm.date,
            sm.state,
            sm.company_id,
                CASE
                    WHEN (is_duplicated.is_duplicated = 0) THEN sm.whs_id
                    ELSE NULL::integer
                END AS "case",
                CASE
                    WHEN ((is_duplicated.is_duplicated = 0) AND (NOT ((sm.whs_id IS NOT NULL) AND (sm.whd_id IS NOT NULL) AND (sm.whs_id <> sm.whd_id)))) THEN sm.whd_id
                    WHEN ((is_duplicated.is_duplicated = 1) AND ((sm.whs_id IS NOT NULL) AND (sm.whd_id IS NOT NULL) AND (sm.whs_id <> sm.whd_id))) THEN sm.whd_id
                    ELSE NULL::integer
                END AS "case"
           FROM generate_series(0, 1, 1) is_duplicated(is_duplicated),
            existing_sm sm
        )
 SELECT min(forecast_qty.id) AS id,
    forecast_qty.product_id,
    forecast_qty.product_tmpl_id,
    forecast_qty.state,
    forecast_qty.date,
    sum(forecast_qty.product_qty) AS product_qty,
    forecast_qty.company_id,
    forecast_qty.warehouse_id
   FROM ( SELECT m.id,
            m.product_id,
            m.tmpl_id AS product_tmpl_id,
                CASE
                    WHEN ((m.whs_id IS NOT NULL) AND (m.whd_id IS NULL)) THEN 'out'::text
                    WHEN ((m.whd_id IS NOT NULL) AND (m.whs_id IS NULL)) THEN 'in'::text
                    ELSE NULL::text
                END AS state,
            (m.date)::date AS date,
                CASE
                    WHEN ((m.whs_id IS NOT NULL) AND (m.whd_id IS NULL)) THEN (- m.product_qty)
                    WHEN ((m.whd_id IS NOT NULL) AND (m.whs_id IS NULL)) THEN m.product_qty
                    ELSE NULL::numeric
                END AS product_qty,
            m.company_id,
                CASE
                    WHEN ((m.whs_id IS NOT NULL) AND (m.whd_id IS NULL)) THEN m.whs_id
                    WHEN ((m.whd_id IS NOT NULL) AND (m.whs_id IS NULL)) THEN m.whd_id
                    ELSE NULL::integer
                END AS warehouse_id
           FROM all_sm m
          WHERE ((m.product_qty <> (0)::numeric) AND ((m.state)::text <> 'done'::text))
        UNION ALL
         SELECT (- q.id) AS id,
            q.product_id,
            pp.product_tmpl_id,
            'forecast'::text AS state,
            (date.date)::date AS date,
            q.quantity AS product_qty,
            q.company_id,
            wh.id AS warehouse_id
           FROM generate_series((((now() AT TIME ZONE 'utc'::text))::date - '3 mons'::interval), (((now() AT TIME ZONE 'utc'::text))::date + '3 mons'::interval), '1 day'::interval) date(date),
            (((stock_quant q
             LEFT JOIN stock_location l ON ((l.id = q.location_id)))
             LEFT JOIN stock_warehouse wh ON (((l.parent_path)::text ~~ concat('%/', wh.view_location_id, '/%'))))
             LEFT JOIN product_product pp ON ((pp.id = q.product_id)))
          WHERE ((((l.usage)::text = 'internal'::text) AND (wh.id IS NOT NULL)) OR ((l.usage)::text = 'transit'::text))
        UNION ALL
         SELECT m.id,
            m.product_id,
            m.tmpl_id AS product_tmpl_id,
            'forecast'::text AS state,
            (generate_series(
                CASE
                    WHEN ((m.state)::text = 'done'::text) THEN (((now() AT TIME ZONE 'utc'::text))::date - '3 mons'::interval)
                    ELSE ((m.date)::date)::timestamp without time zone
                END,
                CASE
                    WHEN ((m.state)::text <> 'done'::text) THEN (((now() AT TIME ZONE 'utc'::text))::date + '3 mons'::interval)
                    ELSE ((m.date)::date - '1 day'::interval)
                END, '1 day'::interval))::date AS date,
                CASE
                    WHEN ((m.whs_id IS NOT NULL) AND (m.whd_id IS NULL) AND ((m.state)::text = 'done'::text)) THEN m.product_qty
                    WHEN ((m.whd_id IS NOT NULL) AND (m.whs_id IS NULL) AND ((m.state)::text = 'done'::text)) THEN (- m.product_qty)
                    WHEN ((m.whs_id IS NOT NULL) AND (m.whd_id IS NULL)) THEN (- m.product_qty)
                    WHEN ((m.whd_id IS NOT NULL) AND (m.whs_id IS NULL)) THEN m.product_qty
                    ELSE NULL::numeric
                END AS product_qty,
            m.company_id,
                CASE
                    WHEN ((m.whs_id IS NOT NULL) AND (m.whd_id IS NULL)) THEN m.whs_id
                    WHEN ((m.whd_id IS NOT NULL) AND (m.whs_id IS NULL)) THEN m.whd_id
                    ELSE NULL::integer
                END AS warehouse_id
           FROM all_sm m
          WHERE (m.product_qty <> (0)::numeric)) forecast_qty
  GROUP BY forecast_qty.product_id, forecast_qty.product_tmpl_id, forecast_qty.state, forecast_qty.date, forecast_qty.company_id, forecast_qty.warehouse_id;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views