User Manual

 

 

Overview

Sales Order Processing Import is a Nolan Business Solutions product developed to import Orders, Invoices and Returns into the Microsoft Business Solution Dynamics GP SOP module. Designed for our customers' requirements, it has the following features:

  • Different file import formats allow for as few or as many fields as you need.
  • Intelligent defaulting of values not supplied in the import files.
  • Support for creating and updating of customers, addresses and items.
  • Full Multicurrency support.
  • Full support for the Euro functionality in Dynamics GP.
  • Any Number of import definitions can be set up, each with different import files and import settings.
  • Support for immediate imports, imports running at a set interval, and even imports running at scheduled times.
  • Audit logs are kept for each import for later viewing and printing.
  • Multi-Company support, SOP Documents can be imported into any Company.
  • Import directly from SQL Interface tables or from CSV text files.

Setup

Install

Before installing SOP Import, please ensure that there are no users logged into Microsoft Business Solutions Dynamics GP as this could adversely affect the installation process.

This process will need to be completed on all workstations on which Dynamics GP is installed. However, if Dynamics GP is run from a network (DYNAMICS.dic is installed in a network location), you only need to install to that location.

  1. Launch the install application.
  2. Follow the on-screen instructions to complete the install. The install will confirm the location of Dynamics GP and will install the SOP Import chunk file into this directory.
  3. Once the install has completed, run Dynamics GP to load the chunk file.

Setup

Form security changes, additions to the menu structure, and any necessary file upgrades are not made during the previous application install process. Use the Install window to do these automatically by following the steps below. See Appendix B for information on which forms and menus will be affected.

  1. Make sure that no other users are logged into the system.
  2. Start Dynamics GP – if you log in using the ‘sa’ user, the install window will automatically be added to your shortcuts (go to step 7). You must login as ‘sa’ to install when using Dynamics GP on a SQL database.
  3. Open the ‘Shortcut Bar’ and select Add.  Then select Other Window.
  4. Enter SOP Import Install into the Name field and then expand the Nolan Business Solutions SOP Import folder in the Available Windows list.
  5. Expand the Company Then find and select the Install SOP Import window in the list and push the Add button.
  6. Click on the Done button to close the Add Shortcut window.
  7. Select SOP Import Install from the Shortcut Bar. A window showing all the companies will appear, as illustrated below.
  8. To register SOP Import, enter your Registration Keys in the fields provided (You will have received these Registration Keys with your purchase of SOP Import). This is required to enable use of SOP Import in companies other than the sample company, ‘Fabrikam, Inc.’.

Note: The registration keys may have a built in expiry date.  This information is displayed at the bottom of the window (to the left of the Install button).

  1. Click on the Register Button, you will receive a message indicating that the Registration was successful.
  2. For SQL Installations, make sure the Database option is ticked to create the tables on SQL, set permissions to them correctly and install stored procedures.
  3. Use the Mark/Unmark, Mark All and Unmark All buttons to mark all the companies to install SOP Import to. Finally, push the Install

The system automatically places all SOP Import windows on a new ‘SOP Import’ sub-menu on the affected Dynamics GP menus, for example Cards, Sales, SOP Import will then list each of the SOP Import setup screens. 

Import Definitions

Overview

An Import Definition defines all the details which are used for an import. The import files, default batch, timing settings and many other options all need to be set up.

 

Import Definitions Maintenance Window

Location

Cards >> Sales >> SOP Import >> Import Definitions

Layout

Processing

The functionality of each of the fields adhere to the standard lookups and zooms within Dynamics GP.

Import Type specifies whether this definition is designed to be run immediate, or to be run periodically on a schedule. Immediate imports will always be run as soon as they are started in the SOP Import Processing window. Scheduled imports can be run at either specified times, or on a regular interval of a set number of minutes. See information on the Import Period and Queue Times fields below for more details on timed imports.

Import Format indicates whether to import from the SQL Interface tables, or from CSV text files.  In the case of text files, the drop-down list defines which of the two HDR formats are used in the import file. A full description of both formats appears in Appendix A.

Import Filename specifies the full pathname to the text file to import.

Save Filename specifies an optional text file to write successfully imported transactions to.

Rejects Filename specifies an optional text file to write rejected transactions to. When a transaction contains an error, it is written to this file.

Import Files specifies what will happen to an import file once it has been imported. ‘Never Delete’ will leave the import file as it is. ‘Delete If Successful’ will delete the import file if the whole file is imported successfully. ‘Always Delete’ will mean the import file is always delete, even if errors occurred.

Disable Date Masking in Filenames determines if the date placeholders should be processed in filenames. See the Filename Handling section on page 11 for more details.

Error Handling specified what will happen when an error is encountered in an import file. When set to ‘Reject whole import file’, the whole file will be rejected. When set to ‘Reject invalid transactions’, any transactions which are invalid are rejected, but valid transactions are still imported. All error messages are always recorded in the error file (the import filename with an .ERR extension).

Import Batch determines which SOP batch imported sales documents are created in. If the batch does not existing at the time of import, it will be created. If this field is blank, the batch will default to "SOP IMPORT".

General Import Options Window

Location

Options button on Import Definitions Maintenance window

Layout

Processing

This window contains all the extra options relating to a normal import. Many options also apply to customer and item imports.

File Sequence Numbers determine how sequence numbers in file names are handled. If set to Sequential, the sequence number is substituted for the # characters in all filenames and then the number is incremented by one. Alternatively, a range of Sequence Numbers can be specified. If there is range, all files available in the range are imported every time the import is run. See the Filename Handling section on page 11 for more details.

Import Schedule specifies when the import will be run for scheduled definitions. Import Period specifies the interval between imports when the definition is run as Recurring. Queue Times contains a list of the times for the import to run if it is run as Queued.

File Formatting defines what characters are used in the import file to separate fields and to delimit field values. Fields can be separated by commas, tabs, or a user entered character. Values can be delimited by single quotes, double quotes, or a user entered character.

Date Format Mask Enter the mask, along with any separators, which describes how the date fields appear in the import file.

Here are some of the supported mask components -

DDD - Day number in the year. If no year is contained in the mask, it will be assumed that the year is the same as the year of the Dynamics GP user date.

d - Single digit day format, e.g 1 for the first, but 11 for the eleventh.

dd - Double digit day format, e.g. 01 for the first, 11 for the eleventh.

m - Single digit month format - as single digit day format.

mm - Double digit month format - as double digit day format.

MMM - Month name, e.g. JAN, FEB, MAR, etc.

yy - Double digit year, e.g. 05, 06, etc.

yyyy - Four digit year, e.g. 2005, 2006, etc.

Separators - Almost any character can be used as a separator, apart from those which would be interpreted as date mask characters, however, please ensure that the separators in the date mask are the same as those in that date string in the import file.

Here are some example dates and how their format would be represented

Date on file

Format Option

21/04/99

dd/mm/yy

04/21/99

mm/dd/yy

042199

mmddyy

21/04/1999

dd/mm/yyyy

045/99

DDD/yy ( day 45 of year 99)

123/1999

DDD/yyyy ( day 123 of year 1999 )

21JAN1999

ddMMMyyyy

21/FEB/99

dd/MMM/yy

 

