ODBC transaction log table in log database

AFLD

SQL_INTEGER

10

Auto increment field (AutoNumber)

RUN_ID

SQL_BIGINT

9

Runtime ID – loaded from internal run id number that was passed in as a –id parameter. This run id is also loaded once into the SYS_RUNID system variable, but the value of SYS_RUNID is not used for TRLOG. The run id always numeric from initial parameter value.

TYP

SQL_VARCHAR

1

Type flag:

• H – header (ST)

• T – trailer (SE)

• D – detail (messages between ST and SE)

• U – user write log command

RUN_DATE

SQL_TIMESTAMP

14

Runtime date – loaded from the

SYS_DATE and SYS_HHMMSS system variables (SYS_DATE and SYS_HHMMSS are loaded from the system time.)

ACKBY_DATE

SQL_TIMESTAMP

14

Date by which an acknowledgement must be made

• Inbound – Before each log write, if TYP is H, date is loaded from the INT_HEAD_DATE and INT_HEAD_TIME system variables, which are loaded from envelope.

  If TYP is not H, then null date.

• Outbound – Before each log write, if TYP is H and an acknowledgment was requested, date is calculated from today’s date plus the value in the DAYS, HOURS, and MINUTES fields in the tradstat database. If TYP is not H, then null date.

TRANS_CODE

SQL_VARCHAR

2

Transaction code – loaded from the SYS_TRCODE system variable.

• Inbound – SYS_TRCODE loaded from envelope.

• Outbound – SYS_TRCODE loaded from parameter.

TRANS_NAME

SQL_VARCHAR

6

Transaction name – loaded from the SYS_TRANS system variable, which is loaded from internal transaction code before each log write.

Message Type – MSH 00009

TPTNER_ID

SQL_VARCHAR

35

Code used to identify the trading partner

Loaded from the SYS_TRADNO system variable.

• Inbound – TPTNER_ID is looked up in trading partner database based on lookup criteria and data in EDI envelope.

• Outbound – TPTNER_ID is loaded from an application file.

VERSION

SQL_VARCHAR

12

Version of EDI standard used in the map

Loaded from the X12_VERSION system variable.

• Inbound – X12 version loaded from envelope

• Outbound – Loaded from tradstat table

  • Version/Release/Industry Identifier Code – GS 08

  • Message Version Number – UNH S009 0052

  • Message Release Number – UNH S009 0054

ISA_TYPE

SQL_VARCHAR

5

EDI standard used by this trading partner in this transaction

• Inbound – Loaded from the INT_VERSION system variable, which is loaded from the EDI envelope.

• Outbound – Loaded from the ISA_TYPE system variable, which is loaded from tradstat table.

  • Interchange Control Version Number – ISA 12

  • Syntax Identifier – UNB S001 0001

  • Syntax Version Number – UNB S001 0002

INTERCHANG

SQL_VARCHAR

35

Interchange code – loaded from the INT_HEAD_NUM system variable

• Inbound – Value taken from the EDI envelope.

• Outbound – Loaded from the ISA control number taken from trading partner table and incremented.

  • Interchange Control Number – ISA 13

  • Interchange Control Count – UNB S004 0020

GROUP_NO

SQL_VARCHAR

35

Group number – loaded from the FUNC_GP_NUM system variable

• Inbound – Value taken from EDI envelope.

• Outbound – Loaded from GS control number taken from the tradstat table and incremented

  • Functional Group Header Control Number – GS 06

  • Batch Control ID – BHS 00091

TRANS_NO

SQL_VARCHAR

35

Transaction number – loaded from the FUNC_ST_NUM system variable

• Inbound – Value taken from EDI envelope.

• Outbound – Loaded from 1000 * (GS control number taken) + transaction count.

APP_RCV_CD

SQL_VARCHAR

35

Code used to identify the group level receiver on outbound maps and sender on inbound maps. Loaded from the APP_RECV_CODE system variable.

• Inbound – Loaded from EDI envelope.

• Outbound – Loaded from Tradepartner.

  • Application Receiver Code – GS 03

  • Interchange Receiver Internal Sub-ID – UNB 0003 0046

  • Receiving Facility – MSH 00006

  • File Receiving Facility – FHS 00072

  • Batch Receiving Facility – BHS 00086

APP_SND_CD

SQL_VARCHAR

35

Code used to identify the group level sender on outbound maps and receiver on inbound maps. Loaded from the APP_SEND_CODE system variable.

• Inbound – Loaded from EDI envelope

• Outbound – Loaded from tradepartner.

  • Application Sender Code – GS 02

  • Interchange Sender Internal Sub-ID – UNB S002 0042

  • Sending Facility – MSH 00004

  • File Sending Facility – FHS 00070

  • Batch Sending Facility – BHS 00084

RECV_CODE

SQL_VARCHAR

35

Code used to identify the interchange level receiver on outbound maps and sender on inbound maps. Loaded from the RECV_CODE system variable.

• Inbound – Loaded from EDI envelope

• Outbound – Loaded from tradepartner.

  • Interchange Receiver ID Code – ISA 08

  • Interchange Receiver ID – UNB S003 0010

  • Receiving Application – MSH 00005

  • File Receiving Application – FHS 00071

  • Batch Receiving Application – BHS 00085

