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. |
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.