Message warehouse fields for FIN messages

The SIPN FIN and FMT FIN services store information about the messages that they process in the following message warehouse views (in each name, ou represents the name of the OU):
DNIV_MWH_ou
This is a view of the main message warehouse table DNIMWH_PT_ou. It contains one entry for each message.
DNF_MWHFN_ou
This is a view of the adjunct message warehouse table DNFMWHFIN_ou. It contains additional fields that apply only to FIN messages processed by the SIPN FIN or FMT FIN services.
Table 1 and Table 2 show which fields are filled for FIN messages. If the OU of a FIN message cannot be determined, the message is stored in the view DNIV_MWH_DNFSYSOU only.
Table 1. Fields of the view DNIV_MWH_ou that are set by the services that process FIN messages
Field name Data type Mandatory Description
MWH_OU_NAME CHAR(8) Yes The name of the OU to which the message belongs.
MWH_WMQI_MSG_ID CHAR(24) FOR BIT DATA Yes The message ID. This ID serves as the primary key in the message warehouse. This ID must be unique within an FTM SWIFT instance, and can be used to identify the information in this and other tables that corresponds to a particular message. Typically, this value is an IBM® MQ generated unique message ID.
GMT_TMSTMP TIME STAMP Yes Date and time, in Coordinated Universal Time (UTC), when the service requested that the message be stored in the message warehouse, or, if the message was updated, the date and time when the service last requested that it be updated.
MWH_MSG_STATUS VARCHAR (16) Yes The status of the message or message file. Possible values are described in Possible values in the MWH_MSG_STATUS field.
MWH_MSG_GRP VARCHAR (50) No The message group ID is used to correlate messages that belong together, such as an outbound message and its acknowledgment. This enables you to search for messages that belong to the same unit of processing.
The value of this field depends on whether the value of the message field ComIbmDni.MessageGroup was set by an application:
  • If so, this field is set to that value
  • If not, the service that processes the message sets this field to a hexadecimal representation of MQMD.MsgId, encoded using code page IBM-1047
MWH_BUSINESS_GRP VARCHAR (50) No The business group ID. This ID correlates the messages that belong to the same business transaction.
MWH_EXT_MSG_ID VARCHAR (50) No The message ID which is from an external system, for example, the Unique Message Reference (UMR) in MERVA ESA.
MWH_XML_MSG CLOB (100M) No The message that was processed by the service. The contents of this field depend on the value of the DnfContentMwh attribute of COs of type DnfOptionsMwh or DnqOptionsMwh:
Index
The value of this field is null.
IndexAndBody
This field contains the FIN message that comprised the body of the FTM SWIFT message, which it stores in the following structure: 
<Msg>
 <BLOB>
   <BLOB>
      hex representation of message body
   </BLOB>
 </BLOB>
</Msg>
.
IndexAndMessage
This field contains the entire FTM SWIFT message (that is, its headers and body), which it stores in the following structure: 
<Msg>
 <MQMD>
   fields of the MQMD as XML elements
 </MQMD>
 <MQRFH2>
   fields of the MQRFH2 as XML elements,
   and one or more NameValueData folder 
   in XML representation
 </MQRFH2>
 <BLOB>
  <BLOB> 
    hex representation of message body
  </BLOB>
 </BLOB>
