Data Lake L0

Tables (18)

AIS EVENT HISTORY (VPP_AIS_HIS)

  • AIS Event ID (AIS_EVNT_ID) — VARCHAR(20) — Primary key — NOT NULL
  • Vessel ID (VSL_ID) — VARCHAR(20) — Foreign key → AIS MASTER.Vessel ID — NOT NULL
  • Vessel IMO Code (VSL_IMO_CD) — VARCHAR(10)
  • Vessel latitude (VSL_LAT) — DECIMAL(10,7) — Decimal degrees (DD) express latitude geographic coordinates as decimal fractions of a degree, the value is bounded by…
  • Vessel longitude (VSL_LON) — DECIMAL(11,8) — Decimal degrees (DD) express longitude geographic coordinates as decimal fractions of a degree, the value is bounded by…
  • Vessel Speed (VSL_SPD) — DECIMAL(5,2) — The vessel speed over ground in knots
  • Vessel Heading (VSL_HD) — DECIMAL(5) — The heading of the vessel in degrees
  • Vessel Course (VSL_COG) — DECIMAL(5) — The vessel course over ground in degrees
  • Vessel Position Date (VSL_PSN_DT) — TIMESTAMP — The time of this position
  • Create Date (CRE_DT) — TIMESTAMP

AIS EVNT HISTORY (DLZ_ASI_EVNT_HIS)

  • Message Unique No (MSG_UQ_NO) — VARCHAR(20) — Foreign key → ORIGIN CONENT.Message Unique No — NOT NULL
  • Vessel IMO Code (VSL_IMO_CD) — VARCHAR(10)
  • Vessel Name (VSL_NM) — VARCHAR(50)

AIS HISTORY (DLO_AIS_HIS)

  • Message Unique No (MSG_UQ_NO) — VARCHAR(20) — Foreign key → AIS EVNT HISTORY.Message Unique No — NOT NULL
  • Vessel IMO Code (VSL_IMO_CD) — VARCHAR(10)
  • Vessel Name (VSL_NM) — VARCHAR(50)
  • Vessel MMSI Code (VSL_MMSI_CD) — VARCHAR(10)
  • Vessel Call Sign Number (VSL_CALL_SGN_NO) — VARCHAR(10)
  • Vessel Flag (VSL_FLG) — VARCHAR(2)
  • Vessel Country Code (VSL_CNT_CD) — VARCHAR(2)
  • Vessel latitude (VSL_LAT) — NUMERIC(10,7) — Decimal degrees (DD) express latitude geographic coordinates as decimal fractions of a degree, the value is bounded by…
  • Vessel longitude (VSL_LON) — NUMERIC(11,8) — Decimal degrees (DD) express longitude geographic coordinates as decimal fractions of a degree, the value is bounded by…
  • Vessel Speed (VSL_SPD) — NUMERIC(5,2) — The vessel speed over ground in knots
  • Vessel Heading (VSL_HD) — NUMERIC — The heading of the vessel in degrees
  • Vessel Draugth (VSL_DRU) — NUMERIC
  • Vessel Rot (VSL_ROT) — NUMERIC
  • Vessel Course (VSL_COG) — NUMERIC — The vessel course over ground in degrees
  • Vessel Length (VSL_LEN) — NUMERIC
  • Vessel Width (VSL_WDT) — NUMERIC
  • Vessel Type Code (VSL_TP_CD) — VARCHAR(10)
  • POL Code (POL_CD) — VARCHAR(5) — The UN location code of the port of loading
  • POL Name (POL_NM) — VARCHAR(100) — The name of the port of loading
  • POL Arrival Date (POL_ARR_DT) — TIMESTAMP — Port of Loading - Scheduled or actual arrival time. If not set, the system will calculate this value based on current v…
  • POD Code (POD_CD) — VARCHAR(5) — The UN location code of the port of destination
  • POD Name (POD_NM) — VARCHAR(100) — The name of the port of destination
  • POD Arrival Date (POD_ARR_DT) — TIMESTAMP — Port of Loading - Scheduled or actual arrival time. If not set, the system will calculate this value based on current v…
  • Vessel Status Description (VSL_STS_DESC) — VARCHAR(100) — The navigational status of the vessel
  • Vessel Position Date (VSL_PSN_DT) — TIMESTAMP — The time of this position

