-- View: public.vw_utility_intransit_lots

-- DROP VIEW public.vw_utility_intransit_lots;

CREATE OR REPLACE VIEW public.vw_utility_intransit_lots AS
 SELECT fm."intCompanyId",
    fm."intPurchaseLineId",
    fm."intShipmentId",
    fm."intLotId",
    fm."dblQuantity",
    fm."strUnit",
    fm."dblQuantityKg"
   FROM vw_utility_stock_movements fm
     JOIN vw_utility_shipment_in ds ON fm."intShipmentId" = ds."intShipmentId"
     JOIN vw_utility_location dl ON fm."intToLocationId" = dl."intLocationId"
  WHERE 1 = 1 AND fm."intLotId" > 0 AND ds."strToLocationType"::text = 'storage'::text AND dl."strLocationType"::text = 'storage'::text AND dl."strLocationName"::text = 'Transit'::text AND fm."strState"::text = 'done'::text AND NOT (EXISTS ( SELECT fm2."intPurchaseLineId",
            fm2."intShipmentId",
            fm2."intLotId",
            fm2."dblQuantity"
           FROM vw_utility_stock_movements fm2
             JOIN vw_utility_shipment_in ds2 ON fm2."intShipmentId" = ds2."intShipmentId"
             JOIN vw_utility_location dl2 ON fm2."intFromLocationId" = dl2."intLocationId"
          WHERE 1 = 1 AND fm2."intLotId" > 0 AND ds2."strToLocationType"::text = 'storage'::text AND dl2."strLocationType"::text = 'storage'::text AND dl2."strLocationName"::text = 'Transit'::text AND fm2."strState"::text = 'done'::text AND fm2."intLotId" = fm."intLotId" AND fm2."intPurchaseLineId" = fm."intPurchaseLineId" AND fm2."intShipmentId" = fm."intShipmentId"));

ALTER TABLE public.vw_utility_intransit_lots
    OWNER TO postgres;