Dates with single digit day / month formats, e.g. dmyy, MUST contain a separator in the date, and thus also the date mask to allow the date decode routine to differentiate between the day and the month. For example, with a date of 11106 with a mask of dmyy, we do not know whether this is the 11th  January 2006, or the 1st November 2006, so it is necessary to include separators in the date string in the import file, e.g. 1-11-06, and the date mask will then be d-m-yy.

N.B. This field must be populated when using text import files. If it isn't you will receive the error message - 'You Must Set Up A Date Format Mask On The General Import Options Definition Window Before You Can Run This Import' - when attempting to run an import. Sites which have upgraded from an earlier SOP Import release which had a different way of defining the date format may well receive this error if they haven't updated their format definitions before attempting an import.

QTY Type for Returns allows the default quantity type for returned items to be selected. This is only used if a RTN line is not found in the Return sales document.

Use Untitled Comments determines how comments on sales documents are handled. If ticked, comments imported will use a Comment ID of ‘*untitled’. If not ticked, a new Comment ID will be created for the comments for each transaction, using the SOP Number of the document as the Comment ID.

The Stock Shortage Options lists the available settings for when an imported item is not in stock. Reject All Order will reject the whole transaction, Override Shortage will import the item as normal leaving negative stock in the inventory, and Back Order Balance and Back Order All will place the required quantities on back order, if the item setup allows back ordering.

Customer Import Options Window

Location

Customer button on Import Definitions Maintenance window

Layout

Processing

Update/Add Addresses determines how addresses are handled in the import. If ticked, address which do not exist in Dynamics GP are created using information from the import file. Also, addresses which do exist are updated with the values from the import file.

Create New Customers determines if non-existent customers should be created.

Default Class ID specifies the default class to use when creating new customers. The Customer Class ID field on ADR records overrides this.

Use Separate Files determines if a separate set of file should be used before the main transaction import. The files are specified by the Filename fields on this window. The import file should only contain ADR records.

Only Import Customers specifies that this Import Definition ID should only be created and/or updating customers and addresses. If ticked, only the import file given on this window is imported.

Item Import Options Window

Location

Item button on Import Definitions Maintenance window

Layout

Processing

This window specifies options that deal with items.

Update Existing Items determines how items are handled in the import. If ticked, existing items are updated with the values from the import file.

Create New Items determines if non-existent items should be created.

Default Class ID specifies the default class to use when creating new items. The Item Class ID field on DTL records overrides this.

Use Separate Files determines if a separate set of file should be used before the main transaction import. The files are specified by the Filename fields on this window. The import file should only contain DTL records.

Only Import Items specifies that this Import Definition ID should only be create and/or updating items. If ticked, only the import file given on this window is imported.

Import Processing

Overview

The Import Processing window uses a specified Import Definition ID to import orders, invoices and returns into the Dynamics GP Sales Order Processing module.

Import Processing Window

Location

Tools >> Routines >> Sales >> SOP Import >> Import Processing

(Also from Import button on Import Definitions Maintenance window.)

Layout

Processing

When an Import Definition is selected, the Processing Definition list is filled with the import options. This shows all information on what this import definition will do.

The Current Activity and Progress fields update during the import to show its progress.

Processing Description

An import process can be in up to three stages. If ‘Use Separate Files’ is on for either Customer or Item imports, these files are validated and imported first. Once these are complete, the main transaction file is read.

Import processing reads through the transaction import file and validates the Orders, Invoices and Returns it contains. If Error Handling is set to ‘Reject invalid transactions’, any transactions which pass validation are imported. If Error Handling is set to ‘Reject whole import file’, any error causes the whole file to be rejected.

By default, imported transactions are all placed in the batch specified in the Import Definition. This batch number can be overridden using the Batch Number field on the transaction header.

Once imported, the transactions are complete documents, just as they would be if they had been entered through Sales Transaction Entry. They can be posted, transferred, etc. just like a normal transaction.

Tax details will be created for the transactions according to the information in the import file. If no details are supplied, tax information is defaulted using information from the customer. Please note that Dynamics GP regularly recalculates all the tax information, so the imported tax details may be lost if a document is changed after import.

Once an Immediate import is complete, a window will be displayed showing any errors. These errors are also written to the import error file (the import file with an .ERR extension). This allows you to see the errors even when running a scheduled import, which will not display the errors window.

An audit is also kept of the imports. It records information such as the import file names, date, time and user. See the Import Audit section on page 12 for more details on viewing this audit information.

 

Filename Handling

Sequence Numbers

When running a scheduled import, it may be required that a different file is imported every time the import is run. Where these files will contain a sequential numeric, the places in the filename occupied by the sequential number may be substituted with # characters. For example:

An import filename of :C:Files/Inv####.csv will become :C:Files/Inv0001.csv on the first import, :C:Files/Inv0002.csv on the second import, and so on.

The number used for # replacement is defined in the File Sequence Numbers section of the import definition options. If this option is set to Sequential, a single file is imported each time the import runs and the sequence number is then incremented by one.

If File Sequence Numbers is set to Range, ‘From’ and ‘To’ sequence numbers are specified. When the import runs, all files in this range are be imported. Files which do not exist are ignored. For example:

From is set to 1 and To is set to 3. The import filename is :C:Files/Inv####.csv. Every time the import runs, it will attempt to import files :C:Files/Inv0001.csv, :C:Files/Inv0002.csv and :C:Files/Inv0003.csv.

Dates

When dates are used in the file names, the placeholders dd and mm can be used. These are replaced with the current day and month. E.g.

An import filename of :C:Files/Invddmm.csv will become :C:Files/Inv3003.csv when imported on the 30th March.

As filenames could contain dd or mm without wanting to have these replaced, the date replacement feature can be disabled using the Disable Date Masking option in the import definition.

Defaulting of Field Values

A sales document contains many more fields than are supplied in the import file definition. Additionally, many fields in the import file can be left blank. This section details where the values for all these fields are defaulted from.

If not supplied, the SOP Document ID is defaulted from the Sales Order Processing Setup window. This affects whether users are allowed to delete or void the transactions, which invoice type orders will raise when transferred to invoice, etc.

If an SOP Number is not given, it defaults to the next available document number for the Document ID of this transaction.

Customer related details, such as addresses, tax schedules, and shipping methods, are all defaulted from the Customer card. For invoices and returns, the customer related nominal accounts used when creating distributions are also defaulted from the Customer card.

Stock is, by default, allocated and fulfilled from the site specified in the Sales Order Processing setup window. See the Stock Handling section below for details on handling of stock levels and serial/lot number tracking.

The customer's Trade Discount percentage will default from the Customer card. It is then used to calculate a Trade Discount Amount based on the document's Subtotal value.

Item related details, such as unit cost, description, and tax schedule, are defaulted from the Inventory Item card. The Item related nominal accounts are also defaulted from the Inventory Item card. If no item price is supplied in the import file (the field contains nothing at all), the items will use their default price for this customer using the full Dynamics GP pricing mechanism. If you wish to have an item with zero price, you must specify 0 in the import file.

SOP Import will make use of multicurrency pricing when defaulting an item price For versions of Dynamics GP prior to 6, this requires Nolan Business Solutions’ Advanced SOP to be installed with Multicurrency Item Pricing being used. For Dynamics GP 6 and greater, the standard Inventory module supports multicurrency item pricing.

