Ledger Posting Guide

 

GL Posting Overview
Posting a transaction to GL means to make the accounting system (not only the Nebula Accounting’s database) aware of that transaction. The goal of posting a transaction to GL is to properly update the accounts’ balances. Nebula Accounting provides several helper routines that handle the posting process of account balances, which will be shown in this paragraph.

Nebula Accounting processes data using SQL Server stored procedures called within the ASP pages with a few exceptions (which will be described later in this document) when the processing is done by the ASP pages themselves.

The GL table is called GeneralJournalEntry and it has the following structure:
Field Name Type Length Description
EntryNo nvarchar 20 The reference of the GL entry. It also serves as the primary key.
Date datetime 8 The date when the entry was made.
TrType int 4 An integer indicating the type of the transaction: invoice, etc. Used in reports.
TrID varchar 50 The reference of the transaction. It is used in reports.
SystemGenerated  bit 1 System Generated or Manually Created Indicator.
PostedYN bit 1 Used only for entries made through the Make Journal Entry screen

Nebula Accounting uses working tables before posting the transaction information to GL. One of these tables is GLWorkDetail having the following structure:

Field Name Type Length Description
GLID varchar 50 The master reference in table GeneralJournalEntry (EntryNo field).
No char 10 The line number.
Credit money 8 The credit amount
Debit money 8 The debit amount
Memo varchar 50 Notes of the transaction
Account varchar 50 The account affected by the transaction.

The purpose of this working table is to consolidate the information before submitting to the GeneralJournalDetail table. This way we can avoid situations when an account has both the credit and debit amounts equal to 0, or when it has two or more entries with different credit and debit amounts.

Every stored procedure that posts a transaction to GL must perform the following operations:

1. Creates an entry in the master table GeneralJournalEntry. The EntryNo value will serve as a key for the GLWorkDetail table.

2. Fill the GLWorkTable line-by-line depending on the nature of the transaction, incrementing the No field each time, since along with the GLID this field constitutes the primary key of the GLWorkDetail table. The default increment step for the No field is 10. You will see how this techniques works when we will show you the posting model of several transactions.

Posting the accounts’ balances

The stored procedure PostCOA updates the accounts’ balances. It has the following structure:

CREATE PROCEDURE PostCOA @Account varchar(50), @Debit float, @Credit float AS
IF @Debit <> 0
UPDATE lsAccounts SET Balance = Balance + @Debit WHERE AccountName = @Account
IF @Credit <> 0
UPDATE lsAccounts SET Balance = Balance - @Credit WHERE AccountName = @Account

It takes three input parameters: the account’s name, the debit amount and the credit amount. When this stored procedure is called, one of the two parameters (credit or debit) is zero, they are never both different than zero.

If @Credit is different than 0, then the account’s balance is credited, i.e. the credit amount is substracted from the account’s balance.

If @Debit is different than 0, then the account’s balance is debited, i.e. the debit amount is added to the account’s balance.


Consolidating the GLWorkDetail table

This task is performed by the qrySumGLWorkDetail view in the database, having the following syntax:

CREATE VIEW qrySumGLWorkDetail
AS
SELECT
Account, SUM(Debit) AS SumOfDebit, SUM(Credit)
AS SumOfCredit, Memo, No
FROM GLWorkDetail
GROUP BY No, Account, Memo


The aggregate functions ensure that there aren’t two lines containing the same account number and having different amounts for debit and credit.

Posting to GL
The most important GL posting routine is the stored procedure called PostGLWorkDetail having the following structure:

CREATE PROCEDURE PostGLWorkDetail @GLID varchar(50) AS
DECLARE @Account varchar(50), @Debit float, @Credit float, @Memo varchar(255), @No int
DECLARE @GLWorkDebitAmount float, @GLWorkCreditAmount float

// It takes as input the reference in the GeneralJournalEntry table

DECLARE GL1 CURSOR FOR SELECT Account, SumOfDebit, SumOfCredit, Memo, No FROM qrySumGLWorkDetail
OPEN GL1

// Opens the qrySumGLWorkDetail and formats the amounts

FETCH NEXT FROM GL1 INTO @Account, @Debit, @Credit, @Memo, @No

WHILE @@FETCH_STATUS = 0
BEGIN

IF @Debit <> @Credit
BEGIN

// If both Credit and Debit are greater than zero, adds two records in the GeneralJournalDetail table.

If @Debit > 0 AND @Credit > 0
BEGIN

// first posts the debit

SELECT @GLWorkDebitAmount = @Debit
SELECT @GLWorkCreditAmount = 0

INSERT INTO GeneralJournalDetail(EntryNo, Account, Debit, Credit, Memo, No) VALUES (@GLID ,@Account, @GLWorkDebitAmount, @GLWorkCreditAmount, @Memo, @No)

// calls the Accounts posting routine

EXEC PostCOA @Account, @GLWorkDebitAmount, @GLWorkCreditAmount

// now posts the credit

SELECT @GLWorkDebitAmount = 0
SELECT @GLWorkCreditAmount = @Credit

INSERT INTO GeneralJournalDetail(EntryNo, Account, Debit, Credit, Memo, No) VALUES (@GLID, @Account, @GLWorkDebitAmount, @GLWorkCreditAmount, @Memo, @No)

// calls the Accounts posting routine

EXEC PostCOA @Account, @GLWorkDebitAmount, @GLWorkCreditAmount
END
ELSE
BEGIN
If @Debit > @Credit
BEGIN

// if Debit > Credit, posts the difference as Debit

SELECT @GLWorkDebitAmount = @Debit - @Credit
SELECT @GLWorkCreditAmount = 0
END
ELSE
BEGIN

// if Credit > Debit, posts the difference as Credit

SELECT @GLWorkDebitAmount = 0
SELECT @GLWorkCreditAmount = @Credit - @Debit
END

