Biztalk Design Document

Mi Limited Mi-EAI-Pilot DETAIL DESIGN DOCUMENT

Table of Contents 1 Introduction.....................................................................................................................................5 Scope.........................................................................................................................................5 Definitions, Acronyms and Abbreviations ................................................................................5 2 Logical Architecture........................................................................................................................6 3 Network Architecture......................................................................................................................7 4 Design Details.................................................................................................................................8 Guidelines .................................................................................................................................8 Step Details (Email Order)......................................................................................................11 Orchestration of Email Orders.................................................................................................12 5 Organizations (OR).......................................................................................................................14 6 Messaging Ports (MP)...................................................................................................................14 7 Distribution Lists (DL)..................................................................................................................14 8 Channels (CH)...............................................................................................................................15 9 Document Definitions (DD)..........................................................................................................16 Order Definition .....................................................................................................................16 Order Status Definition............................................................................................................16 Pick Ticket Definition.............................................................................................................16 Pick Ticket Confirmation (ORDC) Definition.......................................................................16 Pick Ticket Status Definition...................................................................................................16 10 Mapping (MA)............................................................................................................................16 11 Envelops (EN).............................................................................................................................17 Order Envelope........................................................................................................................17 Pick Ticket Envelope..............................................................................................................17 Pick Ticket Confirmation (ORDC) Envelope.........................................................................17 12 Receive Functions(RF)................................................................................................................17 File Receive ............................................................................................................................17 12.1.1 ............................................................................................................................................................17 MSMQ Receive......................................................................................................................18 None........................................................................................................................................18 HTTP Receive..........................................................................................................................18 13 MSMQs (MQ).............................................................................................................................18 14 Orchestrations..............................................................................................................................18 Order Orchestration.................................................................................................................18 Pick Ticket Orchestration........................................................................................................18 15 Parsers.........................................................................................................................................18

Suspended Q Monitoring.........................................................................................................18 Email........................................................................................................................................18 16 Database Schema (New database)...............................................................................................20 Lookup Tables..........................................................................................................................20 Correlation tables.....................................................................................................................21 17 COM components.......................................................................................................................22 OrderCorrelation Component..................................................................................................22 PickTicketCorrelation Component..........................................................................................23 ProcessStatus Component........................................................................................................23 DBAdapter ..............................................................................................................................24 Preprocessor EXE....................................................................................................................24 Delay Handler..........................................................................................................................25 18 FTP Service.................................................................................................................................25 18.1.1 FTP Orders:..........................................................................................................................................25 18.1.2 FTP Pick Tickets:.................................................................................................................................25 18.1.3 FTP Status:...........................................................................................................................................25 19 Data Elements.............................................................................................................................29 19.1.1 Indent Details.......................................................................................................................................29 19.1.2 Pick Tickets (Orders)..........................................................................................................................30 19.1.3 Order Confirmation.............................................................................................................................32 19.1.4 Spare Parts database Temp Tables.......................................................................................................32 19.1.5 Error/Status Codes...............................................................................................................................34 20 Transport Mechanisms................................................................................................................35 21 Platform Information...................................................................................................................35 22 Data volume and performance....................................................................................................36 23 Data Transformation (Mapping)..................................................................................................36 24 Transactions.................................................................................................................................37 25 Application Exceptions...............................................................................................................37 26 Acknowledgements And Receipts...............................................................................................37 27 Recommendations......................................................................................................................37 28 Issues...........................................................................................................................................37 29 Limitations..................................................................................................................................37 30 Unit Testing Guidelines...............................................................................................................37 31 Initialization and Termination.....................................................................................................38 32 Revision History..........................................................................................................................39

1 Introduction Maruti Udyog Ltd (MUL) desires to do pilot implementation of an Application integration tool with following objectives: 1. Familiarize/ accustom better to use these tools in MUL environment and for MUL requirements 2. To evaluate such tools from the perspective of their scope 3. Cost benefit analysis The pilot implementation comprises of integrating the following enterprise applications: 1. I2 DP with Sales Systems 2. Dealer orders to be picked up from the mails and processing of those orders

Scope This document describes the architecture of the system as well as the various modules, data elements, process flow and functionality.

Definitions, Acronyms and Abbreviations MUL Maruti Udyog Limited MARC Material And Resource Control EAI Enterprise Application Integration SP Spare Parts

2 Logical Architecture Unix Oracle8i

Spare Parts

Exchange Server

BizT lak Server

Wi ndows NT, Oracle 8 MARC

3 Network Architecture

Network Architecture

HTTP/S SMTP

F i r e w a l l

Web Server

Spare Parts

MARC Oracle Oracle

Maruti’s Internal Network

Oracle Sales System Exchange Server

Internet

I2- DP

BizTalk Server Oracle Finance Business Partners

4 Design Details The current focus of the EAI pilot is the integration of order processing which spread across multiple systems and departments. The order is processed in three disparate systems such as Mailing, spare parts and MARC system. These three systems will be integrated to process orders, which are received by email attachments. These attachments are positional flat files and contain order information. The diagram below shows the various systems involved and the high-level flow of email order processing. For other order types like extranet the order processing starts at a later stage (After a record is entered into the temp table in Spare Parts System).

Guidelines The following guidelines are made while doing the design. • Mail Robot program will be used to extract mails from exchange server. The file will be placed in BizTalk folder or in a shared folder in the network. • One Pick Ticket may contain items from multiple orders and one order may split into multiple pick tickets. Because of this the activation of pick ticket orchestration from order orchestration won’t happen. • Each Pick Ticket will be send to MARC as an individual file/message. • There will be one and only one response (ORDC) for each pick ticket send to MARC and it will be send/received as individual file/message • The Pick Tickets marked as for MARC only will be send to MARC system • There can be multiple error/exception messages at each stage of orchestration. • A message will be send to BizTalk server for each every order-entry in the temp table of the spare parts system except for the email orders • Extranet/intranet/oracle form orders will start when an order is entered into temp table. • AIC will be written to put data directly into the temp tables in Spare Parts database. • The mailing functionality will be limited to sending mails based on the error code. The content, subject and email IDs of the mail will be given by the applications. • Back Orders also will be treated as a part of a normal order.

