Overview

The project consists of developing an Oracle based application which seamlessly allows porting data from SAP into Oracle. All data fields must have complete validation and adherence to original database specifications. The data transfer will be bi-directional.

System Overview

Oracle version 10 running on Pentium Zeon based

processors. SAP version release 4.6 running on Dell server with at least a terabyte of free disk space. All hardware and software have been verified for proper operation with respective systems

---

Design Considerations

All data types must be validated in both directions before being allowed to enter either database. Any non-compliance will be logged and an error generated. Any errors will invalidate the transfer.

Assumptions and Dependencies

It is assumed that the original data on both sides is valid

• All hardware is functioning
• All Operating systems patches have been applied
• End-user is trained on proper operation of new features
• This project will not change any functionality of current systems

General Constraints

Application must be completely based in Oracle, any activex controls must be approved by project manager prior to implementing them in code.

• Resources for timely completion of project will be availability within 24 hours
• All software development standards and compliance testing will be adhered to at all times
• Both SAP and Oracle will have data passed back and forth with no effect on current operations
• Interface/protocol requirements will be generated before coding begins.
• All code being developed with reside on our development server. No Data or company property can leave company grounds at any time.
• Building security policies will be followed at all times
• Testing will take place on Pilot system for a time not to exceed two months following completion of this project.

Goals and Guidelines

• The KISS principle ("Keep it simple stupid!")
• Emphasis on speed versus memory use
• working, looking, or "feeling" like the existing product

Development Methods

• Define system interaction
• Flowchart all in/out data points
• Develop migration functions
• Test all functions

---

Architectural Strategies

Where ever possible the system will be operated in a transactional mode. All data will be uploaded in a batch and a scheduled transfer will take place upon bi-directional data transfer

• C ,C++ and Java will be used for all programming
• Microsoft .NET will be used to develop web based and windows based Applications.
• J2EE will be used to develop portals and web based Applications.
• All present functions will be used to maximize reuse of existing software components to implement various parts/features of the system
• Compliance to current user interface paradigms must be followed
• Error detection and recovery will follow current company policy and structure

---

System Architecture

Data structure on SAP or Oracle is needed in other system. Operator will initiate transfer utilizing proper top level data transfer number. An extraction of data from remote system will take place. Data will be validated and a batch created on host. Batch will be scheduled and run on host.

---

Policies and Tactics

• Microsoft C/C++ with be used for compiler, interpreter
• Oracle/SAP for database, library
• Speed vs Size will be used in all Engineering trade-offs
• All code and documentation will follow EMS current policies regarding coding guidelines and conventions
• All transactions to / from databases will have traceability
• Software test plans will be defined in first month of project
• Upon completion of project EMS assumes the responsibility for maintaining the software

---

Database Schema Additions

Database table names for reference:

TableName	DAT Name	Table Name	DAT Name
136 Tables:	ACCTWK.DAT	eshipment	shipevis.dat
Account Work File	ACTIND.DAT	Feature Qualifier	QUALST.DAT
Activity Index	ADDMST.DAT	Field Master	FLDMST.DAT
Address Master	AUDTRL.DAT	Freight Carrier	FRTCARR.DAT
Audit Trail	BOLCARR.DAT	General Ledger Acct	GLACCT.DAT
BOL Carrier	BOLDET.DAT	HazMatl Class	HAZCLASS.DAT
BOL Detail	DETSER.DAT	HazMatl Qualifier	HAZQUAL.DAT
BOL Detail B_Serial	BOLLOT.DAT	Invoice Detail	INVDET.DAT
BOL Lot	BOLSER.DAT	Invoice Master	INVMST.DAT
BOL Serial	SHIPSER.DAT	Job Progress	JOBPRO.DAT
BOL Ship B_Serial	BOLSHIP.DAT	Labor Rates	LBRATES.DAT
BOL Shipments	BOLTRACK.DAT	Lot Track Structure	LOTSTR.DAT
BOL Tracking	winnte.bom	Lot Track Template	LOTTMP.DAT
BOM Notes	buyers.dat	Lot Tracking Header	LOTHDR.DAT
Buyers	CDEMST.dat	Lot Tracking Hist	LOTTRK.DAT
Code Master	CFGDSC.DAT	Lot Tracking Notes	LOTNTE.DAT
Config Description	CONFIG.DAT	MAX Notes	MAXNTE.DAT
Configuration Data	CONFIG.DAT	MAX Security	MAXSEC.DAT
Configuration EDI	CONFIG.DAT	MAX Transaction	MAXTRN.DAT
Configuration GPS	CFGIND.DAT	Mfg Part Master	MPNMST.DAT
Configuration Index	CONFIG.DAT	Mfg Part String	MPNSTR.DAT
Configuration LotTk	CONFIG.DAT	MRP Explosion	MRPEXP.DAT
Configuration MlCst	CFGMST.DAT	Multiple Cost	MLTCST.DAT
Configuration Mst	CFGSTR.DAT	NMFC Classification	NMFCCODE.DAT
Configuration Str	SHPCONWT.DAT	NMFC Item	NMFCITEM.DAT
Container Weight	CSTMST.DAT	Notify Data	notify.dat
Customer Master	CUSTPART.DAT	Object Type Master	OBJTYP.DAT
Customer Part Data	CSTPTORD.DAT	Order Master	ORDMST.DAT
Customer Part Xref	CSTVAT.DAT	Packaging Form	PACKFORM.DAT
Customer VAT	dbver.dat	Packaging Material	PACKMATL.DAT
DBVersion	Defects.dat	Part Lot	PRTLOT.DAT
Defect Codes	DOCLNK.DAT	Part Master	PRTMST.DAT
Document Link Mst	EDIAIR.DAT	Part Routing	PRTRTG.DAT
EDI Airport Data	EDIBLASO.DAT	Part Sales	PRTSLS.DAT
EDI Blanket SO Data	txmapop.dat	Part Stock	PRTSTK.DAT
EDI Cust Operation	EDICUST.DAT	Part Vendor	PRTVEN.DAT
EDI Customer Data	ediwork.dat	Part Xref	ALTPRT.DAT
EDI Inv Work File	ediprtqu.dat	PO Receipts	PORECS.DAT
EDI Part Qum	EDISHPCD.DAT	Price Breaks	PRICEBK.DAT
EDI Ship Code Data	EDISHIP.DAT	Product Data	PRODUCT.DAT
EDI Shipper	editrnsx.dat	Product Schedule	PRDLNESC.DAT
EDI TnxHist Detail	txlog1.dat	Product Structure	PRDSTR.DAT
EDI TnxHist Master	EMPMST.DAT	Purchase Order Code	POCODE.DAT
Employee Master	EMPWRK.DAT	Purchase Order Note	PONOTE.DAT
Employee Work	EQUIPCOD.DAT	Report Extract	RPTEXT.DAT

