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;