2136548 - Consulting Note - Upload of Ocean Carrier Schedules

SAVE OFFLINE

2020-04-05

2136548

2136548 - Consulting Note - Upload of Ocean Carrier Schedules Version Language Priority Release Status Component

7 English Correction with medium priority Released for Customer TM-MD-TN-SCH ( Schedule )

Type Master Language Category Released On

SAP Note English Consulting 10.11.2017

Please find the original document at https://launchpad.support.sap.com/#/notes/ 2136548

Symptom You want to upload ocean carrier schedules from an external source into Transportation Management (TM) 9.1 or higher. This consulting note explains how the schedule data can be uploaded, for example from a CSV file or Microsoft Excel file. An FAQ section is listed at the end and samples files for the ocean carrier schedule upload are attached to this SAP Note.

Other Terms XLS XLSX

Reason and Prerequisites You use ocean carrier schedules in your scenario and want to create the schedules in TM based on external data.

Solution For uploading schedules from an external source, you can use the schedule upload report: Application Administration -> General Settings -> Master Data Administration -> Transportation Network -> Schedule Upload (transaction /SCMTMS/SCH_UPLOAD). The report executes process controller strategies of the process controller service 'TM_SCH'. For the upload of ocean carrier schedules, the strategy 'SCHUP_CS_S' is provided in standard via SAP Note 2118641. You can create your own upload strategies to upload schedules from a customer specific source. As a prerequisite, you have configured the relevant settings in Customizing for Transportation Management. For example, you have defined master data such as locations, carriers, and schedule types that are used in the schedules you want to upload to your SAP TM system. Upload Schedules From an External File The following sections describe how you can upload ocean carrier schedules with the standard schedule upload strategy 'SCHUP_CS_S'. Start the schedule upload report /SCMTMS/SCH_UPLOAD and select the execution option 'Upload and Process File' and choose the schedule upload strategy SCHUP_CS_S. Press the pushbutton 'Browse File' and choose the path to the CSV file or Microsoft Excel file you want to upload. Execute the report to start the

© 2020 SAP SE or an SAP affiliate company. All rights reserved

1 of 6

2020-04-05

2136548

upload. Prerequisites for the Schedule Upload • You have created the schedule type used for the upload in Customizing (SAP Transportation Management -> Master Data -> Transportation Network -> Schedule -> Define Schedule Types). For the standard upload format, these schedule types have to use an internal header number range and absolute offset times. The schedule ID is drawn automatically by the system during the upload. • You have created the Customizing entries (UN/LOCODE locations codes, Standard Carrier Alpha Codes (SCAC)) and the master data (business partner, locations) used for the upload. • For the upload of ocean carrier schedules, the used locations in the system have to be defined with the correct UN/LOCODE. In the standard upload format, the ports are identified via their UN/LOCODE. The system determines based on this UN/LOCODE the most appropriate location, which has the corresponding UN/LOCODE and the location type 'port' (1100). The business partners for the carriers have to be defined with the corresponding SCAC code. • The external schedule data has to be mapped to the standard schedule upload format (see attached sample files) if the standard upload strategies are used. In customer-specific upload strategies, any upload format can be used. Upload File Format Description The format for the upload of ocean carrier schedules is based on voyage stages. Each line in the upload file corresponds to a stage of a voyage. A voyage can consist of one or more stages. The sequence of the entries in the upload file is important: The lines have to be grouped by carrier, service loop and voyage. That means for multi-stop voyages, sequential stages have to be listed in ascending order. One file format is used for the initial upload of schedule voyages and for the delta update of already existing voyages. All voyages which have the same carrier and service loop are grouped into one ocean carrier schedule. If the corresponding schedule does not yet exist, a new schedule is created. The schedule upload supports CSV files or MS Excel files. In both cases, the file structure is the same: The first line is the header line with the column names. All other lines starting with the second one contain the actual schedule data. For the CSV file, a comma or a semicolon is required as separator. See the attached sample files as reference. The CSV file format is recommended for the schedule upload, because the file size is smaller compared to a MS Excel file with the same content. Large CSV files can also be uploaded as compressed ZIP archives. CSV files can nevertheless be opened and maintained in MS Excel for a better usability. To prevent MS Excel from automatically changing data (date format or removing of leading zeros), select all fields and format them as "Text". Ocean Carrier Schedule Columns of the ocean carrier schedule: Field SCH_TYPE

Mandatory Type CHAR

Length 4

TSP_ID

-

CHAR

10

TSP_SCAC LOOP_ID VOYAGE VOYAGE_KEY

X X X -