Extranet Orders

BizTalk Server

Spare Parts System

Exchange Email Order Server

Syntax and File level validation

from Exchange Server

Order Data

Order Validation

Fails

Suspended Queue Funds Validation

Back Orders

MARC Load Pick Ticket

Send to Marc

PickTickets

Inventory Allocation

Generate Pick Tickets

Validation of Pick Ticket Pick Ticket Confirmation

Packing Confirmatio n

ORDC

Send to Spare Parts

Process Pick Ticket Confirmation

Generate Invoice

Order Completion

Fig. Email Order processing

The order processing happens in three stages for email orders. For non-email orders the processing starts at stage 2. The stages are

1. Detaching order files from emails and sending to Spare Parts System 2. Processing and Sending the Order detail (Pick Ticket) from Spare Parts to MARC. 3. Processing and Sending the Order confirmation details (Pick Ticket) from MARC to Spare Parts. The various steps involved in the above stages are explained below. Stage 1: Detaching order files from emails and sending to Spare Parts System • • • • • •

MUL receives orders by email attachments. Attachments are detached from the mail and are stored in a folder in a network folder. These attachments are order details positional flat files. BizTalk server will get that file using file receive function. The file will be validated against the XML schema defined. If any Parsing Error, Syntax error or invalid file error happens the order will be moved to suspended Queue. BizTalk will send the validated order file to the Spare parts system using an AIC component or FTP service

Stage 2: Processing and Sending the Order from Spare Parts to MARC. o o o o o o o o o o

The Spare Parts system will load and validate the order files After validation the order will be checked against available funds Then the inventory gets allocated Unavailable items/quantities will move to a back order Pick tickets will get generated for the allocated items/quantities in the order The Pick ticket will be send to BizTalk( By placing it in a folder monitored by BizTalk) If all the stages get executed successfully BizTalk will send a status message to the dealer. If any error, exception or delay happens during above steps, the spare parts system will generate appropriate error messages. Biz Talk will send the pick tickets to the MARC system which are marked for MARC BizTalk server will wait for pick tickets till the Spare Parts systems says that all the Pick tickets are generated for that order including back orders and quantity adjustments from MARC.

•

Stage 3: Processing and Sending the Order confirmation details from MARC to Spare Parts

• •

The MARC system will load and validate the pick tickets. After validation the Pick ticket will get confirmed.

• • • • • •

Then the Packing confirmation happens Then the ORDC (confirmation) files for pick tickets will be sent to Spare Parts. If any error, exception or delay happens during above steps, the MARC system will generate appropriate error messages After receiving the ORDC files the Spare Parts system will process the confirmation. Then the Invoice will get generated A final status update will be sent to the Order after all pick tickets are processed successfully.

Step Details (Email Order) Various steps and actions are detailed out below for the email order processing. Nonemail orders starts from the order validation stage. Process Step

Inform Status

On Failure

Receive Order File Order Capture

Notify Sys Admin

Order Validation

Notify SRS Notify Dealer in case of critical error Notify SRS Notify Dealer

Funds Validation Inventory Allocation Generate Pick Ticket Send Pick Ticket to MARC Load Pick Ticket in MARC Validate Pick Ticket Pick Ticket Confirmation Packing Confirmation

Send Pick

On Delay

Notify SRS

Dealer is informed of order acceptance

Order State on Failure/Delay Wait Abort (in case of error in file structure) Wait (in case of corrective action or terminate) Wait Wait

Notify SRS (after 2 hrs) Notify Sys Admin

Wait Wait

Notify Sys Admin (MARC) Notify WAD

Wait

Notify WAD (for VORs after 6 hrs & for normal after 3 working days) Notify Sys Admin

Wait

Wait

Ticket Confirmation Process Pick Ticket Confirmation Generate Invoice

(MARC) Notify WAD Notify SRS Dealer is informed of order status

Orchestration of Email Orders The orchestration of email orders is depicted below.

BizTalk Orchestration

Fig: Orchestration of Email Order

Wait

Notify WAD Notify SRS

Start Receive Email

SMTPP

Mail Server

Send To Spare Parts

Spare Parts Receive Pick Tickets

Send To Marc

MARC Receive Feedback

Send To Spare Parts Spare Parts

Get Confirmation Get all Pick Tickets

End

5 Organizations (OR) 1. Spare Parts 2. MARC 3. Maruti EAI (Home Organization) Applications (AP) • BizTalk • Email

6 Messaging Ports (MP) Name OrderToBizTalk OrderPortToSP OrderStatustoEAI EmailBackupPort ToBizTalkPort SaveLoopCorr OrderStatusLoop SendPickTicketToEAI SendPickTicketConfirmToEAI SendPickTicketStatusToEAI SendPickTicketToMARC SendSPPickTicketStatusEAI SavePickTktLoopCorr ReceiveXtranetOrder StoreXtranetCorrInfo ORDCtoSPdb NonMARCCorr NonMARCPickTicket SendORDCtoEAI SendORDCtoBackup PickTicketXMLtoFlat

Destination Xlang Schedule BizTalk BizTalk Email Application BizTalk BizTalk BizTalk Xlang Schedule BizTalk BizTalk MARC BizTalk

Schedule/port Order / ReceiveOrder AIC MSMQ/ receivestatus File MSMQ/ ReceiveEmailOrdBkp AIC MSMQ/ receivestatus PickTicket/ ReceivePickTicket

MSMQ/ ReceivePickTicketConfirm MSMQ/ ReceivePickTicketStatus File MSMQ/ ReceivePickTicketStatusFromSP BizTalk AIC XlangSchedule Order/ ReceiveXtranetOrder BizTalk AIC SP AIC Spare Parts AIC XlangSchedule PickTicket/ ReceiveNonMARCPickTicket BizTalk File Spare Parts File MARC File

