EDI to QB for Sales Invoices
From UG
(→Columns Definitions: changed the description of the indicator of non-imported documents (one number)) |
|||
| (One intermediate revision 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 == | + | |
| + | == Requirements == | ||
| + | |||
| + | === Intro === | ||
'''Core need:''' To eliminate double entry of Sales Invoices into 2 systems at Jaguar: | '''Core need:''' To eliminate double entry of Sales Invoices into 2 systems at Jaguar: | ||
| Line 13: | Line 16: | ||
Instead we suggest entering once into CT2 and then periodically upload into QB. | Instead we suggest entering once into CT2 and then periodically upload into QB. | ||
| - | === Example === | + | ==== Example ==== |
See Figures below. | See Figures below. | ||
| Line 29: | Line 32: | ||
[[File:Si.JPG]] | [[File:Si.JPG]] | ||
| - | |||
=== Forward in batches === | === Forward in batches === | ||
| Line 162: | Line 164: | ||
==== Figure 3. List Of Invoices ==== | ==== Figure 3. List Of Invoices ==== | ||
[[File:List of Invoices.gif | 950px]] | [[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.
[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
| 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.
- 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.
- 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
