Tables Requires for data upload via CSV

[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]

Re: Tables Requires for data upload via CSV

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]

Re: Tables Requires for data upload via CSV

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]

Re: Tables Requires for data upload via CSV

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]

Re: Tables Requires for data upload via CSV

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)

[Dave Lee]

Re: Tables Requires for data upload via CSV

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]

Re: Tables Requires for data upload via CSV

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]

Re: Tables Requires for data upload via CSV

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]

Re: Tables Requires for data upload via CSV

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]

Re: Tables Requires for data upload via CSV

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 ?

[Dave Lee]

Re: Tables Requires for data upload via CSV

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 ?

Yes, you can assign whatever sequence number in the CSV and upload it. I also thought of manually assign a higher range of number and hope that it will not meet one day . Thanks for sharing.

[nipunlakhotia]

Re: Tables Requires for data upload via CSV

Hi Dave,

Generally, while loading regions via CSV, it is always advisable to give some number in seq. no. field, rather than leaving it blank.

Also make sure seq. no. given in CSV is large enough (Example: 70000000234), so that it does not contradict with system generated seq. numbers.

********


Another important point has to be taken care while loading region via CSV i.e. always load Region CSV with all rows as "N" in 'LOADED' field.

Then use "Formulate Regions" functionality in Configuration & administrtaion >Process Managenent to formulate regions correctly.

[Dave Lee]

Re: Tables Requires for data upload via CSV

Hi nipunlakhotia,

Thanks for the info. It seem like uploading data into OTM tables via CSV that using seq number, you need to give some large number in seq. no. field.

There are few such tables that uses SEQ_NUM

RATE_GEO_COST --> RATE_GEO_COST_SEQ
REGION_DETAIL --> SEQUENCE_NUM

Thanks.

Regards,
Dave Lee

[nipunlakhotia]

Re: Tables Requires for data upload via CSV

Hi Dave,

A rate record can have many costs defined into it. Thus there can be multiple entries pertaining to RATE_GEO_COST_GROUP_GID in RATE_GEO_COST CSV. There should be different seq. no. for each cost associated with same RATE_GEO_COST_GROUP_GID.

But Seq. no. can be re-used across different RATE_GEO_COST_GROUP_GID.
[Refer screen shot attached]

[Dave Lee]

Re: Tables Requires for data upload via CSV

Hi Dave,

A rate record can have many costs defined into it. Thus there can be multiple entries pertaining to RATE_GEO_COST_GROUP_GID in RATE_GEO_COST CSV. There should be different seq. no. for each cost associated with same RATE_GEO_COST_GROUP_GID.

But Seq. no. can be re-used across different RATE_GEO_COST_GROUP_GID.
[Refer screen shot attached]

Thanks for the info.