A Update Customer Sales
This appendix contains these topics:
-
Section A.5, "Different Types of Batches Created by Sales Update,"
-
Section A.7, "Running Sales Update in Proof vs. Final Mode,"
This section offers a more in-depth technical description of the processes that take place during the Update Customer Sales program (Sales Update).
A.1 System Interfaces and File Updates
This sections describes system interfaces and file updates.
A.1.1 Accounts Receivable and General Accounting (F0311/F0911)
The Sales Order Invoice is a detailed list of all of the items that were sold to a customer. The Accounts Receivable Invoice is a record of the sales orders that relate to a customer. The accounts receivable entries may be created as either summarized or detailed. If the entry is detailed, then a one for one relationship exists between the lines on the invoice and the pay items in the accounts receivable file. If the information is summarized, then one line is created for each line type/due date combination.
General Ledger entries are created for the relief of inventory, cost of goods sold, and revenue. Additional entries may also be created for freight, handling, etc. Either the Branch/Plant constants setup or the Order Line Type setting for GL Interface determines whether or not a line interfaces with the General Ledger.
In the world of accounting, all entries need to be balanced. The entries that are created by Sales Update are not balanced when the accounts receivable interface is active. To create a balanced entry, an entry to the general ledger Accounts Receivable Trade account needs to be created. If the Accounts Receivable processing option (14) is blank (update) and the Order Line type has the A/R Interface set to Y, then an accounts receivable trade entry is created by the General Ledger Post program (P09800) using the Accounts Receivable 'RC' AAI which ties it to the Customer Master G/L Class Code. If the A/R interface is turned off (processing option 14 set to '1'), and the Order Line type has A/R Interface set to Y, then the accounts receivable trade entry is created by the Sales Update program and it uses the distribution AAI '4245'.
For further details see Section A.4, "Bypassing Updating A/R" in this appendix.
A.1.2 Inventory (F41021/F4111)
You can relieve on-hand inventory in the Item Location File (F41021) at either Ship Confirmation or Sales Update.
When relieving inventory at Ship Confirmation, a record is written to the Cardex (F4111) with the sales order document type and number but with no G/L date. Then the on-hand quantity in the Item Location File is updated. At sales update, the sales order document number and type are replaced by the invoice document number and type, and the G/L date assigned during sales update is populated. In order for the system to relieve inventory at Ship Confirmation, the document type has to be included in UDC table 40/IU.
When on-hand inventory is relieved at Sales Update, the Cardex is updated only once. The invoice number and type is written to the Document Number and Type fields on the Cardex and the sales order number is displayed in the Document Number field of the Item Ledger Information video (V4111W). The entry is created using the invoice type and number and includes the G/L date assigned the document during sales update.
A.1.3 Sales History (F4229/F4115)
The system stores historical data in two files, the Item History file (F4115) and the Sales Summary History file (F4229). Processing options in Sales Update determine whether or not they are updated. Information from these files is available in report or video format. Since both the report and the video are based on the comparison of a years worth of data to determine a trend, at least one year worth of history relating to an item must be available.
Information from either file is available via the following:
-
Sales Analysis Summary (P42611): The Item History file maintains information based on order type, line type, address book number, item number and business unit. This report compares sales, margin and percent information per item on a period to date and year to date basis.
-
Buyers Information (V4115): The Detailed Sales History file maintains sales information by item number and business unit. The Buyers Information video compares last year's sales to this year's sales and displays some additional information from the Item Location file.
-
You can also write reports using World Writer over the history files if you need additional information.
A.1.4 Commissions (F42005)
Information relating to commissions earned by sales people is written to the Commissions file only at Sales Update. This is due to the assumption that no commission is truly earned until the sale has completed its cycle. No Accounts Payable or General Ledger records are created for the accrued commissions. These entries must be created manually. Information stored in the Commissions file can be accessed through the Commission/Royalty inquiry (V42120) or through a user defined report.
A.1.5 Sales Order Detail History (F42119)
This file contains a one for one record of all of the lines processed and purged from F4211 by Sales Update when running with processing option 16 set to blank. It also contains records created by the Purge Details to History program - P42996 (Option 18 from menu G42312). This file can be periodically saved to backup media in order to reclaim disk space.
A.1.6 Sales Order Header History (F42019)
This file contains a one for one record of each of the order headers (F4201) that have been archived by Sales Update or by using the standalone purge program - P42992. This file can also be periodically saved to backup media in order to reclaim disk space. Processing option 17 in Sales Update determines whether this file is populated.
A.1.7 Tax File (F0018)
Records are not written to the Tax File until the general ledger transactions (F0911) are posted to the Account Balance file (F0902). Any entries that would be created in the Tax File are printed on the Sales Update report (R42800) without specific general ledger account numbers. Processing option 9 on the General Ledger Post program (P09800) dictates if and how any tax entries will be written to the General Ledger.
A.1.8 Sales Order Detail File (F4211)
By default, Sales Update will advance each order line processed to a next status of 999. By entering an alternative next status code in processing option 6, the user may allow for another process to be inserted after Sales Update. Once this process has completed it will be necessary for the custom interface to set the next status to 999 in order to close out the order line.
In addition to the next and last status codes, Sales Update also updates the general ledger date, the invoice number, invoice type and date (only when the Invoice Print program wasn't previously run).
A.1.9 Associated Text (F4314)
Text lines associated with an order line will be purged if processing option 15 is set appropriately. There is no text history file to archive to.
A.1.10 Price Adjustments History (F4074)
This file maintains a record of pricing that applies to specific lines on an order. If processing option 18 is set, then the records from this file are purged. There is no related history file to archive to. These records are created from set up in the Advanced Pricing Module (System 45).
A.2 Processing Order
Sales update processes all selected F4211 records in two or three major cycles.
A.2.1 First Cycle
If processing option 22 is set, the Update Sales Order Cost/Price program (P42950) will run and process all F4211 records.
A.2.2 Second Cycle
After all records have been processed by P42950, Sales Update will create records in the following files (in the order listed) by completely processing one F4211 at a time:
| Sequence | File | Description |
|---|---|---|
| 1 | F0311 | Accounts Receivable Ledger file (dependent upon processing option 14) |
| 2 | F0911 | Account Ledger file |
Updating of the F0911 is dependent upon two flags which must both be set in order for records to be created at the time of Sales Update.
-
The G/L interface flag in the order line type
-
The interface with G/L flag in the Branch/Plant Constants (this flag impacts the entries for inventory and COGS only)
A.2.3 Third Cycle
After all of the F0311 and F0911 records have been created, Sales Update will update or create records in the following files (in the order listed) by processing one F4211 at a time:
| Sequence | File | Description |
|---|---|---|
| 1 | F41021 | Item Location file
|
| 2 | F4111 | Item Ledger file |
| 3 | F4115 | Item History file (dependent upon processing option 14) |
| 4 | F4229 | Sales Summary History file (dependent upon processing option 14) |
| 5 | F42005 | Sales Commission file (dependent upon processing option 14) |
| 6 | F4314 | Text file (dependent upon processing option 15)
|
| 7 | F4211 | Sales Order Detail file
|
| 8 | F42199 | Sales Order Detail Ledger file
|
| 9 | F4074 | Price Adjustment History file (dependent upon processing option 18) |
| 10 | F42119 | Sales Order Detail History file (dependent upon processing option 16) |
| 11 | F42019 | Sales Order Header History (dependent upon processing option 17)
|
Note:
Records are not created in the Sales Tax File F0018 until the batch from the Sales Update program is Posted. Processing option 4 behind the General Ledger Post program P09800 determines if tax will be written to F0018, and if so, for which tax explanation codes. Valid values for the processing option are:'1' = V.A.T. or Use Tax
'2' = For All Tax Amounts
'3' = For All Tax Explanation Codes
blank = No Update to the File
A.3 Tax AAIs
Like all other transactions, the tax amounts on a sales order have offsetting debits and credits. The amount of tax the client owes to the company and has yet to be collected is added into the Accounts Receivable Trade (F0311) records. The offsetting amount will be written to a liability account specified by the company. The company records the tax it is charging and expecting to collect from customers, so that it can later remit those dollars to a taxing authority. Two AAIs write the offsetting credit, tax liability. One is an accounts receivable AAI (RT) and the other is from Distribution/Manufacturing (4250):
If the tax is PST (Provincial Sales Tax) or VAT (Value Added Tax) the amount is calculated at the time of the General Ledger Post (P09800) where the program uses the RT AAI to write the journal entry. Otherwise the Sales Update will use the Distribution Manufacturing Tax Liability AAI 4250.
Note:
Even if the RT AAI is not being used in a transaction, during Sales Update, it will be validated. If it is missing or the account is invalid, an error message will be generated.A.4 Bypassing Updating A/R
You can bypass updating A/R by setting processing option 14 on Sales Update. The purpose is to restrict the system from creating F0311 records. F0911 records will still be created to make the journal entries balance.
The system uses the 4245 AAI for the A/R entry when bypassing A/R. The F0911 record will be created at the time of Sales Update. When not bypassing A/R, the system uses the RC AAI for the A/R entry. The F0911 record is not created until the sales update I-batch is posted.
You cannot bypass A/R by setting the A/R flag in the order line type to N. That tries to create a 3 way journal entry (JE) that will not post because it will be out of balance.
A.5 Different Types of Batches Created by Sales Update
Depending on processing option settings, Sales Update can produce four different batch types.
Running sales update in detail (not summarizing Inventory and COGS to separate batch):
-
All entries appear in the I-batch.
-
Cardex records have RI document type.
Running sales update summarizing Inventory and COGS to separate batches (Processing option 12 = 1):
-
Sales entries appear in the I-batch.
-
Inventory and COGS appear in the G-batch.
-
Cardex entries have JE document type.
Running interbranch orders through sales update:
-
All "regular" entries (sales, inventory, COGS, and tax) appear in the I-batch.
If you are not creating A/R and A/P batches (processing option 26 is blank), interbranch settlement entries appear in the ST batch.
-
If you are creating A/R and A/P batches (processing option 26 is 1) the system creates a V batch.
A.6 Running Sales Update in Summary vs. Detail
With processing options 10, 11, and 12 behind sales update, you can summarize Accounts Receivable entries, General Ledger entries, and/or summarize Inventory and COGS entries to a different batch.
A.6.1 Accounts Receivable Entries
This section describes accounts receivable entries.
A.6.1.1 Detail
Creates a separate F0311 record for each record in the F4211 with a particular invoice number. The pay item of the first record is 000, with each subsequent line's pay item being incremented by 001.
A.6.1.2 Summary
Summarizes F4211 records with a particular invoice number into one pay item within the F0311 by the following fields:
KCO - Document Key Company
DOC - Document Number
DCT - Document Type
CO - Company
TXA1 - Tax Area
EXR1 - Tax Explanation Code
PTC - Payment Terms
ITM - Item Number if the same is specified within the tax rate and area setup.
If the value for individual F4211 records in any of these eight fields is different, those records will not summarize.
-
Tax Explanation Code and Tax Area
Differences in these fields prohibit summarization because the system does not allow a pay item to have more than one Tax Explanation Code nor more than one Tax Area associated with an A/R pay item. The 'taxable' flag may also prohibit summarization in a particular case. If two lines share all seven fields, including the Tax Explanation Code and Tax Area, but one is taxable and the other is not, two different pay items are created on the A/R side if these lines are being taxed. In this case, the system creates two pay items, one with the full amount taxable and one that is not taxable.
-
Tax Explanation Code and Tax Area and Vertex
If you are using Vertex the Tax Area is a Vertex GeoCode. If you have TDM's set up, lines may not summarize even with the same GeoCode, because with TDM setup, there is the possibility of a different tax rate for each line.
-
Payment Terms and Discount Due Date
F4211 records with the same Payment Terms will not summarize if the calculated Due Dates or Discount Due Dates are different. For example, payment terms do not affect the due date of a credit line; credit lines have the order date as the due date. There is a processing option on the XT0311Z1 server that allows for the computation of due dates on credit lines. For another example, if the extended amount is so small that the calculated discount is less than zero, therefore not creating a discount amount, then the discount due date is the same as the due date for this F4211 record. The key used for summarization includes the discount and net due dates which are calculated based on payment terms. The program will fail to summarize lines if the system calculates different discount due dates, using discount days (DCD).
A.7 Running Sales Update in Proof vs. Final Mode
Processing option 13 controls whether sales update is run in proof or final mode.
A.7.1 Proof Mode
Use proof mode if you want to view what the journal entries will look like and to see what errors may occur. You generate both an Invoice Journal (P42800) and an Error Report (which may not be complete) and, if specified in the processing option, a Sales Journal (P42810). Proof mode does not perform updates to status codes or any files.
A.7.2 Final Mode
Run final mode and you generate an Invoice Journal (P42800), an Error Report (P42801), and a Sales Journal if selected in the processing option. Sales Update updates status codes and files and performs edits against the G/L functional server XT0911Z1 and the A/R functional server XT0311Z1 (checks for duplicate records, etc.).
A.8 Updates to Quantity On-Hand and the Cardex
You may decrement on-hand quantity for an item either at the time of Ship Confirmation (P4205) or at the time of Sales Update. The effect on the Cardex (F4111) differs depending on the method chosen.
A.8.1 Ship Confirmation
To relieve inventory at Ship Confirmation, set up the sales order document type in the 40/IU UDC table. A record with the order number and order document type will be written to the Cardex at the time of Shipment Confirmation. When Sales Update is run it will be overwritten by the invoice number and invoice document type and the G/L date will be populated.
A.8.2 Sales Update
Make certain the order document type is not in the UDC table 40/IU. Sales Update is hard-coded to decrement on-hand quantity if it is not done at Shipment Confirmation. A record is written to the Cardex at the time of Sales Update, with document type RI if the program has been set to not summarize inventory and COGS to a separate batch. If the option to summarize inventory and COGS to a separate batch has been selected the document type will be JE. The G/L date will also be populated. No Cardex records will be written at the time of Ship Confirmation. Also, the invoice number is populated in F4211, but the invoice date is not.
A.9 Versions of Sales Update
There are two versions of Sales Update, one for sales orders that have been invoiced, and one for sales that have not. Each version can be run in either proof mode or final mode. It is very important that the correct version is used. There are two major differences between the versions, one is the data selection and the other is the data sequencing. Although users can and do change the data selection, changing the data sequencing is not advised for either of these Dream Writer versions.
A.9.1 Sales Update - (Proof or Final)
Use these versions when the sales order has been run through Invoice Print (P42565) and it contains a document number (DOC) and type (DCT) in F4211. The data selection for this version is based on Invoice NE *BLANKS. This means that the DREAM Writer will only process records in the Sales Order Detail file (F4211) that have a value in the invoice number field (SDDOC).
Sequencing must be:
001 - Document Number (DOC)
002 - Document Type (DCT)
003 - Document Company (KCO)
A.9.2 Sales Update - Assign Invoice No. - (Proof or Final)
Use these versions when the sales order has not been run through Invoice Print (P42565) and it does not contain a document number (DOC) and type (DCT) in F4211. You may select a next number bucket other than 01 for the invoice (processing option 20), and you may choose a document type other than RI. (processing option 21). The value must be set up in UDC 00/DI.
The Data Selection for this version is *ALL for all fields except Invoice Date. That field is NE* ZEROS. (Any item that is backordered and included on an invoice will have an invoice date associated with it but no invoice number - the invoice number is only assigned once the item is truly shipped and invoiced).
Sequencing must remain:
001 - Order Number (DOCO)
002 - Order Type (DCTO)
003 - Order Number Document Company (KCOO)
A.9.3 How Can I Tell If An Invoice Number Has Been Assigned
If you do not know if an invoice has been created, inquire on the order using On-line Invoice (menu G2112, option 3). If an invoice has been printed, the inquiry will return a value to the Invoice Number and Type fields. If no invoice number has been printed, those fields will remain blank.
Customer Service Inquiry (menu G42112, option 2) will also display the information. When the order appears in the detail portion of the screen, enter a '5' in the option field next to the line in question. This will display an 'Order Detail Information' screen. The invoice number and type are located in the lower right hand portion of the screen in a field titled 'Invoice'.
A.10 Purging Records by Running Sales Update
When running Sales Update there is the option of purging records from various files. If the processing options are set correctly, and the data meets purge criteria, records in the following files will be purged:
-
Price Adjustment History File (F4074)
-
Sales Order Detail File (F4211)
-
Sales Order Header File (F4201)
-
Text Detail File (F4314)
The Sales Order Detail file records and the Sales Order Header file records will be written to the files F42119 and F42019 respectively. In A8.1, F42119 and F42019 exist, but they are not flexible files. Data in the Text Detail file and the Price Adjustment History file will be deleted and not archived.
If you have not set the options to purge during sales update and later want to purge records from one of the above files, purge programs can be executed on an as needed basis. The decision about when to purge can be based on multiple criteria such as how much storage space is available or what type of information need to be readily available.
Note:
Even when the processing options are set correctly, records that have reached a status of 999 prior to running Sales Update will not be included in the programmatic purge. Examples of these records would be lines canceled during commitment processing, or ship confirmation. The reason for this is that the Sales Update program is coded to only purge records whose status is changed to 999 via P42800.A.11 Can Sales Update be re-run?
Four of the most common reasons why a request would be made to re-run Sales Update are:
-
The wrong G/L or Invoice Date was attached to the records
-
Some records were not included in the update
-
Data was erroneously purged and needs to be restored
-
Records were not purged
A.11.1 Change G/L or Invoice Dates
You may have run the Sales Update program at the beginning of a new period (for example July 2) when you meant to book all of the transactions to the previous period (for example June 29). There is no programmatic way to reverse the results of Sales Update. The easiest method of recovery is to create a correcting journal entry that moves summary dollars from one period to another.
If you are determined to re-run the Sales Update, you will need to restore the files to the condition they were in prior to running Sales Update. For most clients, this means restoring the most recent backup tape. All of the data entered prior to when the tape was created will be available. However, you will need to re-enter all of the data processed after the tape was created. This can include (but is not limited to) sales, purchasing, accounts payable, and general ledger information. This can involve a large amount of time.
You may want to know if you can inquire on the transactions that have been created and 'just' change the appropriate dates. Because the general ledger date is a key to the financials files, the programs will not allow the date to be changed. Even if the date could be changed, there are associated fields that do not display on-line. When these fields are not in sync, you will have problems processing transactions.
The invoice date is also a key to the accounts receivable file and it cannot be changed manually.
A.11.2 Records Not Included in Update
If records were not included in the running of the original update, create a version of the program that will select just the records in question. It is strongly advised that the program be run in proof mode to confirm the selection of the records. Once the proof is acceptable, run it in final mode.
A.11.3 Data Purged Erroneously
Processing options behind the Sales Update program dictate what information will be purged once a record has been successfully processed. Short of restoring all files to some point prior to running Sales Update and then completing the process again, there is no way to easily restore data.
A.11.4 Data Not Purged
If records were not purged due to the setup of the processing options, re-running the Sales Update is not the appropriate solution. Use the specific purge programs in the required order to remove data from the files in question. The P00PURGE program will take care of the Sales Order Detail file (F4211), Sales Order Detail History file (F42119) or Sales Order Ledger file (F42199). Additional purge programs exist for the Sales Order Header file (F4201), Sales Order Text Lines file (F4211), Extended Text file (F4314) and the Batch Order Files (F4001Z and F4011Z). There is also a program that will move the sales order detail to history (F4211 to F42119).