AIS MASTER (VPP_AIS_MST)

  • Vessel ID (VSL_ID) — VARCHAR(20) — Primary key — NOT NULL
  • Vessel Name (VSL_NM) — VARCHAR(50) — The name of the Vessel given by the Vessel Operator and registered with IMO.
  • Vessel Voyage No (VSL_VOY_NO) — VARCHAR(20)
  • Vessel IMO Code (VSL_IMO_CD) — VARCHAR(10)
  • Vessel Flag (VSL_FLG) — VARCHAR(2) — The flag of the nation whose laws the vessel is registered under. This is the ISO 3166 two-letter country code
  • Vessel Call Sign Number (VSL_CALL_SGN_NO) — VARCHAR(10) — The carrier who is in charge of the vessel operation based on either the SMDG or SCAC code lists
  • Vesel Operate Code (VSL_OPR_CD) — VARCHAR(5)
  • Delete Flag (DELT_FLAG) — VARCHAR(1)
  • Create Date (CRE_DT) — TIMESTAMP — NOT NULL

CARRIER INFO (VPP_CRR_INFO)

  • Carrier ID (CRR_ID) — VARCHAR(20) — Primary key — NOT NULL
  • Carrier Code (CRR_CD) — VARCHAR(5) — NOT NULL
  • Carrier Name (CRR_NM) — VARCHAR(10) — NOT NULL
  • Service Target Code (SVC_TGT_CD) — VARCHAR(5) — NOT NULL
  • Use Flag (USE_FLG) — VARCHAR(1) — NOT NULL
  • Create Date (CRE_DT) — TIMESTAMP — NOT NULL
  • Create User (CRE_USR) — VARCHAR(50)
  • Update Date (UPD_DT) — TIMESTAMP — NOT NULL
  • Update User (UPD_USR) — VARCHAR(50)

CODE CONVERSION (DLO_COM_CD_CONV_LIST)

  • Code Group Name (CD_GRP_NM) — VARCHAR(100) — Primary key — NOT NULL
  • Source Code (SRC_CD) — VARCHAR(5) — Primary key — NOT NULL
  • Target Code (TRG_CD) — VARCHAR(10)
  • Code Desrcription (CD_DESC) — VARCHAR(100)

COMMON CODE LIST (DLO_COM_CD_LIST)

  • Code Group (CD_GRP) — VARCHAR(3) — Primary key — NOT NULL
  • Common Code (COM_CD) — VARCHAR(5) — Primary key — NOT NULL
  • Code Name (CD_NM) — VARCHAR(100)
  • Code Description (CD_DESC) — VARCHAR(150)

LOCATION MASTER (DLO_LOC_MST)

  • Location Code (LOC_CD) — VARCHAR(5) — Primary key — NOT NULL
  • Location Name (LOC_NM) — VARCHAR(100) — The name of the location.
  • Location latitude (LOC_LAT) — NUMERIC(10,7) — Geographic coordinate that specifies the north–south position of a point on the Earth's surface.
  • Location longitude (LOC_LON) — NUMERIC(11,8) — Geographic coordinate that specifies the east–west position of a point on the Earth's surface.

ORIGIN CONENT (DLZ_ORG_CTNT)

  • Message Unique No (MSG_UQ_NO) — VARCHAR(20) — Primary key — NOT NULL
  • Message Type Code (MSG_TP_CD) — VARCHAR(5)
  • Data Content (DAT_CTNT) — JSON
  • Create Date (CRE_DT) — TIMESTAMP

SHIPMENT SUBSCRIPTION (VPP_SHP_SUBSC)

  • Shipment Subscription ID (SHP_SUBSC_ID) — VARCAHR(20) — Primary key — NOT NULL
  • Container No (CNTR_NO) — VARCHAR(17)
  • Master B/L (MBL_NO) — VARCHAR(20)
  • House B/L No (HBL_NO) — VARCAHR(20)
  • Booking No (BKG_NO) — VARCAHR(20)
  • Carrier Code (CRR_CD) — VARCAHR(5)
  • Current Status Code (CRNT_STS_CD) — VARCAHR(5)
  • Customer ID (CUST_ID) — VARCAHR(50)
  • CRE_USER (CRE_USR) — VARCAHR(50) — NOT NULL
  • Create Date (CRE_DT) — TIMESTAMP
  • Update User (UPD_USR) — VARCAHR(50)
  • Update Date (UPD_DT) — TIMESTAMP
  • Tracking ID (TRK_ID) — VARCAHR(50) — Foreign key → TRACKING_MASTER.Tracking ID — NOT NULL