7 Distribution Lists (DL)

Name EmailOrder

Channel used EmailOrderBacku p ORDCtoSP ORDCtoSP SendORDCtoEAI ReceiveORDC

Port 1 EmailBackupPort

Port 2 ToBizTalkPort

ORDCtoSPdb SendORDCtoEAI SendORDCtoBackup

8 Channels (CH) Name

Port

Source

SendOrderToSP OrderStatusChannel

OrderPortToSP OrderStatusToEAI

BizTalk BizTalk

EmailOrderBackup EmailOrderToPort

EmailOrders OrderToBizTalk

BizTalk XlangSch edule SaveLoopCorr SaveLoopCorr BizTalk OrderStatusLoop OrderStatusLoop Spare Parts ReceivePickTicket SendPickTicketToEAI Spare Parts ReceivePickTicketConf SendPickTicketConfirmT MARC irm oEAI ReceivePickTicketStatu SendPickTicketStausToE MARC s AI SendPickTicketToMA SendPickTicketToMARC BizTalk RC ReceivePickTicketStatu SendSPPickTickectStatus Spare sFromSP EAI Parts SavePickTktLoopCorr SavePickTktLoopCorr BizTalk SendXtranetOrder ReceiveXtranetOrder Spare Parts StoreXtranetCorrInfo StoreXtranetCorrInfo BizTalk ORDCToSP ORDCToSP BizTalk NonMARCCorr ReceiveNonMARCPic kTicket ReceiveORDC

NonMARCCorr NonMARCPickTicket SendORDCToEAI

BizTalk Spare Parts MARC

PickTicketXMLtoText

PickTicketXMLtoFlat

BizTalk

In(Out)bound Document EmailOrderDefinition Order Status Definition EmailOrderDefinition EmailOrderDefinition EmailOrderDefinition Order Status Definition PickTicket Definition PickTicket Confirm Definition PickTicket Status Definition PickTicket Definition PickTicket Status Definition PickTicket Definition EmailOrderDefinition EmailOrderDefinition PickTicket Confirm Definition PickTicket Definition PickTicket Definition PickTicket Confirm Definition PickTicket Definition

9 Document Definitions (DD) Order Definition Name Specification Global Tracking Selection Criteria

:Order Definition :OrderSpec.xml : ORDER REFERENCE NO, CUSTOMER ID :None

Order Status Definition Name Specification Global Tracking Selection Criteria

:Order Status Definition :OrderStatusSpec.xml :OrderID :None

Pick Ticket Definition Name Specification Global Tracking Selection Criteria

