EDI to QB for Sales Invoices

From UG

Jump to: navigation, search


Contents

[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.

File:SiCT2.JPG

[edit] Figure 2 QB

Invoice as it is entered in Quickbooks.

File:Si.JPG


[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

QB CT2
Bill To Bill To
Date Date
Inv# Invoice No
PO number CT#
Ship today date
Item code lookup based on CC#
Description <blank>
Quantity <blank>
Price Each Total invoice
Amount Amount
Total Total invoice

[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.
  • 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

File:Edit Office window IIF check.gif

[edit] Figure 1. QB panel visibility checkbox

File:QB_panel_visibility_checkbox.gif

[edit] Figure 2. QB dashboard panel

a. No documents to import into QB:

File:QB_dashboard_panel_zero.gif

b. There are documents to import into QB:

File:QB_dashboard_panel_link.gif

[edit] Figure 3. List Of Invoices

[edit] History

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