Creating a Transaction Requester document type

Article
05/19/2014

The Transaction Requester allows you to create custom Transaction Requester document types. You can use the Transaction Requester to define new document types that support your specific business needs. For example, you can create a document type to retrieve data from tables that the original Transaction Requester document types do not include.

To create a custom Transaction Requester Service, you first identify the tables and other information that the Transaction Requester requires. You need to identify a document type name, the tables to use, the index columns, the key fields used to join tables, and the data fields you want to include in your document.

Hint: You must recreate all custom requester services after an installation has completed. The install drops and recreates the eConnect_Out_Setup table. Any custom information in the eConnect_Out_Setup table is lost.

To define your document type, you need to add the information you gathered to the eConnect_Out_Setup table. The Transaction Requester uses the information in this table to retrieve and publish eConnect XML documents.

The following table describes the columns of the eConnect_Out_Setup table.

Column name	Data type	Description
DOCTYPE	varchar	Identifies the service.
INSERT_ENABLED	int	Determines whether the service is enabled or disabled for an insert action. Enabled=1, Disabled=0
UPDATE_ENABLED	int	Determines whether the service is enabled or disabled for an update action. Enabled=1, Disabled=0
DELETE_ENABLED	int	Determines whether the service is enabled or disabled for a delete action. Enabled=1, Disabled=0
TABLENAME	varchar	Physical name of table in SQL Server.
ALIAS	varchar	Alias name for DOCTYPE, which is used in the output XML document. (Try to keep alias names as short as possible.)
MAIN	int	Determines whether this record is associated with the primary table or a child table. MAIN=1 defines a primary table; MAIN=2 or greater defines a secondary table. Increment by one for every level.
PARENTLEVEL	int	Determines the parent for this record. When you specify a table as a secondary table (the value of MAIN is greater than 1) you need to specify its parent level. Set the parent level to 1 if it directly links to the main table. If your secondary table links to a child table of the main table, use the value in the child table's MAIN column as your table's PARENTLEVEL value.
ORDERBY	int	Defines whether this level needs to be included in the order by clause. ORDERBY=1 defines this level to be included; ORDERBY=0 defines this level to be ignored.
USERDEF1-5	varchar	Used for any user-defined purpose.
REQUIRED1	varchar	Defines the name of the column to verify whether data exists for it during an insert transaction. If the column specified is empty, this transaction is ignored. If the column has data in it and the service is enabled, the transaction is echoed to the shadow table (eConnect_Out). If no column is specified, all transactions are written out to the shadow table.
INDEX1-15	varchar	Specifies the column name of the primary index that should be used for this table. You can specify 1 to 15 columns.
INDEXCNT	int	Defines the number of columns that are specified for the index columns.
TRIGGER1-15	varchar	Specifies column names used for creating triggers. The columns specified should link back to the columns that you specified in the INDEX1-15 columns of the main table. These columns are used to determine what data needs to be written out to the shadow table (eConnect_Out).
JOINTABLE	varchar	Specifies the table name that needs to be used for joining.
JOIN1-10	varchar	Specifies column names from the table specified in the TABLENAME column that are used to join to the table specified in the JOINTABLE column.
JOINTO1-10	varchar	Specifies column names from the table specified in the JOINTABLE column that are used in conjunction with the columns listed in the JOIN1-10 columns.
DATACNT	int	Defines the number of columns that are specified for the data columns.
DATA1-180	varchar	Specifies names of the data columns that you want to appear in the XML document. You can specify 1 to 180 columns.

Hint: The Transaction Requester cannot support SQL Server data columns that are defined as either text or binary.

To add a document type to the Transaction Requester, use a SQL query to insert a new record to the eConnect_Out_Setup table of your Microsoft Dynamics GP company database. Use the query to populate the columns that uniquely define your document type. To ensure the Transaction Requester can use your document type, your SQL query must include the following required information:

The number of tables you use to retrieve data defines the number of inserts you use to add your document type to the eConnect_Out_Setup table.
When you create a custom document type, you must define a single table as the main table. You specify the main table by setting the value of MAIN to 1.
Additional tables for the document type have a value of MAIN greater than one. In addition, these tables supply a value for the JOINTABLE column.
Take care to match the columns specified by the JOIN1-10 and JOINTO1-10 fields. Use these columns to define the relationships between the joined tables. For example, the column specified by JOIN1 must contain values that match values in the column specified by JOINTO1.

The following SQL example uses two insert statements to define a Transaction Requester document type for employees. The new Transaction Requester document type combines data from the Microsoft Dynamics GP UPR00100 and UPR00102 tables.

/* Employee Document Setup */
/* this insert will create the record for the parent table - UPR00100 */
insert into eConnect_Out_Setup   (
    DOCTYPE,
    MAIN,
    PARENTLEVEL,
    ORDERBY,
    INDEX1,
    INDEXCNT,
    TRIGGER1,
    TRIGGERCNT,
    TABLENAME,
    ALIAS,
    DATACNT,DATA1,
    DATA2,
    DATA3,
    DATA4,
    DATA5,
    DATA6,
    DATA7,
    DATA8,
    DATA9,
    DATA10,
    DATA11,
    DATA12,
    DATA13,
    DATA14,
    DATA15,
    DATA16,
    DATA17
)
select
    'Employee',
    1,
    0,
    1,
    'EMPLOYID',
    1,
    'EMPLOYID',
    1,
    'UPR00100',
    'Employee',
    17,
    'EMPLCLAS',
    'INACTIVE',
    'LASTNAME',
    'FRSTNAME',
    'MIDLNAME',
    'ADRSCODE',
    'SOCSCNUM',
    'BRTHDATE',
    'GENDER',
    'ETHNORGN',
    'Calc_Min_Wage_Bal',
    'DIVISIONCODE_I',
    'DEPRTMNT',
    'JOBTITLE',
    'SUPERVISORCODE_I',
    'LOCATNID',
    'WCACFPAY'