INSERT INTO GeneralJournalDetail(EntryNo, Account, Debit, Credit, Memo, No) VALUES(@GLID, @Account, @GLWorkDebitAmount, @GLWorkCreditAmount, @Memo, @No)

// calls the Accounts posting routine

EXEC PostCOA @Account, @GLWorkDebitAmount, @GLWorkCreditAmount
END

END

// gets the next record from the qrySumGLWorkDetail view

FETCH NEXT FROM GL1 INTO @Account, @Debit, @Credit, @Memo, @No
END

CLOSE GL1
DEALLOCATE GL1

You will notice that every transaction posting routine ends with a call to this storing procedure. 

Vendor/Purchases Posting Routines  

Receivings  

When a receiving is posted, Nebula Accounting checks whether the items are partially or fully received against the original order. If they are fully received, it updates the inventory quantities, and creates a payable into the system. If the items are partially received, it splits the purchase orders in two: it creates a payable for the items received and then creates a new purchase order containing the items not yet received. 

 

Receiving posting scheme  

Account

Debit

Credit

Accounts Payable

 

X

Expense Account

X

 

Freight

X

 

Other Charges

X

 

Discount

 

X

Item Asset Account

X

 

The stored procedure that handles the purchase order split is called SplitPO and it has the following syntax: 

