EDI to QB for Sales Invoices

From UG

Current revision as of 15:18, 21 December 2010

[edit] Parent Mantis

http://mantis.jaguarfreight.com/mantis/view.php?id=2338

[edit] Requirements

[edit] Intro

Core need: To eliminate double entry of Sales Invoices into 2 systems at Jaguar:

• CT2 transportation system
• QuickBooks (QB) accounting system

Instead we suggest entering once into CT2 and then periodically upload into QB.

[edit] Example

See Figures below.

[edit] Figure 1 CT2

Invoice as it is entered in CT2.

[edit] Figure 2 QB

Invoice as it is entered in Quickbooks.

[edit] Forward in batches

All sales invoices/credit notes entered in CT2 would be forwarded to QB in batches (bundles).

[edit] Frequency

Initially once a day at the end of the day.

[edit] Review process

Before batch is populated into QB accounting operators should have a chance for "30 sec review".

[edit] Gaps in invoice numbers

First inv# in new batch should be next number after last one in previous.

[edit] QB fields and Mapping

[edit] Systems Design

[edit] Summary

QB dashboard panel is a part of CT2 dashboard that is located on the homepage of CT2 Internal Application.

This panel is designed for:

1) indicating of new accounting documents in CT2 (should be imported into the QuickBooks accounting system).

2) daily transmission of accounting information from CT2 into the QuickBooks accounting system in IIF-format.

Intuit Interchange Format (IIF) files are text files that QuickBooks uses to import and export data.

• Each IIF-file is a daily batch file.

• This file is created for each Jaguar office, whose in the office settings has a IIF-file creation time: Admin -> Users and Offices -> Offices. See Figure 0.
• Daily upload of IIF-file should be started at the day that is specified in "start since" datebox.
• Suggested daily time of the batch creating is 3 pm. (Time Zone defines by local office time).
  • Example: Daily time of creating set to 3 pm. "Start since" set to 01-Sep-2010. This means that first IIF-file will be created On September 1 at 3 pm. It will contain all docs that were created from 12 am to 3 pm on Sep,1.
    • Next batch will be available to view on September 2 at 3 pm and will contain all accounting docs that were created from 3 pm on September 1 to 3 pm on September 2.
    • Etc.

• This file should be created automatically and it should contain an information about all accounting documents that were created in one certain Jaguar office per one day.

• At now the documents for uploading to batch file are Sales Invoice and Credit Note only for New York office.
• NOTE: Unapproved Credit Notes should not be part of the upload.

• Each IIF-file can be downloaded from QB panel on user's computer for its further transmission into QuickBooks.

[edit] QB dashboard panel

QB dashboard panel placed on the homepage of CT2 Internal Application in the form of the table.

• Visibility of QB panel is set for user in "Dashboard configuration" section of "Edit Jaguar User" window: Admin -> Users and Offices -> Jaguar Users. See on Figure 1.

QB panel indicates number of new documents in CT2 that not imported yet to the QuickBooks. See on Figure 2 (a, b).

Non-zero value in the "Not imported" column is also a link to the #List Of Invoices.

[edit] Columns Definitions

• Document Name: String "Sales Invoice / Credit Note", because the upload batch file should include info of both of these documents.
• Not imported: Number of new documents to import. Here is indicate the total value of the number of Sales Invoices and Credit Notes.

[edit] List Of Invoices

List Of Invoices opens in new window and should represent list of all available batch IIF-files. This list contains also the marks of document's import state and some additional information.

List Of Invoices is a standard CT2 table with paging and sorting features. Each row of the document table corresponds to one date. See on Figure 3.

[edit] Columns Definitions

• No Label: column for (multi)-selecting of rows for change their state to imported/new.

• click on header's checkbox selects all rows of current page.

• Date: Date of batch as Datatypes#CT2 date.

• List Of Invoices should be sorted by this column in descending order.
• Example: 03-Sep-2010.

• Filename: Name of downloadable IIF-file. At the same time it is also a link to downloading. Define also its creation date.

• The output string consists of the following parts:

• the document's ID - CT2QB.
• creation date - formatted as "dd-MMM-yyyy".
• mark of state of current batch: is equal "imported" if batch was imported; is equal "not_imported" if batch was not imported.

• Example: CT2QB_03-Sep-2010_imported.

• Sales Invoices.

• Shows count of Sales Invoices (SI) in batch and range:

• number of the first SI for the reporting day - number of the last SI for the reporting day

• Example: Count: 17, Range: US265 - US281.

• Total Amount: Total of amounts of all SIs included to batch file (2 decimal places).

• Example: 21 050.00.
• NOTE: Header of column should contain short name of Office currency. Example: "Total, USD".

• Credit Notes.

• Shows count of Credit Notes (CN) in batch and range:

• number of the first CN for the reporting day - number of the last CN for the reporting day

• Example: Count: 17, Range: US265 - US281.

• Total Amount: Total of amounts of all CNs included to batch file (2 decimal places). Should be shown with minus sign.

• Example: - 1 120.50.
• NOTE: Header of column should contain short name of Office currency. Example: "Total, USD".

• Imported: Status of batch: new or imported

• Shown as a check mark when a batch is imported into QB. Otherwise shown as a blank.

[edit] Figures

[edit] Figure 0. Enable QB File Import

[edit] Figure 1. QB panel visibility checkbox

[edit] Figure 2. QB dashboard panel

a. No documents to import into QB:

b. There are documents to import into QB:

[edit] Figure 3. List Of Invoices

[edit] History

• 0002589: [EDI to QB] (QB dashboard panel) bug: strange behaviour that possibly hangs the server