:Pick Ticket Definition :PickTicketSpec.xml :Ord_No((Pick ticket ID) : ORD_TYPE :None

Pick Ticket Confirmation (ORDC) Definition Name :Pick Ticket Confirm Definition Specification :PickTicketConfimSpec.xml Global Tracking :Ord_No(Pick ticket ID) Selection Criteria :None Pick Ticket Status Definition Name :Pick Ticket Status Definition Specification :PickTicketStatusSpec.xml Global Tracking :PickTicketID Selection Criteria :None

10 Mapping (MA) None

11 Envelops (EN) Order Envelope Name : Order Envelope Format : Flat File Specification :OrderSpec.xml Pick Ticket Envelope Name : Pick Ticket Envelope Format : Flat File Specification :PickTicketSpec.xml Pick Ticket Confirmation (ORDC) Envelope Name : Pick Ticket Confirm Envelope Format : Flat File Specification :PickTicketConfimSpec.xml

12 Receive Functions(RF) File Receive 12.1.1 Name

File Type

Envelop

OrderStatusLoop PurchaseSiteFunc ReceicePickTicketXM L ReceiveNonMARCPic kTicket ReceiveORDC

xml

OrderStatusLoop

Ord

PickTicketXMLtoText

ReceiveOrder ReceiveOrderQ ReceiveOrderStatus ReceivePickTicket ReceivePickTicketCon firm ReceivePickTicketStat us ReceivePickTicketStat usFromSP ReceiveXtranetOrder

Dat

Ord

Pick Ticket Envolope

Orc

Pick Ticket Confirm Envelope Order Envelope

Xml Ord Orc

Pick Ticket Envolope Pick Ticket Confirm Envelope

Xml Xml Dat

Order Envelope

Channel

ReceiveNonMARCPickT icket ReceiveORDC EmailOrderBackup EmailOrdertoPort OrderStatusChannel ReceivePickTicket ReceivePickTicketConfir m ReceivePickTicketStatus ReceivePickTicketStatus FromSP SendeXtranetOrder

MSMQ Receive None HTTP Receive None

13 MSMQs (MQ) There will be many MSMQ will be defined in the local server. All queues are private. The MSMQs are ReceiveEmailOrder(MQ1) ReceivOrderStatus(MQ2) ReceivePickTicket(MQ3) ReceiveORDC(MQ4) ReceivePickTicketStatus(MQ5)

14 Orchestrations Order Orchestration Pick Ticket Orchestration

15 Parsers 1. 2. 3. 4.

XML Parser Flat File Parser EDIFACT Parser X12 Parser

Suspended Q Monitoring There will be an application/service, which will monitor the suspended Q and take actions whenever an item got moved to the suspended Q. An action will be sending an email alert based on the error type, source, destination, and document type. Except error type all other parameters are optional. The email Ids associated with each error type will be stored in a lookup table.

Email • •

The mail consolidation will be based on time and to_address basis. There won’t be any consolidation for email to the dealers.

• •

All unconsolidated mails will be send immediately Each order will be associated with a region and any error in that region will be sent to the people responsible that region.

To filed in the email. For email to the dealers the dealer email ID will come as a part of the message. Other email Ids will be figured out at runtime based on the error code and region code. From filed in the email The from will be set to some default email ID like “Maruti”. Subject: If the subject field in the message is not empty then the subject will be taken from the message. Otherwise subject value will be based on the error code for unconsolidated mails. And this value will be taken from the lookup table in the local database. For consolidated mail the subject will be something like this. “ Consolidated Mail” All the orders will be listed in ascending order based on date/time. The following field values will be provided in the mail. Order Reference No. Order Type Customer ID Customer Category Consignee ID Consignee Category Error Code Error Description ( Default defined in the Lookup table) Error Details (if any provided as part of status message) All the above fields will list horizontally like a report. There should be a header row to list the column names. The horizontal size will be limited to??? Both consolidated and unconsolidated mails will have the same message body structure. Consolidation: Emails will be consolidated based on the To address. Based on the Order Type and Error Type consolidation frequency will change. Consolidation frequency will be defined in minutes All error codes will be marked with a flag to say weather consolidation is required.

16 Database Schema (New database) There will be a new local database to maintain the correlation, consolidation, lookup and other information. The DBA/Sys Admin will maintain the lookup information in this database. This database will stay in the same server where biztalk databases are installed. The databases may get moved to a new machine when more applications are added to the EAI framework.

Lookup Tables 16.1.1.1StatusCode StatusCode Description(Subject) Details RegionFlag DealerFlag EndLoopFlag Status

: Integer : Varchar(100) : varchar(500) : boolean : Boolean : integer : varchar(50)

16.1.1.2EmailIDLookUp Id EmailID Description

: Integer : Varchar(150) : varchar(150)

16.1.1.3RegionCode VRegionCode varchar(50) vEmailID varchar (150) Id integer(identity seed)

16.1.1.4StatusEmailLink StatusCode Email_Id

Integer Integer

16.1.1.5ConsolidationInfo Lookup StatusCode OrderType Consolidate Frequency

Integer Char(2) (All pick tickets will be of the same type as order) Boolean Integer

16.1.1.6Consolidated Emails OrderID

Char (15)

PickTicketID Statuscode To Subject Body SendFlag Frequency DateCreated

Char (15) integer Varchar (150) Varchar (100) Varchar (500) Boolean integer (Consolidation frequency In minutes) date/time

16.1.1.7SuspendQ GUID

varchar(250)

Correlation tables 16.1.1.8Order Correlation Order ID Order Reference ID LoopInstanceID CustomerId CustomerCategory ConsigneeId ConsigneeCategory Status Lastmodified OrderType CustomerName RegionCode

Char(15) Char(9) varchar(250) varchar(7) varchar(3) varchar(7) varchar(3) varchar(50) datetime varchar(3) varchar(30) varchar(2)

16.1.1.9Pick Ticket Correlation PickTicketId PickTicketInstanceId PickTicketLoopId Status Lastmodified OrderType CustomerId CustomerCategory ConsigneeId ConsigneeCategory CustomerName RegionCode

Char (15) varchar(250) varchar(250) varchar(50) datetime varchar(3) varchar(7) varchar(3) varchar(7) varchar(3) varchar(30) varchar(2)

16.1.1.10Delay InstanceId varchar(250) State varchar(25) TimeSent datetime ExpectedResponseTime datetime Processed integer InstanceType integer

16.1.1.11Holidays Id Details Holiday

integer (identity seed) varchar(50) datetime

16.1.1.12Order Status Status

varchar(50)

16.1.1.13OrderTypes OrderTypes

varchar(50)

16.1.1.14PickticketFilenames PickticketID varchar(15) Filename varchar(50) DateCreated datetime

17 COM components OrderCorrelation Component This component will be responsible for get the order correlation info from the local database. The correlation is done using the instance ID as the message label. This eliminates the need for multiple instances of the Queue to receive messages. CLASSES PRESENT IN ORDER CORRELATION AIC

• • •

IgetCorrelation o This gets the correlation details from the BizTalk and stores in the database. IretrieveCorrelation o This retrieves the correlation details from the database and sends to bizTalk for the process. IRtvLoopCorrelation o This class takes care of the operations, which forms a loop in the process.

PickTicketCorrelation Component This component will be responsible for storing the pick ticket correlation info in the local database. CLASSES PRESENT IN PICKTICKET CORRELATION AIC • IGetPickLoopCorr o This class takes care of the operations, which forms a loop in the process. • IpickTktGetCorr o This gets the correlation details from the BizTalk and stores in the database. • IpickTktRetrieveCorr o This retrieves the correlation details from the database and sends to bizTalk for the process. • IrtvPickLoopCorr o This class takes care of the operations, which forms a loop in the process.

ProcessStatus Component. This component will be responsible for sending emails and other alerts. This component will also responsible for consolidating emails and alerts. The messages, which need to be consolidated, will be stored in the local database. The mail will be consolidated at specified time intervals and will be send using the CDO. Email Consolidation exe This sends a consolidated email by sorting Emails as per the TO ADDRESS, the status code and frequency.

GetOrderCorrelation(AIC) or IGetCorrelation

This component will be responsible for doing the order correlation, which will pick up correlation info from the local database and place it the appropriate instance id as the message label. GetPickTicketCorrelation(AIC) or IPickTktGetCorr This component will be responsible for doing the pick ticket correlation, which will pick up correlation info from the local database and place it the appropriate instance id as the message label. CLASSES PRESENT IN CORRELATION AIC • • • •

IGetLoopCorrelationAIC o This class takes care of the operations, which forms a loop in the process. IGetPickLoopCorrAIC o This class takes care of the operations, which forms a loop in the process. PickTktGetCorrAIC o This gets the correlation details from the BizTalk and stores in the database. XtranetGetCorrelation o This gets the correlation details from the BizTalk and stores in the database.

DBAdapter OrderToDB(AIC) This AIC will insert the order directly into the Spare Parts Database temp table InsertPickTicketIntoDB(AIC) This AIC will insert the pick ticket directly into the Spare Parts Database temp table

Preprocessor EXE • • •

Removes the additional carriage returns in the flat file, and makes it ready for the Biz Talk to parse. Transfers the files from Source folder to the Destination folder (folders which are present in the network, should be shared.). Keeps a back up of the flat file, if configured o To configure for backup, set the registry entry of ‘BackUpFlag’ to ‘1’.

Delay Handler

18 FTP Service Windows FTP utility will be used to send and receive files from the Unix Server (Spare Parts). There will be 3 exe scheduled for FTP, they are 18.1.1 FTP Orders: Used to get the XtranetOrder files from the spare parts to the Biz Talk Server 18.1.2 FTP Pick Tickets: Used to do the following operations, 1. Put the PicklTicketConfirm files from BizTalk to SpareParts Server. 2. Get the NonMARCPickTicket files from the spare parts to the Biz Talk Server 3. Get the PickTicket files from the spare parts to the Biz Talk Server 18.1.3 FTP Status: Used to do the following operations, 1. Get the OrderStatus files from the spare parts to the Biz Talk Server 2. Get the OrderCompletion files from the spare parts to the Biz Talk Server 3. Get the PickTicketStatusFromSP files from the spare parts to the Biz Talk Server

Order Orchestration Exchange Server

Start Network Shared Folder

CH File

RF

DD

BizTalk Messaging

MP

Spar e Parts

AIC

MP Receive Order

DD

CH BizTalk Messaging

StoreCorrInfo Send to Spare Parts

FTP/File

RF

CH1 DD MP BizTalk Messaging

AIC

Receive Status

Success?

Proces N

Y CH FTP/File

RF

DD

MP

BizTalk Messaging

AIC

Order Completion

END

PICK TICKET ORCHESTRATION

Start

Spare Parts

File

CH

FTP/File

RF

File

RF

Receive Pick Ticket

DD

CH BizTalk Messaging

CH File

MP

BizTalk Messaging

MP

M ARC

DD

DD

MP

BizTalk Messaging

Send Pick Ticket to MARC

AIC

Receive Status Action Success?

CH File

RF

AIC/FTP

RF

Y

AIC

Receive ORDC

DD

CH BizTalk Messaging

CH FTP/File

MP

BizTalk Messaging

MP

Spare Parts

DD

N

DD

MP

BizTalk Messaging

Send To Spare Parts

AIC

Receive Status

Action Success?

Y E

N

Error/Exception Cases Process Step Receive Order File

Send Order file to Spare Parts

Order paValidation

Funds Validation

Description The order file will be placed in a folder in BizTalk server. BizTalk server will get that using file receive function. The file will be validated against the XML schema defined for the order file. BizTalk will send the validated order file to the Spare parts system using an AIC or FTP component

Error Cases Parsing Error, Syntax error or invalid file error

Actions The order will be moved to suspended Queue.

Delivery failure

After receiving the order file Spare Parts server will load and validate the order information

Order Validation Failed Critical error

Retry 3 times, after that the order will be moved to suspended Queue Notify SRS

Check whether the dealer has enough funds to execute the order

Not enough funds Enough funds

Notify Dealer Email to SRS Email to Dealer Notify Dealer about the acceptance

Inventory Allocation Generate Pick Tickets

Allocate inventory Pick ticket will be generated for allocated orders. The unallocated items will be moved to a back order. Spare Parts system will inform BizTalk when the order is complete including the back orders

No action within 2 Hours

Email to SRS

Send Pick Ticket to MARC

Each of the pick ticket will be send and tracked separately. The pick ticket level tracking will continue till the invoice generation. Pick Tickets will be consolidated after the invoice generation

Failed to deliver

Notify Sys Admin

Failed to load

Notify Sys Admin (MARC) Notify WAD

Load Pick Ticket in MARC Validate Pick Ticket

Validation failed

Comments No further action will be taken. Alert mechanism on suspended queue is under discussion

All the steps within the Spare parts system are considered as a single activity. Spare Parts system will send appropriate error code and messages to the BizTalk server. BizTalk will take appropriate action based on the error code Alert on suspended queue is required here. Because undelivered items will get moved to suspended queue by default. Error code needs to be defined Error code needs to be defined

Pick Ticket Confirmation Packing Confirmation Send Pick Ticket Confirmation Process Pick Ticket Confirmation Generate Invoice

Pick tickets will be send via BizTalk

VORs after 6 hrs & normal orders after 3 working days) Sending failed

