[Dave Lee]

Hi,

I have compile a list of tables require when uploading configuration data into OTM.

Location

· LOCATION
· CORPORATION
· LOCATION_REFNUM
· LOCATION_CORPORATION
· LOCATION_ADDRESS
· LOCATION_ROLE_PROFILE
· CONTACT

THU

· SHIP_UNIT_SPEC

Equipment

· EQUIPMENT_GROUP
· EQUIPMENT_GROUP_PROFILE
· EQUIPMENT_GROUP_PROFILE_D


Rates

· HNAME_SET
· HNAME_SET_MEMBER
· X_LANE
· LANE
· RATE_SERVICE
· SERVICE_TIME
· RATE_VERSION

Does anyone have the tables for Rate Offerring, Rate Record and Itineries.

Thanks

Regards,
Dave Lee

[oliver]

Hi Dave,

i guess the ERD gives you the info that you need, like for itinerary :
- itinerary
- itinerary_detail
- leg
- transport_mode
- region
- etc......

Rgds
Oliver

[Dave Lee]

Thanks. Do you know where can i get the ERD from? One more question, for migration between UAT environment to Production environment, is there other better methods beside data upload via CSV in OTM?

[jeff.depaul]

Dave,
There is a better way, you can post the data load to the CSVUtil servlet. The call would look something like this:
"java glog.database.admin.CSVUtil -command iu -maxError 1 -connectionId <<db_name>> -dataDir $FILEDIR/ -dataFileName $TABLENAME -runsqlloader -debug -xvalidate Y"

We wrapped the call to this in a shell script which reads the tables in order from another file recursively (similar to what you mentioned above) - specifically we used for loading rates. The program takes some time to get setup just right but once set, you can use it for loading just about any data that you would load csv from the front end.

-Jeff

[arunk]

Originally Posted by Dave Lee

Thanks. Do you know where can i get the ERD from? One more question, for migration between UAT environment to Production environment, is there other better methods beside data upload via CSV in OTM?

Dave,

FYI - a related post on ERD.
http://www.otmfaq.com/forums/f63/otm-erwin-diagram-702/ (OTM Erwin Diagram) (OTM Erwin Diagram)

[Dave Lee]

I have encountered a problem when i upload Region data.

There are two tables to upload

-REGION
-REGION_DETAIL

Region table is quite straightforward. But Region Detail has a SEQUENCE_NUM column which is giving me a headache. I have tried to leave this field empty and let OTM generate the number but i notice that doing so will result in the sequence_num being assign duplicate. If i manually assign the next seq number in the excel before upload, i am unable to add location to that region thru OTM online screen as it will throw an exception.

Anyone know how to upload data if that table has sequence number? Thanks

[kumar]

Hi all,

While uploading data into OTM via XML, data will go through via I_transmission or I_transaction table like wise when we upload data into OTM via CSV Is there any intermediatry tables are present in OTM? If so, please tell me table names....

Thanks in advance!!!
Kumar

[bmj_23]

Hi - when you are uploading data from CSV format in to OTM, there are no intermediary tables that are involved. Either the data gets loaded or it does not (because of PK, FK errors etc) - the data does not get staged (as is the case with i_transmissions and i_transaction) and then processed, it is simply loaded or not.

[Dave Lee]

Originally Posted by bmj_23

Hi - when you are uploading data from CSV format in to OTM, there are no intermediary tables that are involved. Either the data gets loaded or it does not (because of PK, FK errors etc) - the data does not get staged (as is the case with i_transmissions and i_transaction) and then processed, it is simply loaded or not.

Yes, this is what i notice also. But what i dun understand is why OTM behave differently when upload CSV vs manually enter it in OTM for tables that uses sequence number. For example, when i tried to upload region via CSV, i notice that the sequence number in region detail table is not the next running number of the last region detail record. When i manually enter it in OTM, the sequence number is the next running number of the last region detail record.
So if i upload region via CSV, i will may have the same sequence number in region details table. OTM can still insert the data because the primary key to this table are
REGION_GID and SEQUENCE_NUM. I am not sure whether having same sequence number will impact any downstream flow?
Thanks

[bmj_23]

It has been a while since I looked at those actual tables involved, but as far as I remember, you can assign whatever sequence number you like to data that you upload via CSV. If the standard sequence that is being generated when you create records via the UI is 1,2,3 etc, I would use a sequence starting with 91, 92, 93 etc, for data you are uploading.

The basic issue as I understand it is that when you create a record via the UI, OTM is using an oracle sequence number to generate the corresponding sequence number - you are bypassing that logic when uploading via CSV. I am not aware of any downstream issues.

Anyone else with experience of this ?