This is the multi-page printable view of this section. Click here to print.
See [[Batch Payments|Modules/Accounts/Batch-Payments]]
Go to Setup -> Ledger Setup -> Periodic Payments - from here you can set up payments AND receipts. There is an uzLET within the cashbook module to show overdue periodic payments.
Use the sidebar option ‘Convert To Account’ when viewing the Lead - this will require the entry of an account number. Once converted the Sales or Purchase Ledger details can be set up by going to the appropriate main menu option:
Use the main menu option: Accounts -> People
Use the search option by company and and click the Search button. This list can be printed, emailed or exported by clicking on the Output button.
When we install uzERP for customers we install a live instance and a test instance. The test instance is for testing, training, etc. and it is often useful for it to run on a recent copy of the live system.
To make a copy of the live system database and load it to the test system, use the following procedure.
Change the placeholders [dump-file-name.sql] and [test-db-name] in the commands, below, to suit your own uzERP configuration.
sudo -u postgres pg_dump -Fc uzerp-live > [dump-file-name.sql]
sudo -u postgres dropdb [test-db-name]
sudo -u postgres createdb --locale=en_GB.UTF-8 [test-db-name]
sudo -u postgres pg_restore --dbname=uzerp-test [dump-file-name.sql]
sudo -u postgres /usr/bin/vacuumdb -z [test-db-name]
Create your own dashboard by pulling uzLETS from every module you have access to.
Also manage Preferences, Holiday Requests, Expenses and saved data.
Home -> My Preferences and change ‘Items to display per page’.
Home -> My Preferences and pick a printer from the list under ‘Default Printer’.
Home -> My Preferences and choose Enable browser PDF printing.
You can set up a dashboard on any module page by adding ‘uzLETS’. These are small blocks that contain useful summary information. On the Home Page all uzLETS from all modules that you have access to are available. This allows you to create a cross-module dashboard of high level information or items requiring action. On each module page you can also add the uzLETS for that module to create module dashboards.
The steps are quite simple:
Depending on your setup the browser will either download the file or open it in the appropriate client application.
The FAQ information here answers some common questions about uzERP. There are also some operational [[How To|How To/Index]] questions and answers describing activities by module.
Hardware depends on the number of users but is relatively modest.
A server should be dedicated to uzERP and running a Linux server variant - we recommend Ubuntu Server Edition. See more detail on software [[requirements]].
uzERP will work in any modern, web-standards compliant browser. The system has been extensively tested using Firefox and Google Chrome. If you need to use Internet Explorer, then version 11 onwards is required.
uzERP is built using solid Open Source tools and will scale to any number of users, depending on your hardware. Since there are no per-user licencing restrictions you can add users as you need them at no extra cost.
uzERP has a full contact management module which supports Leads, Accounts and Person records. Each contact can have multiple addresses, phone numbers and email addresses.
There are many definitions - one we particularly like is:
CRM, or Customer Relationship Management, is a company-wide business strategy designed to reduce costs and increase profitability by solidifying customer loyalty. True CRM brings together information from all data sources within an organisation (and where appropriate, from outside the organisation) to give one, holistic > view of each customer in real time. This allows customer facing employees in such areas as sales, customer support, and marketing to make quick yet informed decisions on everything from cross-selling and up-selling opportunities to target marketing strategies to competitive positioning tactics.
uzERP allows the entry of contact details for actual and prospective clients. The CRM module specifically allows the tracking of opportunities, campaigns and activities. Because of the progressive disclosure embedded in the user interface, information is quickly available to those who need it.
uzERP’s project management module is quite extensive and can be used for large scale manufacturing or on-site construction projects.
uzERP has an extremely flexible stock (inventory) management system. There are facilities to sort products by group and type as well as process route, free format text columns for analysis and full search capabilities. Stock can be stored at multiple locations within multiple stores (warehouses). Locations can also be ‘bin controlled’ to give further granularity.
uzERP will handle make to stock, make to order and large batch processing. Multi-level product structures and complex routes are available, including sub-contract operations. There are facilities to enter departments and work centres, make product substitutions and carry out late configuration and shop floor data collection. Shop floor paperwork is catered for, and backflushing of materials used in production can be accomplished for high volume manufacturing.
uzERP can do that as well. You don’t have to deploy all of the functionality - and since all available modules are delivered with the software, you can pick and choose which options work best for your business.
There is a comprehensive shop floor data collection system within the Manufacturing module. there are user defined shift patterns work centres and performance codes so your reporting can be very ‘granular’. The Reporting module allows for complex custom reports to be built and there is always the option to output summary data to spreadsheet for further analysis.
uzERP can handle multi-warehouse/location scenarios or very simple use-cases. Some businesses are necessarily complicated but uzERP can help by improving visibility of stock, streamlining procedures and providing a clean and familiar browser based user-interface for your staff. Searching is embedded, so finding the right information, such as ,‘Where is my stock?’ or ‘Which orders are due today?’, is very easy indeed.
uzERP has comprehensive [[supply and demand management|supplydemand]] capabilities, including summary reports showing on order, available stock and fulfilment.
uzERP covers Sales Ledger and Purchase Ledger (AR and AP) plus General Ledger and Cash Book which are all linked to the back end systems for invoicing and goods received meaning you can drill back from accounting transactions or forward from commercial systems.
There are multiple VAT rates which can be allocated to items sold. Each trading partner is allocated to a VAT status to determine whether the VAT is actually charged, and there are options to produce UK intrastat information. The adaptability means that other tax regimes should be catered for fairly easily.
Yes, uzERP is multi-currency.
When looking at an individual GRN there is a side bar option ‘Cancel GRN’.
This section is for those of you who want to get more from uzERP by writing custom SQL for reports etc or playing with uzLETS.
[Here be Dragons](http://en.wikipedia.org/wiki/Here_be_dragons) - some of this stuff is pretty `toxic` if used incorrectly and could cause tears before bedtime. If things go wrong, you're on your own... you have been warned!!So you have a transaction, say an invoice or stock movement, with a date and you’d like to sort it by GL period to line up with the accounting periods rather than its calendar month. Here’s how for sales invoices:
SELECT invoice_number, invoice_date, year, period
FROM gl_periods p, si_header i
WHERE p.enddate = ( SELECT min(enddate)
FROM gl_periods z
WHERE z.period `<>` 0 z.enddate >= i.invoice_date
AND p.usercompanyid = z.usercompanyid)
ORDER BY invoice_number
By default, views in the public schema are not surfaced in the uzERP Report Writer. If you want to use a standard view for reporting then set up a new view under the reports schema - the example below takes the standard GL transactions view and makes it available for reporting
CREATE OR REPLACE VIEW reports.my_gl_transactions AS
SELECT id, docref, usercompanyid, glaccount_id, glcentre_id, glperiods_id,
trandate, source, "comment", reference, twincurrency_id, twinrate,
"type", twinvalue, "value", account, cost_centre, glperiod, twincurrency,
year_period
FROM gltransactionsoverview;
You can of course omit columns or add WHERE clauses to pare the data down.
Use this technique to create custom views from any data in the system - as long as the view is in the reports schema then it will be available for reporting.
This looks a bit complicated, but the date_part - date_part(‘year’, invoice_date)||to_char(date_part(‘month’, invoice_date),‘FM09’) - could be encapsulated in a pgsql function.
SELECT date_part('year', invoice_date)||to_char(date_part('month', invoice_date),'FM09') as inv_year_month,
CASE WHEN date_part('year', current_date)||to_char(date_part('month', current_date),'FM09')=date_part('year',
invoice_date)||to_char(date_part('month', invoice_date),'FM09')
THEN 'current = '||net_value
WHEN date_part('year', current_date - interval '1
month')||to_char(date_part('month', current_date - interval '1
month'),'FM09')=date_part('year',
invoice_date)||to_char(date_part('month', invoice_date),'FM09')
THEN '1 Month = '||net_value
WHEN date_part('year', current_date - interval '2
month')||to_char(date_part('month', current_date - interval '2
month'),'FM09')=date_part('year',
invoice_date)||to_char(date_part('month', invoice_date),'FM09')
THEN '2 Month = '||net_value
END
FROM si_header;
Checking the date function, it does handle the differing day lengths in months, as follows:-
SELECT date_part('year', timestamp
'2011-01-04')||to_char(date_part('month', timestamp '2011-01-04'),'FM09')
, date_part('year', timestamp '2011-03-31'- interval '1
month')||to_char(date_part('month', timestamp '2011-03-31'- interval '1
month'),'FM09')
, date_part('year', timestamp '2011-04-01'- interval '1
month')||to_char(date_part('month', timestamp '2011-04-01'- interval '1
month'),'FM09');
In the above, taking one month off 31/03/2011 gives 201102; taking one month off 01/04/2011 gives 201103; i.e. it works on months not days.
SELECT date_part('year', current_date)||to_char(date_part('month',
current_date),'FM09')
, date_part('year', current_date - interval '1
month')||to_char(date_part('month', current_date - interval '1
month'),'FM09')
, date_part('year', current_date - interval '2
month')||to_char(date_part('month', current_date - interval '2
month'),'FM09')
, date_part('year', current_date - interval '3
month')||to_char(date_part('month', current_date - interval '3
month'),'FM09')
, date_part('year', current_date - interval '4
month')||to_char(date_part('month', current_date - interval '4
month'),'FM09')
, date_part('year', current_date - interval '5
month')||to_char(date_part('month', current_date - interval '5
month'),'FM09')
, date_part('year', current_date - interval '6
month')||to_char(date_part('month', current_date - interval '6
month'),'FM09')
, date_part('year', current_date - interval '7
month')||to_char(date_part('month', current_date - interval '7
month'),'FM09')
, date_part('year', current_date - interval '8
month')||to_char(date_part('month', current_date - interval '8
month'),'FM09')
, date_part('year', current_date - interval '9
month')||to_char(date_part('month', current_date - interval '9
month'),'FM09')
, date_part('year', current_date - interval '10
month')||to_char(date_part('month', current_date - interval '10
month'),'FM09')
, date_part('year', current_date - interval '11
month')||to_char(date_part('month', current_date - interval '11
month'),'FM09');
This applies in something like an aged debt report (shown below) and uses some of the techniques highlighted above:
SELECT
sltransactionsoverview.customer,
sltransactionsoverview.transaction_date,
sltransactionsoverview.id, sltransactionsoverview.transaction_type, sltransactionsoverview.our_reference, sltransactionsoverview.ext_reference,
sltransactionsoverview.gross_value,
sltransactionsoverview.currency,
sltransactionsoverview.rate, sltransactionsoverview.description, sltransactionsoverview.payment_terms,
CASE
WHEN to_char(sltransactionsoverview.transaction_date::timestamp with time zone, 'YYYYMM'::text) = to_char('now'::text::date::timestamp with time zone, 'YYYYMM'::text)
THEN sltransactionsoverview.gross_value
ELSE 0::numeric
END AS current_gross,
CASE
WHEN to_char(sltransactionsoverview.transaction_date::timestamp with time zone, 'YYYYMM'::text) = to_char('now'::text::date - '1 mon'::interval, 'YYYYMM'::text)
THEN sltransactionsoverview.gross_value
ELSE 0::numeric
END AS m1_gross,
CASE
WHEN to_char(sltransactionsoverview.transaction_date::timestamp with time zone, 'YYYYMM'::text) = to_char('now'::text::date - '2 mons'::interval, 'YYYYMM'::text)
THEN sltransactionsoverview.gross_value
ELSE 0::numeric
END AS m2_gross,
CASE
WHEN to_char(sltransactionsoverview.transaction_date::timestamp with time zone, 'YYYYMM'::text) = to_char('now'::text::date - '3 mons'::interval, 'YYYYMM'::text)
THEN sltransactionsoverview.gross_value
ELSE 0::numeric
END AS m3_gross,
CASE
WHEN to_char(sltransactionsoverview.transaction_date::timestamp with time zone, 'YYYYMM'::text) = to_char('now'::text::date - '4 mons'::interval, 'YYYYMM'::text)
THEN sltransactionsoverview.gross_value
ELSE 0::numeric
END AS m4_gross,
CASE
WHEN to_char(sltransactionsoverview.transaction_date::timestamp with time zone, 'YYYYMM'::text) <= to_char('now'::text::date - '5 mons'::interval, 'YYYYMM'::text)
THEN sltransactionsoverview.gross_value
ELSE 0::numeric
END AS m5_gross
FROM sltransactionsoverview
LEFT JOIN slmaster_overview ON sltransactionsoverview.slmaster_id = slmaster_overview.id
LEFT JOIN companyoverview ON slmaster_overview.company_id = companyoverview.id
LEFT JOIN countries ON companyoverview.countrycode = countries.code
WHERE sltransactionsoverview.status::text `<>` 'P'::text
ORDER BY sltransactionsoverview.customer, sltransactionsoverview.transaction_date, sltransactionsoverview.id;
There is a product lines table that is accessed via Logistics > Purchase Orders > Product Lines. A product line can be linked to a stock item and can also be specific to a supplier if required.
As above, go to Logistics > Sales Orders > Product Lines. The line to be purchased is added here with its price, supplier etc - there is no need to reference it back to a stock item if it is for a service
uzERP will allow you to receive more items than were actually ordered and
Where an order line shows an outstanding quantity that will not be received, it can be marked as completed.
Only the original creator of the order can edit part received lines and mark them complete.
Although there is no specific functionality relating to call-off orders in PO processing each order line within an order can have a different delivery date. In this case the delivery date of the whole order is set to the latest date for a line. As goods are received they are booked to the relevant line and the order status is set to part-received until all lines are completed. There is a caveat… once the order is authorised, printed and sent to the supplier in order to make changes re-authorisation is required. This makes it unsuitable where the order quantities are not fixed i.e. the requirements need amending after the order is raised.
Go to the sales ledger record and click the side bar action ‘Outstanding’ under Related Items.
Under the customer’s account in sales ledger and look at the ‘All Invoices’ side bar link - if its paid you can click on ‘paid’ to see the cross reference transactions and therefore the payment. If the payment is unallocated use the ‘Outstanding’ link to find the payment.
Go to the customer under Sales ledger and look for the side bar action ‘Stop Account’
There is a product lines table that is accessed via Logistics -> Sales Orders -> Product Lines. A product line can be linked to a stock or a service item and can also be specific to a customer if required. Products can be included in price lists.
Go to Logistics -> Sales Orders -> Product Lines. The line to be sold is added here with its price and reference it back to the Stock Item from the drop down.
Menu option Logistics -> Sales Orders -> Price Types
In the sales ledger the customer can be allocated to a price type (list).
There are two ways:
To add stocks you go to the menu option Manufacturing->Stock Items->Add a stock item OR Manufacturing->Stock Items and then ‘add’ from the sidebar. This adds the ‘internal’ stock information for stores and manufacturing purposes. To get them into sales orders then you need to add external information (i.e. price) which then looks at SO Product lines. Go here Logistics->Sales Orders->Products
Note that an SO product doesn’t necessarily have to be on stock.
When viewing the details for an item click the sidebar action ‘Show Sales Invoices’.
Use menu option Manufacturing -> Stores, click the store you are interested in and then the location - then use the sidebar action ‘Show Stock Balances’.