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