INTRODUCTION TO UTILITIES
This Chapter describes various utilities and functionality available in Compiere. Some of this functionality will be used rarely, but it eases the transition from existing applications. Other functionality will be used frequently and is not logically included in other chapters.
Compiere provides the ability to import records using a comma delimited, tab delimited or fixed format file. This may be done at implementation time to bring in all of your Business Partners from an existing application. It may also be used on a regular basis to import Invoices, Orders, Journals or Products.
Records can be imported for single table records (e.g. Conversion Rates) directly to the table. More complex relationships (e.g. Orders, Business Partners and Products) require that the records be loaded to an intermediate table where they are then imported. Imported records are bound by all of the database and program logic constraints defined. This ensures that bad or corrupt data is not imported.
To perform a data import you must complete the following steps:
- Define the Import Loader Format
- Define the Import File
- Load the File
- Verify the data
- Process the Import
Compiere currently supports Import for the following entities.
- Business Partner
- GL Journal
- Report Line
- Currency Rate
- Bank Statement
To create, change or delete an Import Format, open the Import Format window by clicking on Import Format from the Main Menu.
Enter the Name and the Description of the Import Format. You can indicate whether you want your Import Format to be Active.
Select a Table from the drop down list. This identifies the intermediate table into which the file will be loaded.
Select the Format of the file to be imported. Supported types are Comma Separated, Tab Separated, and Fixed Format.
Select the Copy Lines button if there is an existing Format you want to copy from. After selecting the button you will be prompted to select an existing Import Format.
Click on the Format Field tab to enter the Fields for this Import Format
The grid view displays all of the fields for this Import Format. To create, modify or delete a Format Field click the 'Edit Record' button.
The Format Field tab defines the fields, their order, position, and data type. It also allows you to perform data conversion.
Enter a Sequence. This defines the display order of the Format Field records.
Enter a Name for this field and select the Active check box.
Select a Column value from the drop down list box. This list contains the columns that may be imported for the table entered on the Import Format header.
Select a Data Type of Constant, Date, Number, or String. The window will change dynamically based on your selection.
If you select Constant, the Constant field will display. Enter the value you want to use to populate this column for each imported row.
If you select Date, the Date Format field displays. Enter the format of the date in the file to be imported.
If you select Number, the Decimal Point field and Divide by 100 check box displays. Enter the value used (if any) for the decimal point in the file to be imported. Select the Divide by 100 check box if you want the number in the file to be divided by 100 prior to being loaded.
If you select String there is no additional data required.
The Start No indicates the starting position for this field in the file. If the Format of the file is Comma or Tab Separated then this field represents the position of the field in the file (e.g. 1 being the first field, 3 being the third field). If the Format of the file is Fixed Position then you will enter a Start No and End No for each field. This allows you to use the same field in the file for more than one field in the import table (e.g. same value used for Key and Name of Business Partner)
Note: When the Data Type is Constant the Start and End No fields do not display.
The Callout field allows you to define, in Java, data conversion rules for this field.
Script is not currently supported.
Import File Loader
The Import File Loader defines the File to be loaded and the Format to be used.
To Import a file open the Import File Loader window by clicking on Import File Loader from the main menu. The icon in front of the Import File Loader menu item indicates that it is a window.
Click the Select File to Load button. The default location is Compiere2/Data/Import but you can navigate to anywhere on your network.
Select an Import Format from the drop down list box of defined formats.
When you have selected a File and Format the window displays the data to be loaded and additional information.
The top of the window displays the number of records on the file and their length. The window also displays the actual data to be imported.
Clicking on the Next Record button will display the first record, mapped to the Import Format
This allows you to verify the mapping of the fields in the file to the Import Format prior to importing the records.
If you are happy with this data click the Process button to start the import.
When a file is loaded it is loaded into an intermediate table (the Table Name defined in the Import Loader Format). You can review and change the data in this table prior to importing it.
To view, change, or delete loaded records open the appropriate Import window from Main Menu, (e.g.Import Business Partner.) The icon in front of the Import Business Partner menu item indicates that it is a window.
All of the fields on this window are found on one of the tabs of the Business Partner window.
For more information on Business Partners, refer to the Business Partners Chapter.
Update any fields as necessary.
If you are happy with the data select the Import Business Partners button.
Select the Tenant the imported records are for and select the Delete old imported records check box if you want records that were imported in previous processes to be deleted.
Select the OK button to start the import process.
When the Import is completed, a corresponding dialog message window will display.
The dialog message window indicates the number of errors and records inserted and updated.
If any errors were reported, return to the appropriate Import window (in our example Import Business Partner) to view the error.
An error in the Import would enter a message in the Import Error Message field. You can correct the problem in this window or if it is an error in the file you can correct the file and re-load it.
Because this record was not in error the 'Imported' check box is selected. This prevents the system from attempting to import it a second time.
Import Hints and Tips
- Test new formats with a small number of records.
- Set all Y/N fields explicitly (using constants in the Import Loader Format if necessary). A NULL value indicates 'Don't Know'. In general, selection boxes are not selected if the value is not "Y" (i.e. could be null or any other value). All selection tests are made on "Y" or "not Y" (i.e. NULL values are not "not Y", but NULL (don't know). Refer to SQL Language introductions for more details.
- When importing Orders or Invoices you must define a Document Type Name or Document Type ID. The name must be identical to the name defined in Document Type (case sensitive). You can determine the Document Type ID by opening the Document Type window, select the desired Document Type and click on the lower right hand corner of the screen (where the record count displays). An info dialog will display and it will show the record id for that record.
- If you import ID's they are assumed to be correct. No validation is done during the import process.
- The best way to set the Document Type Name is as a Constant in your Import File Format or as an imported field in the file.
- If you are using Constants for field values (like Document Type) then you should have a separate Import File for each Document Type. You can define unique File Loader Formats by copying the lines and changing the Document Type field constant value
- If you have loaded a large file that contains errors, you can delete all of the loaded records by doing a DELETE in SQL against the import table (e.g. I_BPartner) Make sure that the table is prefixed with an 'I'. This is a unique instance where SQL can be performed against Compiere tables.
There may be times when there is a need to combine two entities. Perhaps a duplicate product was entered with a slightly different name, or two of your Business Partners have joined forces. In this case it may be desirable to move all of the supporting records from one entity to the other. Merge Entities provides this functionality.
You can "Merge" the following entities:
- Business Partner
To Merge Entities open the Merge Entities window by clicking on Merge Entities from the Main Menu. The icon in front of the Merge Entities menu item indicates that it is a window.
This window defines the Merge from Entity and the Merge to Entity.
Simply select the desired values for the Merge From and Merge To fields.
In this example, someone set up a new product 'Azaleas' when 'Azalea Bush' already existed. The two entities will be merged. Select the OK button.
A corresponding confirmation message will display.
If you want to continue with the Merge select the OK button.
If there were any errors during the Merge process the Merge would roll back and the errors would appear here.
Merge Hints and Tips
- Always back up your database before performing a Merge. There is no undo or rollback.
- Shut down the Application Server and make sure no other users are on the system. The Application Server caches data which you may be deleting
- Any inventory for the Merge from Product must be moved prior to the Merge. A Product with inventory cannot be deleted.
- If a Product has a shipment record it cannot be deleted.
- If Business Partners are merged any Locations and Contact/Users for the Merge from Business Partner are added to the Merge to Business Partner.
- There are no accounting consequences done during the Merge. You can reset the accounting at a later time but that should be done only after analyzing and determining the desired end result.
There may be times when Compiere is unable to Post a document. Most often this is caused by a Period that has not been opened. When this occurs the Posted button on the Document window will display the error message.
A Notice will also be generated. You can open Notices by clicking on the Notice button at the bottom of the Menu.
The Notice window will appear:
- The User/Contact displays the User who is the owner of this document.
- The Table indicates the table that incurred the error.
- The Reference identifies the specific document.
- The Text displays the error message along with a suggested remedy.
Once the problem has been corrected (in this example the Period has been Opened) you must resubmit the posting. The Accounting Engine will not automatically try to re post documents that generated an error.
To Resubmit Posting, open the Resubmit Posting window by clicking on Resubmit Posting from the Main Menu.
Select the Start button to update the status of the document so the Accounting Engine will process them.
A parameter window will display.
You must select a Tenant and you may select a specific Table if desired. If the Table is left blank it will process for all tables.
When the process has completed a message dialog is displayed indicating the number of records updated and deleted.
When the Accounting Engine is run, it will now select these documents and attempt to post them.
Often there are transactions that occur on a regular basis and usually for the same amount. Examples of these may be Maintenance Invoices, Rental, or Lease fees.
Compiere provides the functionality to copy or recur and existing GL Journal, Project, Invoice, or Order. You define the frequency and the number of times it should process.
To create or change a Recurring Document, open the Recurring window by clicking on Recurring from the Main Menu.
Enter a Name, a Description, and Help/Comment if you like.
Select the Active check box to indicate that this entity is active.
Select a Frequency Type of Daily, Weekly, Monthly, or Quarterly and a Frequency to define how often the document should recur. For example, if you select a Frequency Type of 'Monthly' and a Frequency of 2 the Frequency is every 2 months.
Select the Maximum Runs for this recurring document. Based on the Frequency Type and Frequency the Date next run displays the next time the process will run. This can be changed if desired.
Select the Recurring Type of GL Journal, Project, Order, or Invoice and then identify the existing document to recur (in our example an invoice)
The History section displays the Date last run for this Recurring Document and the Remaining Runs.
Select the Start Process button to create the document.
When the process has completed a new document (in our example an invoice) has been created. It is a copy of the original document with the exception of the document no and date. You can modify the document if necessary and then 'Complete' it.
The Recurring window is updated to reflect the fact that the process has been run.
The Date next run field is updated to next month.
The Date last run field is set to the current date and the Remaining Runs field displays 9.
Click on the Runs tab to view a history of all runs for this Recurring document.
This display only tab indicates the date Created, the Document Date and the document created (in our example an Invoice). There will be one entry for each recurring run.
There are different costs that are incurred or billed to a customer. In Compiere they are called Charges. A charge may be a shipping fee, a bank fee or a handling fee for example. A charge is associated with a document (e.g. Invoice). Simply put, a Charge identifies the GL accounts to book the amount to.
To define a Charge you use the Generate Charges window. You then assign a tax category and amount in the Charge window. You can also create your charges manually in the Charge window.
The Generate Charges window associates a Charge with a natural GL Account.
To create Charges open the Generate Charges window by clicking on Generate Charges from the Main Menu.
This window displays all of your income and expense accounts for the Tenant's Account Schema.
Enter a Key and optionally a Description. Select the Expense check box if this charge will be an expense.
Select the Account to create the charge from and click the Create button.
When the process has completed the following message dialog will be displayed.
The next step is to further define the Charge.
To create, modify or delete Charges, open the Charge window by clicking on Charge from the Main Menu.
The Name is the Name that was entered on the Generate Charges window. You can enter a Description if you like.
Select the Active check box so this charge may be used in documents.
Enter a Charge amount if you applicable. This is the amount that will default when this Charge is used in a document. It can be changed in the document.
Enter the Tax Category that this Charge should use. If you have selected the Same Tax check box, the charge will use the same tax as the main document.
The Price includes tax option is not available at this time.
Select a Business Partner associated with this Charge if this Charge will be used in Requisitions.
Click the Accounting tab to view the accounting information for this charge.
The Generate Charges process creates the Charge and uses the GL Account selected for both the revenue and expense. You may define separate accounts for each if you like. Click the Charge Expense or Charge Revenue field to display the Accounting Info window. From here you can select a different account to be used.
Once the charges have been defined they can be used in Documents.
In an Invoice (Vendor) you can select either a Product or a Charge for each line.
Note: Charges may also be used in Requisitions, Purchase Orders, Sales Orders, Customer Invoices, and Payments.
BILL OF MATERIALS
Use Bill of Materials (BOM) to specify the composition of a product. To enter BOM and BOM Components, the product needs to have the BOM checked. Once the BOM setup is complete it needs to be successfully verified before you can use that product.
BOM Type and BOM Use
The valid BOM Types are Current Active, Future, Maintenance, Previous, Repair, and Spare.
The valid BOM Uses are Master and Manufacturing.
Multiple Bill of Materials
It is possible to define multiple BOMs for a product. You can only have one active BOM with a BOM Type 'Current Active' and BOM Use 'Master.' Unless specified, the Current Active, Master BOM is used by default.
Bill of Materials Components
The Bill of Materials Component list is used to define the components and their quantity that make up a product. You can define mandatory, optional and alternate components by using Component Type of Standard, Optional and Alternative respectively.
In Compiere Manufacturing, users can additionally define a Component BOM. The Component BOM can be optionally specified if the component itself is a BOM product. If the Component BOM is not specified, Compiere will try to derive it by looking for a BOM with the same BOM Type and BOM Use as the parent product.
Bill of Materials Alternative Groups
In Compiere, you can define your own alternative groups and use them when defining Bill of Materials. To define new alternative groups, open the Product BOM Alternative Group window from the Product Setup submenu of the Main Menu.
Alternative Groups are generic though you may optionally restrict alternative groups to a product. If you specify a product when defining an alternative group, you can only use that group in BOM setups for that specific product. If you do not specify a product when defining an alternative group, that group may be used in BOM definitions for any product.
Bill of Materials Validation
The Verify BOMs process can be invoked directly from the menu, or by clicking the Verify BOM button in the Product tab of the Product window. BOM verification will ensure that the BOM does not contain recursive relationships and that at least one active BOM and BOM component is defined. A Product needs to have its BOMs verified before they are made available for use. A Product will be invalidated if certain changes are made to its BOMs or BOM Components.
Multi-Level Bill of Materials
It is possible to create multiple levels in the BOM by including components that are BOMs themselves. All Compiere standard BOM-related functionality assumes that you are working with a single active, Current Active, Master BOM, at all BOM levels.
In Compiere Manufacturing, it is possible to select a specific BOM with which to work. If a component is a BOM itself and has a Component BOM specified, Compiere Manufacturing will use it. If the Component BOM is not specified, Compiere Manufacturing will derive it using the BOM Type and BOM Use of the parent product to find a matching BOM for the component.
BOM Explode is the ability to insert the exploded bill of materials structure into a target document (e.g. Sales Order, Work Order, Project, etc.) All levels in the BOM are recursively exploded so long as the BOM Component is an unstocked product. You can indicate if Optional items of the BOM are to be included as well as select the desired item from an Alternative Group. There are some restrictions on multi-level explosion if the BOM Component is marked optional or alternative.
To initiate the BOM Explode function, open the BOM Explode window by clicking on BOM Explode from the Main Menu. The icon in front of the BOM Explode menu item indicates that it is a window.
Select the document that you want to work with and then a BOM product.
If you select a Work Order, the product will be defaulted from the Work Order. The individual components that comprise the BOM product are displayed.
Optionally update the quantity. Make your selection of optional and alternative components and modify individual quantities if you so choose.
Then click the Process button to create one line for each component in the target document.
For more information on defining Bill of Materials, refer to the Products Chapter.
MODIFYING THE OWNING ORGANIZATION
There may be instances when it is necessary to change the Organization that owns a particular record or group of records. This may be due to an error in data entry or a change in a business' structure. Compiere provides three utility processes which allow you to change the Organization that owns a Warehouse, Business Partner or Product.
To change the Organization that owns a Warehouse, open the Warehouse Organization window by clicking on Warehouse Organization from the Main Menu.
The Warehouse Organization process will change the ownership of a Warehouse, its Locators, Storage, and Accounting Schema. It can also be used to verify the ownership of a Warehouse and its dependent entities.
Select the OK button to start the process. A corresponding parameter dialog window will display.
Select an Organization and the Warehouse that you want to be owned by this Organization or the Warehouse that you want to verify for this Organization.
Select the OK button to complete the process.
A corresponding dialog message window is displayed.
The dialog message window indicates the number of Warehouses, Accounting Schema, Locators, and Storage records that have been modified. These are now owned by the new Organization selected.
In addition, all accounting transactions associated with this Warehouse refer to the new Organization.
Business Partner Organization
To change the Organization that owns a Business Partner open the Business Partner Organization window by clicking on Business Partner Organization window from the Main Menu.
The Business Partner Organization process will change the ownership of a Business Partner and its Locations, Contacts, Bank Account, and Withholding entities. It can also be used to verify a Business Partner's ownership. You can change the ownership for a single Business Partner or for all Business Partners in a specific Business Partner Group.
Select the OK button to start the process. A corresponding parameter dialog window will display.
Select an Organization and the Business Partner Group or Business Partner that you want to be owned by this Organization or the Business Partner Group or Business Partner that you want to verify for this Organization.
Select the OK button to complete the process. A corresponding dialog message window is displayed.
The dialog message window indicates the number of Business Partners, Accounting Schema, Address, User/Contact, and Bank Account records that have been modified. These are now owned by the new Organization selected.In addition, all accounting transactions associated with this Business Partner(s) refer to the new Organization.
To change the Organization that owns a Product, open the Product Organization window by clicking on Product Organization from the Main Menu.
The Product Organization process will change the ownership of a Product, its BOM, Substitute, Replenish, Purchasing, Business Partner Info, and Costing. It can also be used to verify the ownership of a Product and its dependant entities.
Select the OK button to start the process. The following parameter dialog is displayed.
Select an Organization and the Product Category or Product that you want to be owned by this Organization or the Product Category or Product that you want to verify for this Organization.
Select the OK button to complete the process.
The following message dialog is displayed.
The message dialog indicates the number of Products, Accounting Schema, BOM, Purchasing, and Language records that have been modified. These are now owned by the new Organization selected.
In addition, all accounting transactions associated with this Product(s) refer to the new Organization.
Compiere provides the ability to archive documents and reports. They are stored in the database and be retrieved either from the source record (e.g. Vendor Invoice in the case of documents) or by using the Archive Viewer.
You can Archive all Documents, Documents, and Reports or you can Archive selected Documents and Reports
As mentioned you may automatically archive all documents and reports or you can allow archives to be done on a document by document basis. The Archive Option is defined at the Tenant level.
To open the Tenant window in Compiere, click on Tenant from the Main Menu. The icon in front of the Tenant menu item indicates that it is a Window.
Select an Auto Archive option of All (Reports, Documents), Documents, External Documents, or None. If Documents is selected then all internal Documents (e.g. Inventory, Material Receipt) are archived. If External Documents is selected then all documents sent to a Business Partner (e.g. Invoices, Dunning Letters) are archived. If an option other than 'None' is selected, then the documents or reports are archived when they are printed.
You can also Archive selected documents or reports. This is done from the Print Preview window so you must have Always Preview Print selected in Preferences.
Note: This option is available in the Java Client only.
Select the Archive Icon from the tool bar.
A corresponding confirmation message box is displayed.
When you return to the Vendor Invoice window for the record that was archived, you can now retrieve the archived document.
When you select the Archive icon on the tool bar you will see that one record has been archived.
When you click on the document name (as there may be more than one archived version), the Archive Viewer opens with the document displayed.
Here, you can modify the Name and add Description and Help if desired.
Note: As Archived documents are stored as a PDF image in the database, field and column security are not enforced.
You can view any archived documents (regardless of the method), in the Archive Viewer.
To open the Archive Viewer window, click on Archive Viewer from the Main Menu. The icon in front of the Archive Viewer menu item indicates that it is a Window.
The Archive Viewer has two tabs: Query and View. In the Query tab you can enter criteria that will restrict the records returned.
For Documents you can select a Business Partner to return archived documents for just that Business Partner.
Select a Table to restrict the documents returned to a specific table (e.g. Invoices)
Enter a Name of the Archived Document.
Enter any Description or Help that was entered in the Archive to retrieve specific documents.
Select a Create by to return documents that were entered by a specific User.
Enter a Created to and from date.
If you have selected the Reports check box, the following window is dynamically displayed.
To retrieve selected reports you can select a Process (e.g. Open Items).
The remaining query criteria are the same as for the Document query.
The window has moved to the View tab. Here we see all records that met the query requirements.
In this window you can print, move through multiple page documents, zoom in and out and rotate the document by using the buttons at the top of the viewer. You can move through the selected documents and modify the Name, Description and Help by using the buttons and fields on the right side of the viewer.