SHIPMENT SUBSCRIPTION PARTNER (VPP_SHP_SUBSC_PRNR)

  • Shipment Partner ID (SHP_PRNR_ID) — VARCHAR(50) — Primary key — NOT NULL
  • Shipment Subscription ID (SHP_SUBSC_ID) — VARCHAR(20) — Foreign key → SHIPMENT SUBSCRIPTION.Shipment Subscription ID — NOT NULL

TRACKIGN VESSEL1 (DLO_TRAK_VSL)

  • Event ID (EVNT_ID2) — VARCHAR(50) — Foreign key → TRACKING EVENT HISTORY.Event ID — NOT NULL
  • Message Unique No (MSG_UQ_NO) — VARCHAR(20) — Foreign key → TRACKING EVENT HISTORY.Message Unique No — NOT NULL
  • Vessel IMO Code (VSL_IMO_CD) — VARCHAR(10) — The unique reference for a registered Vessel. The reference is the International Maritime Organisation (IMO) number, al…
  • Vessel Name (VSL_NM) — VARCHAR(50) — The name of the Vessel given by the Vessel Operator and registered with IMO.
  • Vessel Flag (VSL_FLG) — VARCHAR(2) — The flag of the nation whose laws the vessel is registered under. This is the ISO 3166 two-letter country code
  • Vessel Call Sign Number (VSL_CALL_SGN_NO) — VARCHAR(10) — The carrier who is in charge of the vessel operation based on either the SMDG or SCAC code lists
  • Vessel Operator Code (VSL_OPR_CD) — VARCHAR(5)
  • Vessel Operator Carrier Code (VSL_OPR_CRR_CD) — VARCHAR(10) — The carrier who is in charge of the vessel operation based on either the SMDG or SCAC code lists
  • Vessel Operator Carrier Code List Provider Code (VSL_OPR_LST_CD) — VARCHAR(5) — Identifies the code list provider used for the operator and partner carriercodes. Enum: [ SMDG, NMFTA ]

TRACKING EVENT HISTORY (DLO_TRAK_EVNT_HIS)

  • Event ID (EVNT_ID) — VARCHAR(50) — Primary key — NOT NULL
  • Message Unique No (MSG_UQ_NO) — VARCHAR(20) — Foreign key → TRACKING EVNT HISTORY.Message Unique No — NOT NULL
  • Equipment Seq (EQ_SEQ) — NUMERIC — Foreign key → TRACKING EVNT HISTORY.Equipment Seq — NOT NULL
  • Event Seq (EVNT_SEQ) — NUMERIC
  • Master B/L Number (MBL_NO) — VARCHAR(20)
  • Booking Number (BKG_NO) — VARCHAR(20)
  • Event Type (EVNT_TP_CD) — VARCHAR(10) — The Event Type of the object - to be used as a discriminator. NB: This field should be considered Metadata
  • Event Code (EVNT_CD) — VARCHAR(4) — Identifier for type of Transport event ARRI (Arrived) DEPA (Departed) LOAD (Loaded) DISC (Discharged) GTIN (Gated in) G…
  • Event Description (EVNT_DESC) — VARCHAR(150) — Vessel Arrival
  • Event Class Code (EVNT_CLSS_CD) — VARCHAR(3) — Code for the event classifier can be ACT (Actual) PLN (Planned) EST (Estimated)
  • Event Date (EVNT_DT) — TIMESTAMP — The local date and time, where the event took place or when the event will take place, in ISO 8601 format.
  • Event Created Date (EVNT_CRE_DT) — TIMESTAMP — he timestamp of when the event was created. NB: This field should be considered Metadata
  • Transport Call ID (TRSP_CALL_ID) — VARCHAR(100) — The unique identifier for a transport call
  • Transport Service Code (TRSP_SVC_CD) — VARCHAR(5) — The code of the service for which the schedule details are published.
  • Transport Voyage Number (TRSP_VOY_NO) — VARCHAR(50) — The vessel operator-specific identifier of the export Voyage.
  • Transport Mode Code (TRSP_MOD_CD) — VARCHAR(10) — The mode of transport as defined by DCSA. Enum: [ VESSEL, RAIL, TRUCK, BARGE ]
  • Equipment Number (EQ_NO) — VARCHAR(15) — The unique identifier for the equipment, which should follow the BIC ISO Container Identification Number where possible…
  • Equipment ISO Code (EQ_ISO_CD) — VARCHAR(4) — Unique code for the different equipment size/type used for transporting commodities. The code is a concatenation of ISO…
  • Empty Indicatior Code (EQ_MTY_IND_CD) — VARCHAR(5) — Code to denote whether the equipment is empty or laden. Enum: [ EMPTY, LADEN ]
  • Soure Type Code (SRC_TP_CD) — VARCHAR(10)

