383 lines
15 KiB
SQL
383 lines
15 KiB
SQL
CREATE OR REPLACE VIEW public.vw_utility_trade_pnl_quantities AS
|
|
WITH lot_current_qty AS (
|
|
SELECT
|
|
l.id AS lot_id,
|
|
round(
|
|
COALESCE(lh.quantity, 0::numeric)
|
|
* COALESCE(lot_uom.factor, 1::double precision)::numeric
|
|
/ NULLIF(COALESCE(line_uom.factor, 1::double precision)::numeric, 0::numeric),
|
|
5
|
|
) AS pnl_quantity,
|
|
lot_uom.name AS quantity_uom
|
|
FROM lot_lot l
|
|
LEFT JOIN lot_qt_hist lh ON lh.lot = l.id AND lh.quantity_type = l.lot_state
|
|
LEFT JOIN product_uom lot_uom ON lot_uom.id = l.lot_unit_line
|
|
LEFT JOIN purchase_line pl ON pl.id = l.line
|
|
LEFT JOIN sale_line sl ON sl.id = l.sale_line
|
|
LEFT JOIN product_uom line_uom ON line_uom.id = COALESCE(pl.unit, sl.unit)
|
|
), purchase_pnl_lots AS (
|
|
SELECT
|
|
'purchase'::text AS pnl_side,
|
|
p.id AS purchase_id,
|
|
p.number AS purchase_number,
|
|
p.reference AS purchase_reference,
|
|
pl.id AS purchase_line_id,
|
|
pl.finished AS purchase_line_finished,
|
|
s.id AS sale_id,
|
|
s.number AS sale_number,
|
|
s.reference AS sale_reference,
|
|
sl.id AS sale_line_id,
|
|
sl.finished AS sale_line_finished,
|
|
l.id AS valued_lot_id,
|
|
l.lot_type AS valued_lot_type,
|
|
NULL::integer AS lot_qt_id,
|
|
lcq.pnl_quantity,
|
|
lcq.quantity_uom AS quantity_unit,
|
|
pl.product AS product_id,
|
|
COALESCE(shi.from_location, sint.from_location, sho.from_location, p.from_location) AS from_location_id,
|
|
COALESCE(shi.to_location, sint.to_location, sho.to_location, p.to_location) AS to_location_id,
|
|
shi.id AS shipment_id,
|
|
shi.bl_date,
|
|
pl.from_del AS delivery_date_from,
|
|
pl.to_del AS delivery_date_to
|
|
FROM purchase_line pl
|
|
JOIN purchase_purchase p ON p.id = pl.purchase
|
|
JOIN lot_lot l ON l.line = pl.id
|
|
JOIN lot_current_qty lcq ON lcq.lot_id = l.id
|
|
LEFT JOIN sale_line sl ON sl.id = l.sale_line
|
|
LEFT JOIN sale_sale s ON s.id = sl.sale
|
|
LEFT JOIN stock_shipment_in shi ON shi.id = l.lot_shipment_in
|
|
LEFT JOIN stock_shipment_internal sint ON sint.id = l.lot_shipment_internal
|
|
LEFT JOIN stock_shipment_out sho ON sho.id = l.lot_shipment_out
|
|
WHERE pl.type::text = 'line'::text
|
|
AND (l.lot_type::text = ANY (ARRAY['virtual'::character varying::text, 'physic'::character varying::text]))
|
|
), sale_pnl_direct_lots AS (
|
|
SELECT
|
|
'sale'::text AS pnl_side,
|
|
p.id AS purchase_id,
|
|
p.number AS purchase_number,
|
|
p.reference AS purchase_reference,
|
|
pl.id AS purchase_line_id,
|
|
pl.finished AS purchase_line_finished,
|
|
s.id AS sale_id,
|
|
s.number AS sale_number,
|
|
s.reference AS sale_reference,
|
|
sl.id AS sale_line_id,
|
|
sl.finished AS sale_line_finished,
|
|
l.id AS valued_lot_id,
|
|
l.lot_type AS valued_lot_type,
|
|
NULL::integer AS lot_qt_id,
|
|
lcq.pnl_quantity,
|
|
lcq.quantity_uom AS quantity_unit,
|
|
sl.product AS product_id,
|
|
COALESCE(shi.from_location, sint.from_location, sho.from_location, s.from_location) AS from_location_id,
|
|
COALESCE(shi.to_location, sint.to_location, sho.to_location, s.to_location) AS to_location_id,
|
|
shi.id AS shipment_id,
|
|
shi.bl_date,
|
|
sl.from_del AS delivery_date_from,
|
|
sl.to_del AS delivery_date_to
|
|
FROM purchase_line pl
|
|
JOIN purchase_purchase p ON p.id = pl.purchase
|
|
JOIN lot_lot l ON l.line = pl.id AND l.sale_line IS NOT NULL
|
|
JOIN sale_line sl ON sl.id = l.sale_line
|
|
JOIN sale_sale s ON s.id = sl.sale
|
|
JOIN lot_current_qty lcq ON lcq.lot_id = l.id
|
|
LEFT JOIN stock_shipment_in shi ON shi.id = l.lot_shipment_in
|
|
LEFT JOIN stock_shipment_internal sint ON sint.id = l.lot_shipment_internal
|
|
LEFT JOIN stock_shipment_out sho ON sho.id = l.lot_shipment_out
|
|
WHERE pl.type::text = 'line'::text
|
|
AND l.lot_type::text = 'physic'::text
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM purchase_pnl_lots ppl
|
|
WHERE ppl.valued_lot_id = l.id
|
|
AND ppl.sale_line_id = sl.id
|
|
)
|
|
), sale_pnl_matched_virtual_lots AS (
|
|
SELECT
|
|
'sale'::text AS pnl_side,
|
|
p.id AS purchase_id,
|
|
p.number AS purchase_number,
|
|
p.reference AS purchase_reference,
|
|
pl.id AS purchase_line_id,
|
|
pl.finished AS purchase_line_finished,
|
|
s.id AS sale_id,
|
|
s.number AS sale_number,
|
|
s.reference AS sale_reference,
|
|
sl.id AS sale_line_id,
|
|
sl.finished AS sale_line_finished,
|
|
sale_lot.id AS valued_lot_id,
|
|
sale_lot.lot_type AS valued_lot_type,
|
|
lqt.id AS lot_qt_id,
|
|
lcq.pnl_quantity,
|
|
lcq.quantity_uom AS quantity_unit,
|
|
sl.product AS product_id,
|
|
COALESCE(shi.from_location, sint.from_location, sho.from_location, s.from_location) AS from_location_id,
|
|
COALESCE(shi.to_location, sint.to_location, sho.to_location, s.to_location) AS to_location_id,
|
|
shi.id AS shipment_id,
|
|
shi.bl_date,
|
|
sl.from_del AS delivery_date_from,
|
|
sl.to_del AS delivery_date_to
|
|
FROM purchase_line pl
|
|
JOIN purchase_purchase p ON p.id = pl.purchase
|
|
JOIN lot_lot purchase_lot ON purchase_lot.line = pl.id
|
|
AND purchase_lot.lot_type::text = 'virtual'::text
|
|
AND purchase_lot.sale_line IS NULL
|
|
JOIN lot_qt lqt ON lqt.lot_p = purchase_lot.id
|
|
AND lqt.lot_s IS NOT NULL
|
|
AND lqt.lot_quantity > 0::numeric
|
|
JOIN lot_lot sale_lot ON sale_lot.id = lqt.lot_s
|
|
AND sale_lot.lot_type::text = 'virtual'::text
|
|
JOIN sale_line sl ON sl.id = sale_lot.sale_line
|
|
JOIN sale_sale s ON s.id = sl.sale
|
|
JOIN lot_current_qty lcq ON lcq.lot_id = sale_lot.id
|
|
LEFT JOIN stock_shipment_in shi ON shi.id = lqt.lot_shipment_in
|
|
LEFT JOIN stock_shipment_internal sint ON sint.id = lqt.lot_shipment_internal
|
|
LEFT JOIN stock_shipment_out sho ON sho.id = lqt.lot_shipment_out
|
|
WHERE pl.type::text = 'line'::text
|
|
), sale_pnl_unmatched_lots AS (
|
|
SELECT
|
|
'sale'::text AS pnl_side,
|
|
NULL::integer AS purchase_id,
|
|
NULL::character varying AS purchase_number,
|
|
NULL::character varying AS purchase_reference,
|
|
NULL::integer AS purchase_line_id,
|
|
NULL::boolean AS purchase_line_finished,
|
|
s.id AS sale_id,
|
|
s.number AS sale_number,
|
|
s.reference AS sale_reference,
|
|
sl.id AS sale_line_id,
|
|
sl.finished AS sale_line_finished,
|
|
l.id AS valued_lot_id,
|
|
l.lot_type AS valued_lot_type,
|
|
NULL::integer AS lot_qt_id,
|
|
lcq.pnl_quantity,
|
|
lcq.quantity_uom AS quantity_unit,
|
|
sl.product AS product_id,
|
|
COALESCE(shi.from_location, sint.from_location, sho.from_location, s.from_location) AS from_location_id,
|
|
COALESCE(shi.to_location, sint.to_location, sho.to_location, s.to_location) AS to_location_id,
|
|
shi.id AS shipment_id,
|
|
shi.bl_date,
|
|
sl.from_del AS delivery_date_from,
|
|
sl.to_del AS delivery_date_to
|
|
FROM sale_line sl
|
|
JOIN sale_sale s ON s.id = sl.sale
|
|
JOIN lot_lot l ON l.sale_line = sl.id
|
|
AND l.line IS NULL
|
|
AND (l.lot_type::text = ANY (ARRAY['virtual'::character varying::text, 'physic'::character varying::text]))
|
|
JOIN lot_current_qty lcq ON lcq.lot_id = l.id
|
|
LEFT JOIN stock_shipment_in shi ON shi.id = l.lot_shipment_in
|
|
LEFT JOIN stock_shipment_internal sint ON sint.id = l.lot_shipment_internal
|
|
LEFT JOIN stock_shipment_out sho ON sho.id = l.lot_shipment_out
|
|
WHERE sl.type::text = 'line'::text
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM lot_qt lqt
|
|
WHERE lqt.lot_s = l.id
|
|
AND lqt.lot_p IS NOT NULL
|
|
AND lqt.lot_quantity > 0::numeric
|
|
)
|
|
), pnl_lot_rows AS (
|
|
SELECT
|
|
purchase_pnl_lots.pnl_side,
|
|
purchase_pnl_lots.purchase_id,
|
|
purchase_pnl_lots.purchase_number,
|
|
purchase_pnl_lots.purchase_reference,
|
|
purchase_pnl_lots.purchase_line_id,
|
|
purchase_pnl_lots.purchase_line_finished,
|
|
purchase_pnl_lots.sale_id,
|
|
purchase_pnl_lots.sale_number,
|
|
purchase_pnl_lots.sale_reference,
|
|
purchase_pnl_lots.sale_line_id,
|
|
purchase_pnl_lots.sale_line_finished,
|
|
purchase_pnl_lots.valued_lot_id,
|
|
purchase_pnl_lots.valued_lot_type,
|
|
purchase_pnl_lots.lot_qt_id,
|
|
purchase_pnl_lots.pnl_quantity,
|
|
purchase_pnl_lots.quantity_unit,
|
|
purchase_pnl_lots.product_id,
|
|
purchase_pnl_lots.from_location_id,
|
|
purchase_pnl_lots.to_location_id,
|
|
purchase_pnl_lots.shipment_id,
|
|
purchase_pnl_lots.bl_date,
|
|
purchase_pnl_lots.delivery_date_from,
|
|
purchase_pnl_lots.delivery_date_to
|
|
FROM purchase_pnl_lots
|
|
UNION ALL
|
|
SELECT
|
|
sale_pnl_direct_lots.pnl_side,
|
|
sale_pnl_direct_lots.purchase_id,
|
|
sale_pnl_direct_lots.purchase_number,
|
|
sale_pnl_direct_lots.purchase_reference,
|
|
sale_pnl_direct_lots.purchase_line_id,
|
|
sale_pnl_direct_lots.purchase_line_finished,
|
|
sale_pnl_direct_lots.sale_id,
|
|
sale_pnl_direct_lots.sale_number,
|
|
sale_pnl_direct_lots.sale_reference,
|
|
sale_pnl_direct_lots.sale_line_id,
|
|
sale_pnl_direct_lots.sale_line_finished,
|
|
sale_pnl_direct_lots.valued_lot_id,
|
|
sale_pnl_direct_lots.valued_lot_type,
|
|
sale_pnl_direct_lots.lot_qt_id,
|
|
sale_pnl_direct_lots.pnl_quantity,
|
|
sale_pnl_direct_lots.quantity_unit,
|
|
sale_pnl_direct_lots.product_id,
|
|
sale_pnl_direct_lots.from_location_id,
|
|
sale_pnl_direct_lots.to_location_id,
|
|
sale_pnl_direct_lots.shipment_id,
|
|
sale_pnl_direct_lots.bl_date,
|
|
sale_pnl_direct_lots.delivery_date_from,
|
|
sale_pnl_direct_lots.delivery_date_to
|
|
FROM sale_pnl_direct_lots
|
|
UNION ALL
|
|
SELECT
|
|
sale_pnl_matched_virtual_lots.pnl_side,
|
|
sale_pnl_matched_virtual_lots.purchase_id,
|
|
sale_pnl_matched_virtual_lots.purchase_number,
|
|
sale_pnl_matched_virtual_lots.purchase_reference,
|
|
sale_pnl_matched_virtual_lots.purchase_line_id,
|
|
sale_pnl_matched_virtual_lots.purchase_line_finished,
|
|
sale_pnl_matched_virtual_lots.sale_id,
|
|
sale_pnl_matched_virtual_lots.sale_number,
|
|
sale_pnl_matched_virtual_lots.sale_reference,
|
|
sale_pnl_matched_virtual_lots.sale_line_id,
|
|
sale_pnl_matched_virtual_lots.sale_line_finished,
|
|
sale_pnl_matched_virtual_lots.valued_lot_id,
|
|
sale_pnl_matched_virtual_lots.valued_lot_type,
|
|
sale_pnl_matched_virtual_lots.lot_qt_id,
|
|
sale_pnl_matched_virtual_lots.pnl_quantity,
|
|
sale_pnl_matched_virtual_lots.quantity_unit,
|
|
sale_pnl_matched_virtual_lots.product_id,
|
|
sale_pnl_matched_virtual_lots.from_location_id,
|
|
sale_pnl_matched_virtual_lots.to_location_id,
|
|
sale_pnl_matched_virtual_lots.shipment_id,
|
|
sale_pnl_matched_virtual_lots.bl_date,
|
|
sale_pnl_matched_virtual_lots.delivery_date_from,
|
|
sale_pnl_matched_virtual_lots.delivery_date_to
|
|
FROM sale_pnl_matched_virtual_lots
|
|
UNION ALL
|
|
SELECT
|
|
sale_pnl_unmatched_lots.pnl_side,
|
|
sale_pnl_unmatched_lots.purchase_id,
|
|
sale_pnl_unmatched_lots.purchase_number,
|
|
sale_pnl_unmatched_lots.purchase_reference,
|
|
sale_pnl_unmatched_lots.purchase_line_id,
|
|
sale_pnl_unmatched_lots.purchase_line_finished,
|
|
sale_pnl_unmatched_lots.sale_id,
|
|
sale_pnl_unmatched_lots.sale_number,
|
|
sale_pnl_unmatched_lots.sale_reference,
|
|
sale_pnl_unmatched_lots.sale_line_id,
|
|
sale_pnl_unmatched_lots.sale_line_finished,
|
|
sale_pnl_unmatched_lots.valued_lot_id,
|
|
sale_pnl_unmatched_lots.valued_lot_type,
|
|
sale_pnl_unmatched_lots.lot_qt_id,
|
|
sale_pnl_unmatched_lots.pnl_quantity,
|
|
sale_pnl_unmatched_lots.quantity_unit,
|
|
sale_pnl_unmatched_lots.product_id,
|
|
sale_pnl_unmatched_lots.from_location_id,
|
|
sale_pnl_unmatched_lots.to_location_id,
|
|
sale_pnl_unmatched_lots.shipment_id,
|
|
sale_pnl_unmatched_lots.bl_date,
|
|
sale_pnl_unmatched_lots.delivery_date_from,
|
|
sale_pnl_unmatched_lots.delivery_date_to
|
|
FROM sale_pnl_unmatched_lots
|
|
), pnl_lot_rows_with_finished AS (
|
|
SELECT
|
|
pnl_lot_rows.pnl_side,
|
|
pnl_lot_rows.purchase_id,
|
|
pnl_lot_rows.purchase_number,
|
|
pnl_lot_rows.purchase_reference,
|
|
pnl_lot_rows.purchase_line_id,
|
|
pnl_lot_rows.purchase_line_finished,
|
|
pnl_lot_rows.sale_id,
|
|
pnl_lot_rows.sale_number,
|
|
pnl_lot_rows.sale_reference,
|
|
pnl_lot_rows.sale_line_id,
|
|
pnl_lot_rows.sale_line_finished,
|
|
pnl_lot_rows.valued_lot_id,
|
|
pnl_lot_rows.valued_lot_type,
|
|
pnl_lot_rows.lot_qt_id,
|
|
pnl_lot_rows.pnl_quantity,
|
|
pnl_lot_rows.quantity_unit,
|
|
pnl_lot_rows.product_id,
|
|
pnl_lot_rows.from_location_id,
|
|
pnl_lot_rows.to_location_id,
|
|
pnl_lot_rows.shipment_id,
|
|
pnl_lot_rows.bl_date,
|
|
pnl_lot_rows.delivery_date_from,
|
|
pnl_lot_rows.delivery_date_to,
|
|
CASE
|
|
WHEN pnl_lot_rows.pnl_side = 'purchase'::text THEN pnl_lot_rows.purchase_line_finished
|
|
WHEN pnl_lot_rows.pnl_side = 'sale'::text THEN pnl_lot_rows.sale_line_finished
|
|
ELSE false
|
|
END AS valued_line_finished,
|
|
CASE
|
|
WHEN pnl_lot_rows.valued_lot_type::text = 'virtual'::text
|
|
AND (
|
|
pnl_lot_rows.pnl_side = 'purchase'::text
|
|
AND pnl_lot_rows.purchase_line_finished IS TRUE
|
|
OR pnl_lot_rows.pnl_side = 'sale'::text
|
|
AND pnl_lot_rows.sale_line_finished IS TRUE
|
|
)
|
|
THEN true
|
|
ELSE false
|
|
END AS ignored_by_finished_rule
|
|
FROM pnl_lot_rows
|
|
)
|
|
SELECT
|
|
pnl_side,
|
|
purchase_id,
|
|
purchase_number,
|
|
purchase_reference,
|
|
purchase_line_id,
|
|
purchase_line_finished,
|
|
sale_id,
|
|
sale_number,
|
|
sale_reference,
|
|
sale_line_id,
|
|
sale_line_finished,
|
|
valued_lot_id,
|
|
valued_lot_type,
|
|
valued_line_finished,
|
|
ignored_by_finished_rule,
|
|
sum(pnl_quantity) AS pnl_quantity_raw,
|
|
sum(
|
|
CASE
|
|
WHEN ignored_by_finished_rule THEN 0::numeric
|
|
ELSE pnl_quantity
|
|
END
|
|
) AS pnl_quantity_after_finished_rule,
|
|
quantity_unit,
|
|
product_id,
|
|
from_location_id,
|
|
to_location_id,
|
|
shipment_id,
|
|
bl_date,
|
|
delivery_date_from,
|
|
delivery_date_to
|
|
FROM pnl_lot_rows_with_finished
|
|
GROUP BY
|
|
pnl_side,
|
|
purchase_id,
|
|
purchase_number,
|
|
purchase_reference,
|
|
purchase_line_id,
|
|
purchase_line_finished,
|
|
sale_id,
|
|
sale_number,
|
|
sale_reference,
|
|
sale_line_id,
|
|
sale_line_finished,
|
|
valued_lot_type,
|
|
valued_lot_id,
|
|
valued_line_finished,
|
|
ignored_by_finished_rule,
|
|
quantity_unit,
|
|
product_id,
|
|
from_location_id,
|
|
to_location_id,
|
|
shipment_id,
|
|
bl_date,
|
|
delivery_date_from,
|
|
delivery_date_to;
|