CREATE PROCEDURE SplitPO @TransID varchar(50) AS 
// it takes as input parameter the Reference of the purchase order to be received 
DECLARE 
@a float, @IDD varchar(50), @NewRef nvarchar(20), @PORef nvarchar(20), 
@Date datetime, @DueDate datetime, @Vendor varchar(50), @Class varchar(50), 
@Memo nvarchar(50), @BillTo nvarchar(150), @ShipTo nvarchar(150), @ReceiveBy datetime,
@VendorMessage nvarchar(50), @ShipVia varchar(50), @TrackingNumber nvarchar(50), 
@VendorInvoice nvarchar(20), @Total float, @Term varchar(50), @ID int, @No int, 
@Item varchar(50), @Quantity float, @ReceivedQty float, @Rate float, @Freight float, 
@Discount float, @OtherCharges float, @Amount float, @Description nvarchar(255), @Project varchar(50) 
BEGIN TRANSACTION DECLARE @DiscAmount float, @DC varchar(50) 
// gets a new document number 
SELECT @IDD = 'REC-' + @TransID 
//Retrieves the fields from the vpPurchaseOrders table for that PO SELECT 
@Date = Date, @DueDate = DueDate, @Term = Term, @VendorInvoice = VendorInvoice, 
@PORef = PORef, @Total = Total, @Vendor = Vendor, @Freight = Freight, 
@Discount = Discount, @OtherCharges = OtherCharges 
FROM vpPurchaseOrders 
WHERE PORef = @TransID 
//finds the sum of the differences between ordered quantities and the received quantities 
Select @a = SUM(Quantity-ReceivedQty) 
FROM vpPurchaseOrderDetails 
WHERE vpPurchaseOrderDetails.PORef = @TransID IF @a=0 
BEGIN 
// if the sum is 0, then the PO was fully received, no split is necessary 
DELETE FROM GLWorkDetail 
// Updates the ReceivedYN flag in the vpPurchaseOrders table indicating the order was fully received UPDATE vpPurchaseOrders 
SET ReceivedYN=1 WHERE vpPurchaseOrders.PORef = @TransID 
// Increase the vendor’s balance 
UPDATE lsVendors SET Balance = Balance + @Total WHERE VendorName = @Vendor 
// Creates the master entry in the GeneralJournalEntry table, setting the TrType to 2 (Receiving) 
INSERT INTO GeneralJournalEntry(Date, EntryNo, TrType, TrID, PostedYN, SystemGenerated) 
VALUES (CONVERT(varchar, CURRENT_TIMESTAMP), @IDD, 2, @TransID, 1, 1) 
// if @Discount is not zero, then post it to the Discounts account 
SELECT @DiscAmount = (SELECT SUM(Amount) 
FROM vpPurchaseOrderDetails 
WHERE PORef = @TransID) * @Discount / 100 
// inserts into the working table the Freight amount 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@IDD, 'Freight Expense', @Freight, 0, 'Freight', 10) 
// inserts into the working table the AP amount 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@IDD, 'Accounts Payable', 0, @Total, 'AP', 20) 
// inserts into the working table the Discount amount 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@IDD, 'Discounts', 0, @DiscAmount, 'Discount', 30) 
// inserts into the working table the Charges amount 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@IDD, 'Other Expense', @OtherCharges, 0, 'Charge', 40) 
// creates a new bill. The reference of the bill is the same as the reference of the purchase order. The other fields are simply copied from the vpPurchaseOrders table 
INSERT INTO vpBills(Date, Vendor, Reference, Type, Term, DueDate, VendorInvoice, Freight, Discount, OtherCharges, Amount, Memo, PayAmount, DueAmount, PostedYN, PORef) 
VALUES(CONVERT(varchar, @Date, 101), @Vendor, @PORef, 'Receiving', @Term, @DueDate, @VendorInvoice, @Freight, @Discount, @OtherCharges, @Total, @Memo, 0, @Total, 1, @TransID) DECLARE @BID int SELECT @BID = 50 
// retrieves the detail items from the purchase order and posts the inventory changes to GL 
DECLARE @AssetAccount varchar(50) 
DECLARE Curs1 CURSOR FOR 
SELECT Item, Quantity, ReceivedQty, Rate, AssetAccount 
FROM vpPurchaseOrderDetails INNER JOIN lsItems ON vpPurchaseOrderDetails.Item = lsItems.ItemName WHERE PORef=@TransID OPEN Curs1 FETCH NEXT 
FROM Curs1 INTO @Item, @Quantity, @ReceivedQty, @Rate, @AssetAccount 
WHILE @@FETCH_STATUS = 0 
BEGIN 
// since the items are received, updates the inventory quantities on hand 
UPDATE lsItems SET QuantityOnHand = QuantityOnHand + @ReceivedQty WHERE ItemName = @Item 
// posts the inventory asset account balance for that item 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@IDD, @AssetAccount , @Rate * @Quantity, 0, 'INV Asset', @BID) SELECT @BID = @BID + 1 //gets the next item from the purchase order 
FETCH NEXT FROM Curs1 
INTO @Item, @Quantity, @ReceivedQty, @Rate, @AssetAccount 
END CLOSE Curs1 
DEALLOCATE Curs1 
// Posts to GL 
EXEC PostGLWorkDetail @IDD END 
ELSE BEGIN 
// The PO must be split, the items were partially received 
DELETE FROM GLWorkDetail 
//Creates a new purchase order containing the items not received. The header information is copied from the original purchase order, except the Reference field, adding “-1” to the original reference number. For example, if the original reference was 1500, the new PO will have the reference 1500-1. 
SELECT @PORef = PORef, @Date=Date, @Class = Class, @Memo=Memo, @BillTo =BillTo,@ShipTo=ShipTo,@ReceiveBy=ReceiveBy,@VendorMessage=VendorMessage, @ShipVia=ShipVia, @TrackingNumber=TrackingNumber, @VendorInvoice = VendorInvoice 
FROM vpPurchaseOrders 
WHERE PORef=@TransID 
INSERT INTO vpPurchaseOrders(PORef, Date, Vendor, Class, Memo, BillTo, ShipTo, ReceiveBy, VendorMessage, ShipVia, TrackingNumber, Freight, Discount, OtherCharges, Total, ReceivedYN ) 
VALUES (@PORef+'-1',@Date,@Vendor,@Class,@Memo,@BillTo,@ShipTo, @ReceiveBy,@VendorMessage,@ShipVia,@TrackingNumber, 0, @Discount, 0, @Total,0) 
// Post the received items. Creates a master entry in the GeneralJournalEntry table 
INSERT INTO GeneralJournalEntry(Date, EntryNo, TrType, TrID, PostedYN, SystemGenerated) 
VALUES (CONVERT(varchar, CURRENT_TIMESTAMP), @IDD, 2, @TransID, 1, 1) 
// retrieves the items list from the original PO 
DECLARE Curs1 CURSOR FOR SELECT ID, PORef, No, Item, Quantity, ReceivedQty, Rate, Amount, Description, Project, AssetAccount 
FROM vpPurchaseOrderDetails 
INNER JOIN lsItems ON vpPurchaseOrderDetails.Item = lsItems.ItemName 
WHERE PORef = @TransID 
SELECT @BID = 10 OPEN Curs1 FETCH NEXT 
FROM Curs1 INTO @ID, @PORef, @No, @Item, @Quantity, @ReceivedQty, @Rate, @Amount, @Description, @Project, @AssetAccount 
WHILE @@FETCH_STATUS = 0 
BEGIN 
IF @Quantity - @ReceivedQty<>0 BEGIN 
// the received qty is different then the ordered qty, Nebula Accounting must insert into the new PO the difference between them 
INSERT INTO vpPurchaseOrderDetails(PORef, No, Item, Quantity, ReceivedQty, Rate, Amount, Description, Project) 
VALUES(@PORef+'-1', @No, @Item, @Quantity - @ReceivedQty, 0, @Rate, @Rate * (@Quantity - @ReceivedQty), @Description, @Project) 
// the original PO must be updated with the received quantities, otherwise it will still appear as not fully received. 
UPDATE vpPurchaseOrderDetails SET Quantity = ReceivedQty, Amount = Rate * ReceivedQty 
WHERE ID=@ID 
// for the received items, posts the inventory asset changes to GL
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@IDD, @AssetAccount, @Rate * @ReceivedQty, 0, 'INV Asset', @BID) 
SELECT @BID = @BID + 10 
END 
ELSE BEGIN
// for this line of the PO, the quantity received is equal to the ordered quantity, so post the asset changes to GL 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@IDD, @AssetAccount, @Rate * @ReceivedQty, 0, 'INV Asset', @BID) 
SELECT @BID = @BID + 10 END // in both cases, updates the inventory quantities on hand 
UPDATE lsItems SET QuantityOnHand = QuantityOnHand + @ReceivedQty WHERE ItemName = @Item 
// gets the next detail item from the original PO 
FETCH NEXT FROM Curs1 INTO @ID, @PORef, @No, @Item, @Quantity, @ReceivedQty, @Rate, @Amount, @Description, @Project, @AssetAccount 
END 
CLOSE Curs1 
DEALLOCATE Curs1 
DECLARE @SumOld float, @DSC float 
// Updates the original PO totals 
SELECT @SumOld = SUM(Amount), @DSC = SUM(Amount) * @Discount / 100 
FROM vpPurchaseOrderDetails WHERE PORef = @TransID 
UPDATE vpPurchaseOrders 
SET ReceivedYN = 1, Total = @SumOld + Freight + OtherCharges - @DSC 
WHERE vpPurchaseOrders.PORef = @TransID 
// Updates the new PO totals 
SELECT @SumOld = SUM(Amount), @DSC = SUM(Amount) * @Discount / 100 
FROM vpPurchaseOrderDetails 
WHERE PORef=@PORef+'-1' 
UPDATE vpPurchaseOrders 
SET Total = @SumOld - @DSC 
WHERE vpPurchaseOrders.PORef = @PORef+'-1' SELECT @SumOld = Total 
FROM vpPurchaseOrders WHERE PORef = @TransID 
// creates a payable into the system for the received PO 
// inserts into the working table the Freight amount 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@IDD, 'Freight Expense', @Freight, 0, 'Freight', @BID) 
// inserts into the working table the AP amount 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@IDD, 'Accounts Payable', 0, @SumOld, 'AP', @BID+10) 
// inserts into the working table the Discount amount 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@IDD, 'Discounts', 0, @DSC, 'Discount', @BID+20) 
// inserts into the working table the Charges amount 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@IDD, 'Other Expense', @OtherCharges, 0, 'Charge',@BID+30) 
// updates the vendor’s balance, by adding the total amount 
UPDATE lsVendors SET Balance = Balance + @SumOld 
WHERE VendorName = @Vendor // posts it to GL EXEC PostGLWorkDetail @IDD 
SELECT @Date = Date, @DueDate = DueDate, @Term = Term, @VendorInvoice = VendorInvoice, @PORef = PORef, @Total = Total, @Vendor = Vendor, @Freight = Freight, @Discount = Discount, @OtherCharges = OtherCharges 
FROM vpPurchaseOrders 
WHERE PORef = @TransID 
// creates the bill for the received PO 
INSERT INTO vpBills(Date, Vendor, Reference, Type, Term, DueDate, VendorInvoice, Freight, Discount, OtherCharges, Amount, Memo, PayAmount, DueAmount, PostedYN, PORef) VALUES(CONVERT(varchar, @Date, 101), @Vendor, @PORef, 'Receiving', @Term, @DueDate, @VendorInvoice, @Freight, @Discount, @OtherCharges, @Total, @Memo, 0, @Total, 1, @TransID) 
END COMMIT TRANSACTION 