</Msg>
This is the default.
MWH_AMOUNT DECIMAL (19,5) No The amount specified in the message.
For ISN and OSN messages, the value is derived from the first field 32 encountered in the message. If the field option of the field 32 is:
A
The amount is specified starting from character 10 until the end of the field 32.
B
The amount is specified starting from character 4 until the end of the field 32.
not A or B
The field is null.
For ISN ACKs, the field is null.
MWH_CURRENCY CHAR(3) No The ISO symbol for currency of the amount specified in the message. For ISN and OSN messages, the value is derived from the first field 32 encountered in the message. If the field option of this field is:
A
The currency is specified starting from character 7 until character 9.
B
The currency is specified starting from character 1 until character 3.
not A or B
The field is null.
For ISN ACKs, the field is null.
MWH_VALUE_DATE DATE No The value date for the amount or item within the message. For ISN and OSN messages, the value is derived from the first field 32 encountered in the message. If the field option of this field is:
A
The value date is specified starting from character 1 until character 6.
not A
The field is null.
For ISN ACKs, the field is null.
MWH_SENDER VARCHAR (128) No Identification of the sender of the message. For ISN and OSN messages, the value is the BIC12 of the sender of the message. For ISN ACKs, the value is set to 'SWIFT'.
MWH_RECEIVER VARCHAR (128) No The BIC12 of the receiver of the message.
MWH_ISN INTEGER No The input sequence number (ISN) used to identify a message that is passed to the network. For ISN messages and ACKs, the value is the input sequence number of the FIN message. For OSN messages, this field is null.
MWH_OSN INTEGER No The output sequence number (OSN) used to identify the message that is received from the network. For OSN messages, the value is the output sequence number of the FIN message. For ISN messages and ACKs, this field is null.
MWH_APPL_REFERENCE VARCHAR (32) No Application assigned unique identification. For ISN and OSN messages, the value is the content of field 108. For ISN ACKs, this field is null.
MWH_GMT_TS TIME STAMP No Date and time, in Coordinated Universal Time (UTC), when the service requested that the message be stored in the message warehouse.
MWH_AUTH_STATUS CHAR(8) No The authentication and authorization status of the message. For FIN messages, possible values are:
(NULL)
The authentication and authorization checks were not performed.
OK
The authentication and authorization checks were successful.
DG_FAIL
The attempt to authenticate the message by verifying its digest failed.
SG_FAIL
The attempt to authenticate the message by verifying its signature failed.
SG_INV
The message could not be authenticated because the certificate for signature verification was revoked or expired.
RM_NFND
RMA is in the mandatory usage stage for the service, and at least one of the following conditions applies:
  • The RM data store does not contain an authorisation for the specified combination of own BIC, correspondent BIC, service, and direction.
  • The authorisation does not provide the necessary permissions.
  • The current date and time are not within the validity period of the authorisation.
RM_INV
RMA is in the mandatory usage stage for the service, and the RMA authorisation for the specified combination of own BIC, correspondent BIC, service, and direction was revoked, deleted, or rejected.

If more than one authentication and authorization status applies to a message, the status that is closest to the bottom of this list is the value that is recorded. For example, if both SG_FAIL and RM_NFND apply, RM_NFND is recorded.
MWH_PD_INDICATOR CHAR(1) No Whether the message is a possible duplicate:
Y
Message is a possible duplicate.
N
Message is not a possible duplicate.
MWH_APPL_NAME VARCHAR (100) No The name of the application that originated the message. For ISN ACKs and OSN messages, the value is always SWIFTNETFIN. If the message was created with the MER Facility, the value is DNQMER.
MWH_USER_ID VARCHAR (100) No For a message that is not routed to by the MER Facility:
  • For an ISN message, this field contains the user ID of the application that originated the message.
  • For an ISN ACK or OSN message, the value of this field is always SWIFTNETFIN.
For a message that is processed by the MER Facility, this field contains the broker started-task user ID. To determine the user ID of the user or application that originated the message, examine the message history.
MWH_TRAN_REFERENCE VARCHAR (16) No Unique identification of the transaction. For ISN and OSN messages, the value is the content of field 20 or field 20C. For ISN ACKs, this field is null.
MWH_USER_DEF1 VARCHAR (128) No Use these fields to satisfy your own processing requirements.
MWH_USER_DEF2 VARCHAR (128) No
MWH_USER_DEF3 VARCHAR (128) No
MWH_IDENTIFICATION VARCHAR (16) No Unique identification within the network.
MWH_SESSION INTEGER No Session ID of the session.

For FIN messages, this is the number of the FIN session used to send the message.
MWH_MSG_TYPE VARCHAR (30) No The type of message processed by the SIPN FIN or FMT FIN services:
  • For FIN or GPA messages, this is set to the SWIFT message type, for example, FIN103 or GPA098.
  • For acknowledgments, this is set to ACK or NAK, depending on whether the value in field 451 of the message is 0 (ACK) or 1 (NAK).
MWH_EXT_IDENTIFIER VARCHAR (16) No An identifier that indicates which service processed the message:
ComIbmDnfFIN
SIPN FIN
ComIbmDnfFMTF
FMT FIN
ComIbmDnqMM
Message Management general purpose routing
ComIbmDnqER
MER routing
ComIbmDnqPrint
Message printing
MWH_MSG_REFERENCE VARCHAR (64) No A reference to the message within the extension.
MWH_PRIORITY CHAR (1) No The priority of the message.
For FIN message, Possible values:
S
System
U
Urgent
N
Normal
For ISN ACKs, this field is null.
MWH_EXTENSION_DEF1 VARCHAR (128) No An extension can use this field to satisfy special processing requirements.
If browser-based message printing is enabled, this field contains the date and time, in Coordinated Universal Time (UTC), when the MER Facility routed and printed a message. If the message was printed multiple times, this is the timestamp of the last print action. The value is a combined date and time with one of the following patterns:
  • yyyy-mm-ddThh:mm:ss
  • yyyy-mm-ddThh:mm:ss.nnnnnn
