EDI to QB for Sales Invoices
From UG
Contents |
Parent Mantis
http://mantis.jaguarfreight.com/mantis/view.php?id=2338
Intro
Core requirement: To eliminate double entry of Sales Invoices into 2 systems at Jaguar:
- CT2 transportation system
- QuickBooks accounting system
Example
See Figures below.
Figure 1 CT2
Invoice as it is entered in CT2.
Figure 2 QB
Invoice as it is entered in Quickbooks.
Requirements
Forward in batches
All sales invoices/credit notes entered in CT2 would be forwarded to QB in batches (bundles).
Frequency
Initially once a day at the end of the day.
Review process
Before batch is populated into QB accounting operators should have a chance for "30 sec review".
Gaps in invoice numbers
First inv# in new batch should be next number after last one in previous.
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 |
Systems Design
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 bunch 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 from right.
- Suggested daily time of the bunch creating is 3 p.m. (Time Zone defines by local office time).
- 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 bunch 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.
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 #QB document table.
Columns Definitions
- Document Name: String "Sales Invoice / Credit Note", because the upload bunch file should include info of both of these documents.
- Not imported: Number of new documents to import. Here is indicate the values for Sales Invoices and Credit Notes separately.
QB document table
QB document table should represent list of all available bunch IIF-files. The table 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 3.
Columns Definitions
- Date: Date of bunch as Datatypes#CT2 date.
- Document table 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 bunch: is equal "imported" if bunch was imported; is equal "not_imported" if bunch was not imported.
- Example: CT2QB_03-Sep-2010_imported.
- Sales Invoices.
- Shows count of Sales Invoices (SI) in bunch 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 bunch 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 bunch 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 bunch 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 bunch: new or imported
- Shown as a checkbox:
- "checked" state of checkbox means imported status of bunch,
- "unchecked" state of checkbox means new bunch which not imported yet into QB.
- Should be set to "checked" by user manually after the successfull import of bunch into QB.
- Action of "unchecking" should be warned by System and surely should be confirmed by user.
- Shown as a checkbox:
Figures
Figure 0. Enable IIF-file creation
Figure 1. QB panel visibility checkbox
Figure 2. QB dashboard panel
a. No documents to import into QB:
b. There are documents to import into QB:
Figure 3. List of Invoices