Notify WAD

Processing failed

Notify Sys Admin (MARC) Notify WAD Notify SRS

Invoicing failed Success

Notify WAD Notify SRS Inform dealer

19 Data Elements The data exchanged between the systems are • Indent details • Pick Tickets • Order Confirmation (feedback) • Status Messages The details about data being exchanged are given below. 19.1.1 Indent Details This file is a positional flat file, which contains dealer indents for Spare parts. The following is the structure of the indent file and a sample indent file. Indent files are of the type DAT. The name of the file is … Structure of Indent File Header Information POSITION(1:1) constant "1" to indicate it as header POSITION(2:8) char CUSTOMER ID (1) POSITION(9:11) char CUSTOMER CATEGORY (2) POSITION(12:18) char CONSIGNEE ID POSITION(19:21) char CONSIGNEE CATEGORY POSITION(22:30) char ORDER REFERENCE NO (3) POSITION(31:40) date "dd/mm/yyyy" REFERENCE DATE POSITION(41:45) char TOTAL NO OF PARTS ORDERED POSITION(46:53) char TOTAL QTY ORDERED POSITION(54:57) char ORDER TYPE Detail Information POSITION(1:) constant "2" to indicate it as detail POSITION(2:5) char SERIAL NO POSITION(6:20) char PART NO

POSITION(21:27) char

QTY

Sample Indent Data File 15803 DDL5803 DDL58030603R01/07/2002 2 103141M05163 200 2 209482-00448 20 2 314190M72F10 2 2 417700M60D52 5 2 522100M83060 3

0

0REG

19.1.2 Pick Tickets (Orders) Pick tickets are generated by spare parts system. The pick tickets are positional flat files and contain multiple orders in a single file. These pick tickets are sent to MARC. Pick ticket files are of the type ORD and its name start with ORD and unique order number. Structure of Pick Ticket The pick ticket file contains 5 sections, the transaction header, order header and line item details, end of transaction and the trailer.