TRACKING EVNT HISTORY (DLZ_TRAK_EVNT_HIS)

  • Equipment Seq (EQ_SEQ) — NUMERIC — Primary key — NOT NULL
  • Message Unique No (MSG_UQ_NO) — VARCHAR(20) — Foreign key → ORIGIN CONENT.Message Unique No — NOT NULL
  • Equipment No (EQ_NO) — VARCHAR(15)
  • Master B/L Number (MBL_NO) — VARCHAR(20)

TRACKING LOCATION1 (DLO_TRAK_LOC)

  • Event ID (EVNT_ID) — VARCHAR(50) — Foreign key → TRACKING EVENT HISTORY.Event ID — NOT NULL
  • Message Unique No (MSG_UQ_NO) — VARCHAR(20) — Foreign key → TRACKING EVENT HISTORY.Message Unique No — NOT NULL
  • Location Type Code (LOC_TP_CD) — VARCHAR(5) — POL, POR, TS...
  • Location Name (LOC_NM) — VARCHAR(100) — The name of the location.
  • Location Code (LOC_CD) — VARCHAR(5) — The UN Location code specifying where the place is located.
  • Location Facility Code (LOC_FCTY_CD) — VARCHAR(8) — The code used for identifying the specific facility. This code does not include the UN Location Code.
  • Location Fcility Type Code (LOC_FCTY_TP_CD) — VARCHAR(4) — A specialized version of the facilityCode to be used in TransportCalls. The code to identify the specific type of facil…
  • Location latitude (LOC_LAT) — NUMERIC(10,7) — Geographic coordinate that specifies the north–south position of a point on the Earth's surface.
  • Location longitude (LOC_LON) — NUMERIC(11,8) — Geographic coordinate that specifies the east–west position of a point on the Earth's surface.
  • Location Address Name (LOC_ADDR_NM) — VARCHAR(100) — Name of the address
  • Location Address Street Name (LOC_ADDR_ST_NM) — VARCHAR(100) — The name of the street of the party’s address.
  • Location Address Street Number (LOC_ADDR_ST_NO) — VARCHAR(50) — The number of the street of the party’s address.
  • Location Address City Name (LOC_ADDR_CTY_NM) — VARCHAR(100) — The city name of the party’s address. maxLength: 65
  • Location Address Post Code (LOC_ADDR_PST_CD) — VARCHAR(10) — The post code of the party’s address.

TRACKING_MASTER (VPP_TRK_MST)

  • Tracking ID (TRK_ID) — VARCAHR(50) — Primary key — NOT NULL
  • Container No (CNTR_NO) — VARCAHR(17) — NOT NULL
  • Master B/L (MBL_NO) — VARCAHR(20)
  • Current Event ID (CRNT_EVNT_ID) — VARCAHR(50)
  • POR Code (POR_CD) — VARCAHR(5)
  • POR Name (POR_NM) — VARCAHR(100)
  • POL Code (POL_CD) — VARCAHR(5)
  • POL Name (POL_NM) — VARCAHR(100)
  • POD Code (POD_CD) — VARCAHR(5)
  • POD Name (POD_NM) — VARCAHR(100)
  • DEL Code (DEL_CD) — VARCAHR(5)
  • DEL Name (DEL_NM) — VARCAHR(100)
  • 1ST TS Location Code (N1ST_TS_LOC_CD) — VARCAHR(5)
  • 1ST TS Location Name (N1ST_TS_LOC_NM) — VARCAHR(100)
  • 2ND TS Location Code (N2ND_TS_LOC_CD) — VARCAHR(5)
  • 2ND TS Location Name (N2NN_TS_LOC_NM) — VARCAHR(100)
  • 1ST Vessel ID (N1ST_VSL_ID) — VARCAHR(20)
  • 2ND Vessel ID (N2ND_VSL_ID) — VARCAHR(20)
  • 3RD Vessel ID (N3RD_VSL_ID) — VARCAHR(20)
  • POL Estimate Departure Date (POL_ETD_DT) — TIMESTAMP
  • POL Actual Departure Date (POL_ATD_DT) — TIMESTAMP
  • 1ST TS Actual Arrival Date (N1ST_TS_ATA_DT) — TIMESTAMP
  • 1ST TS Actual Departure Date (N1ST_TS_ATD_DT) — TIMESTAMP
  • 2ND TS Actual Arrival Date (N2ND_TS_ATA_DT) — TIMESTAMP
  • 2ND TS Actual Departure Date (N2ND_ATD_DT) — TIMESTAMP
  • POD Estimate Arrival Date (POD_ETA_DT) — TIMESTAMP
  • POD Actual Arrival Date (POD_ATA_DT) — TIMESTAMP
  • 1st Received POD ETA Date (N1ST_RCV_POD_DT) — TIMESTAMP
  • Create Date (CRE_DT) — TIMESTAMP — NOT NULL
  • Update Date (UPD_DT) — TIMESTAMP

