81 lines
3.2 KiB
SQL
81 lines
3.2 KiB
SQL
-- View: public.vw_utility_shipment_in
|
|
|
|
-- DROP VIEW public.vw_utility_shipment_in;
|
|
|
|
CREATE OR REPLACE VIEW public.vw_utility_shipment_in AS
|
|
SELECT 0 AS "intShipmentId",
|
|
NULL::date AS "dtmEffectiveDate",
|
|
''::character varying AS "strBookingNumber",
|
|
''::character varying AS "strShipmentNb",
|
|
''::character varying AS "strShipmentReference",
|
|
''::character varying AS "strBillOfLading",
|
|
NULL::date AS "dtmBillOfLadingDate",
|
|
NULL::date AS "dtmETADestination",
|
|
NULL::date AS "dtmOriginPlannedDate",
|
|
NULL::date AS "dtmPlannedDate",
|
|
''::character varying AS "strState",
|
|
''::character varying AS "strSupplier",
|
|
''::character varying AS "strWarehouse",
|
|
''::character varying AS "strWarehouseInput",
|
|
''::character varying AS "strStorage",
|
|
''::character varying AS "strFromLocation",
|
|
''::character varying AS "strToLocation",
|
|
''::character varying AS "strToLocationType",
|
|
''::character varying AS "strTransportType",
|
|
''::character varying AS "strVessel",
|
|
''::character varying AS "strVesselIMO",
|
|
''::character varying AS "strVesselYearOfBuilt",
|
|
''::character varying AS "strCarrier",
|
|
NULL::date AS "dtmETAPol",
|
|
NULL::date AS "dtmETD",
|
|
NULL::date AS "dtmETL",
|
|
NULL::date AS "dtmUnloadingDate",
|
|
NULL::date AS "dtmBookingDate",
|
|
''::character varying AS "strReceiveNumber",
|
|
NULL::date AS "dtmReceiveDate",
|
|
''::character varying AS "strCargoMode"
|
|
UNION ALL
|
|
SELECT ssin.id AS "intShipmentId",
|
|
ssin.effective_date AS "dtmEffectiveDate",
|
|
ssin.booking AS "strBookingNumber",
|
|
ssin.number AS "strShipmentNb",
|
|
ssin.reference AS "strShipmentReference",
|
|
ssin.bl_number AS "strBillOfLading",
|
|
ssin.bl_date AS "dtmBillOfLadingDate",
|
|
ssin.etad AS "dtmETADestination",
|
|
ssin.origin_planned_date AS "dtmOriginPlannedDate",
|
|
ssin.planned_date AS "dtmPlannedDate",
|
|
ssin.state AS "strState",
|
|
sup.name AS "strSupplier",
|
|
wh.name AS "strWarehouse",
|
|
inp.name AS "strWarehouseInput",
|
|
sto.name AS "strStorage",
|
|
src.name AS "strFromLocation",
|
|
dest.name AS "strToLocation",
|
|
dest.type AS "strToLocationType",
|
|
ssin.transport_type AS "strTransportType",
|
|
vessel.vessel_name AS "strVessel",
|
|
vessel.vessel_imo AS "strVesselIMO",
|
|
vessel.vessel_year AS "strVesselYearOfBuilt",
|
|
car.name AS "strCarrier",
|
|
ssin.eta AS "dtmETAPol",
|
|
ssin.etd AS "dtmETD",
|
|
ssin.etl AS "dtmETL",
|
|
ssin.unloaded AS "dtmUnloadingDate",
|
|
ssin.booking_date AS "dtmBookingDate",
|
|
ssin.receive_nb AS "strReceiveNumber",
|
|
ssin.receive_date AS "dtmReceiveDate",
|
|
ssin.cargo_mode AS "strCargoMode"
|
|
FROM stock_shipment_in ssin
|
|
LEFT JOIN party_party sup ON ssin.supplier = sup.id
|
|
LEFT JOIN stock_location wh ON ssin.warehouse = wh.id
|
|
LEFT JOIN stock_location inp ON ssin.warehouse_input = inp.id
|
|
LEFT JOIN stock_location sto ON ssin.warehouse_storage = sto.id
|
|
LEFT JOIN stock_location src ON ssin.from_location = src.id
|
|
LEFT JOIN stock_location dest ON ssin.to_location = dest.id
|
|
LEFT JOIN trade_vessel vessel ON ssin.vessel = vessel.id
|
|
LEFT JOIN party_party car ON ssin.carrier_ = car.id;
|
|
|
|
ALTER TABLE public.vw_utility_shipment_in
|
|
OWNER TO postgres;
|