Vouchers

Vouchers posting scheme

Account

Debit

Credit

Accounts Payable

 

X

Expense Account

X

 

Freight

X

 

Other Charges

X

 

Discount

 

X

Vouchers are posted with the help of the PostBill stored procedure. 

CREATE PROCEDURE postBILL @Reference varchar(50) AS 
BEGIN 
TRANSACTION 
DELETE FROM GLWorkDetail 
DECLARE @Vendor varchar(50), @Amount float, @Account varchar(50), @ExpAmount float, @GLID varchar(50), @D float, @Freight float, @Charges float, @NewNo int 
// retrieves the header information from the vpBills table 
SELECT @Vendor = Vendor, @Amount = Amount, @D = Discount, @Freight = Freight, @Charges = OtherCharges FROM vpBills 
WHERE Reference = @Reference // increases the vendor’s balance 
UPDATE lsVendors 
SET Balance = Balance + @Amount WHERE VendorName = @Vendor 
// first, creates the master entry in the GeneralJournalEntry. TrType is set to 1 
INSERT INTO GeneralJournalEntry(Date, EntryNo, SystemGenerated, PostedYN, TrType, TrID) 
VALUES (CONVERT(varchar, CURRENT_TIMESTAMP, 101), 'VOUCHER-' + @Reference, 1, 1, 1, @Reference) 
// gets a new document number 
SELECT @GLID = 'VOUCHER-' + @Reference 
// Credits the AP account INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@GLID, 'Accounts Payable', 0, @Amount, 'AP', 10) SELECT @NewNo = 20 
// retrieves the list of accounts from that voucher 
DECLARE Expenses CURSOR FOR 
SELECT Account, Amount FROM vpBillExpenses 
WHERE Reference = @Reference OPEN Expenses FETCH NEXT 
FROM Expenses INTO @Account, @ExpAmount 
WHILE @@FETCH_STATUS = 0 
BEGIN 
// for each voucher detail line, debits the expense account 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@GLID, @Account, @ExpAmount, 0, 'Expense', @NewNo) 
SELECT @NewNo = @NewNo + 10 FETCH NEXT 
FROM Expenses INTO @Account, @ExpAmount 
END 
CLOSE Expenses 
DEALLOCATE Expenses 
DECLARE @Discount float 
// applies discounts, if any 
SELECT @Discount = (SELECT SUM(Amount) 
FROM vpBillExpenses 
WHERE Reference = @Reference) * @D / 100 
// applies freight charges, if any 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@GLID, 'Freight Expense', @Freight, 0, 'Freight', @NewNo) 
SELECT @NewNo = @NewNo + 10 
// applies misc charges, if any 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@GLID, 'Other Expense', @Charges, 0, 'Charge', @NewNo) 
SELECT @NewNo = @NewNo + 10 
// Discount 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@GLID, 'Discounts', 0, @Discount, 'Discount', @NewNo) 
SELECT @NewNo = @NewNo + 10 
// posts it to GL 
EXEC PostGLWorkDetail @GLID 
// marks the voucher as posted 
UPDATE vpBills 
SET PostedYN = 1, PORef = @Reference 
WHERE Reference = @Reference 
COMMIT TRANSACTION

Bill Payments

Bill payment posting scheme

Account

Debit

Credit

Accounts Payable

X

 

Cash Account

 

X

Posting a bill payment to GL is done with the help of the PostBillPayment stored procedure. 

