23 lines
1.3 KiB
SQL
23 lines
1.3 KiB
SQL
CREATE OR REPLACE VIEW public.vw_bi_itsa_fct_shipments_v2 AS
|
|
SELECT q.valued_lot_id AS "intLotId",
|
|
q.product_id AS "intProductId",
|
|
q.from_location_id AS "intFromLocationId",
|
|
q.to_location_id AS "intToLocationId",
|
|
q.shipment_id AS "intShipmentId",
|
|
q.purchase_line_id AS "intPurchaseLineId",
|
|
q.sale_line_id AS "intSaleLineId",
|
|
q.pnl_quantity_after_finished_rule AS "Shipped Quantity",
|
|
q.quantity_unit AS "Quantity Unit",
|
|
COALESCE(p."dblUnitPrice", (0)::numeric) AS "Purchase Price",
|
|
COALESCE(s."dblUnitPrice", (0)::numeric) AS "Sale Price",
|
|
q.bl_date AS "BL Date",
|
|
q.delivery_date_from AS "Delivery Date From",
|
|
q.delivery_date_to AS "Delivery Date To",
|
|
q.valued_lot_type AS "Lot Type",
|
|
COALESCE(book."intBookId", 0) AS "intBookId"
|
|
FROM (((vw_utility_trade_pnl_quantities q
|
|
LEFT JOIN vw_utility_purchase_physical_contract p ON ((q.purchase_line_id = p."intPurchaseLineId")))
|
|
LEFT JOIN vw_utility_sale_physical_contract s ON ((q.sale_line_id = s."intSaleLineId")))
|
|
LEFT JOIN vw_utility_book_dimension book ON (((s."intSaleLineId" = book."intTradeLineId") AND (book."strTradeCategory" = 'Physical'::text) AND (book."strTradeType" = 'Sale'::text))))
|
|
WHERE ((q.ignored_by_finished_rule = false) AND (q.shipment_id IS NOT NULL));;
|