EDI to QB for Sales Invoices
From UG
(Difference between revisions)
(→Figures: added Figures 2 and 3) |
(→QB dashboard panel) |
||
| Line 72: | Line 72: | ||
=== Summary === | === Summary === | ||
| - | QB dashboard panel is a part of CT2 dashboard that located on the homepage of CT2 Internal Application. | + | QB dashboard panel is a part of CT2 dashboard that is located on the homepage of CT2 Internal Application. |
| - | This panel is designed for daily transmission of accounting information from CT2 to the QuickBooks accounting system in IIF-format. | + | This panel is designed for: |
| + | :1) indicating of new accounting documents in CT2 ('''''should be imported''' to the QuickBooks accounting system''). | ||
| + | :2) daily transmission of accounting information from CT2 to 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 batch file and should contain an information about all account documents that were created in CT2 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.'' | : NOTE: ''Unapproved Credit Notes should not be part of the upload.'' | ||
| - | |||
* A daily time of creating of the batch is 6 p.m. (NY Time Zone = EDT). | * A daily time of creating of the batch is 6 p.m. (NY Time Zone = EDT). | ||
: NOTE: ''It would be desirable to be able to set this value in the Admin's part of CT2.'' | : NOTE: ''It would be desirable to be able to set this value in the Admin's part of CT2.'' | ||
| + | * Each IIF-file can be downloaded from QB panel on user's computer for its further transmission into QuickBooks. | ||
=== Systems Design === | === Systems Design === | ||
| - | |||
| - | See on [[#Figure 1. QB dashboard panel | Figure 1]]. | + | ==== 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]]. | ||
| + | |||
| + | 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]]. | ||
| + | |||
| + | ===== Columns Definitions ===== | ||
| + | * '''Document Name''': Type of document. | ||
| + | * '''Not imported''': Number of new documents of this type. | ||
| + | |||
| + | ==== QB document table ==== | ||
| + | |||
| + | Each document table corresponds to one type of document and should represent list of all available batch IIF-files by this type. | ||
| + | |||
| + | 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]]. | ||
| + | |||
| + | ===== Columns Definitions ===== | ||
| - | + | * '''Date''': Date of batch as [[Datatypes#CT2 date]]. | |
| - | * '''Date''' | + | |
| - | : | + | |
:* Table of batches should be sorted by this column in descending order | :* Table of batches should be sorted by this column in descending order | ||
:* ''Example:'' 03-Sep-2010. | :* ''Example:'' 03-Sep-2010. | ||
| - | * '''Filename''' | + | * '''Filename''': Name of downloadable IIF-file. At the same time it is also a link to downloading. |
| - | : | + | |
:* Define type of documents that included to file and also its creation date. The output string has the following format: | :* Define type of documents that included to file and also its creation date. The output string has the following format: | ||
::* the document's ID - '''SI''' for Sales Invoice, '''CN''' for Credit Note. | ::* the document's ID - '''SI''' for Sales Invoice, '''CN''' for Credit Note. | ||
| Line 101: | Line 114: | ||
::* underscore sign as a separator between ID and date. | ::* underscore sign as a separator between ID and date. | ||
:* ''Example:'' SI_100903. | :* ''Example:'' SI_100903. | ||
| - | * '''Description''' with range of included documents | + | * '''Description''' with range of included documents. |
:* Shows '''count''' of documents in batch and '''range''': | :* Shows '''count''' of documents 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 document for the reporting day - number of the last document 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. |
| - | :* Status of batch: ''new'' '''or''' ''imported'' | + | :* ''Example:'' - 1 050.00. |
| + | * '''Imported''': Status of batch: ''new'' '''or''' ''imported'' | ||
:* Shown as a checkbox: | :* Shown as a checkbox: | ||
:** "checked" state of checkbox means ''imported'' status of batch, | :** "checked" state of checkbox means ''imported'' status of batch, | ||
Revision as of 11:59, 9 September 2010
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# |
| Amount | Amount |
| Total | Total invoice |
QB dashboard panel
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 to the QuickBooks accounting system).
- 2) daily transmission of accounting information from CT2 to 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 should be created automatically and it should contain an information about all account documents that were created in CT2 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.
- A daily time of creating of the batch is 6 p.m. (NY Time Zone = EDT).
- NOTE: It would be desirable to be able to set this value in the Admin's part of CT2.
- Each IIF-file can be downloaded from QB panel on user's computer for its further transmission into QuickBooks.
Systems Design
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.
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.
Columns Definitions
- Document Name: Type of document.
- Not imported: Number of new documents of this type.
QB document table
Each document table corresponds to one type of document and should represent list of all available batch IIF-files by this type.
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 and Figure 3.
Columns Definitions
- Date: Date of batch as Datatypes#CT2 date.
- Table of batches 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 type of documents that included to file and also its creation date. The output string has the following format:
- the document's ID - SI for Sales Invoice, CN for Credit Note.
- creation date - 6 digits in format "yyMMdd".
- underscore sign as a separator between ID and date.
- Example: SI_100903.
- Description with range of included documents.
- Shows count of documents in batch and range:
- number of the first document for the reporting day - number of the last document for the reporting day
- Example: Count: 17, Range: US265 - US281.
- Total Amount: Total of amounts of all CTs included to batch file.
- Example: - 1 050.00.
- Imported: Status of batch: new or imported
- Shown as a checkbox:
- "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.
- Shown as a checkbox:
Figures
Figure 1. QB dashboard panel
Figure 2. QB panel for Sales Invoices
Figure 3. QB panel for Credit Notes