CREATE PROCEDURE postBILLPayment @BillID varchar(50), @Account varchar(50), @Amount float, @CheckNo int, @Date datetime, @NewCheckID int, @Total float 
AS 
// it takes as input parameters the BillID of the bill to be payed, the account to pay from, the amount to be payed, the check number, the date when the payment was done and the total of the bill 
BEGIN TRANSACTION 
DELETE FROM GLWorkDetail 
DECLARE @Vendor varchar(50), @PayAmount float, @OrigAmount float, @Reference varchar(50), @InvDate datetime DECLARE @VenAddr varchar(255), @VenName varchar(50), @VenCity varchar(255), @VenState varchar(25), @VenZip varchar(25) 
// retrieves the header information from the vpBills table 
SELECT @InvDate = Date, @Vendor = Vendor, @PayAmount = PayAmount, @OrigAmount = Amount, @Reference = VendorInvoice FROM vpBills WHERE Reference = @BillID 
// gets a new document number 
DECLARE @GLID varchar(50) SELECT @GLID = 'BILLPMT-'+@BillID 
// updates the bill's payed amount 
UPDATE vpBills SET PayAmount = PayAmount + @Amount, DueAmount = Amount - PayAmount - @Amount WHERE Reference = @BillID // creates the GL header. Sets the TrType to 5 
INSERT INTO GeneralJournalEntry(Date, EntryNo, SystemGenerated, PostedYN, TrType, TrID) 
VALUES (CONVERT(varchar, CURRENT_TIMESTAMP), @GLID, 1, 1, 5, 0) 
// decreases the vendor's balance 
UPDATE lsVendors SET Balance = Balance - @Amount WHERE VendorName = @Vendor 
// fills the GL detail lines with the cash amount and the payable amount 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@GLID, @Account, 0, @Amount, @VenName, 10) 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES (@GLID, 'Accounts Payable', @Amount, 0, @VenName, 20) 
// creates the payment detail line 
INSERT INTO vpBillPayments(Reference, Account, Vendor, Date, CheckNo, Amount) 
VALUES(@BillID, @Account, @Vendor, CONVERT(varchar, CURRENT_TIMESTAMP), @NewCheckID, @Amount) 
SELECT @VenAddr = Address1, @VenCity = City, @VenState = State, @VenZip = Zip 
FROM lsVendors WHERE VendorName = @Vendor 
// updates the check's info 
UPDATE bChecks SET CheckNo = @CheckNo, Account = @Account, Payee = @Vendor, Address = @VenAddr + ', ' + @VenCity + ' ' + @VenState + ' ' + @VenZip, Date = @Date, Amount = @Total 
WHERE CheckID = @NewCheckID 
INSERT INTO bCheckDetails(CheckID, InvDate, InvRef, InvOrigAmount, Amount, BillID) 
VALUES(@NewCheckID, @InvDate, @Reference, @OrigAmount, @Amount, @BillID) 
UPDATE GeneralJournalEntry SET SystemGenerated = 1, PostedYN = 1, TrType = 2, TrID = @GLID 
WHERE EntryNo = @GLID 
// posts it to GL 
EXEC PostGLWorkDetail @GLID 
COMMIT TRANSACTION

Customer/Sales Posting Routines  

Invoices

Invoice posting scheme

Account

Debit

Credit

Accounts Receivable

X

 

Discount

X

 

Sales

 

X

Sales Tax

 

X

Freight Income

 

X

Inventory

 

X

COGS

X

 

The invoices are posted with the help of the PostInvoice stored procedure having the following structure:

CREATE PROCEDURE postINV @InvoiceNo varchar(50) AS 
BEGIN TRANSACTION 
DELETE FROM GLWorkDetail 
DECLARE @total float, @taxable float, @SalesTax varchar(50), @Cust varchar(50), @Freight float, @Handling float 
// retrieves the header information of the invoice 
SELECT @total = Total, @taxable = TaxTotal, @SalesTax = SalesTax, @Cust = Customer, @Handling = Handling, @Freight = Freight FROM csInvoices 
WHERE InvoiceNo = @InvoiceNo 
// adds the salesman's commission, if applicable 
DECLARE @Rep varchar(50) SELECT @Rep = Rep 
FROM lsCustomers 
WHERE CustomerName = @Cust 
IF @Rep<>'' 
BEGIN EXEC addCommission @Rep, @total 
END COMMIT TRANSACTION 
BEGIN TRANSACTION 
DELETE FROM GLWorkDetail 
// updates the inventory 
UPDATE lsItems SET QuantityOnHand = QuantityOnHand - csInvoiceItems.Quantity, TotalValue = Cost * (QuantityOnHand - csInvoiceItems.Quantity) FROM lsItems, csInvoiceItems 
WHERE csInvoiceItems.InvoiceNo = @invoiceNo 
AND lsItems.ItemName = csInvoiceItems.Item 
AND lsItems.ItemType=1 
DECLARE @TaxPercent float 
// gets a new document number 
DECLARE @GLID varchar(50) SELECT @GLID = 'INV ' + CONVERT(varchar, @InvoiceNo) 
// creates a master entry in the GeneralJournalEntry table. Sets the TrType to 4 
INSERT INTO GeneralJournalEntry(Date,EntryNo, SystemGenerated, PostedYN, TrType, TrID) 
VALUES (CONVERT(varchar,CURRENT_TIMESTAMP,101), @GLID, 1, 1, 4, @InvoiceNo) 
// increases the customer's balance 
UPDATE lsCustomers SET Balance = Balance + @total 
WHERE CustomerName = @Cust 
// debits the Account Receivable account 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, 'Accounts Receivable',@total,0,'AR', 10) DECLARE @Discount float 
// applies the discount, if any 
SELECT @Discount = (SELECT SUM(Amount) FROM csInvoiceItems 
WHERE InvoiceNo = @InvoiceNo) * Discount / 100 
FROM csInvoices 
WHERE InvoiceNo = @InvoiceNo 
DECLARE @No int 
SELECT @No=20 IF @Discount > 0 
BEGIN 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, 'Dicounts', @Discount, 0, 'Discount', @No) 
SELECT @No = @No + 10 
END 
DECLARE @Quantity float, @Amount float, @Cost float, @COGSAccount varchar(50), @IncomeAccount varchar(50), @SalesPrice float, @AssetAccount varchar(50) 
// browses the invoice line items to post the GL accounts 
DECLARE GLDetail CURSOR FOR 
SELECT csInvoiceItems.Quantity, csInvoiceItems.Amount, lsItems.Cost, lsItems.COGSAccount, lsItems.IncomeAccount, lsItems.SalesPrice, lsItems.AssetAccount FROM csInvoiceItems 
INNER JOIN lsItems ON csInvoiceItems.Item = lsItems.ItemName 
WHERE InvoiceNo = @InvoiceNo 
OPEN GLDetail FETCH NEXT FROM GLDetail INTO @Quantity, @Amount, @Cost, @COGSAccount, @IncomeAccount, @SalesPrice, @AssetAccount 
WHILE @@FETCH_STATUS = 0 BEGIN 
// posts the items GL accounts balances: Asset, COGS and Income 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) VALUES(@GLID, @IncomeAccount, 0, @SalesPrice * @Quantity, 'Income Account', @No) 
SELECT @No = @No + 10 INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID,@COGSAccount,@Cost * @Quantity, 0, 'COGS Account', @No) 
SELECT @No = @No + 10 INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID,@AssetAccount,0,@Cost * @Quantity,'Inventory Asset', @No) SELECT @No = @No + 10 
// gets the next line item 
FETCH NEXT 
FROM GLDetail
INTO @Quantity, @Amount, @Cost, @COGSAccount, @IncomeAccount, @SalesPrice, @AssetAccount 
END
// posts the handling and freight amounts, since both credits the Sales account 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) VALUES(@GLID, 'Sales', 0, @Handling + @Freight, 'Sales', @No) SELECT @No = @No + 10 
CLOSE GLDetail 
DEALLOCATE GLDetail 
DECLARE @Account varchar(50) 
// applies the sales tax IF @SalesTax <> "" and @SalesTax<>"No Tax" 
BEGIN DECLARE @TotalPerc float 
SELECT @TotalPerc=SUM(TaxPercent) FROM lsTaxGroupDetail 
WHERE GrpID = @SalesTax 
// browses the lsTaxGroupDetail to collect information about the tax authorities that constitutes the sales tax group 
DECLARE Curs1 CURSOR FOR SELECT Account, TaxPercent 
FROM lsTaxGroupDetail 
WHERE GrpID = @SalesTax 
OPEN Curs1 FETCH NEXT 
FROM Curs1 
INTO @Account, @TaxPercent WHILE @@FETCH_STATUS = 0 
BEGIN 
// Posts the amount to the GL account associated to that particular tax authority 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, @Account, 0, @taxable * @TaxPercent / @TotalPerc, 'Sales Tax', @No) 
SELECT @No = @No + 10 
// gets the next tax authority 
FETCH NEXT FROM Curs1 INTO @Account, @TaxPercent 
END 
CLOSE Curs1 
DEALLOCATE Curs1 
END 
// posts it to GL EXEC PostGLWorkDetail @GLID 
// marks the invoice as posted 
UPDATE csInvoices SET PostedYN = 1 
WHERE InvoiceNo = @InvoiceNo 
COMMIT TRANSACTION

