Nội dung

Accounting In SQL Coding Published by Derek Liew at Smashwords Copyright 2012 Copyright © 2012 by Derek Liew Lei Mun. All rights reserved. All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner. Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, the names are use only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an “as is” basis. The author and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book. About the Author Derek Liew is a qualified chartered accountant and has almost 13 years of experience in financial accounting. Derek is the author of the best-selling Accounting Database Design ebook (published by Smashwords). He has extensive experience in accounting database design and development, and has implemented database for a highly downloadable free accounting software. Derek welcomes your email at derek@accountingdes.com and invites you to visit his web site at http://www.accountingdes.com Acknowledgements Thanks to the hundreds of you who bought my first book on accounting database design. This second book, would not have been written if it wasn’t of your (the readers) continued support. To my wife, thank you for your support and encouragement. This book is dedicated to you. We Want to Hear from You! As the reader of this book, you are our most important critic and commentator. We value your opinion and want to know what we are doing right, what we could do better, what areas you would like to see more, and any other words of wisdom you are willing to pass our way. I welcome your comments. You can email or write to me directly to let me know what you did or didn’t like about this book, as well as what we can do to make this and future books better. Table of Contents Introduction Who This Book Is For? What You Need To Use This Book Preliminary Knowledge Conventions FRONTAL GUI Chapter 1 Inventory System Inventory Table Structure Temp_Product_Category_T Product_Category_T Temp_Product_Accountset_T Product_Accountset_T Temp_Product_T Product_T Inventory Graphical User Interface (GUI) : Product Category GUI Product Accounts Setting GUI Product GUI Business Logic Control: Product Category Entry Accounting Code Entry Stock Item Entry Chapter 2 Procurement System Procurement Table Structure Temp_Creditors Creditors Document_T Temp_Stock_T Stock_Movement_T Pur_Stock_Movement_T Stock_Balance_T Journal_T Purchases_T Purchases_T2 Temp_Purchase Procurement Graphical User Interface (GUI) : Vendor Setup GUI Goods Received Note GUI Purchase Invoice GUI Purchase Credit Note GUI Business Logic Control: Goods Received Note Entry Purchase Invoice Entry Purchase Credit Note Entry Introduction SQL is a computer language which is widely used for communication with databases. Whether you are a programmer, web application developer, database designer, or even an accountant, a good knowledge in SQL will bring you an edge in your working profession. I have been working with various type of accounting software and ERP system for several years, and it is a disappointing fact, to find out that, most of these system are either lacking in features or absent in certain tools. There are many available accounting software in the market, and some are even available free of charge, but most of these softwares are poorly developed in terms of scale and functionality. There is a big gap between a programmer and an accountant. Although a programmer may well be equipped with database designing and programming language, he or she may not have the technical knowledge of an accountant. I wonder if there is a single book available in this world, which would bridge this gap of knowledge between these two professions. And so, not finding a single book that touches on learning SQL coding for developing an accounting system, I felt a need to gather pieces of my research and development experience into the book you are holding. This book will teach you how to write SQL for an accounting system. You will learn how to develop various modular graphical user interface (GUI) with strong business logic control, to creating conventional referential tables and to writing powerful SQL statement involving the use of joins, subqueries, cursors, advanced data filtering, constraints, stored procedures and triggers, as we progress from one chapter to the next, in building a complete functional accounting system. Who This Book Is For? This book is targeted at database developer, database administrator, web application developer, accountant and anyone who wants to increase their knowledge and skill set in writing powerful SQL statement for a relational accounting database system. What You Need To Use This Book All the topics discussed in this book contain practical examples with it. All the code is written, tested and are supported with screenshots where appropriate. You must have administrative-level access in order to try out the exercise contained in this book. Your system should be equipped with:  Microsoft SQL Server 2000 (or above)  DES 1.2 database *  DES 1.2 application (Optional) * * Notes : The DES 1.2 application would provide user an idea as to the design layout and presentation form of each of the graphical user interface which we are going to build in our exercise, besides facilitating user in trying out the exercise given in our example. Both DES 1.2 and its database is downloadable at http://www.accountingdes.com If you wish not to install DES 1.2, you may still try out the exercise by executing the ‘INSERT’ statement on the SQL Query Analyzer (bundled with SQL Server), or you may manually insert the data directly into the relevant tables. Preliminary Knowledge You are expected to have some basic knowledge on front-end application and middleware development. As we start going through the exercise in this book, we suggest that our reader perform the data entry from the DES 1.2 application itself, as this will not only enhance the learning experience, but also provide an in-depth understanding of the coding function and implication in our database. Should you unable to install DES 1.2, you may still try out the exercise, by manually inserting the data entry into the relevant tables, or via executing an INSERT query from your SQL Query Analyzer. Alternatively, you may build your own front-end application using any of the latest programming languages (e.g. VB.Net, PHP, Java), to perform the data entry, as you go through the exercise from Chapter 6 onwards. Conventions To help you in better understanding this book, different typefaces is used to differentiate between SQL code and regular English, in helping you to identify key concepts. Text that you will see in regular English should appear in Times New Roman font type. SQL syntax would appear in CAPITAL LETTERS. Chapter 1 Inventory System Inventory system is important in allowing us to maintain and control the movement of our inventory items. There is a lot of business software in the market, but majority of these software provider do not bundle their software with good inventory system. Some business software provider claims that their software support different types of costing mechanism, but most of the time you may find that their system merely provide basic functionality, like item setup, item image uploading, but not costing mechanism. In this Chapter, we will walk through a series of steps in creating the inventory tables, the graphical interface for each of the inventory process to building of proper control at client application level, and to back-end scripting in automating the inventory costing mechanism. Inventory Table Structure The inventory tables hold information on products items in a trading organization. The following are the basic tables required in an inventory system : 1.Temp_Product_Category_T 2.Product_Category_T 3.Temp_Product_Accountset_T 4.Product_Accountset_T 5.Temp_Product_T 6.Product_T Create the following fieldnames for each of the above tables, with the following data type and attributes : Temp_Product_Category_T Product_Category_T Temp_Product_Accountset_T Product_Accountset_T Temp_Product_T Product_T Inventory Graphical User Interface (GUI): Product Category GUI A product category GUI generally allows user to define the category name, category code and the default revenue and cost of sales account code for each product item. The product category GUI would basically contains the following textbox input to capture the required information for the Product_Category_T table. Product Accounts Setting GUI Product Accounting code setup screen basically allow user to create another layer of system double-entry automation when a Goods Received Note, Delivery Order or an Invoice transaction is performed. This is where we will define the inventory costing mechanism for each of our product item. We will adopt the first-in-first-out (FIFO) and average (AVEG) method as our preferred costing mechanism in performing our exercise later. Your client application form should have the following textbox input to capture the required information for the Product_Accountset_T table Product GUI This is the final place, where we will assign a category and an account setting code for each of the individual product item to be created in our inventory system. We will also assign a VAT code, which will help us to monitor our VAT Input and Output records. At the front-end, you should create the following textbox input to capture the required information into the Product_T table. Business Logic Control Product Category Entry At the Product Category GUI, user is allowed to create and delete product category from the Product_Category_T table. However, control must be put in place, to prevent user from deleting a product category that has already been assigned to a stock item with transactional posting in the system. This control is critical in enforcing referential integrity and data consistency. We wouldn’t want to have redundant or orphaned records in our Inventory tables. In Chapter 6, we would discover how we can build conditional filtering at our back-end database in preventing such deletion from happening. Accounting Code Entry At this GUI, user has the flexibility in assigning a default costing mechanism for each of the stock item created in the Product_T table. The Update and Delete button, allows user to edit and remove any product account setting code from the Product_Accountset_T table. In our exercise in Chapter 6, we will learn how to create an accounting set code with costing mechanism automation. In Chapter 6, we will also learn how our server-side scripting can monitor the creation, modification and deletion of an accounting set code. Stock Item Entry This GUI allows user to create stock item profile by assigning the default product category and accounting set code created in the above two GUI. User is given the option to create, update and remove any unwanted stock item from the Product_T table. In Chapter 6, we will learn how to build triggers on our table to control the flow of data before allowing any of these three functions to be executed. Chapter 2 Procurement System Procurement refers to sourcing of product from supplier to managing the procurement process from the point of acknowledging receipt of incoming goods to settlement of vendor’s invoices. In this chapter, we will see what are the common user interface form that are normally developed and used in a conventional supply chain system. Procurement Table Structure Procurement Table generally holds information on procurement and will more often contains records that have references to inventory tables. The following are the basic tables required for a procurement system: 1. Temp_Creditors 2. Creditors 3. Document_T 4. Temp_Stock_T 5. Stock_Movement_T 6. Pur_Stock_Movement_T 7. Stock_Balance_T 8. Journal_T 9. Purchases_T 10. Purchases_T2 11. Temp_Purchase Create the following fieldnames for each of the above tables, with the following data type and attributes: Temp_Creditors Creditors Document_T