where
yyyy
A four-digit number representing the year.
mm
A two-digit number representing the month.
dd
A two-digit number representing the day.
hh
A two-digit number representing the hour.
mm
A two-digit number representing the minute.
ss
A two-digit number representing the second.
nnnnnn
A one-digit (tenths of a second) to six-digit (microseconds) number that represents the decimal fraction of a second.
MWH_EXTENSION_DEF2 VARCHAR (128) No An extension can use this field to satisfy special processing requirements.
MWH_EXTENSION_DEF3 VARCHAR (128) No An extension can use this field to satisfy special processing requirements.
MWH_TRAFFIC_TYPE CHAR(3) No The type of message traffic. The value is always FIN.
MWH_BODY_REP CHAR(3) No The format in which the data in the message body is represented:
HEX
The message body is in MTFIN format. In MWH_XML_MSG, the message body is represented as a hexadecimal string encoded using CCSID 1047. For example, the character string {F01 would be represented as X'C0C6F0F1'. To convert a HEX message body to readable characters, see Displaying the contents of FIN messages stored in the message warehouse.
XML
The message body is in MTXML format. In MWH_XML_MSG, the message body is represented as a hexadecimal string encoded using CCSID 1208 (code page UTF-8). To convert an XML message body to readable characters, convert it to binary data and parse it using an XML parser.
These formats are described in SWIFT User Handbook.
MWH_DIRECTION CHAR(1) No The direction of the message transfer from the point of view of the SIPN:
  • For an ISN message or an ISN ACK, the value is set to I
  • For an OSN message, the value is set to O
Possible values are:
I
For a message passed to the SIPN (that is, a SWIFT input message).
O
For a message received from the SIPN (that is, a SWIFT output message).
MWH_ROUT_DEST VARCHAR (30) No The destination to which the message was routed. The value depends on the service which processed the message (that is, it depends on the value in field MWH_EXT_IDENTIFIER):
  • For Message Management general purpose routing (that is, if field MWH_EXT_IDENTIFIER contains value ComIbmDnqMM):

    A value as described in For messages processed by the routing service

  • For MER routing (that is, if field MWH_EXT_IDENTIFIER contains value ComIbmDnqER):

    The value depends on the status value in the MWH_MSG_STATUS field as described in For messages processed by the MER facility

  • For message printing (that is, if field MWH_EXT_IDENTIFIER contains value ComIbmDnqPrint):
    The value in the MWH_ROUT_DEST field depends on the status value in field MWH_MSG_STATUS as follows:
    • If status value is PRINTED or PRINTED_CONF, and if a target MQ queue is configured for the print queue, field MWH_ROUT_DEST contains value MQ
    • Otherwise, field MWH_ROUT_DEST contains the name of the print queue
  • For the following services this field contains no value:
    • SIPN FIN (that is, if field MWH_EXT_IDENTIFIER contains value ComIbmDnfFIN)
    • FMT FIN (that is, if field MWH_EXT_IDENTIFIER contains value ComIbmDnfFMTF)

The deprecated view DNI_MWH_ou provides the same information as DNIV_MWH_ou, except that it cannot display user IDs that are longer than 12 characters. The only difference between the two views is that, in DNI_MWH_ou, the data type of the MWH_USER_ID field is CHAR(12) instead of CHAR(100).

Table 2. Fields of the view DNF_MWHFN_ou
Field name Data type Mandatory Description
MWH_WMQI_MSG_ID CHAR(24) FOR BIT DATA Yes The message ID. This ID serves as the primary key in the message warehouse. This ID must be unique within an FTM SWIFT instance, and can be used to identify the information in this and other tables that corresponds to a particular message. Typically, this value is an IBM MQ generated unique message ID.
MWH_DATE DATE No Input date of an ISN message or ACK, or output date of an OSN message.
MWH_TIME TIME No Input time of an ISN message or ACK, or output time of an OSN message.
MWH_SENDER_ISN CHAR(6) No For OSN messages, the Message Input Reference of the corresponding ISN message.
MWH_DELMON CHAR(1) No Delivery monitoring option as defined by SWIFT.
MWH_OBSPER CHAR(3) No Obsolescence period of the message.
MWH_SERVICE CHAR(3) No Service code. This is the content of field 103 of the message.
MWH_RELREF CHAR(16) No Related reference. This is the content of field 21 of the message.
MWH_EXCEPT_TYPE VARCHAR (32) No If a message could not be processed, the exception type that was used to determine to which exception queue to route the message.