496 lines
16 KiB
SQL
496 lines
16 KiB
SQL
CREATE OR REPLACE VIEW public.vw_utility_shipment_fee_allocation AS
|
|
WITH physical_shipment_lots AS (
|
|
SELECT
|
|
l.lot_shipment_in AS shipment_id,
|
|
l.id AS lot_id,
|
|
l.lot_type,
|
|
l.line AS purchase_line_id,
|
|
l.sale_line AS sale_line_id,
|
|
qh.quantity AS shipment_quantity,
|
|
l.lot_unit_line AS shipment_unit_id
|
|
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.lot_shipment_in IS NOT NULL
|
|
),
|
|
virtual_shipment_lots AS (
|
|
SELECT
|
|
q.lot_shipment_in AS shipment_id,
|
|
vp.id AS lot_id,
|
|
vp.lot_type,
|
|
vp.line AS purchase_line_id,
|
|
COALESCE(vp.sale_line, ps.sale_line) AS sale_line_id,
|
|
q.lot_quantity AS shipment_quantity,
|
|
q.lot_unit AS shipment_unit_id
|
|
FROM lot_qt q
|
|
JOIN lot_lot vp
|
|
ON vp.id = q.lot_p
|
|
AND vp.lot_type::text = 'virtual'::text
|
|
LEFT JOIN lot_lot ps
|
|
ON ps.id = q.lot_s
|
|
WHERE
|
|
q.lot_shipment_in IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM physical_shipment_lots pl
|
|
WHERE
|
|
pl.shipment_id = q.lot_shipment_in
|
|
AND pl.purchase_line_id = vp.line
|
|
AND (
|
|
pl.sale_line_id = COALESCE(vp.sale_line, ps.sale_line)
|
|
OR COALESCE(vp.sale_line, ps.sale_line) IS NULL
|
|
)
|
|
)
|
|
),
|
|
shipment_lots AS (
|
|
SELECT
|
|
physical_shipment_lots.shipment_id,
|
|
physical_shipment_lots.lot_id,
|
|
physical_shipment_lots.lot_type,
|
|
physical_shipment_lots.purchase_line_id,
|
|
physical_shipment_lots.sale_line_id,
|
|
physical_shipment_lots.shipment_quantity,
|
|
physical_shipment_lots.shipment_unit_id
|
|
FROM physical_shipment_lots
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
virtual_shipment_lots.shipment_id,
|
|
virtual_shipment_lots.lot_id,
|
|
virtual_shipment_lots.lot_type,
|
|
virtual_shipment_lots.purchase_line_id,
|
|
virtual_shipment_lots.sale_line_id,
|
|
virtual_shipment_lots.shipment_quantity,
|
|
virtual_shipment_lots.shipment_unit_id
|
|
FROM virtual_shipment_lots
|
|
),
|
|
shipment_context AS (
|
|
SELECT
|
|
shipment_lots.shipment_id,
|
|
shipment_lots.lot_id,
|
|
shipment_lots.purchase_line_id,
|
|
shipment_lots.sale_line_id,
|
|
shipment_lots.shipment_unit_id,
|
|
sum(COALESCE(shipment_lots.shipment_quantity, 0::numeric)) AS shipment_quantity
|
|
FROM shipment_lots
|
|
WHERE shipment_lots.shipment_id IS NOT NULL
|
|
GROUP BY
|
|
shipment_lots.shipment_id,
|
|
shipment_lots.lot_id,
|
|
shipment_lots.purchase_line_id,
|
|
shipment_lots.sale_line_id,
|
|
shipment_lots.shipment_unit_id
|
|
),
|
|
shipment_base AS (
|
|
SELECT
|
|
shipment_context.shipment_id,
|
|
sum(shipment_context.shipment_quantity) AS shipment_quantity
|
|
FROM shipment_context
|
|
GROUP BY shipment_context.shipment_id
|
|
),
|
|
shipment_fee_candidates AS (
|
|
SELECT
|
|
f.id AS fee_id,
|
|
'Shipment'::text AS fee_source,
|
|
f.type AS fee_type,
|
|
CASE
|
|
WHEN f.type::text = 'ordered'::text THEN 1
|
|
WHEN f.type::text = 'scheduled'::text THEN 2
|
|
ELSE NULL::integer
|
|
END AS priority,
|
|
sb.shipment_id,
|
|
f.product AS product_id,
|
|
f.supplier AS supplier_id,
|
|
f.mode AS packaging,
|
|
f.p_r AS pay_or_rec,
|
|
f.state,
|
|
f.weight_type,
|
|
COALESCE(f.quantity, 0::numeric) AS requested_quantity,
|
|
sb.shipment_quantity,
|
|
f.price AS fee_price,
|
|
f.currency AS currency_id,
|
|
f.unit AS unit_id,
|
|
CASE
|
|
WHEN upper(f.p_r::text) = 'REC'::text THEN 1
|
|
ELSE '-1'::integer
|
|
END AS sign_multiplier,
|
|
NULL::text AS forced_cost_group
|
|
FROM shipment_base sb
|
|
JOIN fee_fee f
|
|
ON f.shipment_in = sb.shipment_id
|
|
WHERE f.type::text = ANY (ARRAY['ordered'::character varying::text, 'scheduled'::character varying::text])
|
|
),
|
|
shipment_fee_limits AS (
|
|
SELECT
|
|
c.*,
|
|
LEAST(
|
|
GREATEST(c.requested_quantity, 0::numeric),
|
|
GREATEST(
|
|
c.shipment_quantity
|
|
- COALESCE(
|
|
sum(c.requested_quantity) OVER (
|
|
PARTITION BY c.shipment_id, c.product_id, c.supplier_id
|
|
ORDER BY c.priority, c.fee_id
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
|
|
),
|
|
0::numeric
|
|
),
|
|
0::numeric
|
|
)
|
|
) AS fee_allocatable_quantity
|
|
FROM shipment_fee_candidates c
|
|
),
|
|
shipment_fee_allocated AS (
|
|
SELECT
|
|
fl.fee_id,
|
|
fl.fee_source,
|
|
fl.fee_type,
|
|
fl.priority,
|
|
fl.shipment_id,
|
|
sc.lot_id,
|
|
sc.purchase_line_id,
|
|
sc.sale_line_id,
|
|
fl.product_id,
|
|
fl.supplier_id,
|
|
fl.packaging,
|
|
fl.pay_or_rec,
|
|
fl.state,
|
|
fl.weight_type,
|
|
fl.requested_quantity,
|
|
fl.shipment_quantity,
|
|
fl.fee_price,
|
|
fl.currency_id,
|
|
COALESCE(fl.unit_id, sc.shipment_unit_id) AS unit_id,
|
|
fl.sign_multiplier,
|
|
fl.forced_cost_group,
|
|
LEAST(
|
|
sc.shipment_quantity,
|
|
GREATEST(
|
|
fl.fee_allocatable_quantity
|
|
- COALESCE(
|
|
sum(sc.shipment_quantity) OVER (
|
|
PARTITION BY fl.fee_id
|
|
ORDER BY sc.lot_id, sc.purchase_line_id, sc.sale_line_id
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
|
|
),
|
|
0::numeric
|
|
),
|
|
0::numeric
|
|
)
|
|
) AS allocated_quantity
|
|
FROM shipment_fee_limits fl
|
|
JOIN shipment_context sc
|
|
ON sc.shipment_id = fl.shipment_id
|
|
),
|
|
shipment_fee_coverage AS (
|
|
SELECT
|
|
shipment_fee_allocated.shipment_id,
|
|
shipment_fee_allocated.product_id,
|
|
shipment_fee_allocated.supplier_id,
|
|
max(shipment_fee_allocated.shipment_quantity) AS shipment_quantity,
|
|
sum(shipment_fee_allocated.allocated_quantity) AS allocated_quantity
|
|
FROM shipment_fee_allocated
|
|
GROUP BY
|
|
shipment_fee_allocated.shipment_id,
|
|
shipment_fee_allocated.product_id,
|
|
shipment_fee_allocated.supplier_id
|
|
),
|
|
contract_budgeted_fee_candidates AS (
|
|
SELECT
|
|
f.id AS fee_id,
|
|
'Purchase Contract'::text AS fee_source,
|
|
f.type AS fee_type,
|
|
3 AS priority,
|
|
sc.shipment_id,
|
|
sc.lot_id,
|
|
sc.purchase_line_id,
|
|
sc.sale_line_id,
|
|
f.product AS product_id,
|
|
f.supplier AS supplier_id,
|
|
f.mode AS packaging,
|
|
f.p_r AS pay_or_rec,
|
|
f.state,
|
|
f.weight_type,
|
|
sc.shipment_quantity,
|
|
f.price AS fee_price,
|
|
f.currency AS currency_id,
|
|
COALESCE(f.unit, sc.shipment_unit_id) AS unit_id,
|
|
CASE
|
|
WHEN upper(f.p_r::text) = 'REC'::text THEN '-1'::integer
|
|
ELSE 1
|
|
END AS sign_multiplier,
|
|
NULL::text AS forced_cost_group
|
|
FROM shipment_context sc
|
|
JOIN fee_fee f
|
|
ON f.line = sc.purchase_line_id
|
|
WHERE
|
|
f.type::text = 'budgeted'::text
|
|
AND sc.purchase_line_id IS NOT NULL
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
f.id AS fee_id,
|
|
'Sale Contract'::text AS fee_source,
|
|
f.type AS fee_type,
|
|
3 AS priority,
|
|
sc.shipment_id,
|
|
sc.lot_id,
|
|
sc.purchase_line_id,
|
|
sc.sale_line_id,
|
|
f.product AS product_id,
|
|
f.supplier AS supplier_id,
|
|
f.mode AS packaging,
|
|
f.p_r AS pay_or_rec,
|
|
f.state,
|
|
f.weight_type,
|
|
sc.shipment_quantity,
|
|
f.price AS fee_price,
|
|
f.currency AS currency_id,
|
|
COALESCE(f.unit, sc.shipment_unit_id) AS unit_id,
|
|
CASE
|
|
WHEN upper(f.p_r::text) = 'REC'::text THEN 1
|
|
ELSE '-1'::integer
|
|
END AS sign_multiplier,
|
|
NULL::text AS forced_cost_group
|
|
FROM shipment_context sc
|
|
JOIN fee_fee f
|
|
ON f.sale_line = sc.sale_line_id
|
|
WHERE
|
|
f.type::text = 'budgeted'::text
|
|
AND sc.sale_line_id IS NOT NULL
|
|
),
|
|
contract_budgeted_allocated AS (
|
|
SELECT
|
|
c.fee_id,
|
|
c.fee_source,
|
|
c.fee_type,
|
|
c.priority,
|
|
c.shipment_id,
|
|
c.lot_id,
|
|
c.purchase_line_id,
|
|
c.sale_line_id,
|
|
c.product_id,
|
|
c.supplier_id,
|
|
c.packaging,
|
|
c.pay_or_rec,
|
|
c.state,
|
|
c.weight_type,
|
|
c.shipment_quantity,
|
|
c.fee_price,
|
|
c.currency_id,
|
|
c.unit_id,
|
|
c.sign_multiplier,
|
|
c.forced_cost_group,
|
|
LEAST(
|
|
c.shipment_quantity,
|
|
GREATEST(
|
|
sum(c.shipment_quantity) OVER (
|
|
PARTITION BY c.shipment_id, c.product_id, c.supplier_id, c.fee_id
|
|
)
|
|
- COALESCE(fc.allocated_quantity, 0::numeric)
|
|
- COALESCE(
|
|
sum(c.shipment_quantity) OVER (
|
|
PARTITION BY c.shipment_id, c.product_id, c.supplier_id, c.fee_id
|
|
ORDER BY c.lot_id
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
|
|
),
|
|
0::numeric
|
|
),
|
|
0::numeric
|
|
)
|
|
) AS allocated_quantity
|
|
FROM contract_budgeted_fee_candidates c
|
|
LEFT JOIN shipment_fee_coverage fc
|
|
ON fc.shipment_id = c.shipment_id
|
|
AND fc.product_id = c.product_id
|
|
AND fc.supplier_id = c.supplier_id
|
|
),
|
|
price_rows AS (
|
|
SELECT
|
|
NULL::integer AS fee_id,
|
|
'Purchase Price'::text AS fee_source,
|
|
'price'::text AS fee_type,
|
|
4 AS priority,
|
|
sc.shipment_id,
|
|
sc.lot_id,
|
|
sc.purchase_line_id,
|
|
sc.sale_line_id,
|
|
pl.product AS product_id,
|
|
NULL::integer AS supplier_id,
|
|
NULL::character varying AS packaging,
|
|
NULL::character varying AS pay_or_rec,
|
|
NULL::character varying AS state,
|
|
NULL::character varying AS weight_type,
|
|
sc.shipment_quantity AS allocated_quantity,
|
|
pl.unit_price AS fee_price,
|
|
pl.currency_ AS currency_id,
|
|
COALESCE(pl.unit, sc.shipment_unit_id) AS unit_id,
|
|
1 AS sign_multiplier,
|
|
'PurchasePrice'::text AS forced_cost_group
|
|
FROM shipment_context sc
|
|
JOIN purchase_line pl
|
|
ON pl.id = sc.purchase_line_id
|
|
WHERE sc.purchase_line_id IS NOT NULL
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
NULL::integer AS fee_id,
|
|
'Sale Price'::text AS fee_source,
|
|
'price'::text AS fee_type,
|
|
5 AS priority,
|
|
sc.shipment_id,
|
|
sc.lot_id,
|
|
sc.purchase_line_id,
|
|
sc.sale_line_id,
|
|
sl.product AS product_id,
|
|
NULL::integer AS supplier_id,
|
|
NULL::character varying AS packaging,
|
|
NULL::character varying AS pay_or_rec,
|
|
NULL::character varying AS state,
|
|
NULL::character varying AS weight_type,
|
|
sc.shipment_quantity AS allocated_quantity,
|
|
sl.unit_price AS fee_price,
|
|
ss.currency AS currency_id,
|
|
COALESCE(sl.unit, sc.shipment_unit_id) AS unit_id,
|
|
'-1'::integer AS sign_multiplier,
|
|
'SalePrice'::text AS forced_cost_group
|
|
FROM shipment_context sc
|
|
JOIN sale_line sl
|
|
ON sl.id = sc.sale_line_id
|
|
JOIN sale_sale ss
|
|
ON ss.id = sl.sale
|
|
WHERE sc.sale_line_id IS NOT NULL
|
|
),
|
|
final_fees AS (
|
|
SELECT
|
|
shipment_fee_allocated.fee_id,
|
|
shipment_fee_allocated.fee_source,
|
|
shipment_fee_allocated.fee_type,
|
|
shipment_fee_allocated.priority,
|
|
shipment_fee_allocated.shipment_id,
|
|
shipment_fee_allocated.lot_id,
|
|
shipment_fee_allocated.purchase_line_id,
|
|
shipment_fee_allocated.sale_line_id,
|
|
shipment_fee_allocated.product_id,
|
|
shipment_fee_allocated.supplier_id,
|
|
shipment_fee_allocated.packaging,
|
|
shipment_fee_allocated.pay_or_rec,
|
|
shipment_fee_allocated.state,
|
|
shipment_fee_allocated.weight_type,
|
|
shipment_fee_allocated.allocated_quantity AS fee_quantity,
|
|
shipment_fee_allocated.fee_price,
|
|
shipment_fee_allocated.currency_id,
|
|
shipment_fee_allocated.unit_id,
|
|
shipment_fee_allocated.sign_multiplier,
|
|
shipment_fee_allocated.forced_cost_group
|
|
FROM shipment_fee_allocated
|
|
WHERE shipment_fee_allocated.allocated_quantity > 0::numeric
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
contract_budgeted_allocated.fee_id,
|
|
contract_budgeted_allocated.fee_source,
|
|
contract_budgeted_allocated.fee_type,
|
|
contract_budgeted_allocated.priority,
|
|
contract_budgeted_allocated.shipment_id,
|
|
contract_budgeted_allocated.lot_id,
|
|
contract_budgeted_allocated.purchase_line_id,
|
|
contract_budgeted_allocated.sale_line_id,
|
|
contract_budgeted_allocated.product_id,
|
|
contract_budgeted_allocated.supplier_id,
|
|
contract_budgeted_allocated.packaging,
|
|
contract_budgeted_allocated.pay_or_rec,
|
|
contract_budgeted_allocated.state,
|
|
contract_budgeted_allocated.weight_type,
|
|
contract_budgeted_allocated.allocated_quantity AS fee_quantity,
|
|
contract_budgeted_allocated.fee_price,
|
|
contract_budgeted_allocated.currency_id,
|
|
contract_budgeted_allocated.unit_id,
|
|
contract_budgeted_allocated.sign_multiplier,
|
|
contract_budgeted_allocated.forced_cost_group
|
|
FROM contract_budgeted_allocated
|
|
WHERE contract_budgeted_allocated.allocated_quantity > 0::numeric
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
price_rows.fee_id,
|
|
price_rows.fee_source,
|
|
price_rows.fee_type,
|
|
price_rows.priority,
|
|
price_rows.shipment_id,
|
|
price_rows.lot_id,
|
|
price_rows.purchase_line_id,
|
|
price_rows.sale_line_id,
|
|
price_rows.product_id,
|
|
price_rows.supplier_id,
|
|
price_rows.packaging,
|
|
price_rows.pay_or_rec,
|
|
price_rows.state,
|
|
price_rows.weight_type,
|
|
price_rows.allocated_quantity AS fee_quantity,
|
|
price_rows.fee_price,
|
|
price_rows.currency_id,
|
|
price_rows.unit_id,
|
|
price_rows.sign_multiplier,
|
|
price_rows.forced_cost_group
|
|
FROM price_rows
|
|
WHERE price_rows.allocated_quantity > 0::numeric
|
|
)
|
|
SELECT
|
|
f.shipment_id AS "intShipmentId",
|
|
f.lot_id AS "intLotId",
|
|
COALESCE(f.fee_id, 0) AS "intFeeId",
|
|
f.fee_source AS "strFeeSource",
|
|
f.fee_type AS "strFeeType",
|
|
f.priority AS "intPriority",
|
|
COALESCE(f.purchase_line_id, 0) AS "intPurchaseLineId",
|
|
COALESCE(f.sale_line_id, 0) AS "intSaleLineId",
|
|
COALESCE(f.product_id, 0) AS "intProductId",
|
|
COALESCE(p.code, ''::character varying) AS "strFee",
|
|
COALESCE(f.supplier_id, 0) AS "intSupplierId",
|
|
COALESCE(sup.name, ''::character varying) AS "strSupplier",
|
|
COALESCE(f.packaging, ''::character varying) AS "strPackaging",
|
|
COALESCE(f.pay_or_rec, ''::character varying) AS "strPayOrRec",
|
|
COALESCE(f.state, ''::character varying) AS "strState",
|
|
CASE
|
|
WHEN upper(f.weight_type::text) = 'BRUT'::text THEN 'Gross'::text
|
|
ELSE 'Net'::text
|
|
END AS "strWeighingType",
|
|
f.fee_quantity AS "dblQuantity",
|
|
f.fee_price AS "dblPrice",
|
|
COALESCE(cur.name, ''::character varying) AS "strCurrency",
|
|
COALESCE(uom.name, 'Mt'::character varying) AS "strUnit",
|
|
f.fee_quantity * f.fee_price AS "dblAmount",
|
|
COALESCE(
|
|
f.forced_cost_group,
|
|
CASE
|
|
WHEN upper(p.code::text) ~~ '%FREIGHT%'::text THEN 'Freight'::text
|
|
WHEN upper(p.code::text) ~~ '%PROFIT SHARING%'::text THEN 'Profit Sharing'::text
|
|
ELSE 'Other Costs'::text
|
|
END
|
|
) AS "strCostGroup",
|
|
CASE
|
|
WHEN f.pay_or_rec::text = 'pay'::text THEN - 1::numeric
|
|
WHEN f.pay_or_rec::text = 'rec'::text THEN 1::numeric
|
|
WHEN f.fee_source = 'Purchase Price'::text AND f.fee_type::text = 'price'::text THEN - 1::numeric
|
|
WHEN f.fee_source = 'Sale Price'::text AND f.fee_type::text = 'price'::text THEN 1::numeric
|
|
ELSE - 1::numeric
|
|
END AS "intSignMultiplier"
|
|
FROM final_fees f
|
|
JOIN product_product p
|
|
ON p.id = f.product_id
|
|
LEFT JOIN party_party sup
|
|
ON sup.id = f.supplier_id
|
|
LEFT JOIN currency_currency cur
|
|
ON cur.id = f.currency_id
|
|
LEFT JOIN product_uom uom
|
|
ON uom.id = f.unit_id;
|