Stock Handling

When an item line is created, stock is allocated from the Site ID assigned to the line. If the Document ID for this transaction is not set to ‘Use Separate Fulfilment Process’, item lines are also fulfilled.

If serial or lot number tracking is enabled for the item, serial and lot numbers are automatically allocated. Note that for Returns, serial and lot numbers are not automatically allocated.

If you don’t want serial and lot numbers to be assigned automatically, Serial/Lot (SLN) lines can be used in the import file to specify exact serial or lot numbers to use. If there are insufficient numbers given to cover the line’s quantity, an error will be raised. If there are too many numbers given, any excess is ignored. Note that the serial or lot number must already exist in Dynamics GP before using them in a Serial/Lot line.

For Kit items, all components are individually allocated, and their serial or lot numbers allocated as required, just as for a normal item line.

If the import file includes component details (COM lines), these will be added as components on the kit. If the kit item already has default components setup in inventory, component details are not required in the import file.

If there is insufficient stock in the warehouse to fully allocate an item, or insufficient serial or lot numbers, whether for standard items or kit items, the ‘Stock Shortage’ option is used to determine what to do. Any stock shortages will be reported in the error messages window at the end of the import processing.

If any one item line is rejected from a transaction during import, the entire transaction will then be deleted, and any allocated stock and serial/lot numbers will be released.

Note that all stock handling happens after validation of the import file, so if a transaction is rejected at this stage, the rest of the transactions will continue to be imported. Error Handling of ‘Reject whole import file’ has no effect once validation is complete.

Import Auditing

Overview

Three windows are available for auditing the SOP Import. These are the Audit Enquiry, Audit Report and Audit Cleardown windows. These provide information for every import, detailing the files imported, the number of errors, and which items, customers and documents were created or updated.

 

Audit Enquiry Window

Location

Enquiry >> Sales >> SOP Import >> Import Audit Enquiry

Layout

Processing

The enquiry window shows a complete list of all entries in the Import Audit file, showing the Definition ID used, date and time of import, import, save and reject file names, number of lines in the import file, number of errors found and the total value of the imported transactions.

Selecting an import audit line also displays the details for the import. This shows if a document has been created, an item has been created or updated, a customer has been created, and an address has been created or updated.

The Audit Report button provides a quick way of getting to the Audit Report window.

 

Audit Report Window

Location

Reports >> Sales >> SOP Import >> Import Audit Report

Layout

Processing

The Audit Report window allows an audit report to be produced using restrictions to limit the output. Various options may be setup and saved.

The ‘In Detail’ specifies whether import details are printed on the report. Import details are show in the bottom window of the Audit Enquiry window.

The ranges available are Import Definition ID, Date and User ID. Use the From and To fields to enter the range required and then Insert it in to the Restrictions list. Use the Remove button to remove a restriction from the list.

The Destination button is used to specify where to print the report to. Destinations available include the screen, the printer or to a file. Any combination of these can be chosen.

The Delete button is used to delete unwanted report Options.

Audit Cleardown Window

Location

Tools >> Routines >> Sales >> SOP Import >> Import Audit Cleardown

Layout

Processing

The cleardown window allows for the removal of entries in the Import Audit file. A range on the Import Definition ID and the Date of import can be specified. Only entries within these ranges will be cleared.

The Start and End Definition ID must be valid definitions. The lookup buttons will open the standard lookup displaying all existing Definition IDs.

The Cleardown button initiates the cleardown process with whatever ranges are specified on the window.

Appendix A: SOP Import File Format

Overview

The import file consists of comma-separated text lines organised in the following order:

CMP - Company to import to.  Must be the first line of the file.

HDR - Document header in either Version 1.x or 2.0 format.

ADR - Address - supports more address information.

CMT - Comment - supports long comments for the document.

DTL - Item detail - may be several per header.

CMT - Comment - comment details for the item.

TAX - Tax details for the Item. May be several.

NOM - Nominal Account Overrides for Item. May be several.

SLN - Serial/Lot number details for Item. May be several.

COM - Item component detail. Optional. May be several per item.

NOM - Nominal Accounts for Component. May be several.

SLN - Serial/Lot number details for Component. May be several.

RTN - Return QTY- Returns Only. Optional return Amounts.

CMS - Commissions detail – may be several per document.

TAX - Tax details - may be several per document.

NOM - Nominal Accounts for the document. May be several.

PAY - Payment/Deposit Details for the document.

TRA - The documents tracking numbers.

 

