EDI to QB for Sales Invoices

From UG

(Difference between revisions)
Jump to: navigation, search
(→QB fields and Mapping)
 
(25 intermediate revisions not shown)
Line 5: Line 5:
http://mantis.jaguarfreight.com/mantis/view.php?id=2338
http://mantis.jaguarfreight.com/mantis/view.php?id=2338
-
== Intro ==
 
-
'''Core requirement:''' To eliminate double entry of Sales Invoices into 2 systems at Jaguar:
+
== Requirements ==
 +
 
 +
=== Intro ===
 +
 
 +
'''Core need:''' To eliminate double entry of Sales Invoices into 2 systems at Jaguar:
* CT2 transportation system
* CT2 transportation system
-
* QuickBooks accounting system
+
* QuickBooks (QB) accounting system
 +
 
 +
Instead we suggest entering once into CT2 and then periodically upload into QB.
-
=== Example ===
+
==== Example ====
See Figures below.
See Figures below.
Line 27: Line 32:
[[File:Si.JPG]]
[[File:Si.JPG]]
-
== Requirements ==
 
=== Forward in batches ===
=== Forward in batches ===
Line 59: Line 63:
|-
|-
|  Item code ||  lookup based on CC#
|  Item code ||  lookup based on CC#
-
|-
 
-
|  Amount ||  Amount
 
-
|-
 
-
|  Total ||  Total invoice
 
-
|-
 
-
|  Price Each ||  Total invoice
 
|-
|-
|  Description ||  <blank>
|  Description ||  <blank>
|-  
|-  
|  Quantity  || <blank>
|  Quantity  || <blank>
 +
|-
 +
|  Price Each ||  Total invoice
 +
|-
 +
|  Amount ||  Amount
 +
|-
 +
|  Total ||  Total invoice
|}
|}
Line 79: Line 83:
:1) indicating of new accounting documents in CT2 ('''''should be imported''' into the QuickBooks accounting system'').
: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.
: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.  
+
  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 should be created automatically and it should contain an information about all account documents that were created in CT2 per one day.
+
* Each IIF-file is a daily batch file.
-
* '''At now''' the documents for uploading to batch file are [[Sales Invoice]] and [[Credit Note]] only for New York office.
+
:* 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. Enable QB File Import | Figure 0]].
-
: NOTE: ''Unapproved Credit Notes should not be part of the upload.''
+
:* Daily upload of IIF-file should be started at the day that is specified in "start since" datebox.
-
* A daily time of creating of the batch is 6 p.m. (NY Time Zone = EDT).
+
:* Suggested daily time of the batch creating is 3 pm. (Time Zone defines by local office time).
-
: NOTE: ''It would be desirable to be able to set this value in the Admin's part of CT2.''
+
:** ''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.
* Each IIF-file can be downloaded from QB panel on user's computer for its further transmission into QuickBooks.
-
=== QB dashboard panel ===
 
-
QB dashboard panel indicates number of new documents in CT2 that not imported yet to the QuickBooks. QB panel placed on the homepage of CT2 Internal Application in the form of the table. See on [[#Figure 1. QB dashboard panel | Figure 1]].
+
=== 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 visibility checkbox | Figure 1]].
 +
QB panel indicates number of new documents in CT2 that not imported yet to the QuickBooks.
 +