Part Master 114 Fields, Record Length = 653

Field Offset Length Type Description

PRTNUM_01 0 15 String Part Identifier

TYPE_01 15 1 String Part Type Code

CLSCDE_01 16 1 String Class Code

PLANID_01 17 3 String Planner Identifier

COMCDE_01 20 10 String Commodity Code

LLC_01 30 2 Integer Low Level Code

PMDES1_01 32 25 String Part Description

PMDES2_01 57 25 String Part Description#2

BOMUOM_01 82 2 String Bom Unit Of Measure

STAENG_01 84 1 String Engineering Status Code

STAACT_01 85 1 String Accounting Status Code

FRMPLN_01 86 1 String Firm Planned Code

PRDDTE_01 87 4 Btrieve Date Date Released To Production

FILL01_01 91 2 String Filler

OBSDTE_01 93 4 Btrieve Date Date Obsoleted

FILL02_01 97 2 String Filler

WGTDEM_01 99 2 String Weight Dimension

WGT_01 101 8 IEEE_Float Weight

EXCDTE_01 109 4 Btrieve Date Exception Date

FILL03_01 113 2 String Filler

EXCFLG_01 115 1 String Exception lag

DRANUM_01 116 15 String Drawing Number

DELSTK_01 131 8 String Deliver To Stockroom

CYCCDE_01 139 1 String Cycl eCount Code

CYCDTE_01 140 4 Btrieve Date Cycle Count Date

FILL04_01 144 2 String Filler

CYCNUM_01 146 2 Integer Year-to-Date Cycle Counts

CYCPER_01 148 2 Integer Cycle Count Tolerance (Percent)

OBSOLT_01 150 2 Integer Original CYCDOL field

CYCOOT_01 152 2 Integer Cycle Count Out-Of-Tolerance

ORDPOL_01 154 1 String Order Policy Code

YIELD_01 155 2 Integer Yield (Percent)

TNXDTE_01 157 4 Btrieve Date Transaction Date

FILL05_01 161 2 String Filler

ROP_01 163 8 IEEE_Float Reorder Point Amount

ROQ_01 171 8 IEEE_Float Reorder Quantity Amount

SAFSTK_01 179 8 IEEE_Float Safety Stock Amount

MINQTY_01 187 8 IEEE_Float Minimum Order Quantity

MAXQTY_01 195 8 IEEE_Float Maximum Order Quantity

MULQTY_01 203 8 IEEE_Float Order Quantity Multiple

AVEQTY_01 211 8 IEEE_Float Average Order Quantity

ISSMTD_01 219 8 IEEE_Float Issue Quantity Month-to-Date

ISSYTD_01 227 8 IEEE_Float Issue Quantity Year-to-Date

SALMTD_01 235 8 IEEE_Float Sales Quantity Month-to-Date

SALYTD_01 243 8 IEEE_Float Sales Quantity Year-to-Date