SEND_CODE

SQL_VARCHAR

35

Code used to identify the interchange level sender on outbound maps and receiver on inbound maps.

Loaded from the SEND_CODE system variable.

• Inbound – Loaded from EDI envelope

• Outbound – Loaded from tradepartner.

  • Interchange Sender ID Code – ISA 06

  • Interchange Sender ID – UNB S002 0004

  • Sending Application – MSH 00003

  • File Sending Application – FHS 00069

  • Batch Sending Application – BHS 00083

RECV_QUAL

SQL_VARCHAR

4

Qualifier that specifies the type of code used to identify the interchange level receiver on outbound maps and sender on inbound maps.

Loaded from the RECV_QUAL system variable.

• Inbound – Loaded from EDI envelope

• Outbound – Loaded from tradepartner.

  • Interchange Receiver ID Qualifier – ISA 07

  • Interchange Receiver ID Code Qualifier – UNB S003 0007

SEND_QUAL

SQL_VARCHAR

4

Qualifier that specifies the type of code used to identify the interchange level sender on outbound maps and receiver on inbound maps.

Loaded from the SEND_QUAL system variable.

• Inbound – Loaded from EDI envelope

• Outbound – Loaded from tradepartner.

  • Interchange Sender ID Qualifier – ISA 06

  • Interchange Sender ID Code Qualifier – UNB S002 0007

ERRORS

SQL_BIGINT

10

Total errors – loaded from internal system count of errors between ST and SE.

The LOG_ERRS and TOT_ERRS system variables are loaded at same time. LOG_ERRS is the number of errors between ST and SE. TOT_ERRS is the total number of errors for the run.

STAT

SQL_VARCHAR

1

Status – loaded from internal count of total # errors.

The LOG_STATUS system variable is also loaded at time of write log.

• W – SEG_ST, SEG_SE, User Write

• T – Bad Tradepartner

• S – Bad ISA, GS or ST

• U – Stop Run

• A – Abort Trans

• F – Fatal Error

• E – Other Error Message

BYTE-COUNT

SQL_BIGINT

10

Count of the number of bytes between ST and SE – will be zero for every ST and increment until SE is written.

The LOG_SIZE system variable is loaded with the byte count at time of write log.

DIR

SQL_VARCHAR

3

Direction of map

• Outbound – OUT

• Inbound – IN, CMP, PRT

There is no system variable

FLOW_LEVEL

SQL_VARCHAR

5

Level of segment in flow – the LOG_LEVEL system variable is loaded at time of write log.

RECORD_NAM

SQL_VARCHAR

10

Record name – the LOG_RECNAME system variable is loaded at time of write log. Can be assigned by user in write log if the following conditions are present:

• Mapping is in progress and the field being mapped to or from is a record field, then this record name value is used.

• Error occurs during a rule, then code attempts to identify the record name involved.

• The field involved is a memory variable or a string variable, then the memory variable or string variable is written to the RECORD_NAM log field.

• No record is associated with the error, then this field is blank. This field cannot be written to by the user assigning a value to the system variable.

RECORD_NO

SQL_VARCHAR

6

Record number – the LOG_READ_CNT system variable can be assigned by user in write log command. Otherwise the system variable and log are loaded and written at the same time.

• Inbound – This is the line number being processed in the incoming EDI file.

• Outbound – This field will contain the count that this record type has been read if the error message involves a record. Otherwise it will be zero.

FIELD_NAME

SQL_VARCHAR

15

Field name – the LOG_FIELDNAME system variable can be assigned by user in write log command. Otherwise LOG_FIELDNAME and FIELD_NAME log field are loaded at the same time of write log.

If mapping is in progress and the field being mapped to or from is a record field, then this field name value is used.

If error occurs during a rule, then code attempts to identify the field name involved.

This field cannot be written to by the user directly assigning a value to the system variable.

SEGMENT

SQL_VARCHAR

3

Segment – can be assigned by user in write log command.

• Inbound – Current segment name LOG_SEG

• Outbound – Will have a value on write logs for SEG_ST and SEG_SE types and for errors that occur during direct mapping to EDI file.

SEG_COUNT

SQL_INTEGER

10

Segment count – can be assigned by user in write log command.

Count of segments between ST and SE inclusive.

The SEGMENT_COUNT system variable loaded every time segment is written/read from EDI File.

ELEMENT

SQL_VARCHAR

2

Element – can be assigned by user in write log command.

The LOG_ELEM system variable

SUBELEM

SQL_VARCHAR

2

Sub-element – can be assigned by user in write log command.

The LOG_SUBELEM system variable

SEV_CODE

SQL_VARCHAR

2

Severity code – can be assigned by user in write log command.

For all system error messages this code is a 1.

For other system non-error messages this code is 0.

MSG_NO

SQL_VARCHAR

5

Message number – can be assigned by user in write log command.

The LOG_MSG_NO system variable

MSG_TEXT

SQL_VARCHAR

100

Message text – can be assigned by user in write log command.

The LOG_MSG system variable