See on [[#Figure 2. QB dashboard panel | Figure 2]] (a, b).
-
Each row on the QB panel corresponds to one type of accounting document. Each value in the "Not imported" column is a link to the [[#QB document table]].  
+
Non-zero value in the "Not imported" column is also a link to the [[#List Of Invoices]].  
==== Columns Definitions ====
==== Columns Definitions ====
-
* '''Document Name''': Type of document.
+
* '''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 of this type.
+
* '''Not imported''': Number of new documents to import. Here is indicate the total value of the number of Sales Invoices and Credit Notes.
-
=== QB document table ===
+
=== List Of Invoices ===
-
Each document table corresponds to one type of document and should represent list of all available batch IIF-files by this type.
+
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.
-
QB document table is a standard CT2 table with paging and sorting features. Each row of the document table corresponds to one date. See on [[#Figure 2. QB panel for Sales Invoices | Figure 2]] and [[#Figure 3. QB panel for Credit Notes | Figure 3]].
+
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. List Of Invoices | Figure 3]].
==== Columns Definitions ====
==== 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]].
* '''Date''': Date of batch as [[Datatypes#CT2 date]].
-
:* Table of batches should be sorted by this column in descending order
+
:* List Of Invoices should be sorted by this column in descending order.
:* ''Example:'' 03-Sep-2010.
:* ''Example:'' 03-Sep-2010.
-
* '''Filename''': Name of downloadable IIF-file. At the same time it is also a link to downloading.
+
* '''Filename''': Name of downloadable IIF-file. At the same time it is also a link to downloading. Define also its creation date.
-
:* Define type of documents that included to file and also its creation date. The output string has the following format:
+
:* The output string consists of the following parts:
-
::* the document's ID - '''SI''' for Sales Invoice, '''CN''' for Credit Note.
+
::* the document's ID - '''CT2QB'''.
::* creation date - formatted as ''"dd-MMM-yyyy"''.
::* creation date - formatted as ''"dd-MMM-yyyy"''.
-
::* underscore sign as a separator between ID and date.
+
::* mark of state of current batch: is equal ''"imported"'' if batch was imported; is equal ''"not_imported"'' if batch was not imported.
-
:* ''Example:'' SI_100903.
+
:* ''Example:'' CT2QB_03-Sep-2010_imported.
-
* '''Description''' with range of included documents.
+
* '''Sales Invoices'''.
-
:* Shows '''count''' of documents in batch and '''range''':
+
:* Shows '''count''' of Sales Invoices (SI) in batch and '''range''':
-
::* number of the first document for the reporting day - number of the last document for the reporting day
+
::* number of the first SI for the reporting day - number of the last SI for the reporting day
:* ''Example:'' Count: 17, Range: US265 - US281.
:* ''Example:'' Count: 17, Range: US265 - US281.
-
* '''Total Amount''': Total of amounts of all CTs included to batch file.
+
* '''Total Amount''': Total of amounts of all '''SI'''s included to batch file (2 decimal places).
-
:* ''Example:'' - 1 050.00.
+
:* ''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 '''CN'''s 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''
* '''Imported''': Status of batch: ''new'' '''or''' ''imported''
-
:* Shown as a checkbox:
+
:* Shown as a check mark when a batch is imported into QB. Otherwise shown as a blank.
-
:** "checked" state of checkbox means ''imported'' status of batch,
+
-
:** "unchecked" state of checkbox means ''new'' batch which not imported yet into QB.
+
-
:* Should be set to "checked" by user manually after the successfull import of batch into QB.
+
=== Figures ===
=== Figures ===
-
==== Figure 1. QB dashboard panel ====
+
 
-
[[File:QB_dashboard_panel.gif]]
+
==== Figure 0. Enable QB File Import ====
-
==== Figure 2. QB panel for Sales Invoices ====
+
[[File:Edit Office window IIF check.gif]]
-
[[File:QBPanel_SI.gif]]
+
 
-
==== Figure 3. QB panel for Credit Notes ====
+
==== Figure 1. QB panel visibility checkbox ====
-
[[File:QBPanel_CN.gif]]
+
[[File:QB_panel_visibility_checkbox.gif]]
 +
 
 +
==== 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]]
 +
 
 +
==== Figure 3. List Of Invoices ====
 +
[[File:List of Invoices.gif | 950px]]
 +
 
 +
== History ==
 +
 
 +
* 0002589: [EDI to QB] (QB dashboard panel) bug: strange behaviour that possibly hangs the server

Current revision as of 15:18, 21 December 2010


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
Retrieved from "https://wiki.jaguarfreight.com/EDI_to_QB_for_Sales_Invoices"
Personal tools