tradon/ITSA/Data Reconciliation/update_vw_utility_open_position.sql

CREATE OR REPLACE VIEW public.vw_utility_open_position AS
WITH physical_lot_quantity AS (
    SELECT
        'purchase'::text AS side,
        l.line AS contract_line_id,
        SUM(COALESCE(qh.quantity, 0::numeric)) AS physical_quantity
    FROM lot_lot l
    LEFT JOIN lot_qt_hist qh
        ON qh.lot = l.id
        AND qh.quantity_type = l.lot_state
    WHERE
        l.lot_type::text = 'physic'::text
        AND l.line IS NOT NULL
    GROUP BY
        l.line

    UNION ALL

    SELECT
        'sale'::text AS side,
        l.sale_line AS contract_line_id,
        SUM(COALESCE(qh.quantity, 0::numeric)) AS physical_quantity
    FROM lot_lot l
    LEFT JOIN lot_qt_hist qh
        ON qh.lot = l.id
        AND qh.quantity_type = l.lot_state
    WHERE
        l.lot_type::text = 'physic'::text
        AND l.sale_line IS NOT NULL
    GROUP BY
        l.sale_line
),
matched_virtual_quantity AS (
    SELECT
        'purchase'::text AS side,
        lp.line AS contract_line_id,
        SUM(lqt.lot_quantity) AS matched_quantity
    FROM lot_qt lqt
    JOIN lot_lot lp
        ON lp.id = lqt.lot_p
    WHERE
        lp.lot_type::text = 'virtual'::text
        AND lp.line IS NOT NULL
        AND lqt.lot_s IS NOT NULL
        AND lqt.lot_quantity > 0::numeric
    GROUP BY
        lp.line

    UNION ALL

    SELECT
        'sale'::text AS side,
        ls.sale_line AS contract_line_id,
        SUM(lqt.lot_quantity) AS matched_quantity
    FROM lot_qt lqt
    JOIN lot_lot ls
        ON ls.id = lqt.lot_s
    WHERE
        ls.lot_type::text = 'virtual'::text
        AND ls.sale_line IS NOT NULL
        AND lqt.lot_p IS NOT NULL
        AND lqt.lot_quantity > 0::numeric
    GROUP BY
        ls.sale_line
),
open_virtual_lots AS (
    SELECT
        'purchase'::text AS side,
        lqt.id AS int_lot_qt_id,
        lp.id AS int_lot_id,
        pl.id AS int_contract_line_id,
        pp.party AS int_counterparty_id,
        pl.product AS int_product_id,
        uom.name AS uom,
        lqt.lot_quantity AS open_quantity,
        pl.from_del AS period_start,
        pl.to_del AS period_end,
        COALESCE(plq.physical_quantity, 0::numeric) AS physical_lot_quantity,
        pl.quantity_theorical - COALESCE(plq.physical_quantity, 0::numeric)
            - COALESCE(mvq.matched_quantity, 0::numeric)
            AS open_targeted_quantity
    FROM lot_qt lqt
    JOIN lot_lot lp
        ON lp.id = lqt.lot_p
    JOIN purchase_line pl
        ON pl.id = lp.line
    JOIN purchase_purchase pp
        ON pp.id = pl.purchase
    LEFT JOIN product_uom uom
        ON uom.id = lqt.lot_unit
    LEFT JOIN physical_lot_quantity plq
        ON plq.side = 'purchase'::text
        AND plq.contract_line_id = pl.id
    LEFT JOIN matched_virtual_quantity mvq
        ON mvq.side = 'purchase'::text
        AND mvq.contract_line_id = pl.id
    WHERE
        lp.lot_type::text = 'virtual'::text
        AND lqt.lot_s IS NULL
        AND COALESCE(pl.finished, false) = false
        AND lqt.lot_quantity > 0::numeric

    UNION ALL

    SELECT
        'sale'::text AS side,
        lqt.id AS int_lot_qt_id,
        ls.id AS int_lot_id,
        sl.id AS int_contract_line_id,
        ss.party AS int_counterparty_id,
        sl.product AS int_product_id,
        uom.name AS uom,
        -lqt.lot_quantity AS open_quantity,
        sl.from_del AS period_start,
        sl.to_del AS period_end,
        COALESCE(plq.physical_quantity, 0::numeric) AS physical_lot_quantity,
        sl.quantity_theorical - COALESCE(plq.physical_quantity, 0::numeric)
            - COALESCE(mvq.matched_quantity, 0::numeric)
            AS open_targeted_quantity
    FROM lot_qt lqt
    JOIN lot_lot ls
        ON ls.id = lqt.lot_s
    JOIN sale_line sl
        ON sl.id = ls.sale_line
    JOIN sale_sale ss
        ON ss.id = sl.sale
    LEFT JOIN product_uom uom
        ON uom.id = lqt.lot_unit
    LEFT JOIN physical_lot_quantity plq
        ON plq.side = 'sale'::text
        AND plq.contract_line_id = sl.id
    LEFT JOIN matched_virtual_quantity mvq
        ON mvq.side = 'sale'::text
        AND mvq.contract_line_id = sl.id
    WHERE
        ls.lot_type::text = 'virtual'::text
        AND lqt.lot_p IS NULL
        AND COALESCE(sl.finished, false) = false
        AND lqt.lot_quantity > 0::numeric
)
SELECT
    side AS "strContractSide",
    int_lot_qt_id AS "intLotQtId",
    int_lot_id AS "intLotId",
    int_contract_line_id AS "intContractLineId",
    int_counterparty_id AS "intCounterpartyId",
    int_product_id AS "intProductId",
    uom AS "strUom",
    open_quantity AS "dblOpenQuantity",
    period_start AS "dtmPeriodStart",
    period_end AS "dtmPeriodEnd",
    physical_lot_quantity AS "dblPhysicalLotQuantity",
    open_targeted_quantity AS "dblOpenTargetedQuantity"
FROM open_virtual_lots;