Blank lines or lines beginning with a full stop ( ‘.’ ), a semicolon ( ‘;’ ) or a hash ( ‘#’ ) are ignored by the import.

Depending on the Import Definition’s Date Format, all dates should be in one of the following formats:

DD-MM-YY Format: ddmmyy, ddmmyyyy, or dd/mm/yyyy

MM-DD-YY Format: mmddyy, mmddyyyy, or mm/dd/yyyy

Note that dd/mm/yy and mm/dd/yy are not supported date formats.
When a year is given in two digits, the year is assumed to be in the range 1936 to 2035.

Text File Line Definitions

File Header

Field

Name

Size

Comment

1

Record Type

3

CMP

2

Database/Directory

5

Database Name for SQL. See Appendix D

 

Header – Version 1.x

Field

Name

Size

Comment

1

Record Type

3

HDR

2

Customer Number

15

 

3

Document Number

15

 

4

Document Date

Date

 

5

Customer Reference

60

 

6

Comment 1

30

 

7

Comment 2

30

 

8

Address 1

30

 

9

Address 2

30

 

10

Address 3

30

City

11

Address 4

10

State

12

Blank Field

 

Empty field.

13

User ID

15

 

14

Document Type

3

ORD, INV, RTN. Defaults to ORD.

15

Trade Discount Amt.

Number

 

16

Freight Amount

Number

 

17

Misc Amount

Number

 

18

Currency ID

15

 

19

Exchange Rate

Number

Defaults using Document Date

20

Default Site ID

10

 

21

Requested Ship Date

Date

 

22

Shipping Method

15

Defaults if blank

 

Header – Version 2.0

Field

Name

Size

Comment

1

Record Type

3

HDR

2

Customer Number

15

 

3

SOP Type

3

ORD, INV, RTN, QTE Defaults to ORD.

4

SOP Number

15

 

5

Document ID

15

If present, SOP Number above is ignored. Otherwise, defaults based on SOP Type.

6

Document Date

Date

 

7

Customer PO Number

20

 

8

Comment 1

50

 

9

Comment 2

50

 

10

Address 1

30

 

11

Address 2

30

 

12

City

30

 

13

State

30

 

14

Zip

10

 

15

Country

20

 

16

Ship to Address Code

15

If present, above address is ignored

17

User ID

15

 

18

Trade Discount Amt.

Number

 

19

Freight Amount

Number

 

20

Misc Amount

Number

 

21

Deposit/Amount Received

Number

 

22

Currency ID

15

Defaults to functional currency

23

Exchange Rate

Number

Defaults using Document Date

24

User Defined Table 1

20

 

25

User Defined Table 2

20

 

26

User Defined Table 3

20

 

27

User Defined Date 1

Date

 

28

User Defined Date 2

Date

 

29

User Defined 1

20

 

30

User Defined 2

20

 

31

User Defined 3

20

 

32

User Defined 4

20

 

33

User Defined 5

20

 

34

Default Site ID

10

 

35

Requested Ship Date

Date

 

36

Comment ID

15

If present, Comment 1 and Comment 2 are ignored.

37

Shipping Method

15

Defaults if blank

38

Due Date

Date

If not present is calculated by Dynamics GP.

39

Batch Number

15

Defaults from the Import Definition ID.

40

Bill to Address Code

15

 

41

Trade Discount Percent

Number

Defaults from Customer Discount.

42

Default Price Level

10

 

43

Salesperson ID

15

 

44

Sales Territory ID

15

 

45

Commission Percent

Number

 

46

Commission Amount

Number

 

47

Percent of Sale

Number

 

48

Commission Sales Amount

Number

 

49

Tax Schedule ID

15

 

50

Contact Person

30

 

51

Ship to Company Name

30

 

52

Address 3

30

 

53

Payment Terms ID

20

 

54

Debtor Comment1

30

Gets copied to Comment1 of the customer master record

55

Email Recipient

80

Gets added to email To Address of the customer email record.

56

Suppress Trade Discount Defaulting

1

1 = True, 0 = False

57

UPS Zone

3

 

58

Tax Exempt 1

25

 

59

Tax Exempt 1

25

 

60

Import To History

1

1 = True, 0 = False

61

Location ANA Code

13

Used by EDI to get customer code from NC_Customer_Location_Ref table

62

NC Location Code

17

Used by EDI to get customer code from NC_Customer_Location_Ref table

63

Country Code

6

 


Addresses

Field

Name

Size

Comment

1

Record Type

3

ADR

2

Customer Number

15

Used in dedicated customer imports only. Otherwise it is ignored.

3

Customer Class ID

15

Customer create only.

4

Customer Name

30

Customer create only.

5

Short Name

15

Customer create only. Defaults to Customer Name if blank.

6

Statement Name

30

Customer create only.

7

User Defined Field 1

20

Customer create only.

8

User Defined Field 2

20

Customer create only.

9

Bill to Address Code

15

Defaults from HDR(40) if blank. Defaults to ‘BILLING’ in customer create if blank.

10

Bill to Address 1

30

Address create/update only.

11

Bill to Address 2

30

Address create/update only.

12

Bill to Address 3

30

Address create/update only.

13

Bill to City

30

Address create/update only.

14

Bill to State

30

Address create/update only.

15

Bill to Postal Code

10

Address create/update only.

16

Bill to Country

20

Address create/update only.

17

Bill to Phone 1

14

Address create/update only.

18

Bill to Phone 2

14

Address create/update only.

19

Bill to Fax Number

14

Address create/update only.

20

Bill to Contact Name

30

Address create/update only.

21

Ship to Address Code

15

Defaults from HDR(16) if blank. Defaults to ‘SHIPPING’ in customer create if blank.

22

Ship to Name

30

Defaults from Customer’s name if blank.

23

Ship to Address 1

30

Ship to addresses overrides address fields on HDR.

24

Ship to Address 2

30

 

25

Ship to Address 3

30

 

26

Ship to City

30

 

27

Ship to State

30

 

28

Ship to Postal Code

10

 

29

Ship to Country

20

 

30

Ship to Phone 1

14

 

31

Ship to Phone 2

14

 

32

Ship to Fax Number

14

 

33

Ship to Contact Name

30

 

34

Salesperson ID

15

 

35

Sales Territory ID

15

 

36

Ship To UPS Zone

3

 

37

Tax Exempt 1

25

 

38

Tax Exempt 2

25

 

39

Bill To Country Code

6

 

40

Ship To Country Code

6

 

Address create/update only – These fields are ignored when creating a sales document.

Customer create only – These fields are ignored when not creating new customers.

Detail

Field

Name

Size

Comment

1

Record Type

3

DTL

2

Item Number

30

 

3

Quantity

Number

Document line only.

4

Unit Price

Number

Document line only. Defaults if blank.

5

Item Description

100

Defaults if blank.

6

Unit Markdown Amount

Number

Document line only.

7

Shipping Method

15

Document line only. Defaults to HDR(37) if blank

8

Site ID

10

Defaults from HDR(34) if blank.

9

Unit of Measure

8

Document line only. Defaults if blank.

10

Item Class ID

15

Create/update only.

11

Standard Cost

Number

Create/update only.

12

Current Cost

Number

Create/update only.

13

List Price

Number

Create/update only.

14

Item Short Description

15

Create/update only.

15

Generic Description

10

Create/update only.

16

Price Schedule

10

Create/update only. Defaults from Item Class if blank.

17

Default Price Level

15

Create/update only. Defaults from Item Class if blank.

18

Item User Category 1

10

Create/update only. Defaults from Item Class if blank.

19

Item User Category 2

10

(as above)

20

Item User Category 3

10

(as above)

21

Item User Category 4

10

(as above)

22

Item User Category 5

10

(as above)

23

Item User Category 6

10

(as above)




24

Alternate Item 1

30

Create/update only.

25

Alternate Item 2

30

Create/update only.

26

Template Price List Item

30

Create/update only. Dynamics GP 6+ only.

27

Requested Ship Date

Date

Defaults if blank. Document line only.

28

Fulfilment Date

Date

Defaults if blank. Document line only.

29

Actual Ship Date

Date

Defaults if blank. Document line only.

30

Markdown Percent

Number

Used if Markdown Amount  is blank.

31

Drop Ship Flag

Number

 0 = Normal; 1 = Drop Ship

32

Unit Cost

Number

Defaults if blank. Drop Ship Only

Document line only – These fields are ignored when creating or updating items.

Create/update only – These fields are ignored when creating item lines on a document.

 

Component – Only needed for kits with no default components.

Field

Name

Size

Comment

1

Record Type

3

COM

2

Item Number

30

 

3

Component Item No.

30

 

4

Quantity

Number

Actual QTY, not per parent QTY

5

Price

Number

Not supported.

Nominal – Used to override account numbers on existing distribution lines.

Field

Name

Size

Comment

1

Record Type

3

NOM

2

Account Number

30

 

3

Distribution Type

8

*

4

Distribution Amount

Number

Not used.

5

Item Number

30

if related to a specific item

6

Component Item No.

30

if related to a specific component

* SALES, RECV, TAKEN, AVAIL, TRADE, FREIGHT, MISC, TAXES, MARK, COMMEXP, COMMPAY, OTHER, COGS, INV, DEPOSITS, CASH, RETURNS, IN USE, IN SERVICE, DAMAGED, UNIT.
Note: Only SALES, COGS and INV apply to Item and Component lines.

Tax

Field

Name

Size

Comment

1

Record Type

3

TAX

2

Tax Detail ID

15

 

3

Taxable Amount

Number

 

4

Tax Amount

Number

 

5

Item Number

30

If related to a specific DTL line, Item Number of the DTL line.

Serial/Lot Number

Field

Name

Size

Comment

1

Record Type

3

SLN

2

Item Number

30

Item Number of the associated DTL line.

3

Component Item Number

30

Component Item Number of the associated COM line.

4

Serial/Lot Number

20

Serial Number or Lot Number, depending on what the item tracks.

5

Lot Quantity

Number

Ignored. Reserved for future expansion.

6

Lot Attribute 1

10

Ignored. Reserved for future expansion.

7

Lot Attribute 2

10

Ignored. Reserved for future expansion.

8

Lot Attribute 3

10

Ignored. Reserved for future expansion.

9

Lot Date 1

Date

Ignored. Reserved for future expansion.

10

Lot Date 2

Date

Ignored. Reserved for future expansion.

 

Payments

Field

Name

Size

Comment

1

Record Type

3

PAY

2

Payment Type

4

CASH, CHK or CARD

3

Payment Amount

Number

 

4

Chequebook ID or

Credit Card Name

15

Chequebook ID for CASH and CHK payments. Card Name for CARD payments.

5

Cheque / Card Number

20

 

6

Authorization Code

15

 

7

Expiration Date

Date

 

8

Payment Doc. Number

15

Currently not supported

9

Cash Account

Acc. Number.

Applies only to Orders

10

Deposits Account

Acc. Number

Applies only to Orders

Return Quantities

Field

Name

Size

Comment

1

Record Type

3

RTN

2

Item Number

30

 

3

QTY On Hand

Number

 

4

QTY Returned

Number

 

5

QTY In Use

Number

 

6

QTY In Service

Number

 

7

QTY Damaged

Number

 

Comment - if present for header, all comment information in the HDR is ignored.

Field

Name

Size

Comment

1

Record Type

3

CMT

2

Item Number

30

If related to a specific item

3

Comment

200

 

4

Comment ID

15

If present, Comment is ignored

Commissions

Field

Name

Size

Comment

1

Record Type

3

CMS

2

Salesperson ID

15

 

3

Sales Territory

15

 

4

Commission Percent

Number

 

5

Commission Amount

Number

 

6

Percent of Sales

Number

 

7

Commission Sales Amount

Number

 

Tracking Numbers

Field

Name

Size

Comment

1

Record Type

3

TRA

2

Tracking Number

40

Tracking number

Example Files

Example 1, using Header version 2.0:

HDR,AARONFIT0001,INV,,STDINV,15011999,,,,,,,,,,,LESSONUSER1,,,,,Z-UK,0.61,,,,31011999

CMT,,Confirm with accounts before processing,

DTL,ACCS-CRD-12WH,1,5.00

; specify a tax detail id

TAX,UKVAT-PS175N0,5.00,0.88

; specify the main accounts

NOM,100610000,SALES,-5.00,,

NOM,200510000,TAXES,-0.88,,

NOM,200650000,RECV,5.00,,

; make a payment too

PAY,CHK,5.88,FIRST NATIONAL,100623,,,,,

 

Example 2, using Header version 2.0:

HDR,AARONFIT0001,INV,,STDINV,15011999,,,,,,,,,,,LESSONUSER1

; import a kit item

DTL,HDWR-DCD-0001,1,

; specify INV and COGS accounts for components

NOM,000110000,INV,,HDWR-DCD-0001,ITCT-CIR-CD85

NOM,000210000,COGS,,HDWR-DCD-0001,ITCT-CIR-CD85

NOM,000110000,INV,,HDWR-DCD-0001,RESR-TRR-68KM

NOM,000210000,COGS,,HDWR-DCD-0001,RESR-TRR-68KM

NOM,000110000,INV,,HDWR-DCD-0001,RMTL-CAP-10MF

NOM,000210000,COGS,,HDWR-DCD-0001,RMTL-CAP-10MF

NOM,000110000,INV,,HDWR-DCD-0001,TRAN-STR-N394

NOM,000210000,COGS,,HDWR-DCD-0001,TRAN-STR-N394

; specify the main accounts

NOM,100610000,SALES,,,

NOM,200510000,TAXES,,,

NOM,200650000,RECV,,,

 

Appendix B: Dynamics GP Menu Entries

As part of the install procedure for Sales Order Import, the following items are added to the Dynamics GP menus:

Cards, Sales, SOP Import

           Import Definitions               - Import Definitions Maintenance window

Enquiry, Sales, SOP Import

           Import Audit Enquiry          - SOP Import Audit Enquiry window

Tools, Routines, Sales, SOP Import

           Import Processing               - Import Processing window

           Import Audit Cleardown     - SOP Import Audit Cleardown window

Reports, Sales, SOP Import

           Import Audit Report            - SOP Import Audit Reports window

Appendix C: Third Party Integration

It is possible to run an SOP import from code in another third-party application for Microsoft Business Solutions Dynamics GP. The following script has been made available to provide a consistent interface to SOP Import:

{

  RunImport of form NC_SOPImport_API

    

  Runs SOP Import with the given Import Definition ID. If the filename is provided,

  it overrides the import filename from the definition.

  If the import is run successfully, the SOP Number from the documents created are

  returned in a comma separated list. If no documents are created successfully, the

  field will be empty.

    

  Status values:

     0:     Import was run. Check SOP Numbers for the result.

     1:     Registration Keys incorrect.

     2:     Import Definition does not exist.

}

 

in       'Import Definition ID'        I_sImportDef;

in       string                        I_sFilename;

inout    text                          IO_txSOPNumber;

out     integer                       O_nStatus;

To run this script, the following piece of code could be used:

call with name "RunImport of form NC_SOPImport_API" in dictionary 2418,

     ImportDefinitionID,

     Filename,

     SOPNumbers,

     Status;

 

Appendix D: Multi-Company Imports

It is now possible to import to companies over that the company the import is currently logged into.  The CMP record allows the import to switch to another company, this MUST the first record in an import file and can only appear once.  This will switch the import to the specified company for the rest of that import file.

If the CMP record is not specified the import will run as normal (Importing to the company which is currently logged into).

Appendix E: SQL Interface Tables

The SOP Import module from Nolan Business Solutions provides SQL Interface tables which may be used on SQL installations instead of using the CSV text files.

This appendix provides details of the SQL Interface tables, and how they should be used to import data to Dynamics GP.

Multiple SQL Interface tables are used by the SOP Import module, to represent the different record types utilised by the CSV text file import.  The list provided below indicated the relationship between the SQL Interface tables and the record types:

Interface Table

Record Type

Type Id

Content

NCSIM400

HDR

1

Transaction Header

NCSIM400

DTL

2

Line Item Detail

NCSIM401

CMT

8

Comment Text

NCSIM402

COM

4

Line Item Components

NCSIM403

NOM

3

Nominal Accounts

NCSIM404

PAY

6

Payment/Deposit Info

NCSIM405

RTN

7

Returned Qty Breakdown

NCSIM406

TAX

5

Tax Breakdown

NCSIM407

ADR

10

Address Details

NCSIM408

SLN

9

Serial/Lot Number Info

NCSIM409

CMS

11

Commissions Breakdown

NCSIM410

ING

13

Integration Reference

NCSIM411

NOT

12

Record Note Text

NCSIM413

ISA

16

Line Item Shipping Address

NCSIM414

TRA

17

Tracking Number

 

Each of the SQL Integration tables contains four mandatory columns which form the primary index for the table.  These fields are:

           NC_Reference_ID
           NC_Reference_Sequence
           CMRECNUM
           RCRDTYPE

NC_Reference_ID should contain a unique (external) reference number for each group of documents to be processed.  For example, a CSV interface file could contain three orders – by inserting the corresponding data into the SQL Interface tables with the same NC_Reference_ID for each, the three orders are still logically grouped together.  You may use a unique NC_Reference_ID for every transaction if you wish, but all the records for a single transaction must share the same NC_Reference_ID.

NC_Reference_Sequence is the logical position of the table row within the collection of records sharing a single NC_Reference_ID.  This is used to mimic the behaviour in the CSV file interface, where the relative position of the records affects how the lines are processed.  Each record within a single NC_Reference_ID must have a unique NC_Reference_Sequence.

CMRECNUM is the position of the table row within the current document.  When using a distinct reference number (NC_Reference_ID) for each transaction, CMRECNUM will match the NC_Reference_Sequence.

RCRDTYPE indicates the type of data being provided, as per the ‘Type Id’ values given in the list on the previous page.

For example, to import three simple orders (single item line each), under a single NC_Reference_ID, the following values could be used:

    NC_Reference_ID      NC_Reference_Sequence    CMRECNUM       RCRDTYPE
    EXAMPLE001              1                                                1                          1
    EXAMPLE001              2                                                2                          2
    EXAMPLE001              3                                                1                          1
    EXAMPLE001              4                                                2                          2
    EXAMPLE001              5                                                1                          1
    EXAMPLE001              6                                                2                          2

Notice how the CMRECNUM counter resets with each new document.

When populating the SQL Interface tables, the NCSIM400 table is the main table which controls the import processing.  Whilst multiple interface tables have been provided, to hold the necessary field for each of the different record types, every record must also be represented by a row in the NCSIM400 table.  This allows the import process to read through the NCSIM400 table in sequence, and reference additional tables only as required by the indicated record types.

For example, to import a single order with three line items, comments against both the order and the second line item, and a deposit amount, the following table rows should be created:

NCSIM400, Sequence

Record Type

Additional Table

1

1 (HDR)

None

2

8 (CMT)

NCSIM401

3

2 (DTL)

None

4

2 (DTL)

None

5

8 (CMT)

NCSIM401

6

2 (DTL)

None

7

6 (PAY)

NCSIM404

 

In addition to indicating the record types (and thus the additional tables required), the NCSIM400 table also holds the integration status (NC_Status column).  Status values should be used as follows:

Value

Indicated Status

Comments

1

Building Document

Use a status of 1 whilst inserting records to the SQL Interface tables, to ensure processing does not begin until all records are created.

2

Ready to Import

Update records to a status of 2 when they are ready for processing.

3

Importing

SOP Import will update the status to 3 when it is processing the records.

4

Integration Successful

SOP Import has completed processing these records and they integrated successfully.

5

Integration Failed

SOP Import encountered an error during validation or processing of the document and the transaction was rejected.

 

Interface Table Definitions

By default, the SQL Interface tables do not have any bound defaults, and do not allow NULL values.  This means that a value must be provided for every column when populating the tables.  To simplify the use of the interface tables, you can use the NCsmBindTableDefaults stored procedure to bind Dynamics GP defaults to all the columns in the NCSIM series of tables.  This stored procedure is not created automatically, but must be loaded from the NC_BindDefaults.sql script file (installed to the SQL folder within the Dynamics GP client folder) into each company database using the interface tables.  Use the following SQL statements to execute the stored procedure once it has been created:

declare @errState int

exec NCsmBindTableDefaults 0, @errState output

 

Headers (HDR – 1 – NCSIM400)

When writing a CSV, the first line of an imported order will be the HDR line.  Similarly when doing an import from SQL tables, the first record in the your record sequence should always be the HDR record.  The HDR record will be inserted into the NCSIM400 table; please note however, that not all fields within this table are used for the HDR record as the NCSIM400 table stores both HDR and DTL records.  The field mappings for the HDR record within the NCSIM400 table are as follows.  (Fields marked with * are required.)

 

SQL Field Name

Field Description

NC_Reference_ID *

Reference ID

NC_Reference_Sequence *

Reference Sequence

NC_Status *

Status

USERID

User ID

CMRECNUM

Record Number

RCRDTYPE *

Record Type

CUSTNMBR *

Customer Number

SOPTYPE

SOP Type

SOPNUMBE

SOP Number

LNITMSEQ

Line Item Sequence

CMPNTSEQ

Component Sequence

BACHNUMB

Batch Number

DOCID

Document ID

DOCDATE

Document Date

CSTPONBR

Customer PO Number

NCCMMNT1

NC Comment 1

NCCMMNT2

NC Comment 2

ADDRESS1

Address Line 1                                                                                                                         

ADDRESS2

Address Line 2                                                                                                                         

CITY

City

STATE

State / County

ZIP

Zip Code / Post Code

COUNTRY

Country

PRSTADCD

Primary Ship to Address Code

PRBTADCD

Primary Bill to Address Code

USER2ENT

User to Enter

TRDISAMT

Trade Discount Amount

FRTAMNT

Freight Amount

MISCAMNT

Misc Amount

DEPRECVD

Deposit Received

CURNCYID

Currency ID

XCHGRATE

Exchange Rate

USRTAB01

User Defined Table 1

USRTAB09

User Defined Table 2

USRTAB03

User Defined Table 3

USRDAT01

User Defined Date 1

USRDAT02

User Defined Date 2

USERDEF1

User Defined 1

USERDEF2

User Defined 2

USRDEF03

User Defined 3

USRDEF04

User Defined 4

USRDEF05

User Defined 5

ReqShipDate

Requested Ship Date

SHIPMTHD

Shipping Method

DUEDATE

Due Date

TRDISPCT

Trade Discount Percent

SLPRSNID

Salesperson ID

SALSTERR

Sales Territory

NC_Commission_Percent_St

Commission Percent

COMMAMNT

Commission Amount

NC_Percent_of_Sale_Strin

Percent of Sale

CMMSLAMT

Commission Sale Amount

TAXSCHID

Tax Schedule ID

CNTCPRSN

Contact Person

NC_Ship_To_Company_Name

NC Ship to Company Name

ADDRESS3

Address 3

PYMTRMID

Payment Terms ID

COMMENT1

Comment1 (Comment 1 on Customer)

Email_Recipient

Email Recipient (To Email address on Cust)

NCSUPPTD

Suppress Trade Discount Defaulting

UPSZONE

UPS Zone

TAXEXMT1

Tax Exempt 1

TAXEXMT2

Tax Exempt 2

NC_Import_Hist

Import To History?

NC_Location_ANA_Code

Location ANA Code – Used by EDI

NC_Location_Code

NC Location Code - Used by EDI

NC_Product_DUN_Code

Product DUN Code – Used by EDI

NC_Product_EAN_Code

Product EAN Code – Used by EDI

NC_Partner_Product_Code

Partner Product Code

VENDORID

Vendor ID

VNDITNUM

Vendor Item Number

CONTLNSEQNBR

Contract Line Sequence Number

XTNDPRCE

Extended Price

CCode

Country Code

Record_Text

Record Text

 

 So, for example, you would insert the following information into the NCSIM400 table to represent your HDR record (we are going to use REF00001 as our reference id)

 

NC_Reference_ID           = ‘REF00001’

NC_Reference_Sequence             = 1

NC_Status                        = 2

RCRDTYPE                        = 1 (HDR = 1)

 

As well as these fields you must also insert customer number as this is also required for the import.  And, obviously, all the header fields you are importing for the order.

 

Detail lines (DTL – 2 – NCSIM400)

 

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

NC_Status

Status

USERID

User ID

CMRECNUM

Record Number

RCRDTYPE

Record Type

ReqShipDate

Requested Ship Date

ITEMNMBR

Item Number

UOFM

U of M

QUANTITY

Quantity

UNITPRCE

Unit Price

ITEMDESC

Item Description

MRKDNAMT

Markdown Amount

NCUSEDPR

NC Use Default Price

LOCNCODE

Location Code

NC_Item_Shipping_Method

Item Shipping Method

CLASSID

Class ID

STNDCOST

Standard Cost

CURRCOST

Current Cost

LISTPRCE

List Price

ITMSHNAM

Item Short Name

ITMGEDSC

Item Generic Description

PriceGroup

Price Group

PRICELVL

Price Level

USCATVLS_1

User Category Values[1]

USCATVLS_2

User Category Values[2]

USCATVLS_3

User Category Values[3]

USCATVLS_4

User Category Values[4]

USCATVLS_5

User Category Values[5]

USCATVLS_6

User Category Values[6]

NC_Template_Price_List_I

Template Price List Item

ACTLSHIP

Actual Ship Date

FUFILDAT

Fulfillment Date

MRKDNPCT

Markdown Percent

DROPSHIP

Drop Ship

UNITCOST

Unit Cost

NC_Empty_QTY

NC Empty Quantity

TAXSCHID

Tax Schedule ID

Record_Text

Record Text

 

So if we wanted to add a line to our header record we would insert the following key fields into NCSIM400, in addition to all the line fields we wished to import.

 

NC_Reference_ID           = ‘REF00001’

NC_Reference_Sequence             = 2 (second line of the import)

NC_Status                        = 2

RCRDTYPE                        = 2 (DTL)

 

Comments (CMT – 8 – NCSIM401)

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

ITEMNMBR

Item Number

COMMENT_1 – 4

Comment

COMMNTID

Comment ID

CMMTTEXT

Comment Text

 

Components (COM – 4 – NCSIM402

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

CMPTITNM               

Component Item Number

QUANTITY               

Quantity

UNITPRCE               

Unit Price

 

Nominal Info (NOM – 3 – NCSIM403)

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

ACTNUMBR_n

Individual segments of the account number

DISTTYPE

Distribution Type

DISTAMNT

Distribution Amount

ITEMNMBR

Item Number

CMPTITNM

Component Item Number

 

Nominal lines are used to override account numbers on existing distribution lines, so for example, we could add one against the order we are importing.  Because we nominals (as well as all the other record types above 2) are not kept in the NCSIM400 table, we will need to write to both tables, so first we would insert the following key fields into NCSIM403

 

NC_Reference_ID           = ‘REF00001’

NC_Reference_Sequence             = 3

RCRDTYPE                        = 3 (NOM)

 

And then we would need to insert a corresponding line in the NCSIM400 table:

 

NC_Reference_ID           = ‘REF00001’

NC_Reference_Sequence             = 3 (third line of the import)

NC_Status                        = 2

RCRDTYPE                        = 3 (NOM)

 

Note that the key fields are the same for both tables; you need the line in NCSIM400 table so that the system knows where to look.  The other types follow this same basic principle of writing to both tables.

 

Payments (PAY – 6 – NCSIM404)

 

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

PYMTTYPE

Payment Type

AMNTPAID

Amount Paid

CHEKBKID

Chequebook/Checkbook ID

CHEKNMBR

Cheque/Check Number

AUTHCODE

Authorisation code

EXPNDATE

Expiration Date

DOCNUMBR

Document Number

CASHINDEX

Cash Index

DEPINDEX

Deposits Index

 

Return Quantities (RTN – 7 – NCSIM405)

 

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

ITEMNMBR

Item Number

QTYONHND 

Quantity on Hand

QTYRTRND

Quantity Returned

QTYINUSE

Quantity in Use

QTYINSVC

Quantity in Service

QTYDMGED

Quantity Damaged

 

Tax (TAX – 5 – NCSIM406)

 

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

TAXDTLID

Tax Detail ID

TDTTXSLS  

Total Taxable Sales

TXDTLAMT

Tax Detail Amount

ITEMNMBR

Item Number

 

Address Info (ADR – 10 – NCSIM407)

 

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

CUSTNMBR               

Customer Number                                                                                                                 

CUSTCLAS               

Customer Class                                                                                                                  

CUSTNAME               

Customer Name                                                                                                                    

SHRTNAME               

Short Name                                                                                                                      

STMTNAME               

Statement Name                                                                                                                   

USERDEF1               

User Defined 1                                                                                                                       

USERDEF2               

User Defined 2                                                                                                                        

PRBTADCD               

Bill to Address Code                                                                                                              

ADDRESS1               

Address 1                                                                                                                        

ADDRESS2               

Address 2                                                                                                                       

ADDRESS3               

Address 3                                                                                                                        

CITY                   

City                                                                                                                           

STATE                  

State                                                                                                                           

ZIP                    

Zip                                                                                                                            

COUNTRY                

Country                                                                                                                        

PHNUMBR1               

Phone Number 1                                                                                                                    

PHNUMBR2               

Phone Number 2                                                                                                                   

FAXNUMBR               

Fax Number                                                                                                                       

CNTCPRSN               

Contact Person                                                                                                                  

PRSTADCD               

Ship to Address Code                                                                                                               

ShipToName             

Ship To Name                                                                                                                     

NC_Ship_Address_1       

Ship Address 1                                                                                                                   

NC_Ship_Address_2      

Ship Address 2                                                                                                                   

NC_Ship_Address_3      

Ship Address 3                                                                                                                   

NC_Ship_City           

Ship City                                                                                                                        

NC_Ship_State          

Ship State                                                                                                                      

NC_Ship_Zip            

Ship Zip                                                                                                                         

NC_Ship_Country        

Ship Country                                                                                                                    

NC_Ship_Phone_1        

Ship Phone 1                                                                                                                     

NC_Ship_Phone_2        

Ship Phone 2                                                                                                                     

NC_Ship_Fax            

Ship Fax                                                                                                                         

NC_Ship_Contact_Person 

Ship Contact Person                                                                                                              

SLPRSNID               

Salesperson ID                                                                                                                   

SALSTERR               

Sales Territory                                                                                                                 

NC_Ship_UPS_Zone

Ship UPS Zone

NC_Ship_Tax_Exempt_1

Ship Tax Exempt 1

NC_Ship_Tax_Exempt_2

Ship Tax Exempt 2

CCode

Bill Country Code

NCSHPCCD

Ship Country Code

 

Serial/Lot Numbers (SLN – 9 – NCSIM408)

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

ITEMNMBR

Item Number

CMPTITNM

Component Item Number

SERLTNUM

Serial/Lot Number

SERLTQTY

Serial/Lot Qty

LOTATRB1

Lot Attribute 1

LOTATRB2

Lot Attribute 2

LOTATRB3

Lot Attribute 3

LOTATRB4

Lot Attribute 4

LOTATRB5

Lot Attribute 5

 

Commissions (CMS – 11 – NCSIM409)

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

SLPRSNID               

Salesperson ID                                                                                                                  

SALSTERR               

Sales Territory                                                                                                                 

NC_Commission_Percent_St

Commission Percent                                                                                                              

COMMAMNT               

Commission Amount                                                                                                                

NC_Percent_of_Sale_Strin

Percent of Sale                                                                                                                  

CMMSLAMT               

Commission Sale Amount                                                                                                            

 

 

Integration Reference (ING – 13 – NCSIM410)

 

This is a special purpose Interface table used to assist the linking of the integrated SOP documents with the original data in the source system.  If the document being processed includes an Integration Reference record, the import process will insert a record in the NCSIM006 table in the DYNAMICS database once the document has been processed (whether integration succeeds or fails).  The NCSIM006 table may then be read to determine what happened to data submitted for integration.  On success, the NC_Import_Key column in NCSIM006 will contain the Dynamics GP SOP Document Number for the document created.  On failure, the STRGA255 column will contain the error messages indicating why integration failed.

 

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

NC_Integration_ID

External Integration Reference

 

Notes (NOT – 12 – NCSIM411)

 

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

TXTFIELD               

Record Note Text

 

Item Shipping Address (ISA – 16 – NCSIM413)

 

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

ITEMNMBR               

Item Number                                                                                                                     

PRSTADCD                

Ship to Address Code                                                                                                              

ShipToName             

Ship To Name                                                                                                                      

CNTCPRSN               

Contact Person                                                                                                                  

ADDRESS1               

Address1                                                                                                                        

ADDRESS2               

Address2                                                                                                                       

ADDRESS3               

Address3                                                                                                                        

CITY                   

City                                                                                                                           

STATE                  

State                                                                                                                          

ZIPCODE                

Zip                                                                                                                             

COUNTRY                

Country                                                                                                                        

PHONE1                 

Phone1                                                                                                                          

PHONE2                 

Phone2                                                                                                                         

PHONE3                 

Phone3                                                                                                                          

FAXNUMBR               

Fax Number                                                                                                                      

CCode

Country Code

 

Tracking Numbers (TRA – 17 – NCSIM414)

 

SQL Field Name

Field Description

NC_Reference_ID

Reference ID

NC_Reference_Sequence

Reference Sequence

CMRECNUM

Record Number

RCRDTYPE

Record Type

Tracking_Number               

Sop Tracking Number

 

 

Integration Reference (VEN – 18 – NCSIM416)

This is a special purpose Interface table used to define IDS specific fields. At present the preferred vendor ID only is used.

 

NC_Reference_ID

char(31)

Unchecked

NC_Reference_Sequence

int

Unchecked

CMRECNUM

numeric(19, 5)

Unchecked

RCRDTYPE

smallint

Unchecked

ITEMNMBR

char(31)

Unchecked

VENDORID

char(15)

Unchecked

NCSPBOOL_1

tinyint

Unchecked

NCSPBOOL_2

tinyint

Unchecked

NCSPBOOL_3

tinyint

Unchecked

NCSPBOOL_4

tinyint

Unchecked

NCSPBOOL_5

tinyint

Unchecked

NCSPBOOL_6

tinyint

Unchecked

NCSPBOOL_7

tinyint

Unchecked

NCSPBOOL_8

tinyint

Unchecked

NCSPBOOL_9

tinyint

Unchecked

NCSPBOOL_10

tinyint

Unchecked

NCSPCURR_1

numeric(19, 5)

Unchecked

NCSPCURR_2

numeric(19, 5)

Unchecked

NCSPCURR_3

numeric(19, 5)

Unchecked

NCSPCURR_4

numeric(19, 5)

Unchecked

NCSPCURR_5

numeric(19, 5)

Unchecked

NCSPDATE_1

datetime

Unchecked

NCSPDATE_2

datetime

Unchecked

NCSPDATE_3

datetime

Unchecked

NCSPDATE_4

datetime

Unchecked

NCSPDATE_5

datetime

Unchecked

NCSPDATE_6

datetime

Unchecked

NCSPDATE_7

datetime

Unchecked

NCSPDATE_8

datetime

Unchecked

NCSPDATE_9

datetime

Unchecked

NCSPDATE_10

datetime

Unchecked

NC_SpareInts_1

smallint

Unchecked

NC_SpareInts_2

smallint

Unchecked

NC_SpareInts_3

smallint

Unchecked

NC_SpareInts_4

smallint

Unchecked

NC_SpareInts_5

smallint

Unchecked

NC_SpareInts_6

smallint

Unchecked

NC_SpareInts_7

smallint

Unchecked

NC_SpareInts_8

smallint

Unchecked

NC_SpareInts_9

smallint

Unchecked

NC_SpareInts_10

smallint

Unchecked

NC_SpareStrings_1

char(255)

Unchecked

NC_SpareStrings_2

char(255)

Unchecked

NC_SpareStrings_3

char(255)

Unchecked

NC_SpareStrings_4

char(255)

Unchecked

NC_SpareStrings_5

char(255)

Unchecked

Field Type Definitions

The following fields are imported as strings within normal (csv) SOP Import but are stored as numeric values within the actual SQL tables; mappings between the two values for the needed fields are provided here.

 

SOP Type                                                                   Payment Type (PAY)

SOP Import value

SQL value

 

SOP Import value

SQL value

QTE

1

 

CASH

1

ORD

2

 

CHK

2

INV

3

 

CARD

3

RTN

4

 

 

 

 

Distribution Type (NOM records)

SOP Import value

SQL value

 

SOP Import value

SQL value

SALES

1

 

COMMPAY

12

RECV

2

 

OTHER

13

CASH

3

 

COGS

14

TAKEN

4

 

INV

15

AVAIL

5

 

RETURNS

16

TRADE

6

 

IN USE

17

FRIEGHT

7

 

IN SERVICE

18

MISC

8

 

DAMAGED

19

TAXES

9

 

UNIT

20

MARK

10

 

DEPOSITS

21

COMMEXP

11

 

 

 

 

 

Appendix F: Document Statistics

 

Document Owner:                         Peter Boniface

File Location:                                   Sales Order Processing Import.docx

Current Version Date:                    25 May 2016

 

Revision Summary:

Date

Author of Revision

Summary

May 2010

Peter Boniface

Updates for Dynamics GP 2010

 

 

 

 

We use cookies to enhance your online browsing experience. By continuing to use this site, you agree to accept our use of cookies. Read our Privacy Statement to find out more.

var _glc =_glc || []; _glc.push('all_ag9zfmNsaWNrZGVza2NoYXRyDwsSBXVzZXJzGMTP7pYDDA'); var glcpath = (('https:' == document.location.protocol) ? 'https://my.clickdesk.com/clickdesk-ui/browser/' : 'http://my.clickdesk.com/clickdesk-ui/browser/'); var glcp = (('https:' == document.location.protocol) ? 'https://' : 'http://'); var glcspt = document.createElement('script'); glcspt.type = 'text/javascript'; glcspt.async = true; glcspt.src = glcpath + 'livechat-new.js'; var s = document.getElementsByTagName('script')[0];s.parentNode.insertBefore(glcspt, s);