318 lines
16 KiB
SQL
318 lines
16 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,
|
|
lqt.lot_quantity AS 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;;
|