CHAR CHAR CHAR CHAR

4 40 10 35

Description Schedule Type; if empty, the default schedule type from the schedule upload report is used TM specific business partner ID of the carrier. If empty, the system determines the corresponding business partner from the field TSP_SCAC. Standard Carrier Alpha Code for the voyage Name of the Service Loop Voyage Number Unique external ID of a voyage, used to

© 2020 SAP SE or an SAP affiliate company. All rights reserved

2 of 6

2020-04-05

2136548

STAGE_ID

X

CHAR

35

STAGE_TSP_ID

-

CHAR

10

STAGE_TSP_SCAC

-

CHAR

4

STAGE_TYPE VESSEL IMO_ID DEP_PORT_UNLOCODE X EXIT_PORT ARR_PORT_UNLOCODEX ENTRY_PORT DEP_DATE X DEP_TIME X

CHAR CHAR CHAR CHAR CHAR CHAR CHAR DATS NUMC

3 35 11 5 1 5 1 8 4

ARRIVAL_DATE ARRIVAL_TIME

X X

DATS NUMC

4 4

CARGO_CO_DATE

-

DATS

8

CARGO_CO_TIME

-

NUMC

4

DOC_CO_DATE

-

DATS

8

DOC_CO_TIME

-

NUMC

4

DGDOC_CO_DATE

-

DATS

8

DGDOC_CO_TIME

-

NUMC

4

AVAIL_DATE

-

DATS

8

AVAIL_TIME

-

NUMC

4

CHANGE_MODE

-

CHAR

1

distinguish voyages that have the same voyage number within a loop. Unique external ID of a voyage stage, used for the delta update. TM specific business partner ID of the carrier for this stage. If empty, the system determines the corresponding business partner from the field STAGE_TSP_SCAC. Standard Carrier Alpha Code of the stage of the voyage TM specific Stage Type Vessel Name International Maritime Organization Number UN/LOCODE of the Departure Port Port of Exit Flag UN/LOCODE of the Arrival Port Port of Entry Flag Departure Date (YYYYMMDD) Departure Time (HHMM, local time of the departure port) Arrival Date (YYYYMMDD) Arrival Time (HHMM, local time of the arrival port) Cargo Cut-Off Date; if empty, it is filled with the departure date (YYYYMMDD) Cargo Cut-Off Time (HHMM, local time); if empty, it is filled with the departure time Document Cut-Off Date; if empty, it is filled with the departure date (YYYYMMDD) Document Cut-Off Time (HHMM, local time); if empty, it is filled with the departure time Dangerous Goods Document Cut-Off Date; if empty, it is filled with the departure date (YYYYMMDD) Dangerous Goods Document Cut-Off Time (HHMM, local time); if empty, it is filled with the departure time Availability Date; if empty, it is filled with the arrival date (YYYYMMDD) Availability Time (HHMM, local time); if empty, it is filled with the arrival time Change Mode for the delta update (Create C, Update U, Delete D); empty for the initial upload

Initial Upload For the initial upload of the ocean carrier schedules, all voyages have to be completely listed in the file in groups per carrier, service loop and voyage. The change mode field has to be empty for all these new voyages. For multi-stage voyages, the stages have to be listed underneath each other in the correct sequence of their appearance.

© 2020 SAP SE or an SAP affiliate company. All rights reserved

3 of 6

2020-04-05

2136548

Delta Upload After the initial upload, you can regularly upload updated voyage data into the system. For this delta upload, only the changed voyage stages have to be listed in the file. According to the type of the change, the change mode field has to be set: Change Modes: • C: Create - Set for new voyage stages that should be inserted into an existing voyage. • U: Update - Set for existing voyage stages, which should be updated with changed data. • D: Delete - Set for existing voyage stages, which should be deleted. If all stages of a voyage have this delete change mode, the complete voyage is deleted. • ' ': Empty - Set for the initial upload of a new voyage stage that should be created as part of a new voyage. For changing existing, already uploaded voyages, one of the three change modes Create (C), Update (U) or Delete (D) has to be set. If a completely new voyage should be created, the change mode has to be empty like for the initial upload. The sequence in which the delta files are uploaded is important. As the delta updates only contain the changed voyage stages and not the complete voyages, they have to be uploaded in the correct sequence. The system will reject invalid changes, for example an update of a voyage that has not been created yet. Schedule Deletion Stages of a voyage or a complete voyage can be deleted via the delta upload. To delete a complete schedule or voyages that were flagged for deletion, but could not be deleted due to existing references in the system, the schedule deletion report can be used: Application Administration -> General Settings -> Master Data Administration -> Transportation Network -> Schedule Deletion (transaction /SCMTMS/SCH_DELETION). For example, the schedule deletion report could be scheduled regularly to clean up obsolete voyages. Sample Files for the Schedule Upload Attached to this SAP Note are the following sample files. The two files for the initial upload can be uploaded independently of the other files. The delta update files for change, insert and delete can only be uploaded directly after the initial upload of the multi-stage file. The second delete file can only be uploaded after the first delete file. Note: The attached sample files require SAP Note 2241682 as pre-requisite. Otherwise the optional column "STAGE_TYPE" has to be removed from the files. File Name