ORDER HEADER RECORD_I FIELD_NAME FIELD_LENGTH FIELD_TYPE BEGIN_BYTE END_BYTE -------- ------------------------------ ------------ ---------- ---------- ---------ORDH RECORD_ID 4 CHAR 1 4 ORDH ACTION 1 CHAR 6 6 ORDH RECORD_DATE 8 DATE 8 15 ORDH RECORD_TIME 5 CHAR 17 21 ORDH COMP_CODE 3 CHAR 23 25 ORDH CUST_NO 30 CHAR 27 56 ORDH NO_LINE_ITEMS 5 NUMBER 58 62 64

(Pick Ticket No) – Unique Key 81 (Order Type)

ORDH

ORD_NO

15 CHAR

78

ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH

ORD_TYPE ACCOUNT_NO ALLOW_LOT_SPLIT_FLG BO_POLICY CARR_EXCL1_ID CARR_EXCL2_ID CARR_ID CARR_TYPE CONSOL_CNTRL_NO CUST_ADDR1 CUST_ADDR2 CUST_CITY CUST_COUNTRY CUST_DEA_NO CUST_NAME CUST_ORD_DATE CUST_STATE CUST_ZIP DATA_PASSED_THRU DEA_FORM_NO EXPE_ARR_DATE EXPE_SHIP_DATE FRT_PAY_CODE HOST_PRIO PARTIAL_SHIP_FLG

2 CHAR 80 15 CHAR 83 97 1 CHAR 99 99 2 CHAR 101 102 15 CHAR 104 118 15 CHAR 120 134 15 CHAR 136 150 3 CHAR 152 154 9 NUMBER 156 164 40 CHAR 166 205 40 CHAR 207 246 40 CHAR 248 287 10 CHAR 289 298 9 CHAR 300 308 30 CHAR 310 339 8 DATE 341 348 2 CHAR 350 351 10 CHAR 353 362 150 CHAR 364 513 9 CHAR 515 523 8 DATE 525 532 8 DATE 534 541 2 CHAR 543 544 2 CHAR 546 547 1 CHAR 549 549

ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH ORDH

PO_NO SHIP_ORIGIN SPEC_INSTR1_FLG SPEC_INSTR1_LINE SPEC_INSTR2_FLG SPEC_INSTR2_LINE SPEC_INSTR3_FLG SPEC_INSTR3_LINE SPEC_INSTR4_FLG SPEC_INSTR4_LINE SPEC_SHIP_DAYS_CODE TYPE_SHIP USER_ASSGN_DEST WGHT_LIMIT_CARTON LAST_SHIP_DATE ORD_PRIO ORD_REC_DATE SCHEDULE_CODE WAVE_STATUS PARTY_NAME CUST_ADDR3 CUST_PHONE_NO RF_PICKER

15 CHAR 551 565 4 CHAR 567 570 1 CHAR 572 572 40 CHAR 574 613 1 CHAR 615 615 40 CHAR 617 656 1 CHAR 658 658 40 CHAR 660 699 1 CHAR 701 701 40 CHAR 703 742 7 CHAR 744 750 4 CHAR 752 755 10 CHAR 757 766 7 NUMBER 768 774 8 DATE 776 783 2 CHAR 785 786 8 DATE 788 795 1 CHAR 797 797 1 CHAR 799 799 40 CHAR 801 840 40 CHAR 842 881 12 CHAR 883 894 12 CHAR 896 907

Line details RECORD_I FIELD_NAME FIELD_LENGTH FIELD_TYPE BEGIN_BYTE END_BYTE -------- ------------------------------ ------------ ---------- ---------- ---------ORDL RECORD_ID 4 CHAR 1 4 ORDL ACTION 1 CHAR 6 6 ORDL RECORD_DATE 8 DATE 8 15 ORDL RECORD_TIME 5 CHAR 17 21 ORDL COMP_CODE 3 CHAR 23 25 ORDL ORD_LINE_NO 6 NUMBER 27 32 ORDL ORD_NO 15 CHAR 34 48 ORDL ORD_QTY 9 NUMBER 50 58 ORDL SKU 20 CHAR 60 79 ORDL SKU_KEY1 10 CHAR 81 90 ORDL SKU_KEY2 10 CHAR 92 101 ORDL SKU_KEY3 10 CHAR 103 112 ORDL BO_FLG 1 CHAR 114 114 ORDL CROSSDOCK_FLG 1 CHAR 116 116 ORDL CYCLE_CODE 1 CHAR 118 118 ORDL HOST_PRIO 2 CHAR 120 121 ORDL MIN_SMNTHS_BEFORE_EXPR 3 NUMBER 123 125 ORDL PARTIAL_SHIP_FLG 1 CHAR 127 127 ORDL PKG_NO 3 NUMBER 129 131 ORDL PROD_STAT 2 CHAR 133 134 ORDL REQUISITION_NO 10 CHAR 136 145 ORDL SPECIFIC_LOT_NO 15 CHAR 147 161 ORDL TOT_PRICE 7 NUMBER 163 169 ORDL UNIT_PRICE 7 NUMBER 171 177 ORDL UOM_ABBREV 2 CHAR 179 180 ORDL CNTRL_SUBST_FLG 2 CHAR 182 183 ORDL HAZ_MAT_CLASS 7 CHAR 185 191 ORDL LAST_SHIP_DATE 8 DATE 193 200 ORDL LOT_CNTRL_FLG 1 CHAR 202 202 ORDL MAX_CRIT_TEMP 6 NUMBER 204 209 ORDL MIN_CRIT_TEMP 6 NUMBER 211 216 ORDL ORD_PRIO 2 CHAR 218 219 ORDL PROD_CAT 4 CHAR 221 224 ORDL PROD_FAM 4 CHAR 226 229 ORDL QTY_PICKED 9 NUMBER 231 239 ORDL QTY_SHIPPED 9 NUMBER 241 249 ORDL SERIAL_NO_FLG 1 CHAR 251 251 ORDL WAVE_NO 9 NUMBER 253 261 ORDL LIFO_FLG 1 CHAR 263 263 ORDL PACK_BY_SKU_FLG 1 CHAR 265 265

