Mar 072007
 

Because there are so many entries on the web about how to develop a schema not included in the standard install of the BaseEDI accelerator, I would like to share how to create schemas that can be successfully imported and used. 

Use http://www.disa.org/x12workbook/ts/ as implementation guide

Referring to http://msdn2.microsoft.com/en-us/library/aa559550.aspx and http://support.microsoft.com/?id=840113

1. Ensure the header of the schema looks like the following. I’ve highlighted the important values that need to be verified for each transaction. The encoding should be utf – 8.

Manually add the following data types to the schema

2. Use correct Rules subjects. The 4 subject rules are the following:

Covast = EDI Implementation

All  = Either

Any = One Present, Other Required

Grouped = Either Present, Other Required

One = Only one may be present

Example:

This creates the rules in the database and eif that checks for the Syntax Rules, example

Syntax Rules

At least one of DTM02, DTM03 or DTM05 is required.

If DTM04 is present, then DTM03 is required.

If either DTM05 or DTM06 is present, then the other is required.

This creates the internal rules that generate errors like in the following example:

Error encountered: ERROR (81), interchangenr 10209 :

The check condition has not been met. Contact the sender.source format: [5 00401 ,X12-4010]

source document: [812 004010DEFAULT X,CreditDebitAdjustment]

source segment: [data#4,def#4,tag=PER ,name=812_004010_DEFAULT_X_<PER]

source element: [def#3,elm#3,comp#0,name=812_004010_DEFAULT_X_<PER03>],

value: [CR]

undefined!

((812_004010_DEFAULT_X_<PER03> <> [] OR

812_004010_DEFAULT_X_<PER04> <> [] ) AND

(812_004010_DEFAULT_X_<PER05> <> [] OR

812_004010_DEFAULT_X_<PER06> <> [] ) AND

(812_004010_DEFAULT_X_<PER07> <> [] OR

812_00, (msgnr:1 segnr:4)(line:17 pos:6 filepos:761)

3. Ensure that all Subject names have “@” before the attribute name

4. Do not include an ISA, GS, or ST segments in your schema

5. All EDI elements must be XSD attributes not elements

6. Verify Schema Node Properties are correct. Highlighted properties are unique for XSD.

7. Verify Root Properties are correct. Highlighted properties are unique for each XSD.

8. Use Requirement, EDI datatype, and Maximum/Minimum Length must be explicitly declared in XSD.  If you leave the Use Required as (Default) there is no attribute written to the XSD, and validation of documents will fail, you will need to explicidly declare all elements to be either Optional or Required. Verify by looking at actual XSD to make sure they are declared. Do not include Notes on any Attribute, Segment, or Loop.

If there are notes in the schema, what happens is that during the XSD2EDI.exe load it loads the Notes into the dbo.segcon table ‘Transaction<Notes>’ instead of the ‘Transaction<segment name>{occurrence}’, and then tries to load the non-unique column into the dbo.elmnts table, violating a primary key contraint:

xsd2edi : xsd2edi started
xsd2edi : Start parsing schema.
xsd2edi : End of parsing schema.
xsd2edi : Compare hashtotals 1QkjjiQpdJOb6SceeNzjNrQ==
xsd2edi : Start dumping definition to TEMP directory.
xsd2edi : End dumping definition to TEMP directory.
xsd2edi : Start deleting old document definition from repository.
xsd2edi : End deleting old document definition from repository.
xsd2edi : Start inserting new document definition to repository.
xsd2edi : Error in inserting document definition: Error = [Microsoft][SQL Native Client][SQL Server]Violation of PRIMARY KEY constraint ‘PK__elmnts__1A14E395’. Cannot insert duplicate key in object ‘dbo.elmnts’.
xsd2edi : End inserting new document definition to repository.
xsd2edi : (-2160) <Description for error (-2160) cannot be found. Please refer to the Trouble Shooting Guide for this description>
xsd2edi : xsd2edi finished

(who uses Notes anyway?!)

9. Date and Time attribute Properties. Advanced Base Data Type should be set to “xs:string” and General Data Type should be set to either “X12_DT (Simple Type)” or “X12_TM (Simple Type)” .

10. Covast Codelists are saved in the <install directory>\Microsoft BizTalk Server 2006\EDI\Adapter\CodeLists\EDICodelists.mdb database. Use the correct code list according to the implementation guide, an example code list is here.

11. Save the schema as <DocumentNumber>Schema.xsd.

12. Load the schema into the EDI Adapter using <install directory>\Microsoft BizTalk Server 2006\EDI\Subsystem\xsd2edi.exe “[Path to your XSD Schema]\<DocumentNumber>Schema.xsd”

13. Use <install directory>\Microsoft BizTalk Server 2006\EDI\Subsystem\compeif.exe utility to complete import.

14. Deploy the schema

If there is a problem with the XSD2EDI import process, to remove it you simply add the -r switch: <install directory>\Microsoft BizTalk Server 2006\EDI\Subsystem\xsd2edi.exe “[Path to your XSD Schema]\<DocumentNumber>Schema.xsd” -r

As a side note, if you would like to save yourself some time, you can easly contact me through my contact page and I can assist in producing a valid EDI schema.

 

Also a question has been asked about defining a different character set, this is not allowed from the MS website:

Illegal characters

Each document is verified to ensure that all the characters present in the document are allowed according to the character set defined for the document format.

For EDIFACT documents of syntax version UNOA, characters A-Z, 0-9, blank and. , ( ) / – = are allowed.

For syntax version UNOB characters a-z, A-Z, 0-9, blank and . , ( ) / – = : + ` ? are allowed.

All other EDIFACT syntaxes are linked to the standard ISO character sets.

For ANSI X-12 documents characters A-Z, 0-9, blank and !”&'()*+,-./:;?= are allowed and, if desired, a-z, %~@[]_{}\|<>#$.

 

These instructions will work on the following transactions:

100 – Insurance Plan Description, 101 – Name and Address Lists, 102 – Associated Data, 103 – Abandoned Property Filings, 104 – Air Shipment Information, 105 – Business Entity Filings, 106 – Motor Carrier Rate Proposal, 107 – Request for Motor Carrier Rate Proposal, 108 – Response to a Motor Carrier Rate Proposal, 109 – Vessel Content Details, 110 – Air Freight Details and Invoice, 111 – Individual Insurance Policy and Client Information, 112 – Property Damage Report, 113 – Election Campaign and Lobbyist Reporting, 120 – Vehicle Shipping Order, 121 – Vehicle Service, 124 – Vehicle Damage, 125 – Multilevel Railcar Load Details, 126 – Vehicle Application Advice, 127 – Vehicle Baying Order, 128 – Dealer Information, 129 – Vehicle Carrier Rate Update, 130 – Student Educational Record (Transcript), 131 – Student Educational Record (Transcript) Acknowledgment, 132 – Human Resource Information, 133 – Educational Institution Record, 135 – Student Aid Origination Record, 138 – Educational Testing and Prospect Request and Report, 139 – Student Loan Guarantee Result, 140 – Product Registration, 141 – Product Service Claim Response, 142 – Product Service Claim, 143 – Product Service Notification, 144 – Student Loan Transfer and Status Verification, 146 – Request for Student Educational Record (Transcript), 147 – Response to Request for Student Educational Record (Transcript), 148 – Report of Injury, Illness or Incident, 149 – Notice of Tax Adjustment or Assessment, 150 – Tax Rate Notification, 151 – Electronic Filing of Tax Return Data Acknowledgment, 152 – Statistical Government Information, 153 – Unemployment Insurance Tax Claim or Charge Information, 154 – Secured Interest Filing, 155 – Business Credit Report, 157 – Notice of Power of Attorney, 158 – Tax Jurisdiction Sourcing, 159 – Motion Picture Booking Confirmation, 160 – Transportation Automatic Equipment Identification, 161 – Train Sheet, 163 – Transportation Appointment Schedule Information, 170 – Revenue Receipts Statement, 175 – Court and Law Enforcement Notice, 176 – Court Submission, 179 – Environmental Compliance Reporting, 180 – Return Merchandise Authorization and Notification, 185 – Royalty Regulatory Report, 186 – Insurance Underwriting Requirements Reporting, 187 – Premium Audit Request and Return, 188 – Educational Course Inventory, 189 – Application for Admission to Educational Institutions, 190 – Student Enrollment Verification, 191 – Student Loan Pre-Claims and Claims, 194 – Grant or Assistance Application, 195 – Federal Communications Commission (FCC) License Application, 196 – Contractor Cost Data Reporting, 197 – Real Estate Title Evidence, 198 – Loan Verification Information, 199 – Real Estate Settlement Information, 200 – Mortgage Credit Report, 201 – Residential Loan Application, 202 – Secondary Mortgage Market Loan Delivery, 203 – Secondary Mortgage Market Investor Report, 204 – Motor Carrier Load Tender, 205 – Mortgage Note, 206 – Real Estate Inspection, 210 – Motor Carrier Freight Details and Invoice, 211 – Motor Carrier Bill of Lading, 212 – Motor Carrier Delivery Trailer Manifest, 213 – Motor Carrier Shipment Status Inquiry, 214 – Transportation Carrier Shipment Status Message, 215 – Motor Carrier Pickup Manifest, 216 – Motor Carrier Shipment Pickup Notification, 217 – Motor Carrier Loading and Route Guide, 219 – Logistics Service Request, 220 – Logistics Service Response, 222 – Cartage Work Assignment, 223 – Consolidators Freight Bill and Invoice, 224 – Motor Carrier Summary Freight Bill Manifest, 225 – Response to a Cartage Work Assignment, 227 – Trailer Usage Report, 240 – Motor Carrier Package Status, 242 – Data Status Tracking, 244 – Product Source Information, 245 – Real Estate Tax Service Response, 248 – Account Assignment/Inquiry and Service/Status, 249 – Animal Toxicological Data, 250 – Purchase Order Shipment Management Document, 251 – Pricing Support, 252 – Insurance Producer Administration, 255 – Underwriting Information Services, 256 – Periodic Compensation, 259 – Residential Mortgage Insurance Explanation of Benefits, 260 – Application for Mortgage Insurance Benefits, 261 – Real Estate Information Request, 262 – Real Estate Information Report, 263 – Residential Mortgage Insurance Application Response, 264 – Mortgage Loan Default Status, 265 – Real Estate Title Insurance Services Order, 266 – Mortgage or Property Record Change Notification, 267 – Individual Life, Annuity and Disability Application, 268 – Annuity Activity, 269 – Health Care Benefit Coordination Verification, 270 – Eligibility, Coverage or Benefit Inquiry, 271 – Eligibility, Coverage or Benefit Information, 272 – Property and Casualty Loss Notification, 273 – Insurance/Annuity Application Status, 274 – Healthcare Provider Information, 275 – Patient Information, 276 – Health Care Claim Status Request, 277 – Health Care Information Status Notification, 278 – Health Care Services Review Information, 280 – Voter Registration Information, 283 – Tax or Fee Exemption Certification, 284 – Commercial Vehicle Safety Reports, 285 – Commercial Vehicle Safety and Credentials Information Exchange, 286 – Commercial Vehicle Credentials, 288 – Wage Determination, 290 – Cooperative Advertising Agreements, 300 – Reservation (Booking Request) (Ocean), 301 – Confirmation (Ocean), 303 – Booking Cancellation (Ocean),
304 – Shipping Instructions, 309 – Customs Manifest, 310 – Freight Receipt and Invoice (Ocean), 311 – Canadian Customs Information, 312 – Arrival Notice (Ocean), 313 – Shipment Status Inquiry (Ocean), 315 – Status Details (Ocean), 317 – Delivery/Pickup Order, 319 – Terminal Information, 322 – Terminal Operations and Intermodal Ramp Activity, 323 – Vessel Schedule and Itinerary (Ocean), 324 – Vessel Stow Plan (Ocean), 325 – Consolidation of Goods In Container, 326 – Consignment Summary List, 350 – Customs Status Information, 352 – U.S. Customs Carrier General Order Status, 353 – Customs Events Advisory Details, 354 – U.S. Customs Automated Manifest Archive Status, 355 – U.S. Customs Acceptance/Rejection, 356 – U.S. Customs Permit to Transfer Request, 357 – U.S. Customs In-Bond Information, 358 – Customs Consist Information, 361 – Carrier Interchange Agreement (Ocean), 362 – Cargo Insurance Advice of Shipment, 404 – Rail Carrier Shipment Information, 410 – Rail Carrier Freight Details and Invoice, 412 – Trailer or Container Repair Billing, 414 – Rail Carhire Settlements, 417 – Rail Carrier Waybill Interchange, 418 – Rail Advance Interchange Consist, 419 – Advance Car Disposition, 420 – Car Handling Information, 421 – Estimated Time of Arrival and Car Scheduling, 422 – Equipment Order, 423 – Rail Industrial Switch List, 424 – Rail Carrier Services Settlement, 425 – Rail Waybill Request, 426 – Rail Revenue Waybill, 429 – Railroad Retirement Activity, 431 – Railroad Station Master File, 432 – Rail Deprescription, 433 – Railroad Reciprocal Switch File, 434 – Railroad Mark Register Update Activity, 435 – Standard Transportation Commodity Code Master, 436 – Locomotive Information, 437 – Railroad Junctions and Interchanges Activity, 440 – Shipment Weights, 451 – Railroad Event Report, 452 – Railroad Problem Log Inquiry or Advice, 453 – Railroad Service Commitment Advice, 455 – Railroad Parameter Trace Registration, 456 – Railroad Equipment Inquiry or Advice, 460 – Railroad Price Distribution Request or Response, 463 – Rail Rate Reply, 466 – Rate Request, 468 – Rate Docket Journal Log, 470 – Railroad Clearance, 475 – Rail Route File Maintenance, 485 – Ratemaking Action, 486 – Rate Docket Expiration, 490 – Rate Group Definition, 492 – Miscellaneous Rates, 494 – Rail Scale Rates, 500 – Medical Event Reporting, 501 – Vendor Performance Review, 503 – Pricing History, 504 – Clauses and Provisions, 511 – Requisition, 517 – Material Obligation Validation, 521 – Income or Asset Offset, 527 – Material Due-In and Receipt, 536 – Logistics Reassignment, 540 – Notice of Employment Status, 561 – Contract Abstract, 567 – Contract Completion Status, 568 – Contract Payment Management Report, 601 – U.S. Customs Export Shipment Information, 602 – Transportation Services Tender, 620 – Excavation Communication, 625 – Well Information, 650 – Maintenance Service Order, 715 – Intermodal Group Loading Plan, 753 – Request for Routing Instructions, 754 – Routing Instructions, 805 – Contract Pricing Proposal, 806 – Project Schedule Reporting, 810 – Invoice, 811 – Consolidated Service Invoice/Statement, 812 – Credit/Debit Adjustment, 813 – Electronic Filing of Tax Return Data, 814 – General Request, Response or Confirmation, 815 – Cryptographic Service Message, 816 – Organizational Relationships, 818 – Commission Sales Report, 819 – Joint Interest Billing and Operating Expense Statement, 820 – Payment Order/Remittance Advice, 821 – Financial Information Reporting, 822 – Account Analysis, 823 – Lockbox, 824 – Application Advice, 826 – Tax Information Exchange, 827 – Financial Return Notice, 828 – Debit Authorization, 829 – Payment Cancellation Request, 830 – Planning Schedule with Release Capability, 831 – Application Control Totals, 832 – Price/Sales Catalog, 833 – Mortgage Credit Report Order, 834 – Benefit Enrollment and Maintenance, 835 – Health Care Claim Payment/Advice, 836 – Procurement Notices, 837 – Health Care Claim, 838 – Trading Partner Profile, 839 – Project Cost Reporting, 840 – Request for Quotation, 841 – Specifications/Technical Information, 842 – Nonconformance Report, 843 – Response to Request for Quotation, 844 – Product Transfer Account Adjustment, 845 – Price Authorization Acknowledgment/Status, 846 – Inventory Inquiry/Advice, 847 – Material Claim, 848 – Material Safety Data Sheet, 849 – Response to Product Transfer Account Adjustment, 850 – Purchase Order, 851 – Asset Schedule, 852 – Product Activity Data, 853 – Routing and Carrier Instruction, 854 – Shipment Delivery Discrepancy Information, 855 – Purchase Order Acknowledgment, 856 – Ship Notice/Manifest, 857 – Shipment and Billing Notice, 858 – Shipment Information, 859 – Freight Invoice, 860 – Purchase Order Change Request – Buyer Initiated, 861 – Receiving Advice/Acceptance Certificate, 862 – Shipping Schedule, 863 – Report of Test Results, 864 – Text Message, 865 – Purchase Order Change Acknowledgment/Request – Seller Initiated, 866 – Production Sequence, 867 – Product Transfer and Resale Report, 868 – Electronic Form Structure, 869 – Order Status Inquiry, 870 – Order Status Report, 871 – Component Parts Content, 872 – Residential Mortgage Insurance Application, 873 – Commodity Movement Services, 874 – Commodity Movement Services Response, 875 – Grocery Products Purchase Order, 876 – Grocery Products Purchase Order Change, 877 – Manufacturer Coupon Family Code Structure, 878 – Product Authorization/De-authorization, 879 – Price Information, 880 – Grocery Products Invoice, 881 – Manufacturer Coupon Redemption Detail, 882 – Direct Store Delivery Summary Information, 883 – Market Development Fund Allocation, 884 – Market Development Fund Settlement, 885 – Retail Account Characteristics, 886 – Customer Call Reporting, 887 – Coupon Notification, 888 – Item Maintenance, 889 – Promotion Announcement, 891 – Deduction Research Report, 893 – Item Information Request, 894 – Delivery/Return Base Record, 895 – Delivery/Return Acknowledgment or Adjustment, 896 – Product Dimension Maintenance, 920 – Loss or Damage Claim – General Commodities, 924 – Loss or Damage Claim – Motor Vehicle, 925 – Claim Tracer, 926 – Claim Status Report and Tracer Reply, 928 – Automotive Inspection Detail, 940 – Warehouse Shipping Order, 943 – Warehouse Stock Transfer Shipment Advice, 944 – Warehouse Stock Transfer Receipt Advice, 945 – Warehouse Shipping Advice, 947 – Warehouse Inventory Adjustment Advice, 980 – Functional Group Totals, 990 – Response to a Load Tender, 993 – Secured Receipt or Acknowledgment, 996 – File Transfer, 997 – Functional Acknowledgment, 998 – Set Cancellation

  • Ren

    You did a great work. I really appreciate your article and came as timely help. Its great keep up the good work.

  • Ashwini

    Hi Eric,
    I want to customize the invoice message conforms to EANCOM 2002 Syntax 3 version (D01B) and the built in Biztalk EDI Adapter has schemas only up to 1998 release ( D98B). Can you help me please how to proceed.Since this blog talking about X12 standards .
    Thanks
    Ashwini

  • Viren Patel

    Eric, all i can say is wow! Yours, of all blogs, has got to be the best one out there. Thanks for making EDI in Biztalk a little more easier.

  • Thanks!
    I am glad you appreciate that I make a lot of mistakes and admit publicly about them.

  • Mike

    Hi Eric,
    I want to customize the invoice message conforms to EDI 810 X12and the built in Biztalk EDI Adapter. Can you help me please how to proceed.Since this blog talking about X12standards. I need the properties which are must for EDI schema.
    Thanks
    Mike