Description

Ocean_Schedule_Upload_Single_Stage.xlsx

Initial upload of 2 schedules, which have 3 single-stage voyages each

Ocean_Schedule_Upload_Multi_Stage_Initial.xlsx

Initial upload of 2 schedules, which have 2 multi-stage voyages each

Ocean_Schedule_Upload_Multi_Stage_Update_Change.xl Delta update for the initial multi-stage file, which sx updates the first voyage Ocean_Schedule_Upload_Multi_Stage_Update_Insert.xl sx

Delta update for the initial multi-stage file, which inserts stages into the existing voyages

Ocean_Schedule_Upload_Multi_Stage_Update_Delete_1. Delta update for the initial multi-stage file, which xlsx deletes stages of the existing voyages

© 2020 SAP SE or an SAP affiliate company. All rights reserved

4 of 6

2020-04-05

2136548

Delta update for the first delete update, which Ocean_Schedule_Upload_Multi_Stage_Update_Delete_2. deletes a complete voyage (= all of the xlsx voyage's stages) Frequently Asked Questions Q: I want to upload a large file and get a program termination during the upload because of a timeout on the UI. A: For large files where the upload takes longer than the UI timeout, the recommendation is to save the upload file as ZIP-compressed CSV file on the DB. The upload report can then be executed in the background to process the file from the DB. See the referenced SAP Note 1743069. Q: I want to adjust the upload file to my use case by removing optional columns and by changing the column sequence. How can I do that? A: With SAP Note 2238579 the sequence of the columns can be changed and optional columns can be left out in the file. The column names in the header line have to match excatly to the predefined names, except if a mapping is defined (see next question). Q: Can the column names in the file be changed? A: With SAP Note 2242813 a mapping of the external column names in the file to the internal field names can be defined.

Software Components Software Component

Release

SAPTM

120 - 120

SAPTM

130 - 130

SAPTM

140 - 140

This document refers to SAP Note/KBA

Title

2242813

Schedule Upload: Mapping of custom column names in the upload file

2241682

Schedule Upload: Stage type field missing for upload of ocean carrier schedules

2238579

Schedule Upload: Flexible column sequence in upload file

2118641

Ocean Carrier Schedule Integration

1743069

Consulting Note: Schedule Upload from File

© 2020 SAP SE or an SAP affiliate company. All rights reserved

5 of 6

2020-04-05

2136548

This document is referenced by SAP Note/KBA

Title

2680251

/SCMTMS/SCH_UPLOAD - Stage cannot be added to voyage; schedule does not exist

2520178

How to Selectively Delete Schedule Departures in SAP Transportation Management

2241682

Schedule Upload: Stage type field missing for upload of ocean carrier schedules

2118641

Ocean Carrier Schedule Integration

Attachments File Name

File Size

Mime Type

Ocean_Schedule_Upload_Multi_Stage _Update_Change.xlsx

13

application/vnd.openxmlformats-of ficedocument.spreadsheetml.sheet

Ocean_Schedule_Upload_Multi_Stage _Update_Insert.xlsx

14

application/vnd.openxmlformats-of ficedocument.spreadsheetml.sheet

Ocean_Schedule_Upload_Multi_Stage _Update_Delete_1.xlsx

14

application/vnd.openxmlformats-of ficedocument.spreadsheetml.sheet

Ocean_Schedule_Upload_Multi_Stage _Update_Delete_2.xlsx

13

application/vnd.openxmlformats-of ficedocument.spreadsheetml.sheet

Ocean_Schedule_Upload_Single_Stag e.xlsx

14

application/vnd.openxmlformats-of ficedocument.spreadsheetml.sheet

Ocean_Schedule_Upload_Multi_Stage _Initial.xlsx

15

application/vnd.openxmlformats-of ficedocument.spreadsheetml.sheet

Terms of use | Copyright | Trademark | Legal Disclosure | Privacy

© 2020 SAP SE or an SAP affiliate company. All rights reserved

6 of 6