Customer Credits  

Customer credits posting scheme

Account

Debit

Credit

Accounts Receivable

 

X

Discount

 

X

Sales

X

 

Sales Tax

X

 

Freight Income

X

 

Inventory

X

 

COGS

 

X

The customer credits are posted with the help of the postCREDMEM stored procedure. This procedure is actualy a "reversed" invoice, it is similar to an invoice except the credits are posted as debits, and debits are posted as credits, and it has the following structure: 

CREATE PROCEDURE postCREDMEM @id varchar(50) AS 
BEGIN TRANSACTION 
DECLARE @total float, @taxable float, @cust varchar(50), @SalesTax varchar(50), @Freight float, @Handling float 
// gets the customer credit header information 
SELECT @total = Total, @taxable = TaxTotal, @Cust = Customer, @SalesTax = SalesTax, @Freight = Freight, @Handling = Handling FROM csCustomerCredits WHERE CreditNumber = @ID 
DELETE FROM GLWorkDetail
// updates the inventory quantities 
UPDATE lsItems 
SET QuantityOnHand = QuantityOnHand + csCustomerCreditItems.Quantity, TotalValue = Cost * (QuantityOnHand + csCustomerCreditItems.Quantity) 
FROM lsItems, csCustomerCreditItems 
WHERE csCustomerCreditItems.CustomerCreditNo = @ID 
AND lsItems.ItemName = csCustomerCreditItems.Item 
AND lsItems.ItemType=1 
DECLARE @TaxPercent float 
// gets a new document number 
DECLARE @GLID varchar(50) 
SELECT @GLID = 'CREDMEM ' + CONVERT(varchar, @ID) 
// creates the master entry in the GeneralJournalEntry. Sets the TrType to 3 
INSERT INTO GeneralJournalEntry(Date,EntryNo, SystemGenerated, PostedYN, TrType, TrID) 
VALUES (CONVERT(varchar,CURRENT_TIMESTAMP,101), @GLID, 1, 1, 3, @id) 
// decreases the customer’s balance 
UPDATE lsCustomers SET Balance = Balance - @total 
WHERE CustomerName = @Cust 
// posts the AR amount 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, 'Accounts Receivable', 0, @total,'AR', 10) 
DECLARE @Discount float 
SELECT @Discount = (SELECT SUM(Amount) 
FROM csCustomerCreditItems 
WHERE CreditNumber = @ID) * Discount / 100 
FROM csCustomerCredits 
WHERE CreditNumber = @ID 
DECLARE @No int SELECT @No=20 IF @Discount > 0 
BEGIN
// applies discounts 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, 'Discounts', 0, @Discount, 'Discount', @No) 
SELECT @No=@No+10 
END 
DECLARE @Quantity float, @Amount float, @Cost float, @COGSAccount varchar(50), @IncomeAccount varchar(50), @SalesPrice float, @AssetAccount varchar(50) 
// browses the customer credit detail lines in order to post the item’s GL accounts 
DECLARE GLDetail CURSOR FOR 
SELECT csCustomerCreditItems.Quantity, csCustomerCreditItems.Amount, lsItems.Cost, lsItems.COGSAccount, lsItems.IncomeAccount, lsItems.SalesPrice, lsItems.AssetAccount 
FROM csCustomerCreditItems 
INNER JOIN lsItems ON csCustomerCreditItems.Item = lsItems.ItemName 
WHERE CustomerCreditNo = @ID OPEN GLDetail FETCH NEXT 
FROM GLDetail 
INTO @Quantity, @Amount, @Cost, @COGSAccount, @IncomeAccount, @SalesPrice, @AssetAccount WHILE @@FETCH_STATUS = 0 
BEGIN 
// debits the item’s income account 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, @IncomeAccount, @SalesPrice * @Quantity, 0, 'Income Account', @No) 
SELECT @No=@No+10 
// Credits the item’s COGS account 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID,@COGSAccount, 0, @Cost * @Quantity, 'COGS Account', @No) 
SELECT @No=@No+10 
// debits the item’s asset account 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID,@AssetAccount, @Cost * @Quantity, 0, 'Inventory Asset', @No) 
SELECT @No=@No+10 
// gets the next detail item 
FETCH NEXT FROM GLDetail 
INTO @Quantity, @Amount, @Cost, @COGSAccount, @IncomeAccount, @SalesPrice, @AssetAccount 
END 
// posts the handling and freight amounts since both of them debits the Sales account 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, 'Sales', @Handling + @Freight, 0, 'Sales', @No) 
SELECT @No=@No+10 
CLOSE GLDetail 
DEALLOCATE GLDetail 
DECLARE @Account varchar(50) 
// retrieves the tax authorities that constitutes the sales tax group 
IF @SalesTax <> "" AND @SalesTax<>"No Tax" 
BEGIN 
DECLARE @TotalPerc float 
SELECT @TotalPerc=SUM(TaxPercent) 
FROM lsTaxGroupDetail 
WHERE GrpID = @SalesTax 
DECLARE Curs1 CURSOR FOR 
SELECT Account, TaxPercent 
FROM lsTaxGroupDetail 
WHERE GrpID = @SalesTax 
OPEN Curs1 FETCH NEXT 
FROM Curs1 INTO @Account, @TaxPercent 
WHILE @@FETCH_STATUS = 0 
BEGIN 
// posts the amount to the GL account of that particular tax authority 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, @Account, @taxable * @TaxPercent / @TotalPerc, 0, 'Sales Tax', @No) 
SELECT @No=@No+10 FETCH NEXT FROM Curs1 INTO @Account, @TaxPercent 
END 
CLOSE Curs1 
DEALLOCATE Curs1 
END 
// post it to GL EXEC PostGLWorkDetail @GLID 
// marks the customer credit as posted 
UPDATE csCustomerCredits 
SET PostedYN = 1 
WHERE CreditNumber = @ID 
COMMIT TRANSACTION