TRAKING_EVENT_HISTORY (VPP_TRK_EVNT_HIS)

  • Message Sequence (MSG_SEQ) — DECIMAL(2,0) — Primary key — NOT NULL
  • Tracking ID (TRK_ID) — VARCHAR(50) — Foreign key → TRACKING_MASTER.Tracking ID — NOT NULL
  • Event ID (EVNT_ID) — VARCHAR(50)
  • Event Type (EVNT_TP_CD) — VARCHAR(10) — The Event Type of the object - to be used as a discriminator. NB: This field should be considered Metadata
  • Event Code (EVNT_CD) — VARCHAR(4) — Identifier for type of Transport event ARRI (Arrived) DEPA (Departed) LOAD (Loaded) DISC (Discharged) GTIN (Gated in) G…
  • Event Description (EVNT_DESC) — VARCHAR(150) — Vessel Arrival
  • Event Class Code (EVNT_CLSS_CD) — VARCHAR(3) — Code for the event classifier can be ACT (Actual) PLN (Planned) EST (Estimated)
  • Event Date (EVNT_DT) — TIMESTAMP — The local date and time, where the event took place or when the event will take place, in ISO 8601 format.
  • Event Created Date (EVNT_CRE_DT) — TIMESTAMP — he timestamp of when the event was created. NB: This field should be considered Metadata
  • Transport Mode Code (TRSP_MOD_CD) — VARCHAR(10) — The mode of transport as defined by DCSA. Enum: [ VESSEL, RAIL, TRUCK, BARGE ]
  • Event Location Code (EVNT_LOC_CD) — VARCHAR(5)
  • Event Location Name (EVNT_LOC_NM) — VARCHAR(100)
  • Event Location Type Code (EVNT_LOC_TP_CD) — VARCHAR(3)
  • Event Location Facility Code (EVNT_LOC_FCTY_CD) — VARCHAR(8)
  • Event Location Facility Type Code (EVNT_LOC_FCTY_TP_CD) — VARCHAR(4)
  • Event Location Latitude (EVNT_LOC_LAT) — DECIMAL(10,7)
  • Event Location Longitued (EVNT_LOC_LON) — DECIMAL(11,8)
  • CREATE DATE (CRE_DT) — TIMESTAMP — NOT NULL

VESSEL MASTER (DLO_VSL_MST)

  • Vessel IMO Code (VSL_IMO_CD) — VARCHAR(10) — Primary key — NOT NULL
  • Vessel Name (VSL_NM) — VARCHAR(50) — The name of the Vessel given by the Vessel Operator and registered with IMO.
  • Vessel MMSI Code (VSL_MMSI_CD) — VARCHAR(10) — The mmsi number of the vessel
  • Vessel Call Sign Number (VSL_CALL_SGN_NO) — VARCHAR(10) — The carrier who is in charge of the vessel operation based on either the SMDG or SCAC code lists
  • Vessel Flag (VSL_FLG) — VARCHAR(2) — The flag of the nation whose laws the vessel is registered under. This is the ISO 3166 two-letter country code
  • Vessel Country Code (VSL_CNT_CD) — VARCHAR(2) — The code for the country that the vessel s registered in
  • Vessel Length (VSL_LEN) — NUMERIC — The vessel length in meters
  • Vessel Width (VSL_WDT) — NUMERIC — The vessel width in meters
  • Vessel Type Code (VSL_TP_CD) — VARCHAR(10) — The vessel type