SQL-Ledger User and Developer Docs

ap

Thu, 03 Sep 2009 12:30:20 +0000

Vendors

You need to add at least one vendor before creating invoices. Use Vendors–Add Vendor to add new vendors.

To change existing vendors, first you list them using Vendors–Reports–Search. Vendors are listed with hyperlinks to edit each vendor.

AP Transactions

AP–Add Transaction menu adds a new AP Transaction. AP transactions allow you to record your sales in correct GL accounts without creating an invoice.

Invoices

Vendor invoices are created using AP–Vendor Invoice menu. The only mandatory columns are Vendor and Invoice Date. Rest of the columns can be left blank.

Once you enter an item (part, service) and click 'Update', a new line opens. This way you can enter any number of items (parts, services etc.) in the detail portion of the invoice.

Payments

You can record payments from vendor while creating invoices or afterward using Cash–Payment menu option.

api

Thu, 06 May 2010 12:05:10 +0000

ar

Sun, 31 May 2009 13:23:17 +0000

assemblies_and_manufacturing

Thu, 03 Sep 2009 13:33:49 +0000

Using Assembly Items

An assembly is composed of components which are individual parts in the inventory or other sub-assemblies. Assemblies in MyLedger allow you to do manage your manufacturing process.

Work flow for using assemblies:

Define assemblies. Goods & Services–Add Assembly.
Build assemblies. Goods & Services–Stock Assembly. Individual parts are removed and assemblies are added to the stock inventory.
Sell assembly items like any other item.

Please note that you cannot buy parts defined as assemblies.

1. Define Assemblies

As assembly is just like any other inventory item in your sql-ledger with the additional information about its components. You define new assemblies using Goods and Services–Add Assembly.

2. Stock Assembly

This option reduces the quantities of the components and increases the onhand quantity of the assemblies. COGS is not recorded at this point.

COGS for the assembly is recorded from individual components when you sell the assembly. FIFO allocation also occurs at the time of sale. (Rows are inserted in invoice table for component parts with assemblyitem=TRUE)

3. Reports

More Reports–Goods and Services–Stock Assembly gives you a list of your Stock Assembly actions. This report lists the parts taken out of assembly as well as assemblies built.

Goods and Services–Assemblies gives you list of all or selected assemblies with their components:

Goods and Servers–Components gives you a list order by partnumber and the assembly in which it is used.

Work Order You can print work order for a sales orders. Work order lists all component parts required to fullfil a given order of assembly items.

audit_control

Thu, 03 Sep 2009 15:01:06 +0000

Audit Control

You can use System–Audit Control menu to enforce transaction control and log user activities.

Enforce transaction reversal for all dates

You can check this option to prevent any change to any transaction. You can however add a reverse transaction to correct some mistake. This option is highly recommended.

Close Books up to

When you close books upto a certain date, system does not allow changing any transaction prior to this date. Please note that this is not a year end process.

Activate Audit trail

All user activity (adding, changing, deleting transactions) is logged. You can view this log using 'Others–Audit Trial' report.

Remove Audit trail up to

You can use this option to remove audit trail from database up to a certain date. Useful to make your backups small.

backup_and_restore

Fri, 09 Apr 2010 08:26:35 +0000

Backup

To backup all your sql-ledger data, follow these steps:

1. Database.

Use the 'System–Backup' menu option to backup your database.

You can also use pg_dump to backup your db from your linux console:

 pg_dump -U sql-ledger your-db-name | gzip -c > your-db-name.sql.gz

2. Users

Backup sql-ledger/users/ folder. This folder contains information (access rights, password etc.) of all logins.

 cd /usr/local/sql-ledger/
 tar -czvf users.tar.gz users/

3. Templates

Backup sql-ledger/templates/yourtemplates/ folder. This folder contains any customized invoice/orders etc. templates which you may have customized using the System–Templates menu option.

 cd /usr/local/sql-ledger/templates/
 tar -czvf your-templates.tar.gz your-templates/

Restore

To restore your data on the same server or a new server, follow these steps.

1. If you are restoring your backup on the same server, you will need to delete your existing database before creating the new one. Follow this step with care.

 dropdb -U sql-ledger your-db-name

2. Create a new database, uncompress your backup and restore

 createdb -U sql-ledger -E SQL_ASCII your-db-name
 gunzip your-db-name.sql.gz
 psql -U sql-ledger your-db-name < your-db-name.sql

Note: Above code creates database with SQL_ASCII encoding. You might need to change it to something like UTF8 according to your database backup encoding.

3. Copy users.tar.gz to sql-ledger folder and uncompress with tar -xzvf users.tar.gz

4. Copy your-templates.tar.gz to sql-ledger/templates folder and uncompress with tar -xzvf your-templates.tar.gz

Tips

1. The above postgresql commands assume you have a database user/role named sql-ledger. If you don't have this user, use either 'postgres' (for most Linux systems) or 'pgsql' (for FreeBSD port) instead of 'sql-ledger'.

2. It is usually more important to backup your postgres database with contains all your customers/vendors/transactions. You can always recreate users and edit templates in a new installation if you forget to backup these.

3. You might need to edit your users through admin.pl if the new database name is different from the old one or the database connection information (host, user, password) is different.

4. For simplicity sake, there is no harm in backing up entire sql-ledger folder instead of individually backing up users and templates folders. Make sure you don't have redundant database backups in users folder otherwise your backup may be quite large. You will still need to backup your database separately.

basics_of_double_entry_accounting

Sun, 07 Mar 2010 11:22:35 +0000

cost_of_goods_sold

Thu, 03 Sep 2009 14:01:42 +0000

Cost of goods sold

Cost of Goods Sold (COGS) is the purchase price of the goods you just sold. Your sales minus the COGS is your gross profit.

COGS is an important accounting information. Correct COGS gives you a clear picture of the profitability of your products.

Tip: To view the debit and credit accounting transactions for any sale or purchase invoice, enter the invoice number on General Ledger–Reports screen and click Continue button.

1. Sale invoices and COGS

Let us make it clear with an example:

1. You purchase 10 iPhones for $400 each.

Debit: Inventory $4000
Credit: AP $4000

2. A customer comes in and purchases 2 of these at $500 each.

Debit: AR $1000
Credit: Sales $1000

Debit: COGS $800
Credit: Inventory $800

So your gross profit is $200.

MyLedger posts COGS automatically with each sale invoice. It calculates COGS on First-In First-Out (FIFO) basis. This means is that if you purchase 5 more iPhones at $430 each, MyLedger will keep calculating COGS @ $400 each until all 10 iPhones of first purchase transaction are depleted. Afterward it will calculate COGS @ $430.

2. Sales before purchases

MyLedger allows you to sell goods without purchasing these in advance. This is a common practice in many businesses where you have received the goods but do not have the vendor invoice.

This results in negative stock quantity on Goods & Services–Reports–All Items report. No COGS is posted for such transactions at the time of sale. Later when you record purchases, COGS is automatically recorded for these oversold items.

3. Editing Sale Invoices

When you edit and repost an already posted sale invoice, COGS goes out of sync and incorrect accounting entries are posted. This causes incorrect income statement.

To confirm this, display your income statement and write down the COGS amount. Now open and repost any past sales invoice. Compare the new COGS in income statement with the old one.

Ideally you should never edit an invoice. Instead post a reversal of the invoice (using a credit invoice) and create a new invoice. Check the box Enforce transaction reversal for all dates on System–Audit Control screen.

If you do need to edit invoices, you can correct COGS transactions by running the re-posting of invoices through menu System–Repost COGS.

currencies

Thu, 03 Sep 2009 14:46:27 +0000

Multi-currency transactions

You can use multiple currencies with MyLedger.

1. Defining Currencies

2. Buying and selling in foreign currencies

Invoice

Payment

3. Reports

4. Exchange rate differences

5. Funds transfers between foreign currencies

Let us say the exchange rate is 1 GBP = 2.0289 (or reverse 1 USD = 0.4929 GBP)

customization

Sat, 23 Jan 2010 04:22:00 +0000

Customization

SQL-Ledger can be customized in three ways:

1. custom_xx.pl files

You can create your own functions or override any existing function by creating custom scripts in custom_xx.pl files and putting them in bin/mozilla folder.

For example, to add new functions to gl.pl file, add these functions to custom_gl.pl file and put this file into bin/mozilla/ folder. This file will be automatically loaded by sql-ledger before running any functions in gl.pl files.

Once your new functions are there, you can call them using your own custom menu. Custom menu entries are put in custom_menu.ini and follow the same syntax as that of menu.ini.

This method of extending the sql-ledger is upgrade-safe and is the recommended way.

2. Custom Modules

You can build your own modules. To write a module, you need to create at least three files:

Module back-end code which will reside in ./sql-ledger/SL/MyModule.pm
Module front-end code which will reside in ./sql-ledger/bin/mozilla/mymodule.pl
Gateway script in ./sql-ledger. (You just need to make a copy of an existing one. For example cp gl.pl mymodule.pl in ./sql-ledger/ folder.

This method is also upgrade safe.

3. Modify the source code

Sometimes there is a need to directly alter the sql-ledger source code for particular needs. We have, for example, modified few reports (GL Transactions, All Items) in this way. Your changes, however, will be overwritten when you upgrade to new version and you will need to port these changes again to the new version.

A bit discipline and an SCM software like GIT can help manage such changes or patches with easy. We, at ledger123.com, use GIT to track and manage such changes across newer versions of sql-ledger.

data_export_backup

Thu, 03 Sep 2009 12:37:51 +0000

Data export

Data backup

You can backup your data directly through sql-ledger. There are two ways to get your backup using the 'System–Backup' menu.

System–Backup–Send by Email. Backup is sent to your email address through email. You can add or change this email address through Preferences.

System–Backup–Save to File. When you click this menu option your browser will display the save file dialog and you can save backup file on your computer.

Automated backups

MyLedger is hosted on servers with RAID storage. You are protected from hard disk crash. We also perform an automated off-site database backup every 24 hours to ensure that your data is safe.

data_import

Wed, 02 Sep 2009 01:46:59 +0000

Data Import

Sometimes you need to import your sales data into sql-ledger which was produced elsewhere.

You might have a web store where you download your daily sales in CSV format and want to import it into Sql-Ledger. Or you are just moving to sql-ledger from your legacy accounting software and want to move all existing data from old software to sql-ledger.

Following sections provide detailed steps for importing CSV text files.

Sale invoices

Sales invoices can be imported from text files.

1. Format your data

For list of additional data columns that can be imported see step 4.

invnumber,transdate,duedate,customernumber,curr,invoicedescription,partnumber,qty,sellprice,employeenumber,AR,department,warehouse
A100,10/12/2008,10/30/2008,AE001,GBP,Invoice description comes here,B001,10,102,E-001,1100,HARDWARE,LONDON
A100,10/12/2008,10/30/2008,AE001,GBP,Invoice description comes here,F003,6,69,E-001,1100,HARDWARE,LONDON
A101,10/12/2008,10/31/2008,CP002,GBP,Test description,F003,2,32,E-002,1100,SERVICES,PARIS
A102,10/13/2008,11/1/2008,ER003,GBP,Sale of goods,T007,6,12,E-003,1100,SERVICES,LONDON
A103,10/14/2008,11/2/2008,SP007,GBP,Sale,K001,12,32,E-004,1100,HARDWARE,PARIS

(The last column AR is accounts receivable account number which is 1100 in UK chart of accounts)

If your data contains invoices with more than one part, repeat the row with same invoice header information and change the part number and price information. Sql-ledger will import all these rows as a single invoice. (See invoice number A100 above)

2. Upload and preview

Using Import–Sales Invoices menu option, upload this file into Sql-Ledger. You will be shown what will be imported before actual import is done. At this point you can check and uncheck the invoices to be imported.

3. Confirm data import

When you click the Import Sales Invoices button, invoices will be imported. You will be show which invoices were imported successfully.

4. Additional data which can be imported

The sample csv file provided above contains only the most commonly used columns. Here is the complete list.

transdate
invnumber
customernumber
curr
duedate
employeenumber
ordnumber
quonumber
datepaid
shippingpoint
shipvia
waybill
terms
notes
intnotes
language_code
ponumber
cashdiscount
discountterms
partnumber
description
sellprice
discount
qty
unit
serialnumber
projectnumber
deliverydate
AR
taxincluded

Receipts and Payments

You can import payments and match them to invoices using 'Import–Payments'. Following points should be kept in mind.

Payments are matched first on Invoice DCN column and then, if no match is found, on payment amount.
Both AR and AP invoices are matched with payments.
The amount matched is calculated as debit minus credit.

Follow these steps:

Step 1

Create or format the data in a CSV file with structure similar to the given below.

datepaid,memo,debit,credit,dcn
2008/11/03,"payment ref 2121",,38.76,
2008/10/04,"cash payment",,527.5,
2008/10/10,"CC Receipt",,243.08,
2009/11/01,"Payment matched by DCN",,1401.72,1122

Step 2

Import script will read the CSV file and match the payments to AR or AP invoices first on DCN Number and then on invoice due amount, if needed.

In this example, one AP invoice is matched on amount and the other one is matched on DCN number. The other two are AR invoices which are matched on amount.

Step 3

Once you click Import Payments, payments are imported and applied to the matched invoices.

Step 4

For advanced users: You can easily change the script to match the payments on other invoice columns like invoice number. The procedures to modify are 'sub payments' in 'SL/IM.pm' and 'sub im_payment' in 'bin/mozilla/im.pl'.

To match payments only to AR (or AP) invoices, change the UNION queries in SL/IM.pm to select invoices from AR or AP only as required.

Transactions

You can import AR and AP transactions.

For AR Transactions, format your data using the following sample:

invnumber,customernumber,transdate,amount,description,notes,source,memo
00003,AE001,10-11-07,2030,"desc1","notes1","source1","memo1"
00004,CP002,07-12-07,3213,"desc1","notes2","source2","memo2"
00005,SP007,09-12-07,-200,"desc1","notes3","source3","memo3"

For AP transactions, format your data using the following example:

invnumber,vendornumber,transdate,amount,description,notes,source,memo
00003,CB001,10-10-08,2030,"desc1","notes1","source1","memo1"
00004,ES002,10-12-08,3213,"desc2","notes2","source2","memo2"
00005,SA003,12-12-08,-200,"desc3","notes3","source3","memo3"

General Ledger

We have enhanced the sql-ledger import script to add the general ledger import. This feature will help you to move your data from most of the accounting software to sql-ledger in few easy steps:

Step 1

Format your journal data according to the layout show below.

reference,transdate,description,notes,accno,debit,credit,source,memo
GL001,01-20-2008,"Paid for training,support",Next session in 2009,8203,124,0,23211,new hiring 
GL001,01-20-2008,"Paid for training,support",Next session in 2009,1230,0,124,23211,new hiring
GL002,10-19-2008,"Overdue pymt for inv 11,12,13",,1230,204,0,"11,12,13",
GL002,10-19-2008,"Overdue pymt for inv 11,12,13",,1102,0,204,"11,12,13",
GL003,11-20-2008,Invalid transaction for testing,This account is not in chart,00121,0,255,source2,memo2

Keep in mind that:

Import script creates one GL transaction for each unique 'reference' number. There can be any number of lines (rows) in each transaction.
Account must exist in chart of accounts
Debits and credits must be equal before the CSV file can be imported.

Step 2

Using 'Imports–GL Transaction' load the CSV file into sql-ledger. Import script will show the rows which contain valid account number and can be imported.

Step 3

Click Import GL to finish the import script. Transactions successfully imported will be show on the next page.

Customers and Vendors

Customer and Vendor import is similar (except the number column which is customernumber or vendornumber).

Prepare your data file using the sample text provided below. (Change customernumber to vendornumber for vendor import)

customernumber,name,firstname,lastname,contacttitle,phone,fax,email,notes,address1,address2,city,state,zipcode,country
001,Ledger123,Armaghan,Saqib,Consultant,,,saqib@ledger123.com,"These are, just, sample notes",,,London,,"AA7 8BB",UK

Parts

Format your data according to following sample:

partnumber,description,unit,partsgroup,listprice,sellprice,lastcost,rop,bin,image,drawing,notes
B002,"Brush Set",NOS,brush,9.99,9.99,7,150,TOP,noimage,brush.jpg,notes about brush set
D010,"Deluxe Hand Saw",NOS,SAW,17.99,17.99,16,50,TOP,saw.jpg,nodrawing,notes about hand saw
D011,"Digger Hand Trencher",NOS,Picks & Hatchets,18.99,18.99,15,200,TOP,,nodrawing,notes about hand saw

System assigns a unique parts_id to each part imported or group created. Duplicates are not allowed and duplicate check is done on partnumber.

To start the import process, click 'Data Import–Parts' in the menu. Following page will be displayed.

Click 'Browse' to select your CSV file, mark the taxes applicable and select the account links (Defaults are enough most of the time) Click 'Continue' when done. You will be presented with the following screen.

On this screen you can mark the parts to be imported by checking or un-checking the checkbox on each line.

Please note:

The parts which are already in the system (based on partnumber) will not imported. (You will not see a check box with them)
Parts groups which are new will be added. These are marked by a '+' sign after group name.

Click 'Import Parts'. Your CSV file will be processed and parts will be imported. Any new groups will also be added. You will see an output like the following:

Vendor Price List

Format your vendors price list according to the sample CSV data below:

partnumber,vendornumber,vendorpartnumber,lastcost,curr,leadtime
B001,CB001,V-CB001,10,GBP,15
B002,ES002,,14,GBP,45
M004,SA003,,21,GBP,30

Click 'Data Import–Parts Vendors', specify the file with the 'Browse' button and click 'Import Parts Vendors' button.
Following page will be displayed. Here you can un-check the rows which you do not want to import. Rows with invalid vendor number or partnumber will not have the checkbox.

Customer Price List

Format your customer price list according to the sample CSV data below.

partnumber,customernumber,pricegroup,pricebreak,sellprice,validfrom,validto,curr
B001,AE001,PG1,10,11,03-01-2008,,GBP
B002,BP011,,20,12,,03-01-2009,GBP
M004,CP002,,15,20,03-01-2008,03-05-2008,GBP
D08,CP002,test,25,25,,,GBP

Click 'Data Import–Parts Customers', specify the file with the 'Browse' button and click 'Import Parts Customers' button.

Following page will be displayed. Here you can un-check the rows which you do not want to import. Rows with invalid customer number or partnumber will not have the checkbox.

Chart of accounts

Prepare your chart of accounts in your spreadsheet software according to the sample given below.

Upload the chart csv file using 'Import–Chart' menu option.

Check/uncheck the accounts to be imported and click continue to import the selected accounts.

accno,description,charttype,category,link
1000,"CURRENT ASSETS",H,A,
1060,"Checking Account",A,A,AR_paid:AP_paid
1065,"Petty Cash",A,A,AR_paid:AP_paid
1200,"Accounts Receivables",A,A,AR
1205,"Allowance for doubtful accounts",A,A,
1500,"INVENTORY ASSETS",H,A,
1520,"Inventory / General",A,A,IC
1530,"Inventory / Aftermarket Parts",A,A,IC
1800,"CAPITAL ASSETS",H,A,
1820,"Office Furniture & Equipment",A,A,
1825,"Accum. Amort. -Furn. & Equip.",A,A,
1840,Vehicle,A,A,
1845,"Accum. Amort. -Vehicle",A,A,
2000,"CURRENT LIABILITIES",H,L,
2100,"Accounts Payable",A,L,AP
2160,"Corporate Taxes Payable",A,L,
2190,"Federal Income Tax Payable",A,L,
2210,"Workers Comp Payable",A,L,
2220,"Vacation Pay Payable",A,L,
2250,"Pension Plan Payable",A,L,
2260,"Employment Insurance Payable",A,L,
2280,"Payroll Taxes Payable",A,L,
2310,"VAT (10%)",A,L,AR_tax:AP_tax:IC_taxpart:IC_taxservice
2320,"VAT (14%)",A,L,AR_tax:AP_tax:IC_taxpart:IC_taxservice
2330,"VAT (30%)",A,L,AR_tax:AP_tax:IC_taxpart:IC_taxservice
2600,"LONG TERM LIABILITIES",H,L,
2620,"Bank Loans",A,L,
2680,"Loans from Shareholders",A,L,AP_paid
3300,"SHARE CAPITAL",H,Q,
3350,"Common Shares",A,Q,
4000,"SALES REVENUE",H,I,
4020,"Sales / General",A,I,AR_amount:IC_sale
4030,"Sales / Aftermarket Parts",A,I,AR_amount:IC_sale
4300,"CONSULTING REVENUE",H,I,
4320,Consulting,A,I,AR_amount:IC_income
4400,"OTHER REVENUE",H,I,
4430,"Shipping & Handling",A,I,IC_income
4440,Interest,A,I,
4450,"Foreign Exchange Gain",A,I,
5000,"COST OF GOODS SOLD",H,E,
5010,Purchases,A,E,AP_amount:IC_expense
5020,"COGS / General",A,E,AP_amount:IC_cogs
5030,"COGS / Aftermarket Parts",A,E,AP_amount:IC_cogs
5100,Freight,A,E,AP_amount:IC_expense
5400,"PAYROLL EXPENSES",H,E,
5410,"Wages & Salaries",A,E,
5420,"Employment Insurance Expense",A,E,
5430,"Pension Plan Expense",A,E,
5440,"Workers Comp Expense",A,E,
5470,"Employee Benefits",A,E,
5600,"GENERAL & ADMINISTRATIVE EXPENSES",H,E,
5610,"Accounting & Legal",A,E,AP_amount
5615,"Advertising & Promotions",A,E,AP_amount
5620,"Bad Debts",A,E,
5650,"Capital Cost Allowance Expense",A,E,
5660,"Amortization Expense",A,E,
5680,"Income Taxes",A,E,
5685,Insurance,A,E,AP_amount
5690,"Interest & Bank Charges",A,E,
5700,"Office Supplies",A,E,AP_amount
5760,Rent,A,E,AP_amount
5765,"Repair & Maintenance",A,E,AP_amount
5780,Telephone,A,E,AP_amount
5785,"Travel & Entertainment",A,E,
5790,Utilities,A,E,AP_amount
5795,Registrations,A,E,AP_amount
5800,Licenses,A,E,AP_amount
5810,"Foreign Exchange Loss",A,E,

Optionally as a safety measure, you can double check the correctness of your final csv file by opening it in a text editor like notepad or wordpad.

db_schema

Wed, 10 Feb 2010 12:27:29 +0000

Database Schema for SQL-Ledger

Updated to version SQL-Ledger 2.8.28

This schema description will introduce you to sql-ledger underlying database tables and will enable you to create your custom reports using SQL or import data using CSV import features of Postgresql. Please see sql queries for examples of SQL using this schema.

1. General comments on schema

1. Most tables have a primary key column named id. This key is appears as tablename_id as foreign key in other tables. For example customer_id column in tables is foreign key referring to id column in customer table. This id column gets unique sequence number from database sequence also named id.

2. Contrary to what new users might think customernumber, vendornumber, partnumber, invnumber etc. are not primary keys in customer, vendor, parts, ar/ap tables. These columns can also have duplicate values. You can prevent duplicate values by creating UNIQUE indexes on these columns. As mentioned above, in all tables, primary key is the column named id.

2. List of tables

acc_trans

Debit/credit records for all financial tranactions are stored in this table. trans_id is the foreign key in this table which refers to id in ar, ap, gl tables.

address

Purchase invoices and AP transctions header information goes into this table. invoice column contains true for invoices and false for AP transactions.

Sales invoices and AR transactions header information goes into this table. invoice column contains true for invoices and false for AP transactions.

assembly

Stores assembly components for a given assembly. id is id for the given assembly from parts table. parts_id is id for a assembly component from parts table.

audittrail

If you have activated the audittrial in System→Audit Control menu option, a row contain the username and transaction reference is inserted into this table for each transaction addition, deletion or editing activity. This table can grow very large on busy systems. You can also remove past audittrial record using System→Audit Control menu option. There are no reports in the system which show data from this table.

bank

business

Type of business. This table is used to group customers. You can also specify the discount percent applicable to that particular group of customers.

cargo

chart

This table contains Chart of accounts.

contact

curr

customer

Customer master record.

customertax

Customer-tax information is stored in this table. One row for each customer/tax combination.

defaults

System defaults

department

Departments

dpt_trans

Department transactions. One row for each transcation/deparmetn which contains a department reference.

employee

One row for each user/employee.

exchangerate

Exchange rate for a particular date is added to this table if you use foreign currency on a transaction and there is no exchange rate in this table for that transcation date. A maximum of two rates are added to this table for a given date. One for buy and one for sell.

gifi

General Index for Financial Information or GIFI allows you to create an alternate chart of accounts which can be linked to main chart of accounts through the GIFI column on Add Account form. Financial reports can be generated by accno or gifi_accno. You can also consider it an alternate method of group the accounts on chart.

Contains one row for each general ledger transaction.

inventory

Inventory movement is recorded in this table when you use Ship or Receive options on ''Shipping' module.

invoice

Invoice detail lines for sales and purchase invoices go into this table. Qty is -ve for purchase invoices and +ve for sale invoices (unless you change sign when entering invoices for recording returns).

jcitems

language

One row per language defined. These languages are used to translate the parts descriptions into another language to print on invoices etc. Actual translations are stored in translation table.

makemodel

This table can contain one or more rows for any particular part. Make and model information is entered on Add Part screen. This information can be used to further classify or group the parts. For example you can specify which models use a particular part. You can filter on make/model on parts on Parts report.

This tables records header information for four type of transactions; Sales Order, Purchase Order, Quotation and Request for Quotations. vendor_id is NULL if this is a Sales Order or Quotation. customer_id is NULL If this is a Purchase Order or RFQ. Boolean column quotation further differentiates between an Order and a Quotation.

orderitems

Detail lines for Orders and Quotations go into this table.

parts

Contains master records of parts, services, assembly, labour/overhead. inventory_accno_id is NULL for service items.

partscustomer

Per customer pricing for a part is stored in this table. This information is stored from Add Part form. Multiple prices with different quantity breaks can be entered for one customer.

partsgroup

Parts groups allow you to group your parts, services etc according to any criteria.

partstax

One row for each part/tax combination which is specified on Add Part screen.

partsvendor

Per vendor cost for a part as well as lead time and vendor part number are stored in this table. This information is updated through Add Part form.

payment

paymentmethod

pricegroup

One row per price group. Price group can be specified on the customer screen to group one or more customers to specific parts prices. You specify prices for parts group on Add Part screen.

project

One row per project. Project can be linked to sale, purchase or general ledger transaction detail lines. You can run reports filter them on projects.

recurring

Whenever you schedule a transaction, its id and other information for is stored in this table.

recurringemail

One row for each tranaction/formtype combination to be mailed when a recurrening transaction is created and marked to email one or more forms upon generation.

recurringprint

Same as recurringemail but for printing of forms.

semaphore

shipto

One row is added to this table when you create Ship To address in customer or vendor form. trans_id is the customer or vendor id for which shipto address has been created. A row is also added when you specify different shipto on invoice. In this case trans_id is the id of that transaction.

Standard industry codes. Used to group customers.

status

One row for each tax defined. Tax are defined by marking them Tax on Add Account form.

translation

One row for each description translated to a language defined in language table.

vendor

Contains master records for each vendor.

vendortax

Taxes applicable to each vendor are defined in this table. One row for each vendor/tax combination.

warehouse

One row for each warehouse defined.

yearend

You you run System→Yearend process, a journal transaction is generated in the gl table (with detail lines in acc_trans). id for the transaction along with the date is stored in this table. (one row per year end).

3. List of sequences

addressid

assemblyid

contactid

inventoryid

invoiceid

jcitemsid

orderitemsid

defaults

Thu, 03 Sep 2009 12:33:50 +0000

Document Numbers and System Defaults

System–Defaults menu allows you to setup your company, address and related information in MyLedger. Document numbering is also controlled by system defaults.

We setup defaults for document numbers as shown on the following screen shot. You can change these to your liking or organizational needs.

departments

Fri, 01 May 2009 08:16:36 +0000

Departments

Departments are optional and can be used to classify transactions according to a department code.

1. Managing Departments

Departments can be added, changed or deleted using 'System–Departments' menu option.

SQL-Ledger departments can be mapped to the various departments (sales, purchase etc.), branches (London, Oxford etc.) or product divisions (Product 1, Product2 etc.) within your organization.

Departments can be marked as 'Cost Center' or 'Profit Center'. Cost center departments appear only in purchasing module. Profit center departments appear both in purchasing and sales modules.

You can also change 'Department' to anything you like (eg.Branch) using the sql-ledger language customization feature. Note: Departments lookup does not appear on transaction forms unless you define at least one department from System→Departments menu option.

2. Default Department

You can define a default default for users through sql-ledger administrative interface.

You can also restrict the user to view and make transactions to his department only by setting his role to User. Users with role Administrator, Manager, Supervisor always have access to all departments.

3. Using Departments

Once departments are defined you can specify them in your invoices, orders, quotations and other transactions.

4. Reports

Reports allow you to view all or department specific transactions.

Income Statement and Balance sheet can also be compared and displayed by department.

diff_patch_tools

Sat, 23 Jan 2010 04:33:09 +0000

Diff and Patch Tools

Unix diff and patch tools allow you to manage your custom changes easily across newer versions of sql-ledger.

1. Identifying what is changed in new version

Let us say that we have two versions of sql-ledger in folders sql-ledger-2.8.13 and sql-ledger-2.8.14. We try to find what has been changes in 2.8.14.

Files changes

  freebsd# diff -r -q sql-ledger-2.8.13 sql-ledger-2.8.14

Files sql-ledger-2.8.13/SL/Form.pm and sql-ledger-2.8.14/SL/Form.pm differ
Files sql-ledger-2.8.13/SL/IC.pm and sql-ledger-2.8.14/SL/IC.pm differ
Files sql-ledger-2.8.13/SL/IR.pm and sql-ledger-2.8.14/SL/IR.pm differ
Files sql-ledger-2.8.13/SL/IS.pm and sql-ledger-2.8.14/SL/IS.pm differ
Files sql-ledger-2.8.13/SL/OE.pm and sql-ledger-2.8.14/SL/OE.pm differ
Files sql-ledger-2.8.13/SL/RP.pm and sql-ledger-2.8.14/SL/RP.pm differ
Files sql-ledger-2.8.13/VERSION and sql-ledger-2.8.14/VERSION differ
Files sql-ledger-2.8.13/bin/lynx/arapprn.pl and sql-ledger-2.8.14/bin/lynx/arapprn.pl differ
...
...

Code changes

  freebsd# diff -r -u sql-ledger-2.8.13 sql-ledger-2.8.14

diff -r -u sql-ledger-2.8.13/SL/Form.pm sql-ledger-2.8.14/SL/Form.pm
--- sql-ledger-2.8.13/SL/Form.pm        2008-02-17 17:57:39.000000000 +0000
+++ sql-ledger-2.8.14/SL/Form.pm        2008-03-19 14:54:21.000000000 +0000
@@ -78,7 +78,7 @@

   $self->{menubar} = 1 if $self->{path} =~ /lynx/i;

-  $self->{version} = "2.8.13";
+  $self->{version} = "2.8.14";
   $self->{dbversion} = "2.8.8";

   bless $self, $type;
@@ -2330,7 +2330,7 @@

   # remove expired locks
   $query = qq|DELETE FROM semaphore
-              WHERE expires < $expires|;
+              WHERE expires < '$expires'|;
   $dbh->do($query) || $self->dberror($query);
...
...

2. Preparing and Applying Patch

Prepare a file containing changes in new version

freebsd# diff -r -u sql-ledger-2.8.13 sql-ledger-2.8.14 > changes2814.diff

Now changes2814.diff will contain all the changes made in new version. You can apply these changes to old version to bring it update to new version with a single command:

freebsd# patch -p0 -r sql-ledger-2.8.13 < changes2814.diff

Hmm...  Looks like a unified diff to me...
The text leading up to this was:
--------------------------
|diff -r -u sql-ledger-2.8.13/SL/Form.pm sql-ledger-2.8.14/SL/Form.pm
|--- sql-ledger-2.8.13/SL/Form.pm       2008-02-17 17:57:39.000000000 +0000
|+++ sql-ledger-2.8.14/SL/Form.pm       2008-03-19 14:54:21.000000000 +0000
--------------------------
Patching file sql-ledger-2.8.13/SL/Form.pm using Plan A...
Hunk #1 succeeded at 78.
Hunk #2 succeeded at 2330.
Hmm...  The next patch looks like a unified diff to me...
The text leading up to this was:
--------------------------
|diff -r -u sql-ledger-2.8.13/SL/IC.pm sql-ledger-2.8.14/SL/IC.pm
|--- sql-ledger-2.8.13/SL/IC.pm 2007-12-28 23:55:33.000000000 +0000
|+++ sql-ledger-2.8.14/SL/IC.pm 2008-03-05 20:02:07.000000000 +0000
--------------------------
Patching file sql-ledger-2.8.13/SL/IC.pm using Plan A...
Hunk #1 succeeded at 199.
...
...

Now both sql-ledger-2.8.13 and sql-ledger-2.8.14 contain 100% exact code. Let us see what happens when we run diff on these two folders

freebsd# diff -r -u sql-ledger-2.8.13 sql-ledger-2.8.14

Only in sql-ledger-2.8.13/SL: Form.pm.orig
Only in sql-ledger-2.8.13/SL: IC.pm.orig
Only in sql-ledger-2.8.13/SL: IR.pm.orig
...
...

Form.pm.orig (and other .orig files) is the file which was created by patch command and contains the file contents before applying the patch. These files can be deleted safely.

3. Upgrading Your Custom Sql-Ledger

Let us say we have our custom sql-ledger which is a modified copy of sql-ledger-2.8.13 and resides in folder 'ourledger-2.8.13'. Our goal is to bring all changes in 2.8.14 to our custom version. Here are the steps:

Prepare the patch for differences in 2.8.13 and 2.8.14 as described above.

Make sure that your changes in 'ourledger-2.8.13' do not conflict with original author's changes in 2.8.14. You can do this by reviewing changes in 'changes-2814.diff'. See notes below

Apply the patch to 'ourledger-2.8.13'.

freebsd# patch -p0 -r ourledger-2.8.13 < changes2814.diff

4. Notes

Here are few points to keep in mind:

Your patch will apply cleanly even if lines have been added or deleted from the SL/Form.pm provided that your changes do not conflict with the changes of the original author.
If your changes conflict with the original author changes, you need to manually adjust the code by discarding some of your changes or discarding the original author changes. For this purpose, you can make adjustments directly in changes2814.diff'
If you do not want to apply a particular change by the orignal author, you can delete the relevant hunks from changes-2814.diff before applying it to your version of sql-ledger

enhanced_sql-ledger

Sat, 01 May 2010 03:30:19 +0000

enhancements

Sat, 01 May 2010 03:04:29 +0000

faq

Thu, 03 Sep 2009 14:56:56 +0000

Frequently Asked Questions

Account management

How do I cancel my account?

Send an email with subject 'Cancel my account' to support@ledger123.com at least one day before your next billing date.

If you cancel within first month of joining us, your entire hosting fee is refunded.

After first month, if you cancel any time during the month, you are charged for the whole month.

Do you provide phone support?

No. not at this time.

To provide best possible support through email, we use RT. All your requests are assigned a number and resolved as soon as possible.

This helps us keep our prices low and keep us focused on enhancing our services.

Hosting service

I want xxxx feature. Will you add it?

We are always working hard to enhance the application and hosting features. We would love to hear from you but we can't promise it will be added. Your request will be added to the features queue. If there is enough demand for this feature, it will get implemented.

You can, however, always sponsor a new feature by paying for it and get it added on priority basis. Contact sales@ledger123.com.

Where are your servers located?

We host our servers at joyent.com and slicehost.com.

Can I download my data if I don't want to use your service anymore?

You can download your data backup using System–Backup menu. Backup file is in text format and intended for import in postgresql database. With minor changes it can be imported in any database software.

We are also planning other formats (csv, html) but currently there is no release date has been decided.

Can I import data from my old accounting software?

Yes, we offer CSV import for all type of transactions. You need to export data from your old accounting software in CSV format. You can then format it and import it into your hosted account.

See this wiki for details.

If you are having difficulty, open a support ticket.

What are the limitations of free edition?

We have recently removed all limitations of the free hosting plan. The only limitation now is that only a single user can access the free edition.

Can I switch back to my locally hosted sql ledger?

Yes, you can. With every customization or feature, we make sure it does not break backward compatibility with the official sql ledger release.

Download backup of your data and restore it in your local installation. Your all data and reports will be fully functional and accurate. The only downside is that features added by us will not be available.

Do you offer custom forms/reports/modules development for my local hosting?

Yes. We do offer custom sql-ledger development. Contact sales@ledger123.com with your requirements.

forms

Thu, 03 Sep 2009 13:45:53 +0000

Forms and Templates

Print forms for invoices, orders, quotations and financial reports can be customized by you by editing form templates. There are three type of templates:

Type 1: HTML Templates

HTML templates are easier to modify because it of wide spread knowledge of html. Only basic html knowledge is required to edit html templates.

Attach:templates1.jpg

Type 2: Latex Templates

Latex templates are bit complex to understand and modify but offer complete control over printed invoice, order or quotation forms. See below for basic introduction to latex.

Attach:templates3.jpg

Type 3: Text Templates. Used only with Point-of-Sale interface

Text templates are used only for Point-of-Sale receipts printing. These templates allow you to print on 40 character receipt printers.

Attach:templates4.jpg

1. Editing Templates

Templates can be edit through sql-ledger. When you click on a template, it is displayed with 'Edit' button at the end of the template. Clicking the 'Edit' button will open the template in a text box where it can be edited and saved.

Attach:templates2.jpg

2. Template Variables

Sql-ledger replaces actual data into templates using variables which we call template variables. Template variables are enclosed within <% and %>.

Here are some template variables to give you an idea of these:

<%name%>
<%address1%>
<%address2%>
<%city%>
<%state%>
<%zipcode%>
<%country%>
<%contact%>

<%invnumber%>
<%invdate%>
<%duedate%>
<%ordnumber%>
<%employee%>
<%shippingpoint%>
<%shipvia%>

<%runningnumber%>
<%number%>
<%description%>
<%deliverydate%>
<%qty%>
<%unit%>
<%sellprice%>
<%discountrate%>
<%linetotal%>

The best way to view all these template variables and understand their usage is by going through existing templates.

3. Template Control Structure

Template processing engine in sql-ledger allows simple if statement and loops. Example of these are described below:

* 'if' conditions to print a column data conditionally:

          <%if contact%>
          <br><%contact%>
          <br>
          <%end contact%>

          <%if taxincluded%>
          <th colspan=7 align=right>Total</th>
          <td colspan=2 align=right><%invtotal%></td>
          <%end taxincluded%>

          <%if not taxincluded%>
          <th colspan=7 align=right>Subtotal</th>
          <td colspan=2 align=right><%subtotal%></td>
          <%end taxincluded%>

        <%if paid%>
        <tr>
          <th colspan=7 align=right>Paid</th>
          <td colspan=2 align=right>- <%paid%></td>
        </tr>
        <%end paid%>

'for' loop to print all lines on an invoice:

        <%foreach number%>
        <tr valign=top>
          <td align=right><%runningnumber%>.</td>
          <td><%number%></td>
          <td><%description%></td>
          <td><%deliverydate%></td>
          <td align=right><%qty%></td>
          <td><%unit%></td>
          <td align=right><%sellprice%></td>
          <td align=right><%discountrate%></td>
          <td align=right><%linetotal%></td>
        </tr>
        <%end number%>

        <%foreach tax%>
        <tr>
          <th colspan=7 align=right><%taxdescription%> on <%taxbase%> @ <%taxrate%> %</th>
          <td colspan=2 align=right><%tax%></td>
        </tr>
        <%end tax%>

4. An Introduction to Latex

Latex is a complete collection of software tools to create high quaility print documents. Latex templates are used in SQL-Ledger to create high quality print forms like invoices, purhcase orders etc.

Latex is included with Redhat distributions (rpm -qa | grep tetex). For FreeBSD, you can install the teTex port from /usr/ports/print/teTeX.

Latex migh seem overwhelming to a new comer but it is really a simple toolkit to use for customizing the SQL-Ledger templates. In this very short introduction of Latex, we shall go through the basic document format and its use in SQL-Ledger.

Hello world!

1. Create a text file (hello.tex) in your home folder with following text:

    \documentclass[a4paper,11pt]{article}
    \begin{document}
    Hello world!
    \end{document}

2. Compile this tex file into dvi file and use xdvi to view it:

    latex hello.tex
    xdvi hello.dvi

You can also convert it to pdf:

    pdflatex hello.tex
    xpdf hello.pdf

Structure of a Latex Document

Latex commands start with a backslash (\). Parameters can follow the command. Optional parameters are enclosed in [] while mandatory ones are enclosed in {}. {} can also be used to terminated a command mixed within some text (to make it easier to understand the command for the compiler). Special characters in latex (#, $, %, ^, &, _, {, }, ~) are escaped with \ except for the \ character itself (because
is used to break a line). To use literal backslash (\) use can use special command $\backslash$.

Single line comments start with % while multi-line comments can be enclosed between \begin{comment} and \end{comment} structure.

Every latex document starts with \documentclass with parameters ([a4paper,11pt]{article}) following it

getting_started

Thu, 03 Sep 2009 16:05:30 +0000

Initial Setup

We install a sample chart of accounts when your hosting is created. Following steps will ensure smooth sailing with your hosted sql-ledger.

#	Initial step	Menu Option
1	Change your initial password.	Preferences
2	Review the install chart of accounts. Modify if required.	System__Chart_of_Accounts
3	Review tax definitions. Modify if required	System–Taxes
4	Specify your company name and address which will be printed on forms.	System–Defaults
5	Review document numbering defaults. Modify if required.	System–Defaults
6	Upload your logo in PNS and EPS format. Logo is printed on invoices/orders/quotations forms and displayed on login screen and dashboard.	System–Upload Logo
7	Create one or more customers.	Customers–Add Customer
8	Create one or more vendors.	Vendors–Add Vendor
9	Create groups, parts and services.	Goods & Services
10	Enter beginning balances	see below
11	Add one or more users who can login and work on your hosted sql-ledger.	System–Users (optional)
12	Create warehouses if you maintain inventory at multiple warehouses.	System–Warehouses
13	Create departments if you track income/expenses by departments.	System–Departments

Once you have defined the above things you are ready for your day to day usage.

Beginning balances

To enter beginning balances for different accounting entities follow these steps:

Entity	Transaction to establish beginning balance
Customers	AR–Add Transaction
Vendors	AP–Add Transaction
Chart of accounts	General Ledger–Add Transaction
Parts	AP–Vendor Invoice

Notes:

For parts beginning balances, you can add a new vendor named 'Beginning Balance' and add an invoice for this vendor with quantity and cost of parts in the price column.

goods_and_services

Thu, 03 Sep 2009 12:22:29 +0000

Goods & Services

Goods and Services menu allows you to define goods and services that you purchase and sell as part of running your business.

Parts

Parts are tangible items you keep in your stock. You purchase them from your vendors and sell them to your customers for profit.

Services

Services are those items for which you do not track onhand quantities.

Labor/Overhead

Groups

Groups are used to group togather the parts and services. You can filter parts and services reports by selecting a group on search screens.

Groups have another useful functionality. When you check the POS button box during group add or change, they appear as buttons on POS (point-of-sale) screens making it easier to select items within each group.

Price groups

MyLedger has very flexiable pricing mechanism. For example:

You can define customer specific prices for each part.
You can define quantity breaks. (If someone buys 10 units instead of 1, he/she can automatically gets lower price.)
And you can specify start and end dates to offer a special price during, for example, Christmas season.

Price groups take this concept further and allow you to define 'groups' of special prices. Let us say you sell to distributor, dealer and end-user. Each of these groups of customers gets tiered discount/price.

There are three steps to use price groups:

Create three price groups; distributor, dealer and enduser. (Goods & Services-Add Pricegroup)
Define item prices for these price groups. To do this, open the item for editing and select the price group and set the price according to the price group tier. Leave the customer column blank. Repeat this for all items. (Clicking 'Update' will allow you to set prices for multiple pricegroups for a single item.)
Open the customer record for editing and set the applicable price group for that customer.

graphs

Thu, 06 May 2010 12:05:10 +0000

installation

Thu, 06 May 2010 12:05:10 +0000

ledgercart

Sun, 27 Jun 2010 07:14:57 +0000

new_forms

Sat, 23 Jan 2010 06:15:58 +0000

New forms

You can easily build your own data entry forms and reports and integrate seamlessly into sql-ledger. We provide sample code on this page which makes it much easier for you. The code contains comments which will help you to adapt it for your own tables.

1. Screen Shots

Here are screen shots from sampleform.pl installed and running.

2. Installation

This sample form is minimal Perl code required to build and integrate a new data entry form into sql-ledger. This sample form allows you to add, change, delete and search data in the given database table.

Steps to install the sample form:

1. Download the sampleform.perl and rename it to sampleform.pl

2. Create the new table in your dataset by running this sql statement through phppgadmin, pgadmin or psql.

CREATE TABLE production (
	id		INTEGER DEFAULT NEXTVAL('id'),
	reference	TEXT,
	transdate	DATE DEFAULT current_date,
	parts_id	INTEGER,
	description	TEXT,
	qty		FLOAT,
	cost		FLOAT,
	notes		TEXT,
	employee_id	INTEGER
);

3. Copy this script to your ../sql-ledger/bin/mozilla/ folder.

4. Create a gateway script in ../sql-ledger/ folder by copying any existing gateway script. For example; cp gl.pl sampleform.pl

5. Edit your custom_menu.ini and add the following lines:

[Sample Form]

[Sample Form--Add]
module=sampleform.pl
action=add

[Sample Form--Reports]
module=sampleform.pl
action=search

6. Optionally run perl locale.pl in your language folder if you are not using the default English language.

You are done.

3. Tips to build forms on your own data tables.

There should be a column with auto-generated values in your database table. It should be named id. Some thing like id INTEGER DEFAULT NEXTVAL('id') in your table definition will accomplish this.
You can copy/rename sampleform.pl (form1.pl, form2.pl, assets.pl etc.) to create any number of forms.

We can also build a custom form for you with all sql-ledger features and integration. For details send email to sales@ledger123.com.

new_reports

Sat, 23 Jan 2010 06:29:47 +0000

New Reports

With our script customreports.pl and some knowledge of sql-ledger database, you can create custom reports without much effort and which are integrated directly into sql-ledger.

customreports.pl is minimal Perl code required to build and integrate a custom report into sql-ledger.

1. Installation

1. Download customreports.perl, rename to customreports.pl and copy to ./sql-ledger/bin/mozilla/ folder.

2. Create a gateway script in ./sql-ledger/ folder by copying any existing gateway script. For example:

$ cp gl.pl customreports.pl

3. Create or edit your custom_menu.ini and add the following lines:

  [Custom Reports]
  [Custom Reports--Customer List]
  module=customreports.pl
  action=customer_search

4. Optionally run 'perl locale.pl' in your language folder if you are not using the default English language.

You are done.

2. Some Details

This sample report consists of two procedures, 'customer_search' and 'customer_report'.

customer_search displays a basic criteria form and when the user clicks the Continue button, customer_report displays the data.

3. More Reports

Once you have installed this sample report, you can modify it to suite your own needs. To modify this report, you need some knowledge of SQL. See sql queries for some sample sql statements you can use to build new reports.

To create more reports, just copy these two procedures (customer_search, customer_report) within the same .pl file and rename them accordingly. Put your SQL statement and make other changes. A working report should be ready within 5-15 minutes.

Too difficult for you? We can build a custom report based on customreports.pl. Email us at sales@ledger123.com for details.

pos

Mon, 01 Jun 2009 04:27:18 +0000

postgresql

Thu, 06 May 2010 12:05:08 +0000

printing

Tue, 16 Mar 2010 14:49:03 +0000

projects_and_time_cards

Thu, 03 Sep 2009 14:49:18 +0000

Projects

Projects are optional and can be used for following things:

Track income and expenses to specific projects using invoices and general ledger transactions.
Enter time card data.

Notes: Projects lookup appears on transactions forms only if you have created at least one project.

1. Managing Projects

You can add or change projects through Projects menu.

2. Using Projects

Once you have defined projects, you can use them in sales and purchase invoices.

purchase_and_sales_orders

Sun, 07 Mar 2010 12:15:42 +0000

recurring

Thu, 03 Sep 2009 13:35:36 +0000

Recurring Transactions

Recurring Transactions allow you to auto-generate pre-defined invoices, transactions and orders.

This feature can be used for the following:

Recurring billing to a customer (For rent, web hosting, school fee, installment etc.)
Recurring billing from your vendor
Monthly orders to your vendors or from your customers.
Monthly payroll posting using GL Recurring Transactions.
Month-end adjustments and allocations.

1. Scheduling

To generate the next number for a given transaction, leave the Next Number blank.

2. Generating

When recurring transactions are due you are reminded when you login to sql-ledger. With a single click you can generate all recurring transactions, print or email invoices and orders.

repgen_intro

Wed, 10 Feb 2010 14:42:26 +0000

What is report generator?

Generates Perl code for a given SQL SELECT query based on sql-ledger tables. Generated Perl code contains all standard sql-ledger report features plus a few more. Drop this code in a pl file in your sql-ledger folder, add a menu option and start using the report.

How it works?

Step 1: Input report name, report title and SQL query at codegen.ledger123.com

Step 2: Download the generated script and add it to bin/mozilla/custom_rp.pl

Step 3: Add report to menu

[Sales Report]
module=rp.pl
action=sales_search

Step 4: Run the newly generated report

Features of generated report

Select/de-select columns to include in report.
Totals/subtotals for numeric columns.
Grouping on the sorted column with subtotals. (optional)
Summary / detail reports (optional)
Range (from/to) search on numbers and date columns.
Search boolean columns with Y/N.
Drop down fields on search screen for if any of the these columns is found in query: customer, vendor, parts, groups, accounts, employees, salesperson, currency.
CSV,TSV,XML export of report data.
Complete foreign language/locale support. (All sql-ledger languages are supported)
Save displayed report definition (search conditions, selected columns, grouping etc.) and recall with a single click.

Under the hood

At the heart of this report generator is an extensive data dictionary containing information about sql-ledger tables, columns data types, standard labels, sizes, relationships etc. The code generation script takes columns names from the input query and using the data dictionary generates a complete Perl report script.

Free demo/trial

To make sure this service meets your reporting requirements we offer free demo. Just send us report description or SQL query. We shall build the report for you to make sure you get what you are expecting.

Tips/Limitations

To add a hard-coded WHERE clause, edit the generated script and replace the line “WHERE 1 = 1” with your hard coded clause.
To add GROUP BY/HAVING clauses, edit your SELECT query in the generated report.
UNION queries are not supported directly. Work around is to generate the report with one part of union query and then add the complete query by editing the generated code.
If you want to add a column on search screen but not in SELECT list, here is the trick:
1. Generate your report with the column in SELECT list.
2. Edit the generated code and remove the column name from query SELECT list and @columns array.

Frequently asked questions

Q: I don't know SQL. Do you provide any help?

A: You can do one of two things:

Find a pre-written query from this page and add/remove columns according to your requirements.
Email us your requirement at support@ledger123.com, we shall write query for you and send it you.

Q: I know SQL but not familiar with sql-ledger tables and relationships? Is there any help.

A: Here is brief introduction of sql-ledger schema as well as list of all tables and columns.

Q: Do you offer money back gurrantee?

A: No. We offer free trial/demo for any number of reports so that you can make sure that this service fulfills your expectations before placing your order.

Q: I am trying to write a query to get the information I want but it seems that it is not possible with SQL. What should I do.

Let us know your requirements. We may write a query for you.
If you know Perl generate a report on your main query and modify the generated Perl code.
As as last resort you may need our custom report development service. Email for details.

shipping_module

Thu, 03 Sep 2009 15:00:01 +0000

Shipping Module

Shipping module allows you to ship from and receive to warehouses from your orders. Here is the work flow to use the shipping module.

Create a sales or purchase order.
Ship/Receive this order from/to a warehouses.
Open the order and create invoice from it.

See also the documentation of orders entry module.

Shipping module serves the same purpose as putting the quantity in Ship or Recd column of a sales order or a purchase order but allows a different warehouse to be specified and maintain inventory quantities at warehouses.

Following paragraphs discuss the correct work flow to use the shipping module for purchases and sales.

1. Purchases

Create purchase orders for the inventory you want to purchase. If you do not specify a warehouse with order, you can receive the order to any warehouse.
Receive inventory using Shipping–Receive menu option. Select the desired warehouse during this process.
Create AP/Vendor invoice by opening the purchase order which has been received in the above step and clicking the Purchase Invoice button. Do not make any change to partnumber or quantity. Just click the Post button.

Note: When you create invoice from a partially received order, SL closes that order and creates a new one with the remaining order quantities but with same order number.

2. Sales

Create a sales order for the inventory you want to sell. If you do not specify a warehouse with order, you can ship the order from any warehouse.
Ship the order using Shipping–Ship. Shipping warehouse cannot be changed if you have specified one on the order.
Create AR/Customer invoice by opening the sales order which has been shipped in the above step and clicking the Sale Invoice button. Do not make any change to partnumber or quantity. Just click the Post button.

Note: When you create from a partially received order, SL closes that order and creates a new one with the remaining order quantities but with same order number.

3. Reports

Inventory onhand at warehouses:
1. Goods & Services–All Items report. Check the 'Warehouse' checkbox on search screen.
2. Warehouses–Reports–Onhand
Inventory receive/ship activity
1. Warehouses–Reports–Activity report.

4. Precautions

Do not do any of the following things when using the shipping module. It will make your inventory records incorrect.

Creating any new sale or purchase invoices directly (that is, without going through the order/ship/receive steps)
Editing any existing invoices.
Receiving purchase orders directly by putting the qty received in Recd column.
Shipping sales orders directly by putting the qty shipped in Ship column.

sql_queries

Sat, 13 Mar 2010 15:40:34 +0000

start

Sun, 27 Jun 2010 07:34:32 +0000

tables_columns

Thu, 06 May 2010 12:05:09 +0000

taxes

Thu, 06 May 2010 12:05:09 +0000

tips_n_tricks

Sat, 23 Jan 2010 07:22:24 +0000

Tips and Tricks

Making warehouse column mandatory

You have defined warehouses and want to make warehouse column on invoices mandatory so that users don't forget to specify a warehouse. Just add the following line in the update procedure (sub update) in bin/mozilla/is.pl and bin/mozilla/ir.pl

 $form->isblank('warehouse', $locale->text('Warehouse cannot be blank'));

You can use this function to make any column mandatory on any form. Just lookup for “sub update” or “sub save” in the required perl file and put an appropriate isblank line.

Renumbering customer numbers

You can renumber customers with the following code snippet. This code snippet calls sql-ledger API to do the renumbering. Set the required initial number in system defaults.

1;

sub renumber {
   $form->header;
   my $dbh = $form->dbconnect(\%myconfig);
   my $query = qq|SELECT id, name FROM customer ORDER BY name|;
   my $sth = $dbh->prepare($query);
   $sth->execute or $form->dberror($query);
   my $updatequery = qq|UPDATE customer SET customernumber = ? WHERE id = ?|;
   my $updatesth = $dbh->prepare($updatequery);
   while ($ref = $sth->fetchrow_hashref(NAME_lc)){
        $form->{customernumber} = $form->update_defaults($myconfig, "customernumber", $dbh);
        $updatesth->execute($form->{customernumber}, $ref->{id});
        print qq|$form->{customernumber} assigned to $ref->{name}<br>|;

   }
   $dbh->disconnect;

}

Installation:

Put the above code in bin/mozilla/custom_ct.pl
Add following lines to custom_menu.ini. This option will appear in menu. Click to renumber the customers.

[Renumber Customers]]
module=ct.pl
action=renumber

With minor change you can use it to renumber parts, invoices, gl transactions etc.

Make Salesperson readonly for non-admins on POS Interface

Following small patch will restrict the user to the default salesperson on POS interface if his/her role is not 'administrator'.

[sqlledger@home ~/public_html]$ svn diff
Index: bin/mozilla/pos.pl
===================================================================
--- bin/mozilla/pos.pl  (revision 49)
+++ bin/mozilla/pos.pl  (working copy)
@@ -181,6 +181,14 @@
              </tr>
 | if $form->{selectemployee};

+   ($employeename) = split(/--/, $form->{employee});
+   $employee = qq|
+             <tr>
+               <th align=right nowrap>|.$locale->text('Salesperson').qq|</th>
+               <td colspan=3>$employeename</td>
+               <input type=hidden name=employee value="$form->{employee}">
+             </tr>
+| if $myconfig{role} ne 'admin';

   if (($rows = $form->numtextrows($form->{description}, 60, 5)) > 1) {
     $description = qq|<textarea name="description" rows=$rows cols=60 wrap=soft>$form->{description}</textarea>|;

Advance payments

Usually you apply your cash payments (or receipts) to specific invoices.

Sometimes the customer pays an advance and there is no outstanding invoice. Using Cash–Receipt menu option, you can simply post the payment to the customer without applying it to an invoice.

When the invoice comes in from customer, you record the invoice in the normal way.

To adjust the advance, goto Cash–Receipt menu option and select the customer. You will be shown with the outstanding advance and the unpaid invoice from the customer. Check both transactions and click 'Post' button. The advance payment will be adjusted against the new invoice.

Stock adjustment

Use vendor invoice to with +ve qauntity (to increase) or -ve quantity (to decrease) to adjust your stock level. Use 0 for sell price.
Or you can use a sales or purchase order and fill in a quantity in the “Ship” column.

Sales return adjustment

You record returns from customer using AR–Credit Invoice and AR-Credit Note. There are two possibilities now:

You adjust the credit amount to other invoices of the customer. The process is same as adjusting the advance against the customer invoices.
You give the cash back to the customer.

User preferences

Use user_io.pl and put variables there. You can then utilize these variables anywhere you like.

upgrade

Thu, 21 Jan 2010 10:19:06 +0000

Upgrades

Note: Hosted customers are always upgraded to latest releases.

New sql-ledger releases come out with passage of time. It is easy to upgrade to new versions. Here is how you can do it.

Important: Backup your dataset and your sql-ledger installation folder. This will allow you to restore and try again if any thing goes wrong during upgrade.

Method 1

Download and untar the latest sql-ledger-x.xx.tar.gz over your existing installation.

This should be all you need to upgrade if there is not schema change. If there is some schema change (tables or columns added in new version), these changes will be applied once your first login after upgrade.

Method 2

Move to your sql-ledger installation folder

cd /usr/local/sql-ledger/

Download and run latest setup.pl:

   wget http://sql-ledger.org/source/setup.pl
   perl setup.pl

setup.pl will lookup the latest version and will offer something like following:

(u)pgrade to 2.8.11

(i)nstall 2.8.11 (from Internet)

(d)ownload 2.8.11 (no installation)

Type 'u' to upgrade your installation.

Failed upgrades

Sometimes dataset upgrade fails with an error message when you login after application upgrade. System is locked down. The best course of action in this case is:

Remove the nologin file from users folder to unlock the system.

Restore your previous backup and try again by rectifying the error encountered.

You probably might need to manually upgrade your dataset by running the appropriate Pg-upgrade* scripts from sql folder.

usage_guide

Thu, 03 Sep 2009 12:13:51 +0000

Usage Guidelines

SQL-Ledger is a browser-based application. Anybody familiar with internet browsing should feel at home using sql-ledger.

All Sql-Ledger user-interface elements (menu, forms, reports) are displayed using standard HTML. You can use any modern browser to run the application. Firefox is recommended.

Main menu

Sql-ledger menu is displayed in left frame. Menu items can be collapsed into sub menus or individual menu items by clicking them.

Update button

Lookup information (customer/vendor address) and calculations (invoices subtotals etc) are not automatically updated on a form after a change. You need to click the Update button to do these.

Update button does not store anything into database. It just looks up appropriate missing information, does any pending calculations, and adds one more line on multi-line forms like invoices and orders.

It is safe to click Update button as many times as you like. You can also trigger update action by pressing 'Enter' key in any text field.

Adding, changing and deleting records

You can add a new transaction (order, invoice etc.) or a master record (part, customer, vendor etc.) directly through the module menu.

To change or delete a transaction or master record, you use the Reports menu option in the sub menu. Once you display the report by clicking the Continue button, you can use the hyper links on report to change or delete the record.

Example

For example to add a new sale invoice, you click AR–Sale Invoice. To change or delete an existing invoice, you click AR–Reports–Transactions. A selection form is displayed where you can specify date range and include/exclude columns.

When you click Continue, all existing invoices are displayed. Now you can open any existing invoice by clicking invoice number hyper link.

Customers and vendors lookup

There are two ways customer / vendor can be selected on an invoice (or order, quotation).

Method 1

By default customer and vendors are displayed as drop down menus on invoices, orders and quotations.External Link

Method 2

If you have a large number of customers and/or vendors, it is more convenient to select customer by typing its number or name. To do so, goto Preferences and change Dropdown Limit to 0. Now invoice form will allow you to enter customer number or part of customer name to select it.

Parts Lookup

There are three possible ways you can lookup a part on an invoice, order or quotation.

Method 1: Specify Part Number

You can lookup a part by its number.

If you enter partial number, all parts matching this number will be displayed.

You can select the required part and click Continue to return back to invoice screen.

Method 2: Specify Part Description (Name)

You can enter complete or partial part description. If there are more than one part descriptions which match, these will be displayed.

Method 3: Specify Group to List Items in the Group

Departments, warehouses and projects

You can classify your transactions by specifying department, warehouse or project during transaction entry. More details on using these features can be found else where on this wiki.

The important thing to note is that you need to define at lease one department, warehouse or project before the respective drop down list will appear on transaction forms.

Item notes, serial numbers, delivery date, packaging information on invoice lines

By defaults line items on invoices show most commonly used columns.

If you want to enter more information against each line item, check the box next to Description heading on line items. Now when you enter an item code and update, you will see additional columns for delivery date, item notes, serial number, project, packaging, net weight, gross weight and volume.

users

Thu, 03 Sep 2009 12:17:25 +0000

User Management

User management is done through System–Users menu. Only admins can access this menu.

Please keep the following points in mind:

New users are added by clicking the Add User button.
Existing users can be edited by clicking the login name which will open the edit user screen as show below.
Access control: You can enable or disable menu options on the add/change users screen. Un-checking a menu option will hide it from that user's login.
Users with role admin can access this user management screen.
Users with role user are restricted to their default departments.

virtual_appliances

Sat, 23 Jan 2010 03:53:12 +0000

Virtual Appliances

Virtual appliances allows you to run sql-ledger and ledgersmb without any installation headaches on your Windows or Linux machines. These appliances are great for testing and learning.

With ever-increasing computer hardware capabilities, virtual appliances seem to be the future of distributing and deploying servers.

We also offer free sql-ledger hosting which can help you evaluate sql-ledger and use it for a small business.

Download and Run

Two appliances are available:

http://download.ledger123.com/FreeBSD-basic-0.10.tar.bz2 (138 MB, 15-Aug-2008)
http://download.ledger123.com/Ledger123-ubuntu810-jeos-0.20.tar.bz2 (351 MB, 15-Jan-2009)

Decompress. Use 7zip to decompress the bz2 file on windows.

Download the free VMware player available at http://www.vmware.com/player.

Open the virtual machine from within the VMware player.

FreeBSD

Point your browser to http://appliance.ip.address/sql-ledger/admin.pl and create datasets. (see below on how to determine the IP address)

Postgres user is 'pgsql' without any password.
phpPgAdmin can be accessed at http://appliance.ip.address/phppgadmin/

Ubuntu Jeos

Point your browser to http://appliance.ip.address/index.html. This page contains links to the installed software.

Postgres user is 'postgres' without any password.

Appliance IP Address

There are two ways to find the IP address of your appliance:

Power the appliance using vmplayer and watch the boot process to see the IP address assigned to the appliance. It will be something like 192.168.128.208 or 172.16.186.136 etc.

Logon to the appliance console. User is root and password is ledger123. Issue ifconfig to display the all IP addresses. Find one which starts from 192 or 172.

Note: Password is ledger123 everywhere.

Support

Support is available. Visit http://www.ledger123.com/order for details.

vouchers

Thu, 03 Sep 2009 14:51:27 +0000

Vouchers

Few notes:

Vouchers allow you to add transactions (GL, AP, payments) without posting these to the general ledger. These transactions are grouped in batches.

A batch may represent group of transactions added on a particular day or at a particular branch etc. It depends upon you.

Once batches have been added, some one else (probably with higher authority) can inspect the batches and post them.

Transaction entry into vouchers is exactly the same as doing it otherwise with the only difference of batch information before making the actual transaction.

warehouses

Sat, 13 Mar 2010 17:41:03 +0000

workflow

Thu, 04 Jun 2009 14:21:52 +0000

yearend

Thu, 03 Sep 2009 13:20:32 +0000

Year End

System–Yearend menu does the period closing in MyLedger. It creates a GL transaction which clears the income accounts and posts the difference (which is income or loss) to the specified retained earnings account.

Please note:

Year-end process can be run daily, weekly, monthly, quarterly or yearly.

The year-end GL transaction is not included in the income statement which covers period containing a closing transactions.

The year-end GL transaction can be viewed through GL reports and edited or deleted as required.