GO
/* Employee Address Document Setup */
/* this insert will create the record for the child table */
/* (UPR00102) and link it to the parent table (UPR00100) */
insert into eConnect_Out_Setup   (
    DOCTYPE,
    MAIN,
    PARENTLEVEL,
    ORDERBY,
    INDEX1,
    INDEX2,
    INDEXCNT,
    TRIGGER1,
    TRIGGERCNT,
    TABLENAME,
    ALIAS,
    JOINTABLE,
    JOIN1,
    JOINTO1,
    DATACNT,
    DATA1,
    DATA2,
    DATA3,
    DATA4,
    DATA5,
    DATA6,
    DATA7,
    DATA8,
    DATA9,
    DATA10,
    DATA11,
    DATA12,
    DATA13,
    DATA14,
    DATA15,
    DATA16
)
select
    'Employee',
    2,
    1,
    1,
    'EMPLOYID',
    'ADRSCODE',
    2,
    'EMPLOYID',
    1,
    'UPR00102',
    'Address',
    'UPR00100',
    'EMPLOYID',
    'EMPLOYID',
    16,
    'ADDRESS1',
    'ADDRESS2',
    'ADDRESS3',
    'CITY',
    'STATE',
    'ZIPCODE',
    'COUNTY',
    'COUNTRY',
    'PHONE1',
    'PHONE2',
    'PHONE3',
    'FAX',
    'Foreign_Address',
    'Foreign_StateProvince',
    'Foreign_Postal_Code',
    'CCode'
GO

After you add your document type to the eConnect_Out_Setup table, the Transaction Requester works the same as the existing Transaction Requester document types. For example, the Outgoing Service publishes the data fields in an XML document to MSMQ.

The following XML examples show the XML documents that the Transaction Requester produces for the employee document type. The first example shows the master document you get when you set OutputType to 1.

<root>
    <eConnect_Out ACTION="0" EMPLOYID="BARR0001">
        <Employee>
            <EMPLOYID>HEALY0001</EMPLOYID>
            <EMPLCLAS>INST</EMPLCLAS>
            <INACTIVE>0</INACTIVE>
            <LASTNAME>Healy</LASTNAME>
            <FRSTNAME>Joe</FRSTNAME>
            <MIDLNAME></MIDLNAME>
            <ADRSCODE>PRIMARY</ADRSCODE>
            <SOCSCNUM>944229198</SOCSCNUM>
            <BRTHDATE>1961-10-07T00:00:00</BRTHDATE>
            <GENDER>1</GENDER>
            <ETHNORGN>1</ETHNORGN>
            <Calc_Min_Wage_Bal>0</Calc_Min_Wage_Bal>
            <DIVISIONCODE_I></DIVISIONCODE_I>
            <DEPRTMNT>CONS</DEPRTMNT>
            <JOBTITLE>CONS1</JOBTITLE>
            <SUPERVISORCODE_I></SUPERVISORCODE_I>
            <LOCATNID></LOCATNID>
            <WCACFPAY>0</WCACFPAY>
        </Employee>
    </eConnect_Out>
</root>

The following example shows the complete document you get when you set the OutputType to 2.

<root>
    <eConnect_Out ACTION="0" EMPLOYID="BARR0001">
        <Employee>
            <EMPLOYID>HEALY0001</EMPLOYID>
            <EMPLCLAS>INST</EMPLCLAS>
            <INACTIVE>0</INACTIVE>
            <LASTNAME>Healy</LASTNAME>
            <FRSTNAME>Joe</FRSTNAME>
            <MIDLNAME></MIDLNAME>
            <ADRSCODE>PRIMARY</ADRSCODE>
            <SOCSCNUM>944229198</SOCSCNUM>
            <BRTHDATE>1961-10-07T00:00:00</BRTHDATE>
            <GENDER>1</GENDER>
            <ETHNORGN>1</ETHNORGN>
            <Calc_Min_Wage_Bal>0</Calc_Min_Wage_Bal>
            <DIVISIONCODE_I></DIVISIONCODE_I>
            <DEPRTMNT>CONS</DEPRTMNT>
            <JOBTITLE>CONS1</JOBTITLE>
            <SUPERVISORCODE_I></SUPERVISORCODE_I>
            <LOCATNID></LOCATNID>
            <WCACFPAY>0</WCACFPAY>
            <Address>
                <EMPLOYID>HEALY0001</EMPLOYID>
                <ADRSCODE>PRIMARY</ADRSCODE>
                <ADDRESS1>4567 Main Ave</ADDRESS1>
                <ADDRESS2></ADDRESS2>
                <ADDRESS3></ADDRESS3>
                <CITY>Wauwatosa</CITY>
                <STATE>WI</STATE>
                <ZIPCODE>43210-9876 </ZIPCODE>
                <COUNTY></COUNTY>
                <COUNTRY>USA</COUNTRY>
                <PHONE1>4145550150</PHONE1>
                <PHONE2></PHONE2>
                <PHONE3></PHONE3>
                <FAX></FAX>
                <Foreign_Address>0</Foreign_Address>
                <Foreign_StateProvince></Foreign_StateProvince>
                <Foreign_Postal_Code></Foreign_Postal_Code>
                <CCode></CCode>
            </Address>
        </Employee>
    </eConnect_Out>
</root>

Share via

Creating a Transaction Requester document type

Additional resources