CREATE OR REPLACE VIEW public.vw_utility_purchase_physical_contract AS
 SELECT pl.id AS "intPurchaseLineId",
    pl.from_del AS "dtmDeliveryDateFrom",
    pl.to_del AS "dtmDeliveryDateTo",
    pl.quantity AS "dblQuantity",
    pu1.name AS "strPurchaseUom",
    COALESCE(vult.dblintransitquantity, (0)::double precision) AS "dblInTransitQuantity",
    COALESCE(dropship.dbldropshippedquantity, (0)::double precision) AS "dblDropshippedQuantity",
    COALESCE(received.dblreceivedquantity, (0)::double precision) AS "dblReceivedQuantity",
    COALESCE(pl.linked_price, (0)::numeric) AS "dblPrice",
    pu2.name AS "strPriceUnit",
    'n/a'::text AS "strPriceCurrency",
    pl.unit_price AS "dblUnitPrice",
    COALESCE(pl.quantity_theorical, (0)::numeric) AS "dblTheoriticalQuantity",
    round(((pl.quantity * (pl.unit_price)::double precision))::numeric, get_rounding_position(mcr.rounding)) AS "dblLineAmount",
    cur.name AS "strCurrency",
    (0)::numeric AS "dblLineBaseAmount",
    ''::text AS "strBaseCurrency",
    (0)::numeric AS "dblQuantityInBaseUnit",
    ''::text AS "strBaseUnit",
    (0)::numeric AS "dblFixedPrice",
    (0)::numeric AS "dblFixedQuantity",
    (0)::numeric AS "dblFixedAmount",
    (0)::numeric AS "dblUnFixedPrice",
    (0)::numeric AS "dblUnfixedQuantity",
    (0)::numeric AS "dblUnfixedAmount",
    (0)::numeric AS "dblInvoicedQuantity",
    (0)::numeric AS "dblInvoicedAmount",
    ''::text AS "strInvoiceCurrency",
    COALESCE(phys.dblphysicalqty, (0)::double precision) AS "dblPhysicalQty",
    (COALESCE((pl.quantity_theorical)::double precision, (0)::double precision) - COALESCE(phys.dblphysicalqty, (0)::double precision)) AS "dblOpenQuantity",
    COALESCE(inst.dblinstructedqty, (0)::double precision) AS "dblInInstructedQuantity",
    COALESCE(bl.estimated_date, pl.to_del) AS "dtmEstimatedBLDate",
    pl.finished AS "ysnFinished"
   FROM (((((((((((purchase_line pl
     JOIN purchase_purchase pc ON ((pl.purchase = pc.id)))
     JOIN product_uom pu1 ON ((pl.unit = pu1.id)))
     LEFT JOIN product_uom pu2 ON ((pl.linked_unit = pu2.id)))
     LEFT JOIN currency_linked cur ON ((pl.linked_currency = cur.id)))
     LEFT JOIN currency_currency mcr ON ((cur.currency = mcr.id)))
     LEFT JOIN ( SELECT transit."intPurchaseLineId",
            sum(transit."dblQuantity") AS dblintransitquantity
           FROM vw_utility_intransit_lots transit
          GROUP BY transit."intPurchaseLineId") vult ON ((pl.id = vult."intPurchaseLineId")))
     LEFT JOIN ( SELECT t1."intPurchaseLineId",
            sum(t1."dblQuantity") AS dbldropshippedquantity
           FROM vw_utility_dropshipped_lots t1
          GROUP BY t1."intPurchaseLineId") dropship ON ((pl.id = dropship."intPurchaseLineId")))
     LEFT JOIN ( SELECT t1."intPurchaseLineId",
            sum(t1."dblQuantity") AS dblreceivedquantity
           FROM vw_utility_received_lots t1
          GROUP BY t1."intPurchaseLineId") received ON ((pl.id = received."intPurchaseLineId")))
     LEFT JOIN ( SELECT t1."intPurchaseLineId",
            sum(t1."dblQuantity") AS dblphysicalqty
           FROM vw_utility_lot t1
          WHERE ((t1."strType")::text = 'physic'::text)
          GROUP BY t1."intPurchaseLineId") phys ON ((pl.id = phys."intPurchaseLineId")))
     LEFT JOIN ( SELECT t1."intPurchaseLineId",
            sum(t1."dblQuantity") AS dblinstructedqty
           FROM vw_utility_instructed_lots t1
          GROUP BY t1."intPurchaseLineId") inst ON ((pl.id = inst."intPurchaseLineId")))
     LEFT JOIN pricing_estimated bl ON (((pl.id = bl.line) AND ((bl.trigger)::text = 'bldate'::text))))
  WHERE (1 = 1);;