ORDL ORDL ORDL

PO_NO ALLOW_VAS ORIG_ORD_LINE_NO

15 CHAR 267 1 CHAR 283 6 NUMBER

281 283 285

290

Sample Picket Ticket File TRNH 7902080801164 FFL I 08082002 16:20 ORD 000001164 ORDH I 08082002 16:20 MUL DDLE401 00002 8200056 VR X SHAKTI MOTORS PVT LTD RAJA HOUSE,DABWALI ROAD INDA SHAKTI MOTORS PVT LTD HR 125055 01 N DDLE401 NYYYYYY DDLE401 A ORDL I 08082002 16:20 MUL 000001 8200056 000000002 58410-80010 3183893 EA ORDL I 08082002 16:20 MUL 000002 8200056 000000001 72420-80010 3183894 EA ENDT MUL 7902080801164 000000002 000000001 TRLR 7902080801164 000000002 000000001

SIRSA

58410-80010 72420-80010

19.1.3 Order Confirmation The order confirmation file also follows the same structure of the Pick ticket file, except that the data type is ORDC for order confirmation. Pick Tickets have the data type as ORD. The confirmation details are generated at MARC and are sent to spare parts system. These order confirmations files are of the type ORC and its name start with ORDC and a unique number 19.1.4 Spare Parts database Temp Tables

19.1.4.1Mspr_ordh (Pick Ticket Header) Name

Null?

Type

ORDH_TRNH_TRAN_NO ORDH_TRNH_DATA_TYPE

NOT NULL NOT NULL

VARCHAR2(13) VARCHAR2(4)

ORDH_ORD_NO ORDH_RECORD_DATE ORDH_CUST_CCAT_ID ORDH_CUST_ID ORDH_ORD_TYPE ORDH_NO_LINE_ITEMS ORDH_CARR_ID ORDH_CARR_TYPE ORDH_PARTIAL_SHIP_FLAG ORDH_WAVE_STATUS ORDH_ERROR_FLAG ORDH_HOST_REMARKS ORDH_HOST_UPD_DATE ORDH_CREATION_DATE ORDH_USER_ID

NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL

VARCHAR2(15) DATE VARCHAR2(3) VARCHAR2(7) VARCHAR2(2) NUMBER(5) VARCHAR2(15) VARCHAR2(3) VARCHAR2(1) VARCHAR2(1) VARCHAR2(1) VARCHAR2(100) DATE DATE VARCHAR2(30)

NOT NULL NOT NULL

File Field

19.1.4.2mspr_ordl ( Pick Ticket Line Item) Name

Null?

Type

ORDL_ORDH_TRNH_TRAN_NO ORDL_ORDH_TRNH_DATA_TYPE

NOT NULL NOT NULL

VARCHAR2(13) VARCHAR2(4)

Field in email order file

ORDL_ORDH_ORD_NO ORDL_SERIAL_NO ORDL_RECORD_DATE ORDL_ITEM_CODE ORDL_ORD_LINE_NO ORDL_MUL_ORD_NO ORDL_ORDERED_ITEM_CODE ORDL_ORDER_QTY ORDL_QTY_PICKED ORDL_QTY_SHIPPED ORDL_BO_FLAG ORDL_PARTIAL_SHIP_FLAG ORDL_PROD_STAT ORDL_CREATION_DATE ORDL_USER_ID

NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL

VARCHAR2(15) NUMBER(3) DATE VARCHAR2(15) NUMBER(6) VARCHAR2(15) VARCHAR2(240) NUMBER(9) NUMBER(9) NUMBER(9) VARCHAR2(1) VARCHAR2(1) VARCHAR2(2) DATE VARCHAR2(30)

NOT NULL NOT NULL

19.1.4.3mspr_cord( Order Header) Name

Null?

Type

Field in email order file

CORD_CUST_CCAT_ID CORD_CUST_ID CORD_CONS_CCAT_ID CORD_CONS_ID CORD_ORDER_TYPE CORD_REF_NO CORD_REF_DATE CORD_BATCH_NO CORD_QTY CORD_ITEMS

NOT NULL VARCHAR2(3) NOT NULL VARCHAR2(7) NOT NULL VARCHAR2(3) NOT NULL VARCHAR2(7) NOT NULL VARCHAR2(3) NOT NULL VARCHAR2(50) NOT NULL DATE NOT NULL NUMBER(7) NOT NULL NUMBER(6) NOT NULL

VARCHAR2(3) VARCHAR2(7) VARCHAR2(3) VARCHAR2(7) VARCHAR2(3) VARCHAR2(50) DATE NUMBER(7) NUMBER(6) NUMBER(4)

CUSTOMER CATEGORY CUSTOMER ID CONSIGNEE CATEGORY CONSIGNEE ID ORDER TYPE ORDER REFERENCE NO REFERENCE DATE

CORD_STATUS CORD_EXPIRY_DATE CORD_CORRECTED_DATE CORD_CREATION_DATE CORD_USER_ID CORD_LAST_UPD_DATE CORD_USER_ID_UPD_BY CORD_CURRENCY CORD_TRANS_MODE CORD_DISC_FACTOR CORD_DISC_FACTOR_PRINT CORD_DISC_FACTOR_SMC CORD_DISC_FACTOR_SMC_PRINT CORD_SHIP_MARK CORD_CREDIT_IND CORD_VALUE CORD_CATG

NOT NULL NOT NULL

TOTAL QTY ORDERED TOTAL NO OF PARTS ORDERED

VARCHAR2(1) DATE DATE DATE VARCHAR2(30) DATE VARCHAR2(30) VARCHAR2(4) VARCHAR2(3) NUMBER(5,2) NUMBER(5,2) NUMBER(5,2) NUMBER(5,2) VARCHAR2(180) VARCHAR2(1) NUMBER(10,2) VARCHAR2(3)

