• From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,165

Topic: Anatomy of a Sales Transaction

In the making of a New Invoice and receiving payment for it, the following SQLs by operation occur:

1. Make a Sales Order, effect a Customer Delivery and then raise an invoice for it (A Non Cash Direct Invoice):

-- Table Prefix _1
-- Order No. 8
-- Sales Delivery No. 5
-- Sales Invoice No. 5
-- debtor_no 4

-- ST_SALESORDER
INSERT INTO `1_sales_orders` VALUES
('8', '30', '1', '0', '1', '1', 'auto', '', NULL, '2018-01-07', '1', '1', 'N/A', NULL, NULL, 'Donald Easter LLC', '0', 'DEF', '2018-01-17', '3', '400', '0', '0');
INSERT INTO `1_sales_order_details` VALUES
(NULL, '8', '30', '103', 'iPhone Cover Case', '2', '50', '2', '0', '0'),
(NULL, '8', '30', '101', 'iPad Air 2 16GB', '1', '300', '1', '0', '0');
INSERT INTO `1_audit_trail` VALUES
(NULL, '30', '8', '1', '2018-01-07 10:09:06', NULL, '3', '2018-01-07', '0');

-- ST_CUSTDELIVERY
INSERT INTO `1_stock_moves` VALUES
(NULL, '5', '103', '13', 'DEF', '2018-01-07', '50', 'auto', '-2', '10'),
(NULL, '5', '101', '13', 'DEF', '2018-01-07', '300', 'auto', '-1', '200');
INSERT INTO `1_debtor_trans` VALUES
('5', '13', '1', '1', '1', '2018-01-07', '2018-01-17', 'auto', '1', '8', '400', '0', '0', '0', '0', '0', '0', '1', '1', '0', '0', '3', '1');
INSERT INTO `1_debtor_trans_details` VALUES
(NULL, '5', '13', '103', 'iPhone Cover Case', '50', '2.38', '2', '0', '10', '2', '11'),
(NULL, '5', '13', '101', 'iPad Air 2 16GB', '300', '14.29', '1', '0', '200', '1', '12');
INSERT INTO `1_gl_trans` VALUES
(NULL, '13', '5', '2018-01-07', '5010', '', '20', '0', '0', NULL, NULL),
(NULL, '13', '5', '2018-01-07', '1510', '', '-20', '0', '0', NULL, NULL),
(NULL, '13', '5', '2018-01-07', '5010', '', '200', '0', '0', NULL, NULL),
(NULL, '13', '5', '2018-01-07', '1510', '', '-200', '0', '0', NULL, NULL);
INSERT INTO `1_trans_tax_details` VALUES (
(NULL, '13', '5', '2018-01-07', '1', '5', '1', '1', '380.95', '19.05', 'auto', NULL);
INSERT INTO `1_audit_trail` VALUES
(NULL, '13', '5', '1', '2018-01-07 10:09:06', NULL, '3', '2018-01-07', '0');

ST_SALESINVOICE
INSERT INTO `1_refs` VALUES
('5', '10', '001/2018');
INSERT INTO `1_debtor_trans` VALUES
('5', '10', '0', '1', '1', '2018-01-07', '2018-01-17', '001/2018', '1', '8', '400', '0', '0', '0', '0', '0', '0', '1', '1', '0', '0', '3', '1'),
INSERT INTO `1_debtor_trans_details` VALUES
(NULL, '5', '10', '103', 'iPhone Cover Case', '50', '2.38', '2', '0', '10', '0', '13'),
(NULL, '5', '10', '101', 'iPad Air 2 16GB', '300', '14.29', '1', '0', '200', '0', '14');
INSERT INTO `1_gl_trans` VALUES
(NULL, '10', '5', '2018-01-07', '4010', '', '-95.24', '0', '0', NULL, NULL),
(NULL, '10', '5', '2018-01-07', '4010', '', '-285.71', '0', '0', NULL, NULL),
(NULL, '10', '5', '2018-01-07', '1200', '', '400', '0', '0', '2', '1'),
(NULL, '10', '5', '2018-01-07', '2150', '', '-19.05', '0', '0', NULL, NULL);
INSERT INTO `1_trans_tax_details` VALUES (
(NULL, '10', '5', '2018-01-07', '1', '5', '1', '1', '380.95', '19.05', '001/2018', '0');
INSERT INTO `1_audit_trail` VALUES
(NULL, '10', '5', '1', '2018-01-07 10:09:06', NULL, '3', '2018-01-07', '0');

2. Receive a payment for the customer and allocate it entireely to the above invoice:

SET @alloc=400;
SET @bankcharge=5;

INSERT INTO `1_refs` VALUES
('4', '12', '001/2018');
INSERT INTO `1_bank_trans` VALUES
(NULL, '12', '4', '1', '001/2018', '2018-01-07', @alloc-@bankcharge, '0', '0', '2', '1', NULL);
INSERT INTO `1_comments` VALUES
('12', '4', '2018-01-07', 'by outstation cheque');
INSERT INTO `1_cust_allocations` VALUES
(NULL, '1', @alloc, '0000-00-00', '4', '12', '5', '10');
INSERT INTO `1_debtor_trans` VALUES
('4', '12', '0', '1', '1', '2018-01-07', '0000-00-00', '001/2018', '0', '0', '400', '0', '0', '0', '0', @alloc, '0', '1', '0', '0', '0', NULL, '0'),
UPDATE `1_debtor_trans` SET `alloc`=`ov_amount`-@alloc WHERE `type`='10' AND `trans_no`='5' AND `debtor_no`='4'
INSERT INTO `1_gl_trans` VALUES
(NULL, '12', '4', '2018-01-07', '1060', '', @alloc-@bankcharge, '0', '0', NULL, NULL),
(NULL, '12', '4', '2018-01-07', '1200', '', -@alloc, '0', '0', '2', '1'),
(NULL, '12', '4', '2018-01-07', '5690', '', @bankcharge, '0', '0', NULL, NULL);
INSERT INTO `1_audit_trail` VALUES
(NULL, '12', '4', '1', '2018-01-07 10:13:49', NULL, '3', '2018-01-07', '0');

Note that the date of allocation is "0000-00-00" in 2 places above.
The actual date of payment is entered in the case of a Direct Invoice in Cash mode.
An audit trail entry is made after each transaction.

This should be useful for anyone trying to directly insert transactions..
All Account codes and rates are as per the demo chart of accounts  - en_US-demo.sql.

apmuthu's Website