FILENAME

SQL_VARCHAR

160

For ST segments:

• Outbound – FILENAME contains the current EDI outbound file name (can change based on tradstat and tp mailbox entries). Filename is not available as the system variable.

• Inbound – The inbound EDI file name (always the same).Filename is not available as the system variable.

For SE segments and inbound transactions:

FILENAME

• Contains the current file name of any EDI OUT file names (can change based on tradstat, tp mailboxes and tradstat EDI_OUT field)

• Consists of both complete path and file name.

• Is available as the EDI_OUT_FILENAME system variable.

FIELDVAL

SQL_VARCHAR

30

Field value – the LOG_VALUE system variable.

Can be assigned by user in write log command. Otherwise the system variable and log field value are loaded at time of write to the log. If this is an error message, the value of the field code attempts to load the value of the record field, memory variable, or string constant in error.

USER_IDENT

SQL_VARCHAR

35

User-defined field – the SYS_USER_FIELD system variable.

Log field is loaded from system variable.

RTP does not assign values to SYS_USER_FIELD.

ACK_EXPECT

SQL_VARCHAR

1

Flag that specifies whether a TA1 interchange-level acknowledgement is expected, only on outbound maps:

• 1 = TA1 acknowledgement requested

• 0 = TA1 acknowledgement not requested

The ACK_REQSTD system variable.

Inbound – Loaded from EDI Envelope (ISA element 14).

TR_ACK_TYP

SQL_VARCHAR

1

Flag that specifies whether a group-level functional acknowledgement is expected, only on outbound maps:

• 1 = functional acknowledgement requested

• 0 = functional acknowledgement not requested

The corresponding system variable is TR_ACK.

Outbound – Loaded from tradstat table

T_P_IND

SQL_VARCHAR

1

Test/Production Indicator

• T – T Test

• P – Production

• I – Information

• D – Debug

• 1–9

• Null

The TEST_IND system variable

Inbound – Loaded from EDI envelope

Outbound – Loaded from Tradstat.

TRANS_CNT

SQL_INTEGER

10

Transaction count – no system Variable.

Inbound/Outbound – This log value is loaded from an internal count of the number of ST-SE transactions read or written between SEG_GS and SEG_GE.

FILEOFFSET

SQL_BIGINT

10

Number of Bytes File Offset – no system variable. Written from internal count of number of bytes read (inbound) or number of bytes written to the EDI file.

RCOUNT

SQL_SMALLINT

1

Field for Record Manipulation – always set equal to 1 before log write.

SNDR_ROUTE

SQL_VARCHAR

14

(35 for Syntax 4)

Internal code used to identify the interchange level sender on outbound maps and receiver on inbound maps.

Interchange Sender Internal ID – UNB S002 0008

SNDR_SUBID

SQL_VARCHAR

35

Internal sub-code used to identify the interchange level sender on outbound maps and receiver on inbound maps.

(EDIFACT Syntax 4 only)

Interchange Sender Internal Sub-ID – UNB S002 0042

RCVR_ROUTE

SQL_VARCHAR

14

(35 for Syntax 4)

Internal code used to identify the interchange level receiver on outbound maps and sender on inbound maps.

Interchange Receiver Internal ID – UNB S003 0014

RCVR_SUBID

SQL_VARCHAR

35

Internal sub-code used to identify the interchange level receiver on outbound maps and sender on inbound maps.

(EDIFACT Syntax 4 only)

Interchange Receiver Internal Sub-ID-UNB S003 0046

APPL_REF

SQL_VARCHAR

14

Name of the application messages contained in the EDIFACT UNB envelope.

Application Reference – UNB S005 0026

PROC_PRIOR

SQL_VARCHAR

1

Processing Priority Code – UNB S005 0029

COMM_AGM

SQL_VARCHAR

35

Interchange Agreement Identifier – UNB S005 0032

APP_SND_QL

SQL_VARCHAR

4

Qualifier that specifies the type of code used to identify the trading partner at the group level – as the sender on outbound maps and as the receiver on inbound maps.

Application Sender ID/ID Code Qualifier – UNG S006 0007

APP_RCV_QL

SQL_VARCHAR

4

Qualifier that specifies the type of code used to identify the trading partner at the group level – as the receiver on outbound maps and as the sender on inbound maps.

Application Receiver ID/ID Code Qualifier – UNG S007 0007

ASSOC_CODE

SQL_VARCHAR

6

Association Assigned Code –

• UNG S008 0057

• UNH S009 0057

APP_PSWD

SQL_VARCHAR

14

Application Password – UNG S008 0058

CLIST_VER

SQL_VARCHAR

6

(EDIFACT Syntax 4 only)

Code List Directory Version Number – UNH S009 0110

MSG_TYPE

SQL_VARCHAR

6

(EDIFACT Syntax 4 only)

Message Type Sub-Function Identifier – UNH S009 0113

RPT_NO

SQL_VARCHAR

2

(X12 version 40020 and later)

Repeat Number – this field holds the number of a repeating element

ST03

SQL_VARCHAR

35

Implementation Convention Reference – ST 03

Value to be used as the third element in the transaction on outbound X12 processing.