43 lines
2.2 KiB
SQL
43 lines
2.2 KiB
SQL
CREATE OR REPLACE VIEW public.vw_utility_stock_movements AS
|
|
SELECT sm.id AS "intStockId",
|
|
sm.company AS "intCompanyId",
|
|
COALESCE(sm.lot, 0) AS "intLotId",
|
|
COALESCE(sm.cost_price, (0)::numeric) AS "dblCostPrice",
|
|
cur.name AS "strCurrency",
|
|
sm.effective_date AS "dtmEffectiveDate",
|
|
sm.bldate AS "dtmBLDate",
|
|
sm.product AS "intProductId",
|
|
sm.from_location AS "intFromLocationId",
|
|
sm.to_location AS "intToLocationId",
|
|
sm.origin,
|
|
split_part((sm.origin)::text, ','::text, 1) AS "strSourceType",
|
|
COALESCE((split_part((sm.shipment)::text, ','::text, 2))::integer, 0) AS "intShipmentId",
|
|
CASE
|
|
WHEN (((sl_src.type)::text = 'supplier'::text) AND ((sl_dest.type)::text = 'customer'::text)) THEN 'Dropship'::text
|
|
WHEN (split_part((sm.origin)::text, ','::text, 1) = 'purchase.line'::text) THEN 'Inbound'::text
|
|
WHEN (split_part((sm.origin)::text, ','::text, 1) = 'sale.line'::text) THEN 'Outbound'::text
|
|
ELSE 'Unknown'::text
|
|
END AS "strMovementType",
|
|
CASE
|
|
WHEN (split_part((sm.origin)::text, ','::text, 1) = 'purchase.line'::text) THEN (split_part((sm.origin)::text, ','::text, 2))::integer
|
|
ELSE 0
|
|
END AS "intPurchaseLineId",
|
|
CASE
|
|
WHEN (((sl_src.type)::text = 'supplier'::text) AND ((sl_dest.type)::text = 'customer'::text)) THEN lot.sale_line
|
|
WHEN (split_part((sm.origin)::text, ','::text, 1) = 'sale.line'::text) THEN (split_part((sm.origin)::text, ','::text, 2))::integer
|
|
ELSE 0
|
|
END AS "intSaleLineId",
|
|
sm.quantity AS "dblQuantity",
|
|
uom.name AS "strUnit",
|
|
round(((sm.quantity * uom.factor))::numeric, get_rounding_position((uom.rounding)::numeric)) AS "dblQuantityKg",
|
|
sm.unit_price,
|
|
sm.unit_price_updated AS "ysnPriceUpdated",
|
|
sm.state AS "strState",
|
|
lot.lot_status AS "strLotStatus"
|
|
FROM (((((stock_move sm
|
|
JOIN currency_currency cur ON ((sm.currency = cur.id)))
|
|
LEFT JOIN lot_lot lot ON ((sm.lot = lot.id)))
|
|
LEFT JOIN product_uom uom ON ((sm.unit = uom.id)))
|
|
LEFT JOIN stock_location sl_src ON ((sl_src.id = sm.from_location)))
|
|
LEFT JOIN stock_location sl_dest ON ((sl_dest.id = sm.to_location)));;
|