MFGLT_01 251 2 Integer Mfg Leadtime (Days)

MFGPIC_01 253 2 Integer Mfg Picking Leadtime (Days)

MFGOPR_01 255 2 Integer Mfg Operation Leadtime (Days)

MFGSTK_01 257 2 Integer Mfg Stocking Leadtime (Days)

PURLT_01 259 2 Integer Pur Leadtime (Days)

PURPIC_01 261 2 Integer Pur Picking Leadtime (Days)

PUROPR_01 263 2 Integer Pur Operation Leadtime (Days)

PURSTK_01 265 2 Integer Pur Stocking Leadtime (Days)

PRICE_01 267 8 IEEE_Float Price Per Unit

COST_01 275 8 IEEE_Float Cost Per Unit

CSTTYP_01 283 1 String Cost Type Code

CSTDTE_01 284 4 Btrieve Date Cost Calculation Date

FILL06_01 288 2 String Filler

CSTUOM_01 290 2 String Cost Unit Of Measure

CSTCNV_01 292 8 IEEE_Float Bom To Cost Uom Conversion

MATL_01 300 8 IEEE_Float Materials Value This Level $

LABOR_01 308 8 IEEE_Float Labor Value This Level $

316 8 IEEE_Float Variable Overhead This Level$

FOH_01 324 8 IEEE_Float Fixed Overhead This Level $

QUMMAT_01 332 8 IEEE_Float Cumulative Material $

QUMLAB_01 340 8 IEEE_Float Cumulative Labor $

QUMVOH_01 348 8 IEEE_Float Cumulative Overhead Hours $

QUMFOH_01 356 8 IEEE_Float Cumulative Fixed Overhead Hours $

HRS_01 364 8 IEEE_Float Hours This Level

QUMHRS_01 372 8 IEEE_Float Cumulative Labor Hours (Exploded Bom)

ALPHA_01 380 8 IEEE_Float Alpha Factor (Mps)

QUMSUB_01 388 8 IEEE_Float Cumulative Subcontract Cost

PURUOM_01 396 2 String Pur Unit Of Measure

PURCNV_01 398 8 IEEE_Float Pur To Bom Conversion

SCRAP_01 406 2 Integer Planned Scrap Factor (Percent)

BUYER_01 408 3 String Buyer Identifier

INSRQD_01 411 1 String Inspection Required Code

ONHAND_01 412 8 IEEE_Float On-Hand (All Stockrooms)

NONNET_01 420 8 IEEE_Float Non-Nettable On-Hand

SCHCDE_01 428 1 String Forward/Backward Schedule Code

REVLEV_01 429 3 String Revision Level

ACTTYP_01 432 1 String Accounting Type Code

ACTCDE_01 433 1 String Accounting Code

SCHFLG_01 434 1 String Scheduling Flag

MPNFLG_01 435 1 String Manufacturers Part Number Flag

MATLXY_01 436 8 IEEE_Float Matl Value Of X & Y Type Parts

CRPHLT_01 444 4 IEEE_Float Critical Path Lead Time

LOTTRK_01 448 1 String Lot Tracking Flag

MULREC_01 449 1 String Multiple Receipts Flag

SERTRK_01 450 1 String Serial Tracking Flag

LOTSFC_01 451 1 String Lot Control On Shop Floor Flag

SHLIFE_01 452 4 IEEE_Float Shelf Life InDays

DELLOC_01 456 10 String Primary Locator

SUBCST_01 466 8 IEEE_Float Subcontract Cost

PERDAY_01 474 2 Integer Period Days

LSTECN_01 476 12 String Last Engineering ChangeNo.

CURREV_01 488 15 String Current Revision

RECVEN_01 503 7 String Recommended Vendor ID

RTEREV_01 510 15 String Routing Revision Number

RTEDTE_01 525 4 Btrieve Date Routing Revision Date

ALLOC_01 529 8 IEEE_Float Allocated Quantity

JOBEXP_01 537 1 String Explode JOB Progress by default

RNDRQS_01 538 1 String Order Requirements Rounded

EXCREC_01 539 2 Integer Excess Receipts

INDDEM_01 541 1 String Independent demand

VIEWER_01 542 40 String Path Used For BOM Viewer

MCOMP_01 582 3 String Multi-Company

MSITE_01 585 3 String Multi-Site

UDFKEY_01 588 15 String User Defined Key

UDFREF_01 603 25 String User Defined Reference

SUPCDE_01 628 1 String Supply Chain Code (Y,N)

CYCDOL_01 629 8 IEEE_Float Cycle CountTolerance (Dollars)

TMSTMP_01 637 8 TimeStamp TimeStamp

FILLER_01 645 8 String Filler

---

Bibliography

EMS employee policy manual

EMS ISO standards policy and procedures

Microsoft C/C++ reference materials both soft and hard copies.

EMS list of approved software

EMS internet use policy