Customer Payments

Customer payments posting scheme

Account

Debit

Credit

Accounts Receivable

 

X

Cash Account

X

 

Account Debit Credit Accounts Receivable X Cash Account X The customer payments posting is done with the help of the postCUSTPMT stored procedure having the following structure: 

CREATE PROCEDURE postCUSTPMT @MAINID varchar(50) AS 
// it takes as input parameter the ID from the csCustomerPayments table 
BEGIN TRANSACTION 
DELETE FROM GLWorkDetail DECLARE @Unapplied float, @Balance float, @Customer varchar(50), @Account varchar(50), @PaymentAmount float 
// retrieves the payment's header information 
SELECT @Unapplied = UnappliedAmount, @Customer = Customer, @Account = Account, @PaymentAmount = PaymentAmount 
FROM csCustomerPayments 
WHERE Reference = @MAINID 
// updates the customer's existing credit and balance 
UPDATE lsCustomers 
SET ExistingCredit = @Unapplied, Balance = Balance - @PaymentAmount 
WHERE CustomerName = @Customer 
DECLARE @NewID varchar(50) 
// gets a new document number 
SELECT @NewID = 'CUSTPMT-' + @MAINID 
DECLARE @FID varchar(50), @Paid float, @ToPay float 
// creates the master entry in GeneralJournalEntry table. Sets the TrType to 10 
INSERT INTO GeneralJournalEntry(Date, EntryNo, SystemGenerated, PostedYN, TrType, TrID) 
VALUES(CONVERT(varchar,CURRENT_TIMESTAMP,101), @NewID, 1, 1, 10, @MAINID) 
// the payed amount goes to Accounts Receivable as credit and to the posting account as debit 
INSERT INTO GLWorkDetail(GLID, Account, Credit, Debit, Memo, No) 
VALUES (@NewID, 'Accounts Receivable', @PaymentAmount, 0, '', 10) 
INSERT INTO GLWorkDetail(GLID, Account, Credit, Debit, Memo, No) 
VALUES (@NewID, @Account, 0, @PaymentAmount, '', 20) 
// updates the invoice's payment information. TextboxAmount contains the amount actually paid for that invoice 
UPDATE csInvoices 
SET PayAmount = PayAmount + textboxAmount 
FROM csCustomerPaymentDetails 
WHERE InvoiceNo = FID 
AND Reference = @MAINID 
// if Total = PayAmount, then the invoice was paid in full 
UPDATE csInvoices 
SET PaidYN = 1 
WHERE Total = PayAmount 
// posts it to GL 
EXEC PostGLWorkDetail @NewID 
COMMIT TRANSACTION

Inventory adjustments posting scheme:

 1: the new quantity is greater than the old quantity:

Account

Debit

Credit

Adjustment Account

 

X

Asset Account

X

 

 2: the new quantity is smaller than the old quantity: 

Account

Debit

Credit

Adjustment Account

X

 

Asset Account

 

X

The inventory adjustment posting is done by the postINVADJ stored procedure having the following structure:

CREATE PROCEDURE postINVADJ @AdjID varchar(50), @Value float, @AccountID varchar(50) AS 
// it takes three input parameters: @AdjID – the ID of the inventory adjustment record in the InventoryAdjustments table @Value – the new value @AccountID – the adjustment account 
BEGIN TRANSACTION 
DELETE FROM GLWorkDetail 
// gets a new document number 
DECLARE @GLID varchar(50) 
SELECT @GLID = 'INVADJ-'+@AdjID 
// creates the master entry in GeneralJournalEntry table. Sets the TrType to 7 
INSERT INTO GeneralJournalEntry(Date, EntryNo, SystemGenerated, PostedYN, TrType, TrID) 
VALUES(CONVERT(varchar, CURRENT_TIMESTAMP, 101), @GLID, 1, 1, 7, @AdjID) 
// browses the detail lines to retrieve the asset account for each item 
DECLARE C CURSOR FOR SELECT ValueDifference, AssetAccount FROM InventoryAdjustmentDetail 
INNER JOIN lsItems ON ItemName = Item 
AND RefNo = @AdjID 
OPEN C 
DECLARE @VD float, @Acc varchar(50), @No int 
SELECT @No = 10 FETCH NEXT 
FROM C 
INTO @VD, @Acc 
WHILE @@FETCH_STATUS=0 
BEGIN IF @VD > 0 
BEGIN 
// if the new value is positive, debits the asset account and credits the adjustment account 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, @Acc, @VD, 0, 'Inventory', @No) 
SELECT @No = @No + 10 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, @AccountID, 0, @VD, 'Adj Account', @No) 
SELECT @No = @No + 10 END 
ELSE 
BEGIN
 // if the new value is negative, credits the asset account and debits the adjustment account 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, @Acc, 0, @VD, 'Inventory', @No) 
SELECT @No = @No + 10 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, @AccountID, @VD, 0, 'Adj Account', @No) 
SELECT @No = @No + 10 END FETCH NEXT FROM C INTO @VD, @Acc 
END 
CLOSE C 
DEALLOCATE C 
// updates the item quantities 
UPDATE lsItems SET QuantityOnHand = NewQty, TotalValue = NewValue 
FROM lsItems,InventoryAdjustmentDetail 
WHERE InventoryAdjustmentDetail.RefNo=@AdjID 
AND lsItems.ItemName=InventoryAdjustmentDetail.Item 
// posts it to GL EXEC PostGLWorkDetail @GLID 
// marks the inventory adjustment as posted 
UPDATE InventoryAdjustments 
SET PostedYN = 1 
WHERE RefNo = @AdjID 
COMMIT TRANSACTION
 

Transfer Funds

Transfer funds posting scheme

 

Account

Debit

Credit

From Account

 

X

To Account

X

 

The stored procedure that posts the fund transfer is called postTRANSFR and it has the following structure: 

CREATE PROCEDURE postTRANSFR @ID varchar(50), @amount float, @fromaccount varchar(50), @toaccount varchar(50) AS 
// it takes four input parameters: @ID – the ID of the record in bTransferFunds table @amount – the amount to be transferred @fromaccount – the account to transfer the amount from @toaccount – the account to transfer the amount to
BEGIN TRANSACTION 
DELETE FROM GLWorkDetail 
// gets a new document number 
DECLARE @GLID varchar(50) SELECT @GLID = 'TRNSFR-' + @ID 
// creates the master entry in GeneralJournalEntry table. Sets the TrType to 6 
INSERT INTO GeneralJournalEntry(Date, EntryNo, SystemGenerated, PostedYN, TrType, TrID) 
VALUES(CONVERT(varchar, CURRENT_TIMESTAMP, 101), @GLID, 1, 1, 6, @ID) 
// debits and credits the two accounts. 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, @fromaccount, 0, @amount, 'From acc', 10) 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo, No) 
VALUES(@GLID, @toaccount, @amount, 0, 'To acc', 20) 
// posts it to GL EXEC PostGLWorkDetail @GLID 
// marks the fund transfer as posted 
UPDATE bTransferFunds 
SET PostedYN = 1 
WHERE Reference = @ID 
COMMIT TRANSACTION 

Pay Employees

Pay employees posting scheme

 

Account

Debit

Credit

Sales

 

X

Employee’s Account

X

 

The stored procedure that posts payments to the employees is called postPayEmployees and it has the following structure: 

CREATE PROCEDURE postPayEmployees @ID int, @Amount float, @Account varchar(50) AS 
// it takes three input parameters: @ID – the id of the record in the Commissions table for which the payment is done @Amount – amount to be applied @Account – the account from which the commission is paid 
BEGIN TRANSACTION 
// marks the Commission record as paid 
UPDATE Commissions 
SET PaidYN=1 
WHERE ID=@ID 
AND PaidYN=0 
DELETE FROM GLWorkDetail 
DECLARE @GLID varchar(50) 
// gets a new document number 
SELECT @GLID = 'PAYEMPL-'+CONVERT(varchar,@ID) 
// creates the master entry in GeneralJournalEntry. Sets the TrType to 8 
INSERT INTO GeneralJournalEntry(Date, EntryNo, SystemGenerated, PostedYN, TrType, TrID) 
VALUES(CONVERT(varchar, CURRENT_TIMESTAMP, 101), @GLID, 1, 1, 8, @GLID) 
// posts the commission from the income account, and debits the employee's account 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo) 
VALUES(@GLID, 'Sales', @Amount, 0, 'AP') 
INSERT INTO GLWorkDetail(GLID, Account, Debit, Credit, Memo) 
VALUES(@GLID, @Account, 0, @Amount, '') 
// posts it to GL 
EXEC PostGLWorkDetail @GLID 
COMMIT TRANSACTION

I hope that this guide has been helpful.