19.1.4.4mspr_cuod(Order line Item) Name

Null?

Type

CUOD_BATCH_NO CUOD_SL_NO CUOD_CUST_PAGE_NO CUOD_CUST_SL_NO CUOD_ITEM_CODE CUOD_QTY CUOD_ERR_IND

NOT NULL NOT NULL

NUMBER(7) NUMBER(4) NUMBER(2) NUMBER(4) VARCHAR2(15) NUMBER(6) VARCHAR2(1)

NOT NULL NOT NULL

Field in email order file

SERIAL NO PART NO QTY

CUOD_REF_SL_NO CUOD_USER_ID CUOD_PART_REPLACED_B Y CUOD_RATE

NUMBER(4) VARCHAR2(30) VARCHAR2(15)

NOT NULL

NUMBER(8,2)

19.1.5 Error/Status Codes Error/Status Messages 1 Status file Back from Spare Parts to BizTalk ( Order level) 2 Status file Back from Marc to BizTalk (Pick ticket level ) 3 Status file from Spare Parts to BizTalk(Pick Tick level) 4 Status file from Spare Parts to BizTalk(Order Level) Field Required? Size Type 1 Message Orderid 15 Text StatusCode Req Integer Description Req 150 Text

Email Ids Subject OrderType RegionCode ReferenceID CutomerID Customer_Cat

StatusCode

Opt Req Req Req Req Req Req

15

250 Text 100 Text 2 Text 25 Text Text

Type of Message 0 Success 1001 Cancel Order 1002 Error Notification 1003 Delay Notification 1004 Critical Error

Separed by ; dealer email Ids will be a part of message

Actions Send Email Stop Order Send Email Send Email Send Email and Stop Order

2 Message PickTicketID Status Code Description Email Ids Subject OrderType RegionCode

Integer 500 Text 150 Text 100 Text

Type of Message

Actions

Status Code

0 Success 2001 Cancel Order 2002 Error Notification 2003 Delay Notification

Stop Order Send Email Send Email

3 Message PickTicketID Status Code Description Email Ids Subject

Integer 500 Text 150 Text 100 Text Type of Message 0 Success 3001 Cancel Pick Ticket 3002 Error Notification 3003 Delay Notification

Status Code

4 Message

OrderId Status Code Description Email Ids Subject

Integer 500 Text 150 Text 100 Text

Status Code

Type of Message 0 Success 3001 Cancel Order 3002 Error Notification 3003 Delay Notification

Actions Stop Order Send Email Send Email

Actions Stop Order Send Email Send Email

20 Transport Mechanisms o Currently the Indents are received thru Email (SMTP) o The orders and order confirmations are sent using FTP o Dealers view the order status using extranet (HTTP/S)

21 Platform Information There are 5 systems in the current scope. The details about platform, database and other tools installed are given below. Spare Parts: OS

: DigitalUnix

Database Front end

: Oracle 8i : Oracle forms

OS Database Front end

: Windows NT : Oracle 8i : Java

OS Mail server Tools

: Windows 2000 : MS Exchange Server 2000 : MailRobo

OS DB Tools

: Windows 2000 : Proprietary : I2DP

OS Database Front end

: DigitalUnix : Oracle 8i : Oracle forms

MARC:

Email Server:

I2DP:

Sales System:

22 Data volume and performance The data transfer happens at three places. 1. Indents from dealers via email • Happens at every 15 minutes. • File size is: • Average number of files: • Connectivity type: LAN 2. Pick tickets from spare parts to Marc • Happens at every 15 minutes. • File size is: • Average number of files: • Connectivity type: LAN 3. Order confirmation from MARC to spare Parts • Happens at every 15 minutes. • File size is: • Average number of files: • Connectivity type: LAN

23 Data Transformation (Mapping) None. Currently no data transformation is required between the applications.

24 Transactions None

25 Application Exceptions The data has to be validated at various stages. In case of validation failure the administrator has to be notified. The following are places where the validation occurs. o The spare parts orders received by dealers will be validated by spare parts system. o Pick ticket validation failure at MARC o Order confirmation failure at Spare parts system. o Delay exceptions where ever required The failure of data delivery between the systems should be notified to the administrator.

26 Acknowledgements And Receipts The system will have to generate the acknowledgements for the successful delivery of the messages between the applications. The acknowledgements should be generated for the following: o When the Pick tickets are delivered to MARC o When the spare parts receives the order confirmation from MARC.

27 Recommendations o Use standard framework to transfer the data between the different systems. o Use XML files for data transfer between the systems instead of flat files o Trap the errors and exceptions at the application level.

28 Issues Please see the Pending Items (issues) document for details. Most of the issues are resolved except the Delay mechanism.

29 Limitations • •

BizTalk server cannot access the files from UNIX server directly. Custom component will have to be developed to get the files from UNIX server. BizTalk does not generate any alerts automatically. Custom development is involved in generating the alerts.

30 Unit Testing Guidelines This describes the approach to be followed for unit testing. Order processing should be tested for the following

o o o o o o o o o o

Successful loading of the order information to the temporary tables. If validation fails then email should be sent for administrator. Validation of order data and generation of the Pick Ticket. Transfer the Pick Ticket to MARC server. If the transfer of Pick Ticket fails, the administrator has to be notified. Pick up the Feedback from MARC server Transfer the order feedback to Spare parts server If the transfer of order feedback fails then the administrator has to be notified. Upload the feedback details to Spare parts Overall process flow and exception handling

31 Initialization and Termination •

The BizTalk process will start whenever the order is received by mail.

•

The BizTalk process terminates whenever there is a critical failure/exception or after the successful completion of order processing.

32 Revision History Date

Version

Description

Author

26/Sep/2002

1.0

Design Document

Prince Manuel

5/Oct/2002

1.1

Design Document

Prince Manuel

25/Oct/2002

1.2

Design Document

Prince Manuel

15/Nov/2002

1.3

Detailed Design Document

Prince Manuel

23/Jan/2003

1.4

Updated Design Document

Prince Manuel