Difference between revisions of "Project Import Guide"

From Adjutant Wiki

(Created page with " ==General Import Template File Notes== *Template files are typically generated from their respective Import screen using an 'Export Template' button. *Using the 'Export Temp...")
 
 
(8 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 +
__NOTOC__
 +
===General Information===
 +
''''''Menu Location''': Transaction>>Importers/Exporters>>Projects>>Project Import
  
−
==General Import Template File Notes==
+
'''URL''':importmaster_S.htm?mode=PROJECTIMPORT
  
−
*Template files are typically generated from their respective Import screen using an 'Export Template' button.
 
−
*Using the 'Export Template' button from an updated system will help ensure that you are using the most up-to-date import file, and will help avoid errors.
 
−
*Templates generally (but not in all cases) contain 3 rows of header information.
 
−
**The first row is generally labelled with f1 through f## and is used to give a consistent column id to each data column. Do not remove this row unless instructed.
 
−
**The second row is labelled with the column data/field name. The values in this row are used for the file mapping screen. Editing these values is generally not needed and may make the field mapping process difficult.
 
−
**The third row (if present) is labelled either with the same values from row 2, or with more detailed field descriptions.  The third row is available to create a customer-friendly name for the associated data column. 
 
−
***The third row should be removed from the source file prior to importing.  It will cause errors since it will be treated as the first import record on the file.
 
−
*Import files MUST always be saved as 'Microsoft Excel 5.0/95 Workbook (*.xls)' format until further notice. The import screens will generate errors if the file type is not correct.
 
  
−
*It is a good idea to perform a Mapping Table Import using a current source list before beginning on any imports.  Without a current mapping table, the quality of imports can be compromised. The Mapping Table Import only needs to be run once on a newly set up Adjutant system.  It does not need to be run before each import. 
+
==General Data Import Notes==
−
**Export a current list from the Mapping Table Import screen from an updated system (or contact your Implementation Consultant for an updated file).
 
−
**Perform a Mapping Table Import on the target system with the XLS file from the step above.
 
  
 +
Refer to the [[Data Import General Information]] page for information and guidance on general import timeline, formatting import templates, as well as how to ensure you are using the most current template information for data imports.
  
−
==Sales Order Import Notes==
 
  
−
===Timing and Preparation===
 
  
−
The Sales Order Import should occur after all customers have been imported, all Item Master records have been imported, and after account managers and salespersons have been established.
+
==Project Import Notes==
  
−
The Sales Order Import is generally used to import only the open sales order records at the time of go-live. However, it is also possible to import completed sales orders for historical reference.  If importing completed sales orders, understand that completed sales orders should not be reopened and edited because the sales order amounts may recalculate, which would create incorrect historical records.
+
===Timing and Preparation===
  
 +
The Project Import can occur any time after all customers have been imported, all project managers, account managers and salespersons have been established, and all Project Control rules have been completed.
  
 
===General Notes===
 
===General Notes===
  
−
'''Configurable Items''' - The Sales Order import does not have the ability to import custom configuration details with each line item. Discuss this with the customer and develop a plan to capture the configuration details and to update the imported order items manually after the import.
+
The Project Import creates the base project record. In order to fully work with imported project records, customers should expect to complete other required data and linked records such as tasks and orders manually.  
−
 
 
−
'''Order Amounts''' - Customers should expect the order totals for imported open orders to change once they are edited in Adjutant. There are a number of factors which may cause pricing and order totals to recalculate once the order is edited in Adjutant. Minor rounding differences, changes in customer pricing structure, program differences between the legacy system pricing calculations and Adjutant, configuration pricing differences, and tax calculations are just some of the factors that may affect order totals once the imported records are edited in Adjutant. Customers should be prepared for these differences, and a plan of action should be developed to review and adjust orders as needed.
 
−
 
 
−
'''Taxability''' - Confirm with the customer that taxable flags can be reliably set on the source data. The import requires that the taxability of each line item is defined, and if it is not set to yes, it will default to non-taxable. Also, as stated above, tax calculations between two different systems will commonly be different because of rounding or calculation methods. Differences between customer setup information or between the ways that order taxes are controlled  (for example, legacy systems may allow tax rates to be manually decided at the order level compared to being decided by the Ship To location) can lead to different tax calculations. Customers should expect to carefully review and validate each imported order's tax amounts.  
 
  
 +
The Project Import will create the default project phase. Use the Project Phase Import to create additional phases.
  
  
−
==Sales Order Import File Data Scrubbing==
 
  
−
Every customer's data will have different issues that need addressing.  Some of the issues will not make themselves visible until after the data has been imported and is in use during parallel testing.  This is why it is critically important to perform an early import, and keep accurate notes on issues that need to be addressed on a supplemental import, or for a complete re-import. Some common things that need attention during Sales Order data scrubbing include:
+
==Project Import File Data Scrubbing==
  
−
* '''Open vs. Closed sales orders:''' If you are importing completed historical orders, understand that the source data may require significantly more scrubbing, as well as decisions on how to handle obsolete data. Data like salespersons, account managers, customers, rule-based drop-down options, and even items that may no longer be valid will have to be added to Adjutant in order to complete the import. This means deliberately adding obsolete records in several areas in order to support historical data. Discuss the options carefully with the customer before proceeding.
+
Every customer's data will have different issues that need addressing. Some of the issues will not make themselves visible until after the data has been imported and is in use during parallel testing.  This is why it is critically important to perform an early import, and keep accurate notes on issues that need to be addressed on a supplemental import, or for a complete re-import. Some common things that need attention during Project data scrubbing include:
  
 
* '''Dates''' - Make sure all dates follow the MM/DD/YY format before importing. Also review each date column and work with the customer to ensure the right dates are being imported in the right columns.
 
* '''Dates''' - Make sure all dates follow the MM/DD/YY format before importing. Also review each date column and work with the customer to ensure the right dates are being imported in the right columns.
  
−
* '''Amounts''' - Carefully review and verify that the header file totals and the detail file totals match by invoice number. Also verify that the header subtotal/tax total/total amount values add up correctly and match with the extended price and extended tax values in the detail file.
+
* '''Project #''' - The 'msnnum' column sets the project number. If the msnnum value is left blank, the project number will default to the next consecutive MSN datatable keyno available, according to the spreadsheet order. The system will not use CID counters or Project auto-numbering rules to establish the project number. Review the file data and make sure the msnnum value is filled in for all records, unless the customer approves to auto-assign the project numbers based on database keyno sequence.
−
 
 
−
* '''Line numbers''' - If the legacy system doesn't provide line numbers, the import can automatically generate line numbers based on the order of the order details on the import spreadsheet. If line numbers are provided, pay close attention that line number data does not get altered during the scrubbing process. The line number information should also be carefully reviewed for duplicate line numbers on the same order. The import will skip duplicates and only import the last one encountered.
 
−
 
 
−
* '''Terms''' - Make sure terms data is accurate and matches an entry in the TERMS rule. Any imported TERMS value that does not exist in the TERMS rule will create a blank entry in the rule. Best practice is to match each order's terms to a valid TERMS entry. In some systems, order terms are a text field and there is no standardization on the legacy system.  For these instances, it is best to scrub each term value until it matches an existing TERM. Alternatively, you can create a catch-all TERM rule for unknown, incomplete, or undefined term details in the source data.
 
−
 
 
−
* '''Ship Via''' - Make sure Ship Via data is accurate and matches an entry in the SHIPVIA rule. Follow the same guidelines detailed in the Terms section above for scrubbing Ship Via data.
 
−
 
 
−
* '''Freight''' - Make sure Freight data is accurate and matches an entry in the FREIGHT rule. Follow the same guidelines detailed in the Terms section above for scrubbing Freight data.
 
−
 
 
−
* '''FOB''' - Make sure FOB data is accurate and matches an entry in the FOB rule. Follow the same guidelines detailed in the Terms section above for scrubbing FOB data.
 
−
 
 
  
−
==Sales Order Import Screen (IMPORTSO)==
+
* '''Project Manager/Account Manager/Salesperson''' - In order for these records to import and update correctly, the contact record must exist with the correct attribute, and the name must match exactly. Review all names in the file to ensure that all names match and are set up with the correct attributes.
  
−
Menu Location: Transaction>>Importers/Exporters>>Purchase and Sales Orders>>Sales Order Import
+
* '''Project Bill Type/Type/Status''' - These fields are validated against the corresponding rule name value (not the code value). These names must exist in the corresponding rules, or they will not import. The import will not create new rule details for the imported records.
  
−
[[File:IMPORTSO.png]]
+
*'''CompFlag and OnHold''' - These y/n fields must be entered with a lowercase letter. An uppercase Y will not set the corresponding flag.  
  
 +
==Project Import Screen (IMPORTPROJECT)==
  
−
'''File Name(Header)/Browse:''' Use the Browse button to locate and select the completed Sales Order Header import template file (in XLS format). Note - both the Header and Details files are required and must be imported at the same time.
+
'''File Name/Browse:''' Use the Browse button to locate and select the completed Project Import template file (in XLS format).  
  
−
'''File Name(Details)/Browse:''' Use the Browse button to locate and select the completed Sales Order Details import template file (in XLS format). Note - both the Header and Details files are required and must be imported at the same time.
+
'''Export Template:''' Generates a blank Project Import template file
−
 
 
−
'''Export Template:''' Generates a blank Sales Order Header or Details template file
 
  
 
'''Map Fields:''' Fields must be mapped prior to importing. If no changes have been made to the column headings, the mapping screen should show all green, and you can click OK to continue. If any of the Input Field Name columns on the left are red, single-click on the line on the left column, and then double-click the desired mapped field in the right column to complete the mapping.  Repeat for any red lines on the left that should be mapped.  If there are additional columns in the source file that should NOT be mapped, they can be left unmapped (displayed in red). Mandatory fields will require that they be mapped before clicking OK.
 
'''Map Fields:''' Fields must be mapped prior to importing. If no changes have been made to the column headings, the mapping screen should show all green, and you can click OK to continue. If any of the Input Field Name columns on the left are red, single-click on the line on the left column, and then double-click the desired mapped field in the right column to complete the mapping.  Repeat for any red lines on the left that should be mapped.  If there are additional columns in the source file that should NOT be mapped, they can be left unmapped (displayed in red). Mandatory fields will require that they be mapped before clicking OK.
−
 
−
'''Validate to TXT file:''' Selecting this box will create a TXT errors file listing any rows that would create errors such as invalid data, or duplicate records. If no errors are found in the file, the import will still process and no error file will be generated. Checking the validate box only prevents the import process if errors are encountered.
 
  
  
  
−
==Sales Order Import Header File Definitions==
+
==Project Import File Definitions==
  
 
Required fields are indicated with an *  
 
Required fields are indicated with an *  
  
−
'''f1 - TaxTable''' - This field is no longer used
+
'''*f1 - Name''' - Name of the Project. 40 characters max.
−
 
 
−
'''f2 - OrderNo''' - Sales order Order # field value (max 30 characters). This field can be used in the SO Header screen to search for records.
 
−
 
 
−
'''*f3 - Owner''' - Inventory owner name
 
−
 
 
−
'''*f4 - SODate''' - Sales Order Date (MM/DD/YY format)
 
−
 
 
−
'''f5 - ReqDate''' - Sales Order Required Date (MM/DD/YY format)
 
−
 
 
−
'''f6 - Freight''' - Freight terms for the selected sales order. Valid freight terms from the FREIGHT rule should be used. If an entry is found during import that doesn't match an existing FREIGHT rule record, that entry will be added to the FREIGHT rule with no other rule details. This allows the import to complete, but could add unwanted records to the FREIGHT rule.
 
−
 
 
−
'''f7 - Terms''' - Payment terms for the selected sales order. Valid terms from the TERMS rule should be used. If an entry is found during import that doesn't match an existing TERMS rule record, that entry will be added to the TERMS rule with no other rule details. This allows the import to complete, but could add unwanted records to the TERMS rule.
 
−
 
 
−
'''f8 - TaxRate''' - Sales order tax percentage rate in ##.## format. The tax rate is stored in the SO Header for certain forms and reporting on closed or voided orders. For importing open orders, the tax table and tax rate will be updated from the sales order ship to record as soon as the order is edited and the UpTax button is used.
 
−
 
 
−
'''*f9 - SOType''' - Sales Order Type code from the SOTYPE rule. Blank or invalid import entries will use the first entry from the SOTYPE rule.
 
−
 
 
−
'''*f10 - ShipTo''' - Ship To organization number
 
−
 
 
−
'''f11 - Notes''' - Sales order header notes
 
−
 
 
−
'''f12 - Status''' - Sales order status code. Valid entries are 'O' (Open), 'C' (Closed), and 'V' (Void). If Status is blank or invalid, the sales order will be set to 'O'pen status.
 
−
 
 
−
'''f13 - OrdDate''' - This field is no longer in use
 
−
 
 
−
'''f14 - EstDate''' - Sales Order Load Date (MM/DD/YY format)
 
−
 
 
−
'''f15 - ConfirmTo''' - Sales order Confirm to field value (max 60 characters)
 
−
 
 
−
'''f16 - Remarks''' - Sales order Remarks field value (max 100 characters)
 
−
 
 
−
'''f17 - AcctMgr''' - Sales order Account Manager field value. Account Manager entries should exactly match an existing Contact record set up with the Account Manager attribute. If they do not match, the entry will not be imported.
 
−
 
 
−
'''*f18 - HeldFor''' - Inventory Held For name
 
−
 
 
−
'''f19 - PONum''' - Sales order Cust PO# field value (max 30 characters)
 
−
 
 
−
'''f20 - SP''' - Sales order Salesperson field value. Salesperson entries should exactly match an existing Contact record set up with the Salesperson attribute. If they do not match, the entry will not be imported.
 
−
 
 
−
'''f21 - FOB''' - Free on Board value for the selected sales order. Valid FOB names from the FOB rule should be used. If an entry is found during import that doesn't match an existing FOB rule record, that entry will be added to the FOB rule with no other rule details. This allows the import to complete, but could add unwanted records to the FOB rule.
 
−
 
 
−
'''f22 - ShipVia''' - Ship Via value for the selected sales order. Valid ship via names from the SHIPVIA rule should be used. If an entry is found during import that doesn't match an existing SHIPVIA rule record, that entry will be added to the SHIPVIA rule with no other rule details. This allows the import to complete, but could add unwanted records to the SHIPVIA rule.
 
−
 
 
−
'''*f23 - ShipWhse''' - Shipping warehouse name
 
−
 
 
−
'''*f24 - SoNO''' - Sales order number (max 30 characters)
 
−
 
 
−
'''*f25 - BillTo''' - Bill To organization number
 
−
 
 
−
'''*f26 - CustNo''' - Sold To organization number
 
−
 
 
−
 
 
  
−
==Sales Order Import Details File Definitions==
+
'''*f2 - MSNNum''' - Project number. 20 characters max. If left blank, the import will assign a unique number based on the key number value from the database.
  
−
Required fields are indicated with an *
+
'''f3 - SDate''' - Project Start Date. Must be formatted as a date in MM/DD/YY format. This is the first of the two date fields under the Ship To field in the Project Master tab.
  
−
'''*f1 - ShipTo''' - Ship To organization number, must match the Ship To from the header file
+
'''f4 - EDate''' - Project End Date. Must be formatted as a date in MM/DD/YY format. This is the second of the two date fields under the Ship To field in the Project Master tab.
  
−
'''*f2 - ExtPrice''' - Extended subtotal amount (before tax) for the sales order line. This value will be used to calculate the Line Item screen subtotal. It is also used to set the line item unit price by dividing the ExtPrice by the QtyOrd value. These values will automatically recalculate if the sales order is opened and any related fields are edited, which may change the imported amounts.  
+
'''f5 - CompDate''' - Project Completion Date. Must be formatted as a date in MM/DD/YY format.
  
−
'''*f3 - Owner''' - Inventory Owner organization ID
+
'''f6 - ProdDate''' - Production/Fab Date. Must be formatted as a date in MM/DD/YY format.
  
−
'''*f4 - SPrice''' - Sell price in dollars, by SPriceUnit value. This price will remain in place as the agreed-to price, even after editing the imported line.  
+
'''f7 - AddDate''' - Project Add/Creation Date. Must be formatted as a date in MM/DD/YY format. If left blank, the added date will be set to the system date at the time of import.
  
−
'''*f5 - SPriceUnit''' - Sell price unit to define the SPrice. Must be a valid unit of measure in the UNITS rule.
+
'''*f8 - Whse''' - Warehouse name for the project. Must match a warehouse name that has been set up in the Warehouses screen.
  
−
'''f6 - SOStatus''' - This field is no longer in use
+
'''f9 - PM''' - Name of the Project Manager. Must match the full name of a contact in Adjutant with the Project Manager attribute.
  
−
'''*f7 - Price''' - Line item price used to calculate the overall order amount. The Price value used to calculate the sales order header order amount by multiplying the Price by the QtyOrd for all sales order lines. These values will automatically recalculate if the sales order is opened and any related fields are edited, which may change the imported amounts.  
+
'''f10 - ShortNote''' - A note or extended description of the project. Updates the Project Master Remarks area. 200 characters max.
  
−
'''f8 - Notes''' - Line notes for the associated line item
+
'''f11 - BillType''' - Project Billing Type. Must match a value in the Project Billing Types (BILLTYPE) rule.
  
−
'''*f9 - ExtTax''' - Extended tax amount for the associated line item. These values will automatically recalculate if the sales order is opened and any related fields are edited, which may change the imported amounts.  
+
'''f12 - BillAmount''' - The amount that has been billed on the project.  
  
−
'''*f10 - HeldFor''' - Held For organization ID
+
'''f13 - AccrAmount''' - The current accrual amount on the project.
  
−
'''*f11 - ShipWhse''' - Warehouse name in Adjutant for shipping location
+
'''f14 - ContractAmt''' - The contract amount of the project.
  
−
'''f12 - ReqDate''' - Sales order line item Req Date (Required Date)
+
'''*f15 - TCode''' - Project Type. Must match a value in the Project Types (PROJTYPE) rule.
  
−
'''f13 - EstDate''' - Sales order line item Est Date (Load Date)
+
'''*f16 - SCode''' - Project Status. Must match a value in the Project Status (PROJSTATUS) rule.
  
−
'''f14 - UnitFact''' - Unit of measure scaling factor
+
'''f17 - AccMgr''' - Name of the Account Manager. Must match the full name of a contact in Adjutant with the Account Manager attribute.
  
−
'''f15 - Unit''' - Sell unit of measure for the associated line item. Common entries are EA, FT, LBS (or any valid unit of measure established in the UNITS rule)
+
'''f18 - ContractDate''' - Contract Date. Must be formatted as a date in MM/DD/YY format.
  
−
'''f16 - Taxable''' - Taxable flag for the associated line item (Y/N). If field is left blank, the line item will be set as non-taxable.  
+
'''f19 - SP''' - Name of the Salesperson . Must match the full name of a contact in Adjutant with the Salesperson attribute.
  
−
'''*f17 - SoNo''' - Sales order number (max 30 characters) used to link line items to the sales order header record
+
'''f20 - CompFlag''' - Completion status of the project. Use a 'y' if the project is complete and a 'n' if it is still active. '''Must be entered as a lowercase letter.'''
  
−
'''*f18 - Descrip''' - Item description for the associated line item. The description must be entered in order to display on imported records. It will not default from the associated item code.
+
'''f21 - RevNum''' - Revision number of the project.  
  
−
'''f19 - Cost''' - Sell unit cost for the associated line item
+
'''f22 - OnHold''' - On hold status of the project. Use a 'y' if the project is on hold and a 'n' if it is not. '''Must be entered as a lowercase letter.'''
  
−
'''f20 - QtyShp''' - Quantity shipped for the associated line item
+
'''*f23 - SoldTo''' - Organization ID of the customer.
  
−
'''*f21 - QtyOrd''' - Quantity ordered for the associated line item
+
'''*f24 - ShipTo''' - Organization ID of the shipping/work location.
  
−
'''f22 - LineNum''' - Line number for the associated line item. If left blank, the line number will automatically generate according to the spreadsheet order. If duplicate line numbers are found for the same sales order number, only the last record will be imported.  
+
'''f25 through f31 - Custno1/2/3/4''' - Organization ID of other companies linked to the project.
  
−
'''*f23 - Item''' - Item number. The item number must exist in Adjutant. The import will not import invalid items, will not create item codes, and will not default to a MISC code.  
+
'''f26 through f32 - Attrib1/2/3/4''' - Attribute code that signifies the relationship of the other companies linked to the project. Use the Attribute Code from text1 of the Organization Attributes rule - i.e SOLDTO, SHIPTO, BILLTO, REMITTO, SOLDFROM, etc.  
  
  
  
−
==Sales Order Import Reconciliation==
+
==Project Import Reconciliation==
  
 
Reconciling imported data should begin with spot-checking several records field-by-field for complete data import.  Pick records from the source file that have the most data columns filled in.  Verify that all source file data fields imported correctly and display as expected.
 
Reconciling imported data should begin with spot-checking several records field-by-field for complete data import.  Pick records from the source file that have the most data columns filled in.  Verify that all source file data fields imported correctly and display as expected.
  
−
Review the TERMS rule for any unexpected records added to the rule. Depending on the number of records involved, the rule maintenance records can be cleaned up, and the associated sales order records can be manually updated.  Otherwise, clean up the TERMS rule, then delete the imported records, re-scrub the source file, and re-import.
+
Run the Project List report to select the imported records. Review the report for basic details and look for any missing or incorrect records.  
−
 
 
−
Review the FREIGHT rule for any unexpected records added to the rule. Depending on the number of records involved, the rule maintenance records can be cleaned up, and the associated sales order records can be manually updated.  Otherwise, clean up the FREIGHT rule, then delete the imported records, re-scrub the source file, and re-import.
 
−
 
 
−
Review the SHIPVIA rule for any unexpected records added to the rule. Depending on the number of records involved, the rule maintenance records can be cleaned up, and the associated sales order records can be manually updated.  Otherwise, clean up the SHIPVIA rule, then delete the imported records, re-scrub the source file, and re-import.
 
−
 
 
−
Review the FOB rule for any unexpected records added to the rule. Depending on the number of records involved, the rule maintenance records can be cleaned up, and the associated sales order records can be manually updated.  Otherwise, clean up the FOB rule, then delete the imported records, re-scrub the source file, and re-import.
 
−
 
 
−
Run the Sales Order report to select the imported records. Review the report for sales order basic details and look for any missing or incorrect records.
 
−
 
 
−
 
 
−
 
 
−
==Sales Order Import Additional Steps==
 
−
 
 
−
Sales Orders imported with an 'O'pen status should be edited on the Sales Order header screen and the 'UpTax' button should be used to set the correct ShipTo tax table and recalculate taxes according to the latest Adjutant tables. Sales taxes will be recalculated upon invoicing, and may generate errors if the sales order has not been updated after importing.
 
  
−
Review imported orders for fields that are not available on the import file, and manually update any required information that could not be imported. Some key fields to consider are:
 
  
−
* Production Plant
 
−
* Project and Project Phase links
 
−
* Purchase Order links
 
−
* Order header flag checkboxes such as 'Hold', 'Certs', and 'SMS Update'
 
−
* Order line item flag checkboxes such as 'Drop Ship', 'Hide Line', and 'Hide Price'
 
  
−
Configuration details are not available on the import file. Review any configurable items on the import file and update configuration details manually before sending to production.
+
==Project Import Additional Steps==
  
 +
If Project Phases need to be imported, run the Project Phase Import.
  
  
−
==Sales Order Import Database Tables==
 
  
−
'''SOMAST''' - Each imported sales order from the Header file creates a new KEYNOH in the this table, linked to the imported SONO value. The SOMAST KEYNOH value is the link to the SOTRAN table, but you can also link using SONO.
+
==Project Import Database Tables==
  
−
'''SOTRAN''' - Each imported sales order detail line will create an entry in the SOTRAN table. The KEYNOH value should match the SOMAST KEYNOH value for the associated sales order. The SONO value exists in both tables so it is easy to link.  
+
'''MSN''' - Each imported project creates a new KEYNO in the this table. The MSNNUM value holds the project number.
  
 +
'''MSNPHASE''' - Each imported project will create an entry in this table for a default phase. The KEYNO value of the default phase will be linked to the MSNID value which should match the MSN table KEYNO value for the associated project.
  
 +
'''MSNCUST''' - All organization IDs linked to the project are saved in this table as CUSTIDs with a unique KEYNO value for each. They are linked to the MSN table by KEYNOH which will match the MSN KEYNO.
  
  
−
[[Category: Order Entry]]
+
[[Category:Project Management]] [[Category:Import/Export]]
−
[[Category: Import Guides]]
 

Latest revision as of 14:04, 7 December 2022

General Information

'Menu Location: Transaction>>Importers/Exporters>>Projects>>Project Import

URL:importmaster_S.htm?mode=PROJECTIMPORT


General Data Import Notes

Refer to the Data Import General Information page for information and guidance on general import timeline, formatting import templates, as well as how to ensure you are using the most current template information for data imports.


Project Import Notes

Timing and Preparation

The Project Import can occur any time after all customers have been imported, all project managers, account managers and salespersons have been established, and all Project Control rules have been completed.

General Notes

The Project Import creates the base project record. In order to fully work with imported project records, customers should expect to complete other required data and linked records such as tasks and orders manually.

The Project Import will create the default project phase. Use the Project Phase Import to create additional phases.


Project Import File Data Scrubbing

Every customer's data will have different issues that need addressing. Some of the issues will not make themselves visible until after the data has been imported and is in use during parallel testing. This is why it is critically important to perform an early import, and keep accurate notes on issues that need to be addressed on a supplemental import, or for a complete re-import. Some common things that need attention during Project data scrubbing include:

  • Dates - Make sure all dates follow the MM/DD/YY format before importing. Also review each date column and work with the customer to ensure the right dates are being imported in the right columns.
  • Project # - The 'msnnum' column sets the project number. If the msnnum value is left blank, the project number will default to the next consecutive MSN datatable keyno available, according to the spreadsheet order. The system will not use CID counters or Project auto-numbering rules to establish the project number. Review the file data and make sure the msnnum value is filled in for all records, unless the customer approves to auto-assign the project numbers based on database keyno sequence.
  • Project Manager/Account Manager/Salesperson - In order for these records to import and update correctly, the contact record must exist with the correct attribute, and the name must match exactly. Review all names in the file to ensure that all names match and are set up with the correct attributes.
  • Project Bill Type/Type/Status - These fields are validated against the corresponding rule name value (not the code value). These names must exist in the corresponding rules, or they will not import. The import will not create new rule details for the imported records.
  • CompFlag and OnHold - These y/n fields must be entered with a lowercase letter. An uppercase Y will not set the corresponding flag.

Project Import Screen (IMPORTPROJECT)

File Name/Browse: Use the Browse button to locate and select the completed Project Import template file (in XLS format).

Export Template: Generates a blank Project Import template file

Map Fields: Fields must be mapped prior to importing. If no changes have been made to the column headings, the mapping screen should show all green, and you can click OK to continue. If any of the Input Field Name columns on the left are red, single-click on the line on the left column, and then double-click the desired mapped field in the right column to complete the mapping. Repeat for any red lines on the left that should be mapped. If there are additional columns in the source file that should NOT be mapped, they can be left unmapped (displayed in red). Mandatory fields will require that they be mapped before clicking OK.


Project Import File Definitions

Required fields are indicated with an *

*f1 - Name - Name of the Project. 40 characters max.

*f2 - MSNNum - Project number. 20 characters max. If left blank, the import will assign a unique number based on the key number value from the database.

f3 - SDate - Project Start Date. Must be formatted as a date in MM/DD/YY format. This is the first of the two date fields under the Ship To field in the Project Master tab.

f4 - EDate - Project End Date. Must be formatted as a date in MM/DD/YY format. This is the second of the two date fields under the Ship To field in the Project Master tab.

f5 - CompDate - Project Completion Date. Must be formatted as a date in MM/DD/YY format.

f6 - ProdDate - Production/Fab Date. Must be formatted as a date in MM/DD/YY format.

f7 - AddDate - Project Add/Creation Date. Must be formatted as a date in MM/DD/YY format. If left blank, the added date will be set to the system date at the time of import.

*f8 - Whse - Warehouse name for the project. Must match a warehouse name that has been set up in the Warehouses screen.

f9 - PM - Name of the Project Manager. Must match the full name of a contact in Adjutant with the Project Manager attribute.

f10 - ShortNote - A note or extended description of the project. Updates the Project Master Remarks area. 200 characters max.

f11 - BillType - Project Billing Type. Must match a value in the Project Billing Types (BILLTYPE) rule.

f12 - BillAmount - The amount that has been billed on the project.

f13 - AccrAmount - The current accrual amount on the project.

f14 - ContractAmt - The contract amount of the project.

*f15 - TCode - Project Type. Must match a value in the Project Types (PROJTYPE) rule.

*f16 - SCode - Project Status. Must match a value in the Project Status (PROJSTATUS) rule.

f17 - AccMgr - Name of the Account Manager. Must match the full name of a contact in Adjutant with the Account Manager attribute.

f18 - ContractDate - Contract Date. Must be formatted as a date in MM/DD/YY format.

f19 - SP - Name of the Salesperson . Must match the full name of a contact in Adjutant with the Salesperson attribute.

f20 - CompFlag - Completion status of the project. Use a 'y' if the project is complete and a 'n' if it is still active. Must be entered as a lowercase letter.

f21 - RevNum - Revision number of the project.

f22 - OnHold - On hold status of the project. Use a 'y' if the project is on hold and a 'n' if it is not. Must be entered as a lowercase letter.

*f23 - SoldTo - Organization ID of the customer.

*f24 - ShipTo - Organization ID of the shipping/work location.

f25 through f31 - Custno1/2/3/4 - Organization ID of other companies linked to the project.

f26 through f32 - Attrib1/2/3/4 - Attribute code that signifies the relationship of the other companies linked to the project. Use the Attribute Code from text1 of the Organization Attributes rule - i.e SOLDTO, SHIPTO, BILLTO, REMITTO, SOLDFROM, etc.


Project Import Reconciliation

Reconciling imported data should begin with spot-checking several records field-by-field for complete data import. Pick records from the source file that have the most data columns filled in. Verify that all source file data fields imported correctly and display as expected.

Run the Project List report to select the imported records. Review the report for basic details and look for any missing or incorrect records.


Project Import Additional Steps

If Project Phases need to be imported, run the Project Phase Import.


Project Import Database Tables

MSN - Each imported project creates a new KEYNO in the this table. The MSNNUM value holds the project number.

MSNPHASE - Each imported project will create an entry in this table for a default phase. The KEYNO value of the default phase will be linked to the MSNID value which should match the MSN table KEYNO value for the associated project.

MSNCUST - All organization IDs linked to the project are saved in this table as CUSTIDs with a unique KEYNO value for each. They are linked to the MSN table by KEYNOH which will match the MSN KEYNO.

Retrieved from "http://www.abiscorp.com/faq/index.php?title=Project_Import_Guide&oldid=3895"