Useful SQL Statements

To remove General Ledger / Journal Entries before a certain date.

DELETE FROM acc_journal_entry WHERE journaltxnid IN (SELECT pkid FROM acc_journal_transaction WHERE transactiondate < '2003-01-01');
DELETE FROM acc_journal_transaction WHERE transactiondate < '2003-01-01';

To retrieve Negative Stock Balance

SELECT ngt.*, loc.location_code FROM (select stk.locationid,stk.pkid AS stockid,stk.bal, stk.unit_cost_ma ,stk.bal * stk.unit_cost_ma AS amt, itm.item_code, itm.name ,itm.price_list, itm.price_sale from inv_stock AS stk INNER JOIN inv_item AS itm ON (stk.itemid = itm.pkid) where stk.bal < '0' ORDER BY item_code) AS ngt INNER JOIN inv_location AS loc ON (ngt.locationid = loc.pkid);

To retrieve duplicated serial number

SELECT com2.*, loc.location_code,loc.name FROM (SELECT com1.*, itm.item_code, itm.name FROM (SELECT stk.itemid, stk.locationid, sn.* FROM (select stockid,serial,sum(qty) from inv_serial_number_delta group by stockid,serial having sum(qty)>'1') AS sn INNER JOIN inv_stock AS stk ON (stk.pkid= sn.stockid)) AS com1 INNER JOIN inv_item AS itm ON (com1.itemid=itm.pkid)) AS com2 INNER JOIN inv_location AS loc ON (com2.locationid = loc.pkid);

To retrieve serial number stocks with negative quantity

SELECT com2.*, loc.location_code,loc.name FROM (SELECT com1.*, itm.item_code, itm.name FROM (SELECT stk.itemid, stk.locationid, sn.* FROM (select stockid,serial,sum(qty) from inv_serial_number_delta group by stockid,serial having sum(qty)<'0') AS sn INNER JOIN inv_stock AS stk ON (stk.pkid= sn.stockid)) AS com1 INNER JOIN inv_item AS itm ON (com1.itemid=itm.pkid)) AS com2 INNER JOIN inv_location AS loc ON (com2.locationid = loc.pkid);

To compare inv_stock.bal with inv_stock_delta to see if the transactions tally with the stock balance for ONE item code

SELECT sm2.*, loc.location_code FROM (SELECT sm.*, stk.locationid, bal FROM (select stockid,sum(qty) from inv_stock_delta where itemid IN (select pkid from inv_item where item_code='??????') GROUP BY stockid) AS sm INNER JOIN inv_stock AS stk ON (sm.stockid = stk.pkid)) AS sm2 INNER JOIN inv_location AS loc ON (sm2.locationid = loc.pkid) order by location_code;

To detect inv_stock.bal that has discrepancy with inv_stock_delta.qty (sum of qty)

SELECT rpt3.*, loc.location_code FROM (SELECT rpt2.*, itm.item_code, itm.name, itm.price_list, itm.price_sale FROM (SELECT rpt1.*, stk2.itemid, stk2.locationid FROM (SELECT delta.stockid, delta.delta_sum, stk.bal AS stock_bal FROM (SELECT stockid,sum(qty) AS delta_sum FROM inv_stock_delta GROUP BY stockid) AS delta INNER JOIN inv_stock AS stk ON (delta.stockid = stk.pkid) WHERE delta.delta_sum !=stk.bal) AS rpt1 INNER JOIN inv_stock AS stk2 ON (rpt1.stockid = stk2.pkid)) AS rpt2 INNER JOIN inv_item AS itm ON (rpt2.itemid = itm.pkid )) AS rpt3 INNER JOIN inv_location AS loc ON (rpt3.locationid = loc.pkid);

Sample Output:

---------+-----------+-----------+--------+------------+-----------------+------------------------------------------------------------+---------------

10717 | 989.0000 | 988.0000 | 7740 | 1000 | CCC | Credit Card Charges | 10-LY3

6837 | 1.0000 | 0.0000 | 8658 | 1001 | 805529382145 | Software Microsoft Office 2003 (Student & Teacher Edition) | 20-S14

2470 | 18.0000 | 17.0000 | 6530 | 1001 | SP152 | Speaker Sonic Gear 2.0 Morro 210 Blk/Wht | 20-S14

11711 | 4.0000 | 3.0000 | 9660 | 1000 | 6926898186234 | Headset AVF HM688 | 10-LY3

15842 | 10.0000 | 9.0000 | 11181 | 1000 | 097855028860 | Mouse Logitech Wheel PS/2 Black | 10-LY3

15843 | 1.0000 | 0.0000 | 11182 | 1000 | 097855021335 | Mouse Logitech Wheel PS/2 White | 10-LY3

10306 | 17.0000 | 16.0000 | 5093 | 1000 | 836837001107 | Cooler C.Master Compound R9-Ge7-PTK3 | 10-LY3

12186 | 1.0000 | 0.0000 | 9805 | 1000 | 048231295918 | DVD-RW LG 18x GSA-H42N Black | 10-LY3

10405 | -95.0000 | -101.0000 | 9155 | 1000 | OPENING-BALANCE | Opening Balance | 10-LY3

(9 rows)

To detect duplicated Account Receivable or Account Payable per customer per PC CENTER.

SELECT foreign_key,foreign_table, pc_center_id , count(pkid) FROM acc_nominal_account GROUP BY foreign_key,foreign_table,pc_center_id HAVING count(pkid) > '1';

To detect if the Invoice Outstanding Amount is different form the Invoice Amount - Settled Amount (thru Credit Memo and Receipt).

SELECT cinv.pkid,cinv.time_issued, cinv.total_amt, cinv.outstanding_amt, dl.settlement , cinv.total_amt + dl.settlement AS bal_settlement FROM (SELECT tgt_docid, sum(amount) AS settlement FROM acc_doclink WHERE tgt_docref='cust_invoice_index' GROUP BY tgt_docid) AS dl INNER JOIN cust_invoice_index AS cinv ON (dl.tgt_docid = cinv.pkid) WHERE cinv.total_amt + dl.settlement != cinv.outstanding_amt;

To Check if Invoice Item (Details) Amount Total Up to equals the Invoice Amount

SELECT cinv.pkid, cinv.time_issued, cinv.entity_name, cinv.total_amt, citm.amt, cinv.total_amt - citm.amt AS difference FROM (SELECT invoice_id, sum(total_quantity*unit_price_quoted) AS amt FROM cust_invoice_item GROUP BY invoice_id) AS citm INNER JOIN cust_invoice_index AS cinv ON (cinv.pkid = citm.invoice_id) WHERE cinv.total_amt != citm.amt;

Sample Output:

-------+---------------------+--------------------------------+-----------+---------------+--------------

14388 | 2007-06-13 00:00:00 | NS Jass Fashion | 941.9600 | 801.94000000 | 140.02000000

13104 | 2007-05-10 00:00:00 | Kin Fatt Shoe Trading | 185.0600 | 175.32000000 | 9.74000000

4181 | 2006-05-16 00:00:00 | Bata Franchise Store | 1269.2600 | 1269.26300000 | -0.00300000

2549 | 2006-03-22 00:00:00 | Kedai Kasut Famili S/B (Klang) | 437.8500 | 437.85300000 | -0.00300000

1707 | 2006-04-18 00:00:00 | Ta Sin Trading | 660.8800 | 660.88300000 | -0.00300000

(5 rows)

To pull out very old transactions with document "NOTE" (which is now deprecated), where the AR transaction was created, but DocLink not created (means invoice is not offset).

SELECT lst.* , cust.name FROM (SELECT txn.* ,nom.foreign_key AS custid FROM (select pkid, time_param1,nominal_account, foreign_table AS txn_tbl,foreign_key AS txn_key, amount from acc_nominal_account_txn where foreign_table='acc_generic_stmt' and foreign_key NOT IN (select src_docid from acc_doclink where src_docref='acc_generic_stmt')) as txn INNER JOIN acc_nominal_account AS nom ON (txn.nominal_account = nom.pkid AND nom.foreign_table='cust_account_index') ) as lst INNER JOIN cust_account_index AS cust ON (lst.custid = cust.pkid) order by txn_key;