The MFG/PRO data handler performs transformations between QAD MFG/PRO messages and business objects in the following ways:
The sections below describe:
The rules below apply when the MFG/PRO data handler's mapping engine converts triplet messages to Qdoc XML:
<orderQuantity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:enc="http://www.w3.org/2002/12/soap-encoding" xmlns:qcom="http://www.qad.com/qdoc/common" enc:itemType="xsi:decimal" enc:arraySize="6" > <!-- Entries 1, 3 are set to empty and 4, 6 are skipped --> <qcom:entry index="1"/> <qcom:entry index="2">10.583</qcom:entry> <qcom:entry index="3"/> <qcom:entry index="4" skip=ìtrueî/> <qcom:entry index="5">22</qcom:entry> <qcom:entry index="6" skip="true">ignore!</qcom:entry> </orderQuantity>
In order to accomplish the mapping, triplet data from the parent
table is mapped to the highest-level XML element inside the Qdoc.
Data from the child table triplets are mapped to lower-level child
elements. However, the Qdoc XML data is represented in a
structured, de-normalized fashion, with elements for the common
keys in the parent and removed from the child elements. Such
redundant foreign keys are unnecessary in XML, in which the
parent-child relationship is implicit in the structured nesting of
elements. The mapping algorithm recognizes the common key fields
for each MFG/PRO table at run-time. If the Triplet document updates
child, rather than parent, table records, the common key values are
moved from the child XML element to the parent XML element in the
resulting Qdoc. Mapping of fields representing common keys that
appear in the triplet at the child table level is performed by
replacing the child table prefix with the parent table prefix. By
MFG/PRO convention, all field names have a prefix that is an
abbreviation for the database table name, followed by an underscore
character, followed by one or more underscore-delimited tokens that
together identify the field. Common keys across parent and child
tables always have the same field name, except for the
table-specific prefix. For example, the costSimulation
(sct_det) fields equivalent to the spt_det
foreign key fields are sct_sim for spt_sim,
sct_part for spt_part, and sct_site for
spt_site.
The rules described for triplet-to-Qdoc processing are applied during runtime and guided by mapping metadata. The mapping logic resides in the OutboundMappingClass, which is also a child attribute in the QADDataHandlerMO, and the metadata resides in the mapFile, which is a child of the OutboundMappingMO. The mapFile is an XML file containing metadata that:
Table 7 below describes this information, which is stored in the mapFile.
Table name | Qdoc name | BO name | Parent object | Foreign keys |
---|---|---|---|---|
ac_mstr | maintainAccount | account | ||
an_mstr | maintainAnalysisCode | analysisCode | ||
anl_det | maintainAnalysisCode | analysisCodeLink | analysisCode | anl_type, anl_code |
ans_det | maintainAnalysisCode | analysisCodeSelection | analysisCode | ans_type, ans_code |
bom_mstr | maintainBillOfMaterial | billOfMaterial | ||
cc_mstr | maintainCostCenter | costCenter | ||
ccd1_det | maintainCostCenter | costCenterAcct Validation | costCenter | ccd1_cc |
ccd2_det | maintainCostCenter | costCenterSubacct Validation | costCenter | ccd2_cc |
cd_det | maintainMasterComments | masterComment | ||
cm_mstr (published with related ad_mstr data) |
maintainCustomer | customer | ||
ad_mstr (also accessed to synchronize customers, suppliers) |
maintainAddress or maintainCustomer or maintainSupplier | |||
ls_mstr (also accessed to synchronize customers, suppliers; synchronized independently only for non-MFG/PRO address types) |
maintainAddressList | addressList | ||
code_mstr | maintainCode | code | ||
cp_mstr | maintainCustomerItem | item | ||
cs_mstr | maintainCostSet | costSet | ||
cu_mstr | maintainCurrency | currency | ||
dpt_mstr | maintainDepartment | department | ||
en_mstr | maintainEntity | entity | ||
exr_rate | maintainExchangeRate | exchangeRate | ||
fcs_sum | maintainForecastSummary | forecastSummary | ||
glc_cal | maintainGeneralLedger Calendar | generalLedger Calendar | ||
is_mstr | maintainInventoryStatus | inventoryStatus | ||
isd_det | maintainInventoryStatus | inventoryStatus | inventorySta tus | |
pc_mstr | maintainPurchasingPrice List | purchasingPriceList | ||
pi_mstr | maintainPriceList | priceList | ||
pid_det | maintainPriceList | priceListDetail | priceList | |
pl_mstr | maintainProductLine | productLine | ||
ps_mstr | maintainProductStructure | productStructure | ||
pt_mstr | maintainItem | item | ||
ro_det | maintainRoutingOperation | routingOperation | ||
sb_mstr | maintainSubAccount | subAccount | ||
sbd_det | maintainSubAccount | subAccountDetail | subAccount | |
si_mstr | maintainSite | site | ||
sct_det | maintainCostSimulation | costSimulation | ||
spt_det | maintainCostSimulation | costSimulationItem | costSimula tion | |
um_mstr | maintainAlternateUnitOf Measure | alternateUnitOf Measure | ||
vd_mstr (published with related ad_mstr data) |
maintainSupplier | supplier | ||
vp_mstr | maintainSupplierItem | supplierItem | ||
wc_mstr | maintainWorkCenter | workCenter |
The following sample fragment shows how the metadata in mapFile maps items:
<!-- Master table record - no parent --> <record name="ac_mstr"> <qdoc name ="maintainAccount" version="1.0"/> <bo> account</bo> </record> <!-- child table record ñ parent and foreign keys required --> <record name= "anl_det" > <qdoc name = "maintainAnalysisCode" /> <bo> analysisCodeLink </bo> <parent>an_mstr</parent> <fkey>anl_type</fkey> <fkey>anl_code</fkey> </record> <!-- Customer Master table record - no parent --> <record name= "cm_mstr"> <qdoc name="maintainCustomer" /> <bo> customer </bo> </record> <!-- Address master table record --> <record name= "ad_mstr" > <qdoc name="maintainAddress" version="1.1"/> <bo>address</bo> </record> <!-- exception cases mapping --> < record name= "customer" > <qdoc name="maintainCustomer" /> <bo> customer </bo> </ record > < record name= "customeraddress"alias="ad_mstr" > <qdoc name="maintainCustomer" /> <parent>customer</parent> </ record > < record name= "ship-toaddress" > <qdoc name="maintainCustomer" /> <parent>customer</parent> <bo>shipToAddress</bo> </record> <exit_on_error> <bo> customer</bo> <bo> analysisCodeLink </bo> </exit_on_error>
The exception cases in the fragment are mapped using the same syntax as that for the regular records. The difference is that the triplet token is specified instead of the table name. The alias attribute will contain the actual table name and will be used to retrieve the prefix for building the corresponding Qdoc element names. For primary customer and supplier addresses, no business object name is specified. This forces the address fields to appear within the customer and supplier fields at the same level.
The exit_on_error tag indicates whether the connector should shutdown when it encounters an error during the processing of the specified business object. By default, the connector ignores the such errors and continues event retrieval and processing.
Table 8 illustrates the mapping of the Qdoc header elements to QAD MFG/PRO control tags in the triplet message header.
Table 8. Qdoc-triplet header mapping
Qdoc element | Description | Triplet content |
---|---|---|
senderId | Sender URI | @SYSID tag |
receiverId | Receiver URI | @TRADPTRID tag |
senderDocumentId | QAD MFG/PRO's internally assigned document ID | The senderDocumentId on Qdocs must be referenced on Qdoc Confirmation messages so that the QAD MFG/PRO application can cross -reference and confirm the original Qdoc |
descriptor | Multiple-occurrence text string describing the type of document contained in the message. First occurrence: Document Standard Second occurrence: Document Type Third occurrence: Document Revision | Not used. |
confirmationLevel | Designates under what circumstances a Qdoc Confirmation document is requested. Possible values are none, error, or all. | @ACKLVLREQD tag |
dateTimeCreated | Contains the date- and time-stamp for the creation of the document, expressed using the DateTime type prescribed by XML schema | Combination of the following triplet header tags: @DATECREATE, @TIMECREATE, and @TIMEZONE |
senderDocumentRef | Multiple occurrence (up to two occurrences) string providing a reference to the sending application | @MFGPROSITE (if specified)@MFGPROKEY |
receiverDocument Ref | Multi-occurrence string (up to two occurrences) providing a reference to the receiving application. | @MFGPROSITE (if specified)@MFGPROKEY |
This mapping data is stored in a text file that is referenced in the outbound mapping meta-object. The file's content is as follows:
senderId @SYSID receiverId @TRADPTRID senderDocumentId @DOCID confirmationLevel @ACKLVLREQD dateTimeCreated @DATECREATE:@TIMECREATE:@TIMEZONE senderDocumentRef @MFGPROSITE:@MFGPROKEY receiverDocumentRef @MFGPROSITE:@MFGPROKEY
Although request calls issued by the connector are asynchronous, the target QAD MFG/PRO application may issue a confirmation message. When a QAD MFG/PRO application sends a message that confirms receipt of a request call from the connector, the message arrives in triplet format. The MFG/PRO data handler's mapping engine maps the confirmation triplet to a Confirmation Qdoc.
The triplet format of the confirmation message (in MFG/PRO 9.0) differs from the triplet format described for messages described above. The confirmation message triplet uses line breaks instead of pipe characters to separate name-value pairs. It also uses the special section name [END] as an end-tag to close a preceding section name in a blocked manner.
The following is a sample confirmation message:
@@DOC @DOCID 42968 @APPID eQxsl @DOCSTD OAGIS @DOCTYP CONFIRM_BOD @DATEFORM mdy @NUMFORM AMERICAN @CODEPG iso8859-1 @DATA [CONFIRM] [CNTROLAREA] [BSR] VERB=SYNC NOUN=CUSTOMER REVISION=005 [END] [SENDER] LOGICALID=eQxsl COMPONENT=seller TASK=maintain customer REFERENCEID=00000042 CONFIRMATION=2 LANGUAGE= CODEPAGE= AUTHID=eQ [END] [DATETIME(CREATION)] DATE=0001/0021/2003 TIME=03:53:02 TIMEZONE=-0800 [END] [END] [STATUSLVL] STATUSLVL=2 [END] ORIGREF=27506 MFGPROKEY= [CONFIRMMSG] DESCRIPTN=ERROR: VALUE MUST EXIST IN GENERALIZED CODES. Please re-enter. REASONCODE=00000001 [USERAREA] [QAD.CONFIRM_BOD_002.CONFIRMMSG.USERAREA] QAD.DOCSUFX=1 [END] [END] [END] [CONFIRMMSG] DESCRIPTN=ERROR: VALUE MUST EXIST IN GENERALIZED CODES. Please re-enter. REASONCODE=00000002 [USERAREA] [QAD.CONFIRM_BOD_002.CONFIRMMSG.USERAREA] QAD.DOCSUFX=1 [END] [END] [END] [CONFIRMMSG] DESCRIPTN=ERROR: VALUE MUST EXIST IN GENERALIZED CODES. Please re-enter. REASONCODE=00000003 [USERAREA] [QAD.CONFIRM_BOD_002.CONFIRMMSG.USERAREA] QAD.DOCSUFX=1 [END] [END] [END] [END] @@DOCEND
The Confirmation Qdoc this message maps to has the following schema:
<?xml version="1.0" encoding="UTF-8"?>
<schema targetNamespace="http://www.qad.com/qdoc/common" xmlns:qcom=
"http://www.qad.com/qdoc/common" xmlns="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified" xml:lang="EN">
<include schemaLocation="qdocEnvelope-qad_1.xsd"/>
<include schemaLocation="qdocAttributes-qad_1.xsd"/>
<include schemaLocation="qdocExceptionType-qad_1.xsd"/>
<element name="qdocConfirmation" type="qcom:QdocConfirmationType"/>
<complexType name="QdocConfirmationType">
<sequence>
<element name="originalDocumentRef"
type="qcom:QdocEnvelopeType"/>
<element name="processingStage">
<simpleType>
<restriction base="string">
<enumeration value="received"/>
<enumeration value="accepted"/>
<enumeration value="processed"/>
</restriction>
</simpleType>
</element>
<element name="processingOutcome">
<simpleType>
<restriction base="string">
<enumeration value="error"/>
<enumeration value="warning"/>
<enumeration value="success"/>
</restriction>
</simpleType>
</element>
<element name="exception" type="qcom:ExceptionType"
minOccurs="0" maxOccurs="unbounded"/>
<element name="processingResults" minOccurs="0">
<complexType>
<sequence>
<any processContents="lax"
minOccurs="0" maxOccurs="unbounded"/>
</sequence>
</complexType>
</element>
</sequence>
<attributeGroup ref="qcom:commonAttributes"/>
</complexType>
</schema>
Table 9 describes the mapping of Confirmation Qdoc contents to fields in the triplet confirmation message.
Table 9. Confirmation Qdoc-triplet mapping
Confirmation Qdoc path | Triplet path | Description |
---|---|---|
originalDocumentRef/senderId | none | |
originalDocumentRef/receiverId | CONFIRM/CNTROLAREA/ SENDER/LOGICALID | Sets the receiver parameter value only. The connection parameter is omitted. The URI can be any syntactically correct URI: http://www.anyURI.com/path? receiver=<value> |
originalDocumentRef/sender DocumentId | CONFIRM/CNTROLAREA/ SENDER/REFERENCEID | |
originalDocumentRef/descriptor[1] | qdoc | |
originalDocumentRef/descriptor[2] | CONFIRM/CNTROLAREA/ BSR/ VERB | |
originalDocumentRef/descriptor[3] | CONFIRM/CNTROLAREA/ BSR/REVISION | |
originalDocumentRef/dateTime Created | CONFIRM/CNTROLAREA/ [DATETIME(CREATION)]: DATE,TIME,TIMEZONE | Look at @DATEFORM for date triplet date format. Possible values are:mdy, dmy, ymd. The qdoc value format is:o yyyy-mm-ddThh:mm:sszzz:zz |
originalDocumentRef/sender DocumentRef | CONFIRM/CNTROLAREA/ SENDER/REFERENCEID | |
originalDocumentRef/receiver DocumentRef | CONFIRM/CNTROLAREA/ MFGPROKEY | |
processingStage | processed | Hard-coded |
processingOutcome | CONFIRM/CNTROLAREA/ STATUSLVL | 0 = success1 = warning2 = error |
exception/number | none | |
exception/description | CONFIRM/CNTROLAREA/ CONFIRMMSG /DESCRIPTN | |
exception/severity | none | |
exception/field | none | |
exception/context | none | |
processingResults | none |
Once the Qdoc is retrieved, the MFG/PRO data handler invokes the XML data handler to create the corresponding business object. The business object verb is based on the value of the corresponding operation tag in the Qdoc:
For a full description of XML data handler processing, see the Data Handler Guide.
For request processing, the MFG/PRO data handler invokes the XML data handler to convert a business object to a Qdoc. In most cases, the Qdoc is then mapped to the CIM data format. However, the target QAD MFG/PRO application may require a triplet format. The list of inbound Qdocs that need to be mapped to triplet will be stored in an inbound triplet mapping metadata XML file, using the same format as that of the outbound metadata file. If conditional CIM processing is enabled, the connector's SQL Query Dispatcher queries the appropriate MFG/PRO database to obtain updates to the Qdoc prior to converting it to a CIM message. Any confirmation message is sent to the target application as a Qdoc XML, without any additional mapping.
The sections below describe:
For each Qdoc there is a corresponding CIM template file with the same name as the root element of the Qdoc. The path to the CIM template files is an attribute of the InboundMappingMO. For further information on the InboundMappingMO, see Configuring the MFG/PRO data handler. The data handler's mapping engine uses a template to generate the input data for the CIM message by substituting template field names with the corresponding Qdoc element values.
The CIM template file contains all data necessary for the actual CIM message generation in XML format. The following is a sample CIM template:
<cimSchema name="initializeItemCost" version="90_1" procedure="ppcsbtld.p"> <iteration name="initializeItemCost" exitAction="f4"> <frame> <field name="zeroYn" default="yes"/> </frame> <iteration name="itemCost" exitAction="f4"> <frame> <field name="ptPart"/> <field name="site"/> </frame> <frame> <field name="csset"/> </frame> <iteration name="itemCostElement" exitAction="f4"> <frame> <field name="element"/> <field name="cstTl"/> </frame> ... </iteration> ... </iteration> ... </iteration> ... </cimSchema> <cimSchema>
The root element of the template identifies the name and version of the corresponding Qdoc, as well as its MFG/PRO menu procedure, through the 'name', 'version', and 'procedure' attributes respectively. The following elements are noteworthy:
<qdoc:cmSlspsn enc:arraySize="*" enc:itemType="q:name" id="ID000000" ref="ID000000"> <qcom:entry index="1" skip="1">Text</qcom:entry> <qcom:entry index="2" skip="1">Text</qcom:entry> <qcom:entry index="3" skip="1">Text</qcom:entry> <qcom:entry index="4" skip="1">Text</qcom:entry> </qdoc:cmSlspsn>
the CIM template element:
<field name="cmSlspsn" arrayIndex="1"/>
will indicate the first entry in the qdoc array.
A nextAction attribute may also be present indicating the keystroke action necessary to proceed to the next field. Possible values, and their CIM representation, are:
Within the CIM template, an optional enabled attribute is associated with the frame and field elements. This attribute evaluates to a Boolean true-false value. If true, the frame or field will be processed by MFG/PRO databases via the SQL Query Dispatcher and must be populated with data from the source Qdoc. If false, the frame or field is skipped. The default value is true. However, in all meaningful cases the value is not given directly, but must be calculated by evaluating a boolean expression.
The syntax of the enabled attribute's expression is based on a subset of the proposed XQuery 1.0 grammar currently in draft at the W3C. Its value is a Boolean expression enclosed in curly braces ({}) using any of the operators and, or, not, =, and != with optional white space. The operands take one of the following forms:
sql-can-find("select * from pt_mstr where pt_part = $//item[1]//ptPart and pt_site = $//site")
A more complete and complex example from the CIM schema of the sosois.p procedure (the shipSalesOrder Qdoc) that combines all of the above constructs is shown below. First, a frame containing detailed freight information is populated with data only when a freight list has been specified for the sales order in the so_mstr table. Subsequently, a frame updating freight class and weight is used only if the Calculate Freight and Display Freight flags in the preceding frame were set. The tax trailer codes and View/Edit Tax Detail flag in later frames are updatable if and only if MFG/PRO Global Tax Management is turned on in the System Control file. Finally, the last frame containing tax detail by order line is enabled only if the preceding View/Edit Tax Detailflag was set.
<cimSchema name"îsalesOrderShipment" version="90_1" procedure="sosois.p"> <iteration exitAction="f4"> ... <frame enabled="{ sql-can-find('select so_fr_list from so_mstr where so_nbr = $../../frame[1]/soNbr and so_fr_list <> ""') }î> <field name="soFrMinWt"/> <field name="soFrTerms"/> <field name="calcFr"/> <field name="dispFr"/> </frame> ... <frame enabled="{$..//calcFr = true and $..//dispFr = true}"> <field name="detclass"/> <field name="detwt"/> </frame> ... <frame> <field name="soTrl1Cd" enabled="{ sql-can-find('select * from mfc_ctrl where mfc_field = "txc_use_new"') }î> <field name="soTrl1Amt"/> <field name="soTrl2Cdî enabled="{ sql-can-find('select * from mfc_ctrl where mfc_field = "txc_use_new"') }î> <field name="soTrl2Amt"/> <field name="soTrl3Cd" enabled="{ sql-can-find('select * from mfc_ctrl where mfc_field = "txc_use_new"') }î> <field name="soTrl3Amt"/> <field name="taxEdit" enabled="{ sql-can-find('select * from mfc_ctrl where mfc_field = "txc_use_new"') }î default=îyesî> </frame> <frame enabled="{$..//taxEdit = 'yes'}"> <field name="taxLine"/> <field name="taxTrl"/> </frame> ... </iteration> </cimSchema>
There are two aspects of evaluating the enabled expression:
These are discussed in sections below.
An XPath will reference a field in an XML representation of the CIM document that follows the CIM schema model. It also impacts the mapping algorithm described previously, since it is necessary to first build the CIM content as such an XML in order to be able to evaluate these expressions. As such, the algorithm described above in Qdoc-to-CIM processing takes place in two parts:
After the expression inside the sql_can_find call has been parsed and all necessary substitution performed, the result is a valid SQL SELECT statement that has run against the MFG/PRO database to fully evaluate the expression. For a description of the components involved and the communication method, see SQL Query Dispatcher and request processing. The query request format is a string containing a SQL SELECT statement. All queries check for the existence of a record in a database that matches the specified criteria. The reply format is a one-character return code, optionally followed by a message string:
return_code|message
Where:
The list of Qdocs that require conversion to triplet, as opposed to CIM, format are stored in the triplet mapping metadata file (mapFile)as follows:
<inbound> <qdoc name="maintainSupplier" version="1.1"/> <qdoc name="maintainPurchaseOrder" version="1.0"/> </inbound>
The format of the triplet documents that provide the input data for requests to QAD MFG/PRO applications is very similar to the triplet format used for event processing. The major difference is that the triplet Context identifiers are derived from element names in the associated request processing Qdoc rather than from table names in the MFG/PRO database schema.
The general syntax rules for Qdoc-to-triplet processing are as follows: