Sterling GIS/SI: DB Structure: Envelopes

I’m going to explain the Sterling GIS/SI DB structure from the entities prospective. Lets start with Envelopes. There are 3 tables:

  • ENVELOPE – envelope definitions. As you know, every time you change the envelope (map, BP, etc) GIS/SI stores it as a new version, so you can easily rollback if needed. So, this table stores all the versions of envelopes.
  • ENVELOPE_VERSIONS – versions. DEFAULT_VERSION field is used to indicate the selected version of the envelope.
  • ENVELOPE_PARMS – it’s a key/value table, i.e. every pair of data (name and value) creates a record in this table. These params are envelope-type specific, i.e. an Inbound X12 ISA envelope has it’s own params and an outbound X12 GS or UN/EDIFACT UNH have their own.

I won’t describe the structures of these tables in details – you should use ERD instead. But I’m going to give you a couple of SQL queries and a screenshot which would help you to understand it:

select * from ENVELOPE e, ENVELOPE_VERSIONS ev
where
        e.ENVELOPE_ID = ev.ENVELOPE_ID
        and e.ENVELOPE_VERSION = ev.DEFAULT_VERSION
        and NAME = '{Envelope Name}'
 
 
select ep.* from ENVELOPE e, ENVELOPE_VERSIONS ev, ENVELOPE_PARMS ep
where
        e.ENVELOPE_ID = ev.ENVELOPE_ID
        and e.ENVELOPE_VERSION = ev.DEFAULT_VERSION
        and e.ENVELOPE_ID = ep.ENVELOPE_ID
        and e.ENVELOPE_VERSION = ep.ENVELOPE_VERSION
        and e.NAME = '{Envelope Name}'

The first query returns you the default (selected) record of the envelope by name. The second returns you all the params:

Why might you need to have access to the DB/Envelopes? For example, if you need to get the list of all the envelopes with Sender/Receiver ID and so on. You can do it manually with using of the Web Dashboard, but there is an easier and more elegant way to do it.

Gennady Kim

Advertisements

3 thoughts on “Sterling GIS/SI: DB Structure: Envelopes

  1. Pingback: Sterling GIS/SI: DB: Business Processes | EDI shortcuts
  2. I don’t know if it can be usefull but i had to do it and you were really usefull ( you did 90% of my query)

    Query to obtain the list of the envelopes with the maps linked:

    select e.name,e.standard,ep.VALUE from ENVELOPE e, ENVELOPE_VERSIONS ev, ENVELOPE_PARMS ep
    where
    e.ENVELOPE_ID = ev.ENVELOPE_ID
    and e.ENVELOPE_VERSION = ev.DEFAULT_VERSION
    and e.standard =’EDIFACT’
    and e.ENVELOPE_ID = ep.ENVELOPE_ID
    and e.ENVELOPE_VERSION = ep.ENVELOPE_VERSION
    and ep.NAME= ‘ComplianceCheckMapName’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s