Generated: 6/5/2007 11:18:20 PM |
[dbo].[addAccount] |
Owner | dbo |
Parameters | @accountname varchar(50) |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[addAccount] @accountname varchar (50 ) AS DECLARE @openingbalance float , @date datetime BEGIN TRANSACTION -- If the opening balance amount is different than 0 , post it to GL SELECT @date=Date , @openingbalance=OpeningBalance FROM lsAccounts WHERE AccountName = @accountname DELETE FROM GLWorkDetail -- Get a new document number DECLARE @GLID varchar (50 ) SELECT @GLID = ''OPBAL- '' + @accountname IF @openingbalance<>0 BEGIN INSERT INTO GeneralJournalEntry (Date , EntryNo , SystemGenerated , PostedYN , TrType , TrID ) VALUES (CONVERT (varchar ,CURRENT_TIMESTAMP ,101 ), @GLID , 1 , 1 , 8 , @GLID ) IF @openingbalance>0 BEGIN -- adds the opening balance as debit INSERT INTO GLWorkDetail (GLID ,Account ,Debit ,Credit ,Memo , No ) VALUES (@GLID , @accountname , @openingbalance , 0 , '' '', 10 ) -- credits the ''Opening Balance '' account INSERT INTO GLWorkDetail (GLID ,Account , Debit , Credit , Memo , No ) VALUES (@GLID , ''Opening Balance '', 0 , @openingbalance , '' '', 20 ) END ELSE BEGIN -- adds the opening balance as credit INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , @accountname , 0 , @openingbalance , '' '', 10 ) -- debits the ''Opening Balance '' account INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , ''Opening Balance '', @openingbalance , 0 , '' '',20 ) END -- Post it to GL EXEC PostGLWorkDetail @GLID END COMMIT TRANSACTION ' END |
[dbo].[addBill] |
Owner | dbo |
Parameters | @Reference varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[addBill] @Reference varchar (50 ) AS BEGIN TRANSACTION -- sets the amount due to equal the Amount . When you will pay this bill , the amount being paid -- will be sustracted from DueAmount UPDATE vpBills SET DueAmount = Amount WHERE Reference = @Reference COMMIT TRANSACTION ' END |
[dbo].[addCommission] |
Owner | dbo |
Parameters | @EmpName varchar(50), @Amount float |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[addCommission] @EmpName varchar (50 ), @Amount float AS BEGIN TRANSACTION -- increases the Refferal field , and sets the commission for the employee UPDATE lsEmployees SET Refferal=Refferal+1 , Commission=Commission+CommissionRate*@Amount/100 WHERE CommissionYN=1 AND EmployeeName=@EmpName SELECT @Amount = @Amount * CommissionRate / 100 FROM lsEmployees WHERE EmployeeName = @EmpName DECLARE @A float SELECT @A=Commission FROM lsEmployees WHERE CommissionYN=1 AND EmployeeName=@EmpName DELETE FROM GLWorkDetail DECLARE @GLID varchar (50 ), @G int -- gets a new document number SELECT @GLID = ''SALESCOMM-0 '' SELECT @G = 1 WHILE (EXISTS (SELECT EntryNo FROM GeneralJournalEntry WHERE EntryNo=@GLID )) BEGIN SELECT @GLID = ''SALESCOMM- ''+CONVERT (varchar ,@G ) SELECT @G = @G + 1 END -- filling the GL header INSERT INTO GeneralJournalEntry (Date , EntryNo , SystemGenerated , PostedYN , TrType , TrID ) VALUES (CONVERT (varchar , CURRENT_TIMESTAMP , 101 ), @GLID , 1 , 1 , 0 , @GLID ) -- GL scheme: -- Account Debit Credit -- Accounts Payable X -- Income X INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , ''Accounts Payable '', 0 , @Amount , ''AP '', 10 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , ''Sales '', @Amount , 0 , ''Sales '', 20 ) INSERT INTO Commissions (EmpName ,Date ,Amount ,PaidYN ) VALUES (@EmpName , CURRENT_TIMESTAMP , @A , 0 ) -- post it to GL EXEC PostGLWorkDetail @GLID COMMIT TRANSACTION ' END |
[dbo].[addCustomer] |
Owner | dbo |
Parameters | @customerid varchar(50), @openingbalance float, @opbaldate datetime |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[addCustomer] @customerid varchar (50 ), @openingbalance float , @opbaldate datetime AS BEGIN TRANSACTION IF @openingbalance<>0 BEGIN -- if opening balance is not 0 , post an invoice for this customer DELETE FROM GLWorkDetail DECLARE @GLID varchar (50 ), @NewID varchar (50 ) DECLARE @G int SELECT @G = 1 -- gets a new document number SELECT @NewID = MAX (Invoices ) FROM DocNumbers WHILE EXISTS (SELECT InvoiceNo FROM csInvoices WHERE InvoiceNo = @NewID ) BEGIN SELECT @NewID = @NewID+COnvert (varchar ,1 ) END -- create the invoice INSERT INTO csInvoices (InvoiceNo , Customer , Memo , Date , DueDate , Total , PostedYN , Term , ShipVia ) VALUES (@NewID , @customerid , ''Opening balance '', CONVERT (varchar , @opbaldate ,101 ), CONVERT (varchar ,@opbaldate ,101 ),@openingbalance , 1 , NULL , NULL ) DECLARE @No int SELECT @No = (IsNull (MAX (No ), 0 )+10 ) FROM csInvoiceItems WHERE InvoiceNo = @NewID DECLARE @Item varchar (50 ) SELECT TOP 1 @Item = ItemName FROM lsItems INSERT INTO csInvoiceItems (InvoiceNo , No , Item , Description , Amount ) VALUES (@NewID , @No , @Item , ''Opening balance '', @openingbalance ) SELECT @GLID = ''CUSTOPBAL- ''+@NewID -- create the GL header INSERT INTO GeneralJournalEntry (Date , EntryNo , SystemGenerated , PostedYN , TrType , TrID ) VALUES (CONVERT (varchar , @opbaldate , 101 ), @GLID , 1 , 1 , 4 , @NewID ) -- sets the customer ''s balance to the opening balance amount UPDATE lsCustomers SET Balance = Balance + @openingbalance FROM lsCustomers WHERE CustomerName=@customerid -- fills the GL detail lines INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , ''Accounts Recievable '', @openingbalance , 0 , ''AR '', 10 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , ''Opening Balance '', 0 , @openingbalance , ''Op Bal '', 20 ) -- posts it to GL EXEC PostGLWorkDetail @GLID END COMMIT TRANSACTION ' END |
[dbo].[addCUSTPMT] |
Owner | dbo |
Parameters | @InvID varchar(50), @Amount float, @MAINID varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[addCUSTPMT] @InvID varchar (50 ), @Amount float , @MAINID varchar (50 ) AS BEGIN TRANSACTION DECLARE @ToPay float -- updates the sum to be paid SELECT @ToPay = (Total - PayAmount ) FROM csInvoices WHERE InvoiceNo = @InvID -- inserts the item line into the csCustomerPaymentDetails table -- @MainID is the payment ''s reference while the @InvID represents the invoice number INSERT INTO csCustomerPaymentDetails (Reference , FID , textboxAmount , ToPay ) VALUES (@MAINID , @InvID , @Amount , @ToPay ) COMMIT TRANSACTION ' END |
[dbo].[addInventoryPart] |
Owner | dbo |
Parameters | @itemname varchar(50),@totalvalue float,@newqty float, @assetaccount varchar(50) |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[addInventoryPart] @itemname varchar (50 ),@totalvalue float ,@newqty float , @assetaccount varchar (50 ) AS BEGIN TRANSACTION IF @totalvalue<>0 BEGIN -- if the quantity on hand is not posted as 0 , then create a GL record DELETE FROM GLWorkDetail DECLARE @GLID varchar (50 ) SELECT @GLID = ''OPBAL- ''+@itemname INSERT INTO GeneralJournalEntry (Date , EntryNo , SystemGenerated , PostedYN , TrType , TrID ) VALUES (CONVERT (varchar , CURRENT_TIMESTAMP , 101 ), @GLID , 1 , 1 , 8 , @GLID ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , ''Opening Balance '', 0 , @totalvalue , ''Opening Balance Equity '', 10 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , @assetaccount , @totalvalue , 0 , ''Inventory Asset '', 20 ) EXEC PostGLWorkDetail @GLID END COMMIT TRANSACTION ' END |
[dbo].[addVendor] |
Owner | dbo |
Parameters | @vendorid varchar(50),@opbaldate datetime, @openingbalance float, @defexpaccount varchar(50) |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[addVendor] @vendorid varchar (50 ),@opbaldate datetime , @openingbalance float , @defexpaccount varchar (50 ) AS BEGIN TRANSACTION IF @openingbalance<>0 BEGIN -- if the opening balance is not 0 , the creates a bill for this vendor UPDATE lsVendors SET Balance=Balance + @openingbalance FROM lsVendors WHERE VendorName=@vendorid DECLARE @GLID varchar (50 ), @NewID varchar (50 ) -- gets a new document number SELECT @NewID = MAX (Vouchers )+1 FROM DocNumbers WHILE EXISTS (SELECT Reference FROM vpBills WHERE Reference = @NewID ) BEGIN SELECT @NewID = @NewID+Convert (varchar ,1 ) END -- creates the bill INSERT INTO vpBills (Reference , Vendor , Date , DueDate , Amount , Memo , PostedYN ) VALUES (@NewID , @vendorid , CONVERT (varchar , @opbaldate ,101 ),CONVERT (varchar ,@opbaldate ,101 ),@openingbalance , ''Opening Balance '', 1 ) INSERT INTO vpBillExpenses (Reference , Account , Amount , Memo ) VALUES (@NewID , @defexpaccount , @openingbalance , ''Opening Balance '') DELETE FROM GLWorkDetail SELECT @GLID = ''BILL- ''+@NewID -- creates the GL header INSERT INTO GeneralJournalEntry (Date , EntryNo , SystemGenerated , PostedYN , TrType , TrID ) VALUES (CONVERT (varchar , CURRENT_TIMESTAMP , 101 ), @GLID , 1 , 1 , 1 , @NewID ) -- fills the GL detail line INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , ''Accounts Payable '', @openingbalance , 0 , ''AP '', 10 ) DECLARE @No int SELECT @No = (IsNull (MAX (No ), 0 )+10 ) FROM GLWorkDetail WHERE GLID = @GLID INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , @defexpaccount , 0 , @openingbalance , ''Exp Acc '', @No ) -- posts it to GL EXEC PostGLWorkDetail @GLID END COMMIT TRANSACTION ' END |
[dbo].[AllItems] |
Owner | dbo |
Parameters | @CID varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[AllItems] @CID varchar (50 ) AS -- returns the list of items for the shopping cart SELECT CategoryID , PictureURL , ItemName AS Name , SalesDescription AS Description , SalesPrice AS Price FROM lsItems WHERE CategoryID=@CID ' END |
[dbo].[ConvertOrder] |
Owner | dbo |
Parameters | @ID varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[ConvertOrder] @ID varchar (50 ) AS -- converts an order to an invoice DECLARE @IID AS varchar (50 ) DECLARE @LID AS int DECLARE @No AS int DECLARE @NewNumber varchar (50 ) DECLARE @Item AS varchar (50 ) DECLARE @Desc AS nvarchar (50 ) DECLARE @Q AS float DECLARE @Ship AS float DECLARE @Rate AS float DECLARE @Amount AS float DECLARE @Tax AS bit DECLARE @ItemWeight AS float DECLARE @Date AS smalldatetime BEGIN TRANSACTION -- gets a new invoice number SELECT @LID = 1 SELECT @NewNumber = @ID WHILE (EXISTS (SELECT InvoiceNo FROM csInvoices WHERE InvoiceNo=@NewNumber )) BEGIN SELECT @NewNumber = CONVERT (varchar , @NewNumber + ''- '' +Convert (varchar ,@LID )) SELECT @LID = @LID + 1 CONTINUE END -- fills the invoice ''s header INSERT INTO csInvoices (Date , Customer , ShipToID , Class , Memo , Term , InvoiceNo , DueDate , ShipVia , ShipDate , POID ,CustomerMessage , TrackingNumber , Handling , Freight , SalesTax , Discount , Tax , TaxTotal , Total , TotalWeight , ShippingCharge , ApplyYN , PaidYN , PayAmount ) SELECT Date , Customer , ShipToID , Class , Memo ,Term , @NewNumber AS InvoiceNo , DueDate , ShipVia , ShipDate , POID , CustomerMessage , TrackingNumber , Handling , Freight , SalesTax , Discount , Tax , TaxTotal , Total , TotalWeight , 0 AS ShippingCharge , 0 AS ApplyYN , 0 AS PaidYN , 0 AS PayAmount FROM csOrders WHERE csOrders .OrderNo=@ID DECLARE Curs CURSOR FOR SELECT * FROM csOrderItems WHERE OrderNo=@ID OPEN Curs FETCH NEXT FROM Curs INTO @IID , @No , @Item , @Desc , @Q , @Ship , @Rate ,@Amount ,@ItemWeight ,@Date --inserts the order ''s line items WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO csInvoiceItems (InvoiceNo ,No ,Item ,Description ,Quantity ,Rate ,Amount ,ItemWeight ,ShippedDate ) VALUES (@NewNumber ,@No ,@Item ,@Desc ,@Ship ,@Rate ,@Amount ,@ItemWeight ,@Date ) FETCH NEXT FROM Curs INTO @IID , @No , @Item , @Desc , @Q , @Ship , @Rate ,@Amount ,@ItemWeight ,@Date END CLOSE Curs DEALLOCATE Curs -- updates the tracking table INSERT INTO csOrderTracking VALUES (@ID ,@NewNumber ); -- marks the order as being invoiced . UPDATE csOrders SET Invoiced = 1 WHERE OrderNo = @ID --NOTE: the invoice will NOT be posted to GL COMMIT TRANSACTION ' END |
[dbo].[delBILL] |
Owner | dbo |
Parameters | @BillID varchar(50), @CheckNo int, @CheckAmount float |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[delBILL] @BillID varchar (50 ), @CheckNo int , @CheckAmount float AS BEGIN TRANSACTION DELETE FROM GLWorkDetail -- deletes a bill by posting a reverse transaction to GL DECLARE @Vendor varchar (50 ), @PayAmount float , @OrigAmount float , @Account varchar (50 ), @CheckID int -- retrieves the fields from the vpBills table SELECT @CheckID = CheckID , @Account = Account FROM bChecks WHERE CheckNo = @CheckNo SELECT @Vendor = Vendor , @PayAmount = PayAmount , @OrigAmount = Amount FROM vpBills WHERE Reference = @BillID UPDATE vpBills SET PayAmount = PayAmount - @CheckAmount , DueAmount = Amount + PayAmount - @CheckAmount WHERE Reference = @BillID -- gets a new document number DECLARE @GLID varchar (50 ) SELECT @GLID = ''VOID- '' + CONVERT (varchar , @CheckNo ) + ''- '' + CONVERT (varchar , @BillID ) -- creates the GL header INSERT INTO GeneralJournalEntry (Date , EntryNo , TrType , SystemGenerated , PostedYN , TrID ) VALUES (CONVERT (varchar , CURRENT_TIMESTAMP ), @GLID , 9 , 1 , 1 , @CheckID ) UPDATE lsVendors SET Balance = Balance + @CheckAmount WHERE VendorName = @Vendor -- fills the GL detail lines --MODIFIED BY ADI INSERT INTO GLWorkDetail (GLID , No , Account , Debit , Credit , Memo ) VALUES (@GLID , 10 , @Account , @CheckAmount , 0 , ''CASH '') INSERT INTO GLWorkDetail (GLID , No ,Account , Debit , Credit , Memo ) VALUES (@GLID , 20 , ''Accounts Payable '', 0 , @CheckAmount , ''AP '') -- posts it to GL EXEC PostGLWorkDetail @GLID COMMIT TRANSACTION ' END |
[dbo].[GetBankRec] |
Owner | dbo |
Parameters | @defAccount varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[GetBankRec] @defAccount varchar (50 ) AS -- retrieves the transactions not marked as being cleared SELECT Reference AS IID , CONVERT (varchar ,Date ,101 ) AS Date , ''CustPmt '' AS Type , Reference , PaymentAmount AS Amount , ClearedYN FROM csCustomerPayments WHERE ClearedYN=0 AND Account=@defAccount UNION SELECT Reference AS IID , CONVERT (varchar ,Date ,101 ) AS Date , ''TransferTo '' AS Type , Reference , Amount , ClearedYN FROM bTransferFunds WHERE ClearedYN=0 AND PostedYN = 1 AND ToAccount=@defAccount UNION SELECT Reference AS IID , CONVERT (varchar ,Date ,101 ) AS Date , ''VendPmt '' AS Type , Reference , Amount , ClearedYN FROM vpBillPayments WHERE ClearedYN=0 AND Account=@defAccount UNION SELECT Reference AS IID , CONVERT (varchar ,Date ,101 ) AS Date , ''TransferFrom '' AS Type , Reference , Amount , ClearedYN FROM bTransferFunds WHERE ClearedYN=0 AND PostedYN = 0 AND FromAccount= @defAccount ' END |
[dbo].[GetBankRecCredits] |
Owner | dbo |
Parameters | @defAccount varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[GetBankRecCredits] @defAccount varchar (50 ) AS -- selects the credit transaction for reconciliation SELECT Reference AS IID , CONVERT (varchar ,Date ,101 ) AS Date , ''CustPmt '' AS Type , Reference , PaymentAmount AS Amount , ClearedYN FROM csCustomerPayments WHERE ClearedYN=0 AND Account=@defAccount UNION SELECT Reference AS IID , CONVERT (varchar ,Date ,101 ) AS Date , ''TransferTo '' AS Type , Reference , Amount , ClearedYN FROM bTransferFunds WHERE ClearedYN=0 AND PostedYN = 1 AND ToAccount=@defAccount ORDER BY Date ' END |
[dbo].[GetBankRecDebits] |
Owner | dbo |
Parameters | @defAccount varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[GetBankRecDebits] @defAccount varchar (50 ) AS -- selects the debit transaction for reconciliation SELECT Reference AS IID , CONVERT (varchar ,Date ,101 ) AS Date , ''VendPmt '' AS Type , Reference , Amount , ClearedYN FROM vpBillPayments WHERE ClearedYN=0 AND Account=@defAccount UNION SELECT Reference AS IID , CONVERT (varchar ,Date ,101 ) AS Date , ''TransferFrom '' AS Type , Reference , Amount , ClearedYN FROM bTransferFunds WHERE ClearedYN=0 AND PostedYN = 0 AND FromAccount= @defAccount ' END |
[dbo].[GetNewDocNumber] |
Owner | dbo |
Parameters | @Type varchar(20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[GetNewDocNumber] @Type varchar (20 ) AS DECLARE @NewNo varchar (20 ) -- gets the next available document number for different types of transactions . These -- values are read from the DocNumbers table . BEGIN TRANSACTION IF @Type= ''INV '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (Invoices ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (Invoices ) + 1 FROM DocNumbers ) END ) UPDATE DocNumbers SET Invoices=@NewNo END ELSE IF @Type= ''ORD '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (Orders ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (Orders ) + 1 FROM DocNumbers ) END ) UPDATE DocNumbers SET Orders = @NewNo END ELSE IF @Type= ''BP '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (BillPmt ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (BillPmt ) + 1 FROM DocNumbers ) END ) UPDATE DocNumbers SET BillPmt = @NewNo END ELSE IF @Type= ''CP '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (CustPmt ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (CustPmt ) + 1 FROM DocNumbers ) END ) UPDATE DocNumbers SET CustPmt = @NewNo END ELSE IF @Type= ''CC '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (CustCredits ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (CustCredits ) + 1 FROM DocNumbers ) END ) UPDATE DocNumbers SET CustCredits = @NewNo END ELSE IF @Type= ''CHECK '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (CheckNo ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (CheckNo ) + 1 FROM DocNumbers ) END ) UPDATE DocNumbers SET CheckNo = @NewNo END ELSE IF @Type= ''TF '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (Transfers ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (Transfers ) + 1 FROM DocNumbers ) END ) UPDATE DocNumbers SET Transfers = @NewNo END ELSE IF @Type= ''BILL '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (Vouchers ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (Vouchers ) + 1 FROM DocNumbers ) END ) UPDATE DocNumbers SET Vouchers = @NewNo END ELSE IF @Type= ''INVADJ '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (Adjustment ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (Adjustment ) + 1 FROM DocNumbers ) END ) UPDATE DocNumbers SET Adjustment = @NewNo END ELSE IF @Type= ''TT '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (TimeTracking ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (TimeTracking ) + 1 FROM DocNumbers ) END ) UPDATE DocNumbers SET TimeTracking = @NewNo END ELSE SELECT @NewNo = ''1000 '' SELECT @NewNo AS NewNo COMMIT TRANSACTION ' END |
[dbo].[GetVendorPurchases] |
Owner | dbo |
Parameters | @VenID varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[GetVendorPurchases] @VenID varchar (50 ) AS -- gets the list of posted purchase transactions SELECT Reference AS TransID , CONVERT (varchar ,Date ,101 ) AS Date , ''Voucher '' AS Type , Reference , Memo , Amount , ''..\payables\entervouchers .asp?direction=idnumber '' AS Path FROM vpBills WHERE Type= ''VOUCHER '' AND Vendor = @VenID AND PostedYN = 1 UNION SELECT PORef AS TransID , CONVERT (varchar ,Date ,101 ) AS Date , ''Receiving '' AS Type , VendorInvoice AS Reference , Memo , Total AS Amount , ''..\payables\enterpurchaseorders .asp?direction=idnumber '' AS Path FROM vpPurchaseOrders WHERE Vendor = @VenID AND ReceivedYN = 1 ORDER BY Date ' END |
[dbo].[GLTransDetail] |
Owner | dbo |
Parameters | @Account varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[GLTransDetail] @Account varchar (50 ) AS -- this procedure simplifies the parsing of the GL . It uses the GetGLTrans view which lists all the transactions where @Account account is involved . SELECT TrID AS TransID , ID , CASE TrType WHEN 1 THEN ''Voucher '' WHEN 2 THEN ''Receiving '' WHEN 3 THEN ''Cust Credit '' WHEN 4 THEN ''Invoice '' WHEN 5 THEN ''Bill Pmt '' WHEN 6 THEN ''Transfer '' WHEN 7 THEN ''Inv Adjust '' WHEN 8 THEN ''Journal Entry '' WHEN 9 THEN ''Void Check '' WHEN 10 THEN ''Cust Pmt '' ELSE '' '' END AS Type , CONVERT (varchar , Date , 101 ) AS Date , CASE TrType WHEN 1 THEN (SELECT Reference FROM vpBills WHERE Reference = TrID ) WHEN 2 THEN (SELECT Reference FROM vpBills WHERE Reference = TrID ) WHEN 3 THEN (SELECT CreditNumber FROM csCustomerCredits WHERE CreditNumber = TrID ) WHEN 4 THEN (SELECT InvoiceNo FROM csInvoices WHERE InvoiceNo = TrID ) WHEN 5 THEN '' '' WHEN 6 THEN (SELECT Reference FROM bTransferFunds WHERE Reference=TrID ) WHEN 7 THEN (SELECT RefNo FROM InventoryAdjustments WHERE RefNo=TrID ) WHEN 8 THEN EntryNo WHEN 9 THEN (SELECT CheckNo FROM bChecks WHERE CheckID = TrID ) WHEN 10 THEN (SELECT Reference FROM csCustomerPayments WHERE Reference = TrID ) END AS Num , Memo , CASE TrType WHEN 1 THEN ''Accounts Payable '' WHEN 2 THEN ''Accounts Payable '' WHEN 3 THEN ''Accounts Receivable '' WHEN 4 THEN ''Accounts Receivable '' WHEN 5 THEN ''Accounts Payable '' WHEN 6 THEN '' '' WHEN 7 THEN '' '' WHEN 8 THEN '' '' WHEN 9 THEN ''Accounts Payable '' WHEN 10 THEN ''Accounts Receivable '' END AS Split , CASE TrType WHEN 1 THEN ''../payables/entervouchers .asp?direction=idnumber '' WHEN 2 THEN ''../payables/enterpurchaseorders .asp?direction=idnumber '' WHEN 3 THEN ''../receivables/customercredits .asp?direction=idnumber '' WHEN 4 THEN ''../receivables/createinvoices .asp?direction=idnumber '' WHEN 5 THEN '' '' WHEN 6 THEN ''../ledger/transferfunds .asp?direction=idnumber '' WHEN 7 THEN ''../ledger/inventoryadjustment .asp?direction=idnumber '' WHEN 8 THEN ''../ledger/makejournalentries .asp?direction=idnumber '' WHEN 9 THEN ''../payables/printchecks .asp?direction=idnumber '' WHEN 10 THEN ''../receivables/customerpayments .asp?direction=idnumber '' ELSE '' '' END AS Path , EntryNo AS Reference , CONVERT (varchar , CONVERT (money ,CASE (SELECT AccountType FROM lsAccounts WHERE AccountName = @Account ) WHEN ''Bank '' THEN (CASE Debit WHEN 0 THEN -1 * Credit ELSE Debit END ) WHEN ''Accounts Receivable '' THEN (CASE Debit WHEN 0 THEN -1 * Credit ELSE Debit END ) WHEN ''Other CURRENT Asset '' THEN (CASE Debit WHEN 0 THEN -1 * Credit ELSE Debit END ) WHEN ''Fixed Asset '' THEN (CASE Debit WHEN 0 THEN -1 * Credit ELSE Debit END ) WHEN ''Credit Card '' THEN (CASE Debit WHEN 0 THEN -1 * Credit ELSE Debit END ) WHEN ''Other Asset '' THEN (CASE Debit WHEN 0 THEN -1 * Credit ELSE Debit END ) WHEN ''Income '' THEN (CASE Debit WHEN 0 THEN Credit ELSE -1 * Debit END ) WHEN ''Other Income '' THEN (CASE Debit WHEN 0 THEN Credit ELSE -1 * Debit END ) WHEN ''Expense '' THEN (CASE Debit WHEN 0 THEN -1 * Credit ELSE Debit END ) WHEN ''Cost OF Goods Sold '' THEN (CASE Debit WHEN 0 THEN -1 * Credit ELSE Debit END ) WHEN ''Other Expense '' THEN (CASE Debit WHEN 0 THEN -1 * Credit ELSE Debit END ) WHEN ''Accounts Payable '' THEN (CASE Debit WHEN 0 THEN Credit ELSE -1 * Debit END ) WHEN ''Other CURRENT Liability '' THEN (CASE Debit WHEN 0 THEN Credit ELSE -1 * Debit END ) WHEN ''Long Term Liability '' THEN (CASE Debit WHEN 0 THEN Credit ELSE -1 * Debit END ) WHEN ''Equity '' THEN (CASE Debit WHEN 0 THEN Credit ELSE -1 * Debit END ) ELSE 0 END )) AS Amount FROM GetGLTrans WHERE Account = @Account ' END |
[dbo].[InvoiceShippedOrder] |
Owner | dbo |
Parameters | @OrderNo varchar(50) |
Definition | |
Note | |
Called SPs | ConvertOrder |
DDL Code | CREATE PROC [dbo].[InvoiceShippedOrder] @OrderNo varchar (50 ) AS DECLARE @A int SELECT @A = 0 -- invoice the order , if it is not already invoiced IF EXISTS (SELECT OrderNo FROM csOrderTracking WHERE OrderNo = @OrderNo ) SELECT @A = 1 IF @A = 0 BEGIN EXEC ConvertOrder @OrderNo END ' END |
[dbo].[NewConvertOrder] |
Owner | dbo |
Parameters | @ID varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[NewConvertOrder] @ID varchar (50 ) AS BEGIN -- converts an order to an invoice DECLARE @IID AS varchar (50 ) DECLARE @LID AS int DECLARE @No AS int DECLARE @NewNumber varchar (50 ) DECLARE @Item AS varchar (50 ) DECLARE @Desc AS nvarchar (50 ) DECLARE @Q AS float DECLARE @Ship AS float DECLARE @Rate AS float DECLARE @Amount AS float DECLARE @Tax AS bit DECLARE @ItemWeight AS float DECLARE @Date AS smalldatetime BEGIN TRANSACTION -- gets a new invoice number SELECT @LID = 1 SELECT @NewNumber = @ID WHILE (EXISTS (SELECT csInvoices .InvoiceNo FROM csInvoices WHERE csInvoices .InvoiceNo=@NewNumber )) BEGIN SELECT @NewNumber = CONVERT (varchar , @NewNumber + ''- '' +Convert (varchar ,@LID )) SELECT @LID = @LID + 1 CONTINUE END -- fills the invoice ''s header INSERT INTO csInvoices (csInvoices .Date , csInvoices .Customer , csInvoices .ShipToID , csInvoices .Class , csInvoices .Memo , csInvoices .Term , csInvoices .InvoiceNo , csInvoices .DueDate , csInvoices .ShipVia , csInvoices .ShipDate , csInvoices .POID , csInvoices .CustomerMessage , csInvoices .TrackingNumber , csInvoices .Handling , csInvoices .Freight , csInvoices .SalesTax , csInvoices .Discount , csInvoices .Tax , csInvoices .TaxTotal , csInvoices .Total , csInvoices .TotalWeight , csInvoices .ShippingCharge , csInvoices .ApplyYN , csInvoices .PaidYN , csInvoices .PayAmount ) SELECT csOrders .Date , csOrders .Customer , csOrders .ShipToID , csOrders .Class , csOrders .Memo , csOrders .Term , @NewNumber AS InvoiceNo , csOrders .DueDate , csOrders .ShipVia , csOrders .ShipDate , csOrders .POID , csOrders .CustomerMessage , csOrders .TrackingNumber , csOrders .Handling , csOrders .Freight , csOrders .SalesTax , csOrders .Discount , csOrders .Tax , csOrders .TaxTotal , csOrders .Total , csOrders .TotalWeight , 0 AS ShippingCharge , 0 AS ApplyYN , 0 AS PaidYN , 0 AS PayAmount FROM csOrders WHERE csOrders .OrderNo=@ID DECLARE Curs CURSOR FOR SELECT * FROM csOrderItems WHERE OrderNo=@ID OPEN Curs FETCH NEXT FROM Curs INTO @IID , @No , @Item , @Desc , @Q , @Ship , @Rate ,@Amount ,@ItemWeight ,@Date --inserts the order ''s line items WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO csInvoiceItems (csInvoiceItems .InvoiceNo , csInvoiceItems .No , csInvoiceItems .Item , csInvoiceItems .Description , csInvoiceItems .Quantity , csInvoiceItems .Rate , csInvoiceItems .Amount , csInvoiceItems .ItemWeight , csInvoiceItems .ShippedDate ) VALUES (@NewNumber , @No , @Item , @Desc , @Ship , @Rate , @Amount , @ItemWeight , @Date ) FETCH NEXT FROM Curs INTO @IID , @No , @Item , @Desc , @Q , @Ship , @Rate ,@Amount ,@ItemWeight ,@Date END CLOSE Curs DEALLOCATE Curs -- updates the tracking table INSERT INTO csOrderTracking (csOrderTracking .OrderNo , csOrderTracking .InvoiceNo ) VALUES (@ID , @NewNumber ); -- marks the order as being invoiced . UPDATE csOrders SET csOrders .Invoiced = 1 WHERE csOrders .OrderNo = @ID --NOTE: the invoice will NOT be posted to GL COMMIT TRANSACTION END RETURN ' END |
[dbo].[NewInvoiceShippedOrder] |
Owner | dbo |
Parameters | @OrderNo varchar(50) |
Definition | |
Note | |
Called SPs | NewConvertOrder |
DDL Code | CREATE PROC [dbo].[NewInvoiceShippedOrder] @OrderNo varchar (50 ) AS BEGIN DECLARE @A int SELECT @A = 0 -- invoice the order , if it is not already invoiced IF EXISTS (SELECT OrderNo FROM csOrderTracking WHERE OrderNo = @OrderNo ) SELECT @A = 1 IF @A = 0 BEGIN EXEC NewConvertOrder @OrderNo END END RETURN ' END |
[dbo].[NewSplitPO] |
Owner | dbo |
Parameters | @TransID varchar(50) |
Definition | |
Note | |
Called SPs | PostGLWorkDetail PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[NewSplitPO] @TransID varchar (50 ) AS BEGIN 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 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 SELECT @a = SUM (Quantity-ReceivedQty ) FROM vpPurchaseOrderDetails WHERE vpPurchaseOrderDetails .PORef = @TransID IF @a=0 BEGIN -- The PO was fully received -- DELETE FROM GLWorkDetail -- Update the Received flag -- UPDATE vpPurchaseOrders SET ReceivedYN=1 WHERE vpPurchaseOrders .PORef = @TransID UPDATE lsVendors SET Balance = Balance + @Total WHERE VendorName = @Vendor INSERT INTO GeneralJournalEntry (Date , EntryNo , TrType , TrID , PostedYN , SystemGenerated ) VALUES (CONVERT (varchar , CURRENT_TIMESTAMP ), @IDD , 2 , @TransID , 1 , 1 ) SELECT @DiscAmount = (SELECT SUM (Amount ) FROM vpPurchaseOrderDetails WHERE PORef = @TransID ) * @Discount / 100 INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Freight Expense '', @Freight , 0 , ''Freight '', 10 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Accounts Payable '', 0 , @Total , ''AP '', 20 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Discounts '', 0 , @DiscAmount , ''Discount '', 30 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Other Expense '', @OtherCharges , 0 , ''Charge '', 40 ) 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 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 UPDATE lsItems SET QuantityOnHand = QuantityOnHand + @ReceivedQty WHERE ItemName = @Item INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , @AssetAccount , @Rate * @Quantity , 0 , ''INV Asset '', @BID ) SELECT @BID = @BID + 1 FETCH NEXT FROM Curs1 INTO @Item , @Quantity , @ReceivedQty , @Rate , @AssetAccount END CLOSE Curs1 DEALLOCATE Curs1 EXEC PostGLWorkDetail @IDD END ELSE BEGIN -- The PO must be split , the items were partially received -- DELETE FROM GLWorkDetail -- Insert a new row representing the new PO -- SELECT @PORef = PORef , @Date = Date , @Class = Class , @Memo = Memo , @Term = Term , @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 , Term , BillTo , ShipTo , ReceiveBy , VendorMessage , ShipVia , TrackingNumber , Freight , Discount , OtherCharges , Total , ReceivedYN ) VALUES (@PORef+ ''-1 '', @Date , @Vendor , @Class , @Memo , @Term , @BillTo , @ShipTo , @ReceiveBy , @VendorMessage , @ShipVia , @TrackingNumber , 0 , @Discount , 0 , @Total , 0 ) -- Post the received items -- INSERT INTO GeneralJournalEntry (Date , EntryNo , TrType , TrID , PostedYN , SystemGenerated ) VALUES (CONVERT (varchar , CURRENT_TIMESTAMP ), @IDD , 2 , @TransID , 1 , 1 ) 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 -- 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 ) UPDATE vpPurchaseOrderDetails SET Quantity = ReceivedQty , Amount = Rate * ReceivedQty WHERE ID=@ID 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 INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , @AssetAccount , @Rate * @ReceivedQty , 0 , ''INV Asset '', @BID ) SELECT @BID = @BID + 10 END UPDATE lsItems SET QuantityOnHand = QuantityOnHand + @ReceivedQty WHERE ItemName = @Item 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 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 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 INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Freight Expense '', @Freight , 0 , ''Freight '', @BID ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Accounts Payable '', 0 , @SumOld , ''AP '', @BID+10 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Discounts '', 0 , @DSC , ''Discount '', @BID+20 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Other Expense '', @OtherCharges , 0 , ''Charge '', @BID+30 ) 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 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 END RETURN ' END |
[dbo].[PayEmployees] |
Owner | dbo |
Parameters | @Empl varchar(50), @Amount float, @CheckNo int, @Date datetime, @Account varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[PayEmployees] @Empl varchar (50 ), @Amount float , @CheckNo int , @Date datetime , @Account varchar (50 ) AS BEGIN TRANSACTION -- updates the employees '' commission and the BillTo info UPDATE lsEmployees SET Commission = Commission - @Amount WHERE EmployeeName = @Empl DECLARE @BillTo varchar (255 ) SELECT @BillTo = Address1 + '', '' + City + '' '' + State + '', '' + Zip FROM lsEmployees WHERE EmployeeName = @Empl INSERT INTO EMPChecks (CheckNo , Payee , Account , Address , Date , Amount ) VALUES (@CheckNo , @Empl , @Account , @BillTo , @Date , @Amount ) COMMIT TRANSACTION ' END |
[dbo].[postBILL] |
Owner | dbo |
Parameters | @Reference varchar(50) |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[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 SELECT @Vendor = Vendor , @Amount = Amount , @D = Discount , @Freight = Freight , @Charges = OtherCharges FROM vpBills WHERE Reference = @Reference UPDATE lsVendors SET Balance = Balance + @Amount WHERE VendorName = @Vendor -- creates the GL header DECLARE @IsExist int SELECT @IsExist = COUNT (EntryNo ) FROM GeneralJournalEntry WHERE EntryNo = ''VOUCHER '' + @Reference IF @IsExist=0 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 -- fills the GL detail lines INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , ''Accounts Payable '', 0 , @Amount , ''AP '', 10 ) SELECT @NewNo = 20 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 bill detail line , post to GL work table 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 -- Freight -- -- 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 -- Misc Charges -- -- 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 IF @IsExist=0 EXEC PostGLWorkDetail @GLID UPDATE vpBills SET PostedYN = 1 , PORef = @Reference WHERE Reference = @Reference COMMIT TRANSACTION ' END |
[dbo].[postBillPayment] |
Owner | dbo |
Parameters | @AccNoTF int, @BillID varchar(50), @Account varchar(50), @Amount float, @CheckNo int, @Date datetime, @NewCheckID int, @Total float |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[postBillPayment] @AccNoTF int , @BillID varchar (50 ), @Account varchar (50 ), @Amount float , @CheckNo int , @Date datetime , @NewCheckID int , @Total float AS 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 fields from the vpBills table SELECT @InvDate = Date , @Vendor = Vendor , @PayAmount = PayAmount , @OrigAmount = Amount , @Reference = IsNull (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 DECLARE @IsExist int SELECT @IsExist = COUNT (EntryNo ) FROM GeneralJournalEntry WHERE EntryNo = ''BILLPMT- ''+@BillID IF @IsExist=0 INSERT INTO GeneralJournalEntry (Date , EntryNo , SystemGenerated , PostedYN , TrType , TrID ) VALUES (CONVERT (varchar , CURRENT_TIMESTAMP ), @GLID , 1 , 1 , 5 , 0 ) -- updates 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 ) IF (@AccNoTF=1 ) BEGIN DECLARE @accname varchar (50 ) SELECT @accname=AccountName FROM lsAccounts WHERE (AccountNumber = @Account ) END -- creates the payment detail line IF (@AccNoTF=0 ) INSERT INTO vpBillPayments (Reference , Account , Vendor , Date , CheckNo , Amount ) VALUES (@BillID , @Account , @Vendor , CONVERT (varchar , CURRENT_TIMESTAMP ), @NewCheckID , @Amount ) ELSE INSERT INTO vpBillPayments (Reference , Account , Vendor , Date , CheckNo , Amount ) VALUES (@BillID , @accname , @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 IF (@AccNoTF=0 ) UPDATE bChecks SET CheckNo = @CheckNo , Account = @Account , Payee = @Vendor , Address = @VenAddr + '', '' + @VenCity + '' '' + @VenState + '' '' + @VenZip , Date = @Date , Amount = @Total WHERE CheckID = @NewCheckID ELSE UPDATE bChecks SET CheckNo = @CheckNo , Account = @accname , 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 IF @IsExist=0 EXEC PostGLWorkDetail @GLID COMMIT TRANSACTION ' END |
[dbo].[PostCOA] |
Owner | dbo |
Parameters | @Account varchar(50), @Debit float, @Credit float |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[PostCOA] @Account varchar (50 ), @Debit float , @Credit float AS -- updates the account ''s balance . The amount is added if Debit<>0 or substracted if Credit<>0 IF @Debit <> 0 UPDATE lsAccounts SET Balance = Balance + @Debit WHERE AccountName = @Account IF @Credit <> 0 UPDATE lsAccounts SET Balance = Balance - @Credit WHERE AccountName = @Account ' END |
[dbo].[postCREDMEM] |
Owner | dbo |
Parameters | @ID varchar(50) |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[postCREDMEM] @ID varchar (50 ) AS BEGIN TRANSACTION -- this procedure is actualy a "reverse" invoice , it is identical excepts the credits are posted -- as debits , and debits are posted as credits DECLARE @total float , @taxable float , @Cust varchar (50 ), @SalesTax varchar (50 ), @Freight float , @Handling float SELECT @total = Total , @taxable = TaxTotal , @Cust = Customer , @SalesTax = SalesTax , @Freight = Freight , @Handling = Handling FROM csCustomerCredits WHERE CreditNumber = @ID DELETE FROM GLWorkDetail 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 DECLARE @GLID varchar (50 ) SELECT @GLID = ''CREDMEM '' + CONVERT (varchar , @ID ) INSERT INTO GeneralJournalEntry (Date ,EntryNo , SystemGenerated , PostedYN , TrType , TrID ) VALUES (CONVERT (varchar ,CURRENT_TIMESTAMP ,101 ), @GLID , 1 , 1 , 3 , @ID ) UPDATE lsCustomers SET Balance = Balance - @total WHERE CustomerName = @Cust 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 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 ) 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 INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , @IncomeAccount , @SalesPrice * @Quantity , 0 , ''Income Account '', @No ) SELECT @No=@No+10 INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID ,@COGSAccount , 0 , @Cost * @Quantity , ''COGS Account '', @No ) SELECT @No=@No+10 INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID ,@AssetAccount , @Cost * @Quantity , 0 , ''Inventory Asset '', @No ) SELECT @No=@No+10 FETCH NEXT FROM GLDetail INTO @Quantity , @Amount , @Cost , @COGSAccount , @IncomeAccount , @SalesPrice , @AssetAccount END 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 ) IF @SalesTax <> "" AND "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 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 EXEC PostGLWorkDetail @GLID UPDATE csCustomerCredits SET PostedYN = 1 WHERE CreditNumber = @ID COMMIT TRANSACTION ' END |
[dbo].[postCUSTPMT] |
Owner | dbo |
Parameters | @MAINID varchar(50) |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[postCUSTPMT] @MAINID varchar (50 ) AS BEGIN TRANSACTION DELETE FROM GLWorkDetail DECLARE @Unapplied float , @Balance float , @Customer varchar (50 ), @Account varchar (50 ), @PaymentAmount float -- retrieves the payment ''s fields 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 GL header DECLARE @IsExist int SELECT @IsExist = COUNT (EntryNo ) FROM GeneralJournalEntry WHERE EntryNo = @NewID IF @IsExist=0 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 UPDATE csInvoices SET PayAmount = PayAmount + textboxAmount FROM csCustomerPaymentDetails WHERE InvoiceNo = FID AND Reference = @MAINID UPDATE csInvoices SET PaidYN = 1 WHERE Total = PayAmount -- posts it to GL EXEC PostGLWorkDetail @NewID COMMIT TRANSACTION ' END |
[dbo].[PostGLWorkDetail] |
Owner | dbo |
Parameters | @GLID varchar(50) |
Definition | |
Note | |
Called SPs | PostCOA PostCOA PostCOA |
DDL Code | CREATE PROC [dbo].[PostGLWorkDetail] @GLID varchar (50 ) AS DECLARE @Account varchar (50 ), @Debit float , @Credit float , @Memo varchar (255 ), @No int DECLARE @GLWorkDebitAmount float , @GLWorkCreditAmount float -- this is the GL posting routine . It checks whether the detail lines are similar , or nulls -- the values are read from the working table GLWorkDetail and inserted into the -- GeneralJournalDetail table DECLARE GL1 CURSOR FOR SELECT Account , SumOfDebit , SumOfCredit , Memo , No FROM qrySumGLWorkDetail OPEN GL1 FETCH NEXT FROM GL1 INTO @Account , @Debit , @Credit , @Memo , @No WHILE @@FETCH_STATUS = 0 BEGIN IF @Debit <> @Credit BEGIN IF @Debit > 0 AND @Credit > 0 BEGIN SELECT @GLWorkDebitAmount = @Debit SELECT @GLWorkCreditAmount = 0 INSERT INTO GeneralJournalDetail (EntryNo , Account , Debit , Credit , Memo , No ) VALUES (@GLID ,@Account , @GLWorkDebitAmount , @GLWorkCreditAmount , @Memo , @No ) -- posts the Debit EXEC PostCOA @Account , @GLWorkDebitAmount , @GLWorkCreditAmount SELECT @GLWorkDebitAmount = 0 SELECT @GLWorkCreditAmount = @Credit INSERT INTO GeneralJournalDetail (EntryNo , Account , Debit , Credit , Memo , No ) VALUES (@GLID , @Account , @GLWorkDebitAmount , @GLWorkCreditAmount , @Memo , @No ) -- posts the Credit EXEC PostCOA @Account , @GLWorkDebitAmount , @GLWorkCreditAmount END ELSE BEGIN IF @Debit > @Credit BEGIN SELECT @GLWorkDebitAmount = @Debit - @Credit SELECT @GLWorkCreditAmount = 0 END ELSE BEGIN SELECT @GLWorkDebitAmount = 0 SELECT @GLWorkCreditAmount = @Credit - @Debit END INSERT INTO GeneralJournalDetail (EntryNo , Account , Debit , Credit , Memo , No ) VALUES (@GLID , @Account , @GLWorkDebitAmount , @GLWorkCreditAmount , @Memo , @No ) -- posts the Debit if it is greater than the credit , or the Credit if it is greater than the debit EXEC PostCOA @Account , @GLWorkDebitAmount , @GLWorkCreditAmount END END FETCH NEXT FROM GL1 INTO @Account , @Debit , @Credit , @Memo , @No END CLOSE GL1 DEALLOCATE GL1 ' END |
[dbo].[postINV] |
Owner | dbo |
Parameters | @InvoiceNo varchar(50) |
Definition | |
Note | |
Called SPs | addCommission PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[postINV] @InvoiceNo varchar (50 ) AS BEGIN TRANSACTION -- posts an invoice to GL DELETE FROM GLWorkDetail DECLARE @total float , @taxable float , @SalesTax varchar (50 ), @Cust varchar (50 ), @Freight float , @Handling float 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 --modified by ADI 2002/10/10 DECLARE @TaxPercent float -- gets a new document number DECLARE @GLID varchar (50 ) SELECT @GLID = ''INV '' + CONVERT (varchar , @InvoiceNo ) 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 ) 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 item ''s account 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 FETCH NEXT FROM GLDetail INTO @Quantity , @Amount , @Cost , @COGSAccount , @IncomeAccount , @SalesPrice , @AssetAccount END 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 "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 INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , @Account , 0 , @taxable * @TaxPercent / @TotalPerc , ''Sales Tax '', @No ) SELECT @No = @No + 10 FETCH NEXT FROM Curs1 INTO @Account , @TaxPercent END CLOSE Curs1 DEALLOCATE Curs1 END -- posts it to GL EXEC PostGLWorkDetail @GLID UPDATE csInvoices SET PostedYN = 1 WHERE InvoiceNo = @InvoiceNo COMMIT TRANSACTION ' END |
[dbo].[postINVADJ] |
Owner | dbo |
Parameters | @AdjID varchar(50), @Value float, @AccountID varchar(50) |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[postINVADJ] @AdjID varchar (50 ), @Value float , @AccountID varchar (50 ) AS BEGIN TRANSACTION -- posts an inventory adjustment DELETE FROM GLWorkDetail -- gets a new document number DECLARE @GLID varchar (50 ) SELECT @GLID = ''INVADJ- ''+@AdjID INSERT INTO GeneralJournalEntry (Date , EntryNo , SystemGenerated , PostedYN , TrType , TrID ) VALUES (CONVERT (varchar , CURRENT_TIMESTAMP , 101 ), @GLID , 1 , 1 , 7 , @AdjID ) 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 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 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 UPDATE InventoryAdjustments SET PostedYN = 1 WHERE RefNo = @AdjID COMMIT TRANSACTION ' END |
[dbo].[postJournal] |
Owner | dbo |
Parameters | @ID varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[postJournal] @ID varchar (50 ) AS BEGIN TRANSACTION UPDATE GeneralJournalEntry SET SystemGenerated = 0 , PostedYN = 1 , TrType = 8 , TrID = @ID WHERE EntryNo = @ID COMMIT TRANSACTION ' END |
[dbo].[postPayEmployees] |
Owner | dbo |
Parameters | @ID int, @Amount float, @Account varchar(50) |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[postPayEmployees] @ID int , @Amount float , @Account varchar (50 ) AS BEGIN TRANSACTION 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 GL header 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 DECLARE @No int SELECT @No = (IsNull (MAX (No ), 0 )+10 ) FROM GLWorkDetail WHERE GLID = @GLID INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , ''Sales '', @Amount , 0 , ''AP '', @No ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@GLID , @Account , 0 , @Amount , '' '', @No+10 ) -- posts it to GL EXEC PostGLWorkDetail @GLID COMMIT TRANSACTION ' END |
[dbo].[postPayMany] |
Owner | dbo |
Parameters | @AccNoTF int, @Vendor varchar(50), @Account varchar(50), @Amount float, @Date datetime, @GroupID varchar(50) |
Definition | |
Note | |
Called SPs | postBillPayment postBillPayment |
DDL Code | CREATE PROC [dbo].[postPayMany] @AccNoTF int , @Vendor varchar (50 ), @Account varchar (50 ), @Amount float , @Date datetime , @GroupID varchar (50 ) AS DECLARE @POID varchar (50 ), @DueAmount float , @OrigAmount float , @CheckNo varchar (20 ) BEGIN TRANSACTION DECLARE Curs1 CURSOR FOR SELECT Reference , Amount , DueAmount FROM vpBills WHERE DueAmount > 0 .01 AND Vendor = @Vendor AND PostedYN = 1 ORDER BY DueDate DESC OPEN Curs1 FETCH NEXT FROM Curs1 INTO @POID , @OrigAmount , @DueAmount DECLARE @NewCheck int INSERT INTO bChecks (CheckNo ) VALUES (0 ) SELECT @NewCheck = @@IDENTITY DECLARE @Total float SELECT @Total = 0 -- it parses the vpBills table and applies the amount to each bill , until @Amount is 0 SELECT @CheckNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (CheckNo ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (CheckNo ) + 1 FROM DocNumbers ) END ) UPDATE DocNumbers SET CheckNo = CONVERT (int ,@CheckNo ) WHILE @@FETCH_STATUS = 0 BEGIN IF @Amount >= @DueAmount AND @DueAmount > 0 BEGIN SELECT @Amount = @Amount - @DueAmount -- calls the bill payment routine . EXEC postBillPayment @AccNoTF , @POID , @Account , @DueAmount , @CheckNo , @Date , @NewCheck , @OrigAmount SELECT @Total = @Total + @DueAmount END ELSE BEGIN EXEC postBillPayment @AccNoTF , @POID , @Account , @Amount , @CheckNo , @Date , @NewCheck , @OrigAmount SELECT @Total = @Total + @Amount SELECT @Amount = 0 END IF @Amount=0 BREAK FETCH NEXT FROM Curs1 INTO @POID , @OrigAmount , @DueAmount END CLOSE Curs1 DEALLOCATE Curs1 --updates the check entry . GroupID will contain the value generated from the ASP page UPDATE bChecks SET Amount = @Total , GroupID = @GroupID WHERE CheckID = @NewCheck COMMIT TRANSACTION ' END |
[dbo].[postTRANSFR] |
Owner | dbo |
Parameters | @ID varchar(50), @amount float, @fromaccount varchar(50), @toaccount varchar(50) |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[postTRANSFR] @ID varchar (50 ), @amount float , @fromaccount varchar (50 ), @toaccount varchar (50 ) AS BEGIN TRANSACTION DELETE FROM GLWorkDetail -- gets a new document number DECLARE @GLID varchar (50 ) SELECT @GLID = ''TRNSFR- '' + @ID 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 UPDATE bTransferFunds SET PostedYN = 1 WHERE Reference = @ID COMMIT TRANSACTION ' END |
[dbo].[rptAPAgingDetail] |
Owner | dbo |
Parameters | @VenID varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptAPAgingDetail] @VenID varchar (50 ) AS SELECT CASE Type WHEN ''Voucher '' THEN Reference ELSE PORef END AS TransID , Type , CONVERT (varchar ,Date ,101 ) AS Date , Reference AS Num ,CONVERT (varchar ,DueDate ,101 ) AS DueDate , DATEDIFF (day ,DueDate ,getdate ()) AS Aging , DueAmount AS OpeningBalance , CASE Type WHEN ''Voucher '' THEN ''../payables/entervouchers .asp?direction=idnumber '' ELSE ''../payables/enterpurchaseorders .asp?direction=idnumber '' END AS Path FROM vpBills WHERE vpBills .Vendor=@VenID AND DueAmount > 0 .01 AND vpBills .PostedYN = 1 ORDER BY CASE (SELECT AgingReportUse FROM Company ) WHEN 1 THEN Date ELSE DueDate END ' END |
[dbo].[rptAPRegister] |
Owner | dbo |
Parameters | @BegDate DATETIME, @EndDate DATETIME |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptAPRegister] @BegDate DATETIME , @EndDate DATETIME AS SELECT CASE Type WHEN ''Voucher '' THEN Reference ELSE PORef END AS TransID , Type , CONVERT (varchar ,Date ,101 ) AS Date , VendorInvoice AS Number , Vendor , CASE Type WHEN ''Voucher '' THEN ''../payables/entervouchers .asp?direction=idnumber '' ELSE ''../payables/enterpurchaseorders .asp?direction=idnumber '' END AS Path , CASE DueAmount WHEN 0 THEN ''Paid '' ELSE CONVERT (varchar , DueDate , 101 ) END AS DueDate , CASE DueAmount WHEN 0 THEN Amount ELSE DueAmount END AS Amount FROM vpBills WHERE vpBills .PostedYN = 1 AND Date >= @BegDate AND Date <= @EndDate ORDER BY CASE (SELECT AgingReportUse FROM Company ) WHEN 1 THEN Date ELSE DueDate END ' END |
[dbo].[rptARAgingDetail] |
Owner | dbo |
Parameters | @CustID varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptARAgingDetail] @CustID varchar (50 ) AS SELECT InvoiceNo AS TransID , ''Invoice '' AS Type , CONVERT (varchar ,Date ,101 ) AS Date , InvoiceNo AS Num ,CONVERT (varchar ,DueDate ,101 ) AS DueDate ,DATEDIFF (day ,DueDate ,getdate ()) AS Aging , ''../receivables/createinvoices .asp?direction=idnumber '' AS Path , Total AS OpeningBalance FROM csInvoices WHERE Customer=@CustID AND PostedYN = 1 AND PaidYN = 0 ORDER BY Aging DESC ' END |
[dbo].[rptCheckRegister] |
Owner | dbo |
Parameters | @Account varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptCheckRegister] @Account varchar (50 ) AS SELECT CONVERT (varchar ,CheckID ) AS TransID , CONVERT (varchar ,Date ,101 ) AS Date , ''Check '' AS Type , Payee AS Name , CONVERT (varchar ,CheckNo ) AS CheckNo , CONVERT (varchar , Amount ) AS Amount , ''../payables/printchecks .asp?direction=idnumber '' AS Path FROM bChecks WHERE Account = @Account UNION SELECT GeneralJournalEntry .EntryNo AS TransID , CONVERT (varchar ,Date ,101 ) AS Date , ''Transfer '' AS TYPE , '' '' AS Name , '' '' AS CheckNo , CONVERT (varchar , -Credit + Debit ) AS Amount , ''../ledger/makejournalentries .asp?direction=idnumber '' AS Path FROM GeneralJournalEntry INNER JOIN GeneralJournalDetail ON GeneralJournalEntry .EntryNo = GeneralJournalDetail .EntryNo WHERE Account = @Account AND GeneralJournalEntry .TrType = 6 ' END |
[dbo].[rptOpenBills] |
Owner | dbo |
Parameters | @BegDate DATETIME, @EndDate DATETIME |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptOpenBills] @BegDate DATETIME , @EndDate DATETIME AS SELECT CASE Type WHEN ''Voucher '' THEN Reference ELSE PORef END AS TransID , CONVERT (varchar ,vpBills .Date ,101 ) AS Date , VendorInvoice AS Number , Vendor , CONVERT (varchar ,vpBills .DueDate ,101 ) AS DateDue , vpBills .DueAmount AS Due , CASE Type WHEN ''Voucher '' THEN ''../payables/entervouchers .asp?direction=idnumber '' ELSE ''../payables/enterpurchaseorders .asp?direction=idnumber '' END AS Path FROM vpBills WHERE vpBills .Date BETWEEN CONVERT (DATETIME ,@BegDate ,101 ) AND CONVERT (DATETIME ,@EndDate ,101 ) AND PostedYN = 1 AND vpBills .DueAmount > 0 ORDER BY CASE (SELECT AgingReportUse FROM Company ) WHEN 1 THEN Date ELSE DueDate END ' END |
[dbo].[rptOpenPO] |
Owner | dbo |
Parameters | @BegDate DATETIME, @EndDate DATETIME |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptOpenPO] @BegDate DATETIME , @EndDate DATETIME AS SELECT PORef AS TransID , CONVERT (varchar ,Date ,101 ) AS Date , PORef AS Number , Vendor , CASE (SELECT COUNT (vpPurchaseOrderDetails .Item ) FROM vpPurchaseOrderDetails WHERE vpPurchaseOrderDetails .PORef=vpPurchaseOrders .PORef ) WHEN 1 THEN (SELECT lsAccounts .AccountName FROM lsAccounts ,lsItems , vpPurchaseOrderDetails WHERE vpPurchaseOrderDetails .PORef=vpPurchaseOrders .PORef AND lsItems .ItemName=vpPurchaseOrderDetails .Item AND lsAccounts .AccountName=lsItems .AssetAccount ) ELSE ''-Split- '' END AS Account , ''Open '' AS Status , Total FROM vpPurchaseOrders WHERE vpPurchaseOrders .Date BETWEEN CONVERT (DATETIME ,@BegDate ,101 ) AND CONVERT (DATETIME ,@EndDate ,101 ) AND vpPurchaseOrders .ReceivedYN=0 ORDER BY Date ' END |
[dbo].[rptProfit1] |
Owner | dbo |
Parameters | @Cust varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptProfit1] @Cust varchar (50 ) AS SELECT csInvoices .InvoiceNo AS TransID , CONVERT (varchar ,Date ,101 ) AS Date , Customer AS Name , csInvoiceItems .Description AS Memo , lsItems .ItemName AS Item , csInvoiceItems .Quantity AS Qty , lsItems .Cost AS Cost , csInvoiceItems .Quantity *Cost AS TotalCost , csInvoiceItems .Rate AS Price , csInvoiceItems .Quantity * Rate AS TotalPrice , csInvoiceItems .Amount , ''../receivables/createinvoices .asp?direction=idnumber '' AS Path , Amount - Quantity *Cost AS Profit FROM csInvoices , csInvoiceItems , lsItems WHERE csInvoices .InvoiceNo=csInvoiceItems .InvoiceNo AND lsItems .ItemName = csInvoiceItems .Item AND csInvoices .Customer = @Cust AND PostedYN = 1 ' END |
[dbo].[rptProfit2] |
Owner | dbo |
Parameters | @Item varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptProfit2] @Item varchar (50 ) AS SELECT csInvoices .InvoiceNo AS TransID , ''Invoice '' AS Type , CONVERT (varchar ,Date ,101 ) AS Date , csInvoices .InvoiceNo AS Num , Customer AS Name , csInvoiceItems .Description AS Memo , csInvoiceItems .Quantity AS Qty , csInvoiceItems .Rate AS Price , lsItems .Cost AS Cost , csInvoiceItems .Quantity *Cost AS TotalCost , csInvoiceItems .Amount , ''../receivables/createinvoices .asp?direction=idnumber '' AS Path , Amount - Quantity *Cost AS Profit FROM csInvoices , csInvoiceItems , lsItems WHERE csInvoices .InvoiceNo=csInvoiceItems .InvoiceNo AND lsItems .ItemName=csInvoiceItems .Item AND lsItems .ItemName=@Item AND PostedYN = 1 ' END |
[dbo].[rptPurchase1] |
Owner | dbo |
Parameters | @Item varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptPurchase1] @Item varchar (50 ) AS SELECT vpPurchaseOrders .PORef AS TransID ,CONVERT (varchar ,Date ,101 ) AS Date , ''Receiving '' AS Type , VendorInvoice AS Num , Vendor AS Name , Description AS Memo , Quantity AS Qty , Rate AS Price , ''../payables/enterpurchaseorders .asp?direction=idnumber '' AS Path , Amount FROM vpPurchaseOrders , vpPurchaseOrderDetails WHERE vpPurchaseOrders .PORef=vpPurchaseOrderDetails .PORef AND Item=@Item AND ReceivedYN = 1 ' END |
[dbo].[rptPurchase2] |
Owner | dbo |
Parameters | @Vend varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptPurchase2] @Vend varchar (50 ) AS SELECT vpPurchaseOrders .PORef AS TransID , CONVERT (varchar ,Date ,101 ) AS Date , ''Receiving '' AS Type , VendorInvoice AS Num , Vendor AS Name , lsitems .ItemName AS Memo , Quantity AS Qty , Rate AS Price , ''../payables/enterpurchaseorders .asp?direction=idnumber '' AS Path , Amount FROM vpPurchaseOrders , vpPurchaseOrderDetails , lsItems WHERE vpPurchaseOrders .PORef=vpPurchaseOrderDetails .PORef AND vpPurchaseOrderDetails .Item=lsItems .ItemName AND Vendor=@Vend AND ReceivedYN = 1 ' END |
[dbo].[rptPurchase3] |
Owner | dbo |
Parameters | @Item int |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptPurchase3] @Item int AS SELECT vpBills .Reference AS TransID , Type , CONVERT (varchar ,Date ,101 ) AS Date , VendorInvoice AS Num , lsVendors .VendorName AS Name , vpBillItems .Description AS Memo , vpBillItems .Quantity AS Qty , vpBillItems .Rate AS Price , vpBillItems .Amount FROM vpBills , vpBillItems , lsVendors , lsItems WHERE vpBills .Reference=vpBillItems .BillID AND lsItems .ItemName=vpBillItems .Item AND lsVendors .VendorName=vpBills .Vendor AND lsItems .ItemName=@Item AND PostedYN = 1 AND Type= ''Receiving '' ORDER BY Date ' END |
[dbo].[rptPurchase4] |
Owner | dbo |
Parameters | @Vend varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptPurchase4] @Vend varchar (50 ) AS SELECT vpPurchaseOrders .PORef AS TransID , vpPurchaseOrderDetails .Amount FROM vpPurchaseOrders , vpPurchaseOrderDetails WHERE vpPurchaseOrderDetails .PORef=vpPurchaseOrders .PORef AND vpPurchaseOrders .Vendor=@Vend AND ReceivedYN = 1 ' END |
[dbo].[rptSales1] |
Owner | dbo |
Parameters | @Cust varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptSales1] @Cust varchar (50 ) AS SELECT csInvoices .InvoiceNo AS TransID , ''Invoice '' AS Type , CONVERT (varchar ,Date ,101 ) AS Date , csInvoices .InvoiceNo AS Num , Customer AS Name , csInvoiceItems .Description AS Memo , lsItems .ItemName AS Item , csInvoiceItems .Quantity AS Qty , csInvoiceItems .Rate AS Price , ''../receivables/createinvoices .asp?direction=idnumber '' AS Path , csInvoiceItems .Amount FROM csInvoices , csInvoiceItems ,lsItems WHERE csInvoices .InvoiceNo=csInvoiceItems .InvoiceNo AND lsItems .ItemName=csInvoiceItems .Item AND csInvoices .Customer=@Cust AND PostedYN = 1 ' END |
[dbo].[rptSales2] |
Owner | dbo |
Parameters | @Cust varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptSales2] @Cust varchar (50 ) AS SELECT csInvoices .InvoiceNo AS TransID , csInvoiceItems .Amount FROM csInvoices , csInvoiceItems WHERE csInvoices .InvoiceNo=csInvoiceItems .InvoiceNo AND csInvoices .Customer=@Cust AND PostedYN = 1 ' END |
[dbo].[rptSales3] |
Owner | dbo |
Parameters | @Item varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptSales3] @Item varchar (50 ) AS SELECT csInvoices .InvoiceNo AS TransID , ''Invoice '' AS Type , CONVERT (varchar ,Date ,101 ) AS Date , csInvoices .InvoiceNo AS Num , Customer AS Name , csInvoiceItems .Description AS Memo , csInvoiceItems .Quantity AS Qty , csInvoiceItems .Rate AS Price , ''../Transactions/CustomerSales/CreateInvoices .asp?direction=idnumber '' AS Path , csInvoiceItems .Amount FROM csInvoices , csInvoiceItems ,lsItems WHERE csInvoices .InvoiceNo=csInvoiceItems .InvoiceNo AND lsItems .ItemName=csInvoiceItems .Item AND lsItems .ItemName=@Item AND PostedYN = 1 ' END |
[dbo].[rptSales4] |
Owner | dbo |
Parameters | @Item varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptSales4] @Item varchar (50 ) AS SELECT csInvoices .InvoiceNo AS TransID , ''Invoice '' AS Type , CONVERT (varchar ,Date ,101 ) AS Date , csInvoices .InvoiceNo AS Num , Customer AS Name , csInvoiceItems .Description AS Memo , csInvoiceItems .Quantity AS Qty , csInvoiceItems .Rate AS Price , csInvoiceItems .Amount FROM csInvoices , csInvoiceItems ,lsItems WHERE csInvoices .InvoiceNo=csInvoiceItems .InvoiceNo AND lsItems .ItemName=csInvoiceItems .Item AND lsItems .ItemName=@Item AND PostedYN = 1 ' END |
[dbo].[rptSalesTax] |
Owner | dbo |
Parameters | @Grp varchar(50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptSalesTax] @Grp varchar (50 ) AS SELECT DISTINCT InvoiceNo , CONVERT (varchar ,Date ,101 ) AS Date , Total-TaxTotal AS Amount , (Total-TaxTotal )*lsTaxGroups .TotalPercent/100 AS Payment FROM csInvoices ,lsTaxGroups WHERE SalesTax=@Grp AND lsTaxGroups .TaxGroupID=@Grp AND PostedYN = 1 ' END |
[dbo].[sp_get_NewCheckID] |
Owner | dbo |
Parameters | @NewCheckID int OUT |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_get_NewCheckID] @NewCheckID int OUT AS BEGIN BEGIN TRANSACTION INSERT INTO bChecks (CheckNo ) VALUES (0 ) COMMIT TRANSACTION SET @NewCheckID = (SELECT MAX (CheckID ) AS NewCID FROM bChecks ) END RETURN ' END |
[dbo].[sp_get_NewDocNumber] |
Owner | dbo |
Parameters | @Type varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_get_NewDocNumber] @Type varchar (50 ) AS BEGIN DECLARE @NewNo varchar (20 ) -- gets the next available document number for different types of transactions . These -- values are read from the DocNumbers table . IF @Type= ''INV '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (Invoices ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (Invoices ) + 1 FROM DocNumbers ) END ) END ELSE IF @Type= ''ORD '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (Orders ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (Orders ) + 1 FROM DocNumbers ) END ) END ELSE IF @Type= ''BP '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (BillPmt ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (BillPmt ) + 1 FROM DocNumbers ) END ) END ELSE IF @Type= ''CP '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (CustPmt ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (CustPmt ) + 1 FROM DocNumbers ) END ) END ELSE IF @Type= ''CC '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (CustCredits ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (CustCredits ) + 1 FROM DocNumbers ) END ) END ELSE IF @Type= ''CHECK '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (CheckNo ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (CheckNo ) + 1 FROM DocNumbers ) END ) END ELSE IF @Type= ''TF '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (Transfers ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (Transfers ) + 1 FROM DocNumbers ) END ) END ELSE IF @Type= ''BILL '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (Vouchers ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (Vouchers ) + 1 FROM DocNumbers ) END ) END ELSE IF @Type= ''INVADJ '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (Adjustment ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (Adjustment ) + 1 FROM DocNumbers ) END ) END ELSE IF @Type= ''TT '' BEGIN SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (TimeTracking ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (TimeTracking ) + 1 FROM DocNumbers ) END ) END ELSE BEGIN SELECT @NewNo = ''1000 '' END SELECT @NewNo AS NewNo END RETURN ' END |
[dbo].[sp_insert_Accounts] |
Owner | dbo |
Parameters | @UserName varchar (20), @Password varchar (20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_insert_Accounts] @UserName varchar (20 ), @Password varchar (20 ) AS BEGIN INSERT INTO Users (Username , Password ) VALUES (@Username , @Password ) END RETURN ' END |
[dbo].[sp_insert_BillExpenses] |
Owner | dbo |
Parameters | @Reference varchar (20), @Account varchar (50), @Amount money, @Memo varchar (50), @Project varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_insert_BillExpenses] @Reference varchar (20 ), @Account varchar (50 ), @Amount money , @Memo varchar (50 ), @Project varchar (50 ) AS BEGIN DECLARE @No integer SELECT @No = (SELECT ISNULL (MAX (vpBillExpenses .No ),0 )+10 FROM vpBillExpenses WHERE vpBillExpenses .Reference = @Reference ) INSERT INTO vpBillExpenses (Reference , No , Account , Amount , Memo , Project ) VALUES (@Reference , @No , @Account , @Amount , @Memo , @Project ) END RETURN ' END |
[dbo].[sp_insert_Bills] |
Owner | dbo |
Parameters | @Date datetime, @Vendor varchar (50), @Reference varchar (20), @Type varchar (20), @Term varchar (50), @DueDate datetime, @VendorInvoice nvarchar (20), @Freight money, @Discount money, @OtherCharges money, @Amount money, @Memo nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_insert_Bills] @Date datetime , @Vendor varchar (50 ), @Reference varchar (20 ), @Type varchar (20 ), @Term varchar (50 ), @DueDate datetime , @VendorInvoice nvarchar (20 ), @Freight money , @Discount money , @OtherCharges money , @Amount money , @Memo nvarchar (50 ) AS BEGIN INSERT INTO vpBills (Vendor , Date , Reference , DueDate , Term , Memo , VendorInvoice , Discount , Freight , OtherCharges , Amount , Type ) VALUES (@Vendor , @Date , @Reference , @DueDate , @Term , @Memo , @VendorInvoice , @Discount , @Freight , @OtherCharges , @Amount , @Type ) END RETURN ' END |
[dbo].[sp_insert_CustomerPayments] |
Owner | dbo |
Parameters | @Customer varchar (50) , @CheckNo varchar (20) , @CreditCardNo varchar (30) , @PaymentAmount money , @CardExpires datetime , @Date datetime , @NameOnCard nvarchar (30) , @PaymentMethod varchar (50) , @CardZipCode nvarchar (20) , @Memo varchar (50) , @CardApproved bit , @Reference varchar (50) , @ApplyExistingCredits bit , @Apply money , @TotalToApply money , @TotalApplied money , @UnappliedAmount money , @Account varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_insert_CustomerPayments] @Customer varchar (50 ) , @CheckNo varchar (20 ) , @CreditCardNo varchar (30 ) , @PaymentAmount money , @CardExpires datetime , @Date datetime , @NameOnCard nvarchar (30 ) , @PaymentMethod varchar (50 ) , @CardZipCode nvarchar (20 ) , @Memo varchar (50 ) , @CardApproved bit , @Reference varchar (50 ) , @ApplyExistingCredits bit , @Apply money , @TotalToApply money , @TotalApplied money , @UnappliedAmount money , @Account varchar (50 ) AS BEGIN INSERT INTO csCustomerPayments (Customer , CheckNo , CreditCardNo , PaymentAmount , CardExpires , Date , NameOnCard , PaymentMethod , CardZipCode , Memo , CardApproved , Reference , ApplyExistingCredits , Apply , TotalToApply , TotalApplied , UnappliedAmount , Account ) VALUES (@Customer , @CheckNo , @CreditCardNo , @PaymentAmount , @CardExpires , @Date , @NameOnCard , @PaymentMethod , @CardZipCode , @Memo , @CardApproved , @Reference , @ApplyExistingCredits , @Apply , @TotalToApply , @TotalApplied , @UnappliedAmount , @Account ) END RETURN ' END |
[dbo].[sp_insert_GeneralJournalDetail] |
Owner | dbo |
Parameters | @EntryNo varchar (50) , @Account varchar (50) , @Debit money , @Credit money , @Memo varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_insert_GeneralJournalDetail] @EntryNo varchar (50 ) , @Account varchar (50 ) , @Debit money , @Credit money , @Memo varchar (50 ) AS BEGIN DECLARE @No integer SELECT @No = (SELECT ISNULL (MAX (GeneralJournalDetail .No ),0 )+10 FROM GeneralJournalDetail WHERE GeneralJournalDetail .EntryNo = @EntryNo ) INSERT INTO GeneralJournalDetail (EntryNo , No , Account , Debit , Credit , Memo ) VALUES (@EntryNo , @No , @Account , @Debit , @Credit , @Memo ) END RETURN ' END |
[dbo].[sp_insert_GeneralJournalEntry] |
Owner | dbo |
Parameters | @EntryNo varchar (20), @Date datetime |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_insert_GeneralJournalEntry] @EntryNo varchar (20 ), @Date datetime AS BEGIN INSERT INTO GeneralJournalEntry (EntryNo , Date , SystemGenerated , PostedYN ) VALUES (@EntryNo , @Date , 0 , 0 ) END RETURN ' END |
[dbo].[sp_insert_Memorized] |
Owner | dbo |
Parameters | @Name varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_insert_Memorized] @Name varchar (50 ) AS BEGIN INSERT INTO lsMemorized (Name ) VALUES (@Name ) END RETURN ' END |
[dbo].[sp_insert_PurchaseOrderItems] |
Owner | dbo |
Parameters | @PORef varchar (50) , @No int , @Item varchar (50) , @Description nvarchar (255) , @Quantity float , @Rate money , @Amount money , @Project varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_insert_PurchaseOrderItems] @PORef varchar (50 ) , @No int , @Item varchar (50 ) , @Description nvarchar (255 ) , @Quantity float , @Rate money , @Amount money , @Project varchar (50 ) AS BEGIN INSERT INTO vpPurchaseOrderDetails (PORef , No , Item , Description , Quantity , Rate , Amount , Project ) VALUES (@PORef , @No , @Item , @Description , @Quantity , @Rate , @Amount , @Project ) END RETURN ' END |
[dbo].[sp_insert_Terms] |
Owner | dbo |
Parameters | @Term varchar (50) , @Standard bit , @DayTillNetDue smallint , @Discount float , @DaysTillDiscountExpires smallint , @DayOfMonthNetDue smallint , @DueNextMonthIfWithinDays smallint , @Discount1 float , @DayDiscountExpires smallint , @Inactive bit |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_insert_Terms] @Term varchar (50 ) , @Standard bit , @DayTillNetDue smallint , @Discount float , @DaysTillDiscountExpires smallint , @DayOfMonthNetDue smallint , @DueNextMonthIfWithinDays smallint , @Discount1 float , @DayDiscountExpires smallint , @Inactive bit AS BEGIN INSERT INTO [dbo].[lsTerms] (Term , Standard , DayTillNetDue , Discount , DaysTillDiscountExpires , DayOfMonthNetDue , DueNextMonthIfWithinDays , Discount1 , DayDiscountExpires , Inactive ) VALUES (@Term , @Standard , @DayTillNetDue , @Discount , @DaysTillDiscountExpires , @DayOfMonthNetDue , @DueNextMonthIfWithinDays , @Discount1 , @DayDiscountExpires , @Inactive ) END RETURN ' END |
[dbo].[sp_insert_Vendor] |
Owner | dbo |
Parameters | @VendorName varchar (50) , @Company varchar (50) , @MrMs varchar (10) , @FirstName varchar (20) , @MiddleInitial varchar (10) , @LastName varchar (20) , @Contact varchar (30) , @Address1 varchar (50) , @Address2 varchar (50) , @City varchar (50) , @State varchar (50) , @Zip varchar (10) , @Country varchar (50) , @Phone varchar (25) , @Fax varchar (25) , @AltPhone varchar (25) , @AltContact varchar (30) , @PrintOnCheckAs varchar (30) , @AccountNumber varchar (50) , @DefaultExpenseAccount varchar (50) , @VendorType varchar (50) , @Term varchar (50) , @CreditLimit money , @TaxID varchar (20) , @Eligible bit , @Email varchar (30) , @OpeningBalance money , @OpeningBalanceDate datetime , @Inactive bit |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_insert_Vendor] @VendorName varchar (50 ) , @Company varchar (50 ) , @MrMs varchar (10 ) , @FirstName varchar (20 ) , @MiddleInitial varchar (10 ) , @LastName varchar (20 ) , @Contact varchar (30 ) , @Address1 varchar (50 ) , @Address2 varchar (50 ) , @City varchar (50 ) , @State varchar (50 ) , @Zip varchar (10 ) , @Country varchar (50 ) , @Phone varchar (25 ) , @Fax varchar (25 ) , @AltPhone varchar (25 ) , @AltContact varchar (30 ) , @PrintOnCheckAs varchar (30 ) , @AccountNumber varchar (50 ) , @DefaultExpenseAccount varchar (50 ) , @VendorType varchar (50 ) , @Term varchar (50 ) , @CreditLimit money , @TaxID varchar (20 ) , @Eligible bit , @Email varchar (30 ) , @OpeningBalance money , @OpeningBalanceDate datetime , @Inactive bit AS BEGIN INSERT INTO lsVendors (VendorName , Company , MrMs , FirstName , MiddleInitial , LastName , Contact , Address1 , Address2 , City , State , Zip , Country , Phone , Fax , AltPhone , AltContact , PrintOnCheckAs , AccountNumber , DefaultExpenseAccount , VendorType , Term , CreditLimit , TaxID , Eligible , Email , OpeningBalance , OpeningBalanceDate , Inactive ) VALUES (@VendorName , @Company , @MrMs , @FirstName , @MiddleInitial , @LastName , @Contact , @Address1 , @Address2 , @City , @State , @Zip , @Country , @Phone , @Fax , @AltPhone , @AltContact , @PrintOnCheckAs , @AccountNumber , @DefaultExpenseAccount , @VendorType , @Term , @CreditLimit , @TaxID , @Eligible , @Email , @OpeningBalance , @OpeningBalanceDate , @Inactive ) END RETURN ' END |
[dbo].[sp_select_AccountDetail] |
Owner | dbo |
Parameters | @AccountName nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_AccountDetail] @AccountName nvarchar (50 ) AS BEGIN SELECT lsAccounts .AccountNumber , lsAccounts .AccountName , lsAccounts .AccountType , lsAccounts .Subaccount , lsAccounts .AccountName1 , lsAccounts .Description , lsAccounts .OpeningBalance , lsAccounts .Date , lsAccounts .Inactive , lsAccounts .Balance , lsAccounts .BalanceType , lsAccounts .IsDefault FROM lsAccounts WHERE lsAccounts .AccountName = @AccountName END RETURN ' END |
[dbo].[sp_select_AccountTypeDetail] |
Owner | dbo |
Parameters | @Description varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_AccountTypeDetail] @Description varchar (50 ) AS BEGIN SELECT lsAccountTypes .Description FROM lsAccountTypes WHERE lsAccountTypes .Description = @Description END RETURN ' END |
[dbo].[sp_select_BillDetails] |
Owner | dbo |
Parameters | @Reference varchar (20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_BillDetails] @Reference varchar (20 ) AS BEGIN SELECT vpBills .Date , vpBills .Vendor , vpBills .Reference , vpBills .Type , vpBills .Term , vpBills .DueDate , vpBills .VendorInvoice , vpBills .Freight , vpBills .Discount , vpBills .OtherCharges , vpBills .Amount , vpBills .Memo , vpBills .PORef , vpBills .PostedYN FROM vpBills WHERE vpBills .Reference = @Reference END RETURN ' END |
[dbo].[sp_select_BillExpenses] |
Owner | dbo |
Parameters | @Reference varchar (20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_BillExpenses] @Reference varchar (20 ) AS BEGIN SELECT vpBillExpenses .No , vpBillExpenses .BillID , vpBillExpenses .Account , vpBillExpenses .Amount , vpBillExpenses .Memo , vpBillExpenses .Project FROM vpBillExpenses WHERE vpBillExpenses .Reference = @Reference END RETURN ' END |
[dbo].[sp_select_CheckDetail] |
Owner | dbo |
Parameters | @CheckID int |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CheckDetail] @CheckID int AS BEGIN SELECT bChecks .Account , bChecks .Payee , bChecks .Date , bChecks .Address , bChecks .CheckNo , bChecks .CheckID , bChecks .Amount , bChecks .Memo , bChecks .VoidedYN FROM bChecks WHERE bChecks .CheckId = @CheckId END RETURN ' END |
[dbo].[sp_select_CheckItems] |
Owner | dbo |
Parameters | @CheckID int |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CheckItems] @CheckID int AS BEGIN SELECT bCheckDetails .InvRef , bCheckDetails .InvDate , bCheckDetails .InvOrigAmount , bCheckDetails .Amount FROM bCheckDetails WHERE bCheckDetails .CheckID = @CheckID END RETURN ' END |
[dbo].[sp_select_Checks] |
Owner | dbo |
Parameters | @Voided char (1) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Checks] @Voided char (1 ) AS IF @Voided = ''N '' BEGIN SELECT bChecks .CheckID , bChecks .Date , bChecks .CheckNo , bChecks .CheckID , bChecks .Amount , bChecks .Memo FROM bChecks ORDER BY bChecks .Date END IF @Voided = ''Y '' BEGIN SELECT bChecks .CheckID , bChecks .Date , bChecks .CheckNo , bChecks .Amount , bChecks .Memo FROM bChecks WHERE bChecks .VoidedYN = 1 ORDER BY bChecks .Date END RETURN ' END |
[dbo].[sp_select_ClassDetail] |
Owner | dbo |
Parameters | @ClassName varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_ClassDetail] @ClassName varchar (50 ) AS BEGIN SELECT lsClasses .Subclass , lsClasses .ClassName1 , lsClasses .ClassName , lsClasses .Inactive FROM lsClasses WHERE lsClasses .ClassName = @ClassName END RETURN ' END |
[dbo].[sp_select_CustomerCreditDetail] |
Owner | dbo |
Parameters | @CreditNumber nvarchar (20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerCreditDetail] @CreditNumber nvarchar (20 ) AS BEGIN SELECT csCustomerCredits .Customer , csCustomerCredits .Class , csCustomerCredits .Memo , csCustomerCredits .BillTo , csCustomerCredits .Date , csCustomerCredits .POID , csCustomerCredits .CreditNumber , csCustomerCredits .CustomerMessage , csCustomerCredits .Discount , csCustomerCredits .Handling , csCustomerCredits .Freight , csCustomerCredits .SalesTax , csCustomerCredits .Tax , csCustomerCredits .TaxTotal , csCustomerCredits .Total , csCustomerCredits .PostedYN FROM csCustomerCredits WHERE csCustomerCredits .CreditNumber = @CreditNumber END RETURN ' END |
[dbo].[sp_select_CustomerCreditItems] |
Owner | dbo |
Parameters | @CreditNumber nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerCreditItems] @CreditNumber nvarchar (50 ) AS BEGIN SELECT csCustomerCreditItems .CustomerCreditNo , csCustomerCreditItems .No , csCustomerCreditItems .Item , csCustomerCreditItems .Description , csCustomerCreditItems .Quantity , csCustomerCreditItems .Rate , csCustomerCreditItems .Amount , csCustomerCreditItems .Tax FROM csCustomerCreditItems WHERE csCustomerCreditItems .CustomerCreditNo = @CreditNumber ORDER BY csCustomerCreditItems .No END RETURN ' END |
[dbo].[sp_select_CustomerDetail] |
Owner | dbo |
Parameters | @CustomerName nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerDetail] @CustomerName nvarchar (50 ) AS BEGIN SELECT lsCustomers .Login , lsCustomers .Password , lsCustomers .CustomerName , lsCustomers .Company , lsCustomers .MrMs , lsCustomers .FirstName , lsCustomers .MiddleInitial , lsCustomers .LastName , lsCustomers .BillingAddress1 , lsCustomers .BillingAddress2 , lsCustomers .City , lsCustomers .State , lsCustomers .Zip , lsCustomers .Country , lsCustomers .Contact , lsCustomers .Phone , lsCustomers .Email , lsCustomers .Fax , lsCustomers .AltPhone , lsCustomers .AltContact , lsCustomers .Account , lsCustomers .CustomerType , lsCustomers .Term , lsCustomers .Rep , lsCustomers .CreditLimit , lsCustomers .Taxable , lsCustomers .TaxItem , lsCustomers .ResaleNumber , lsCustomers .OpeningBalance , lsCustomers .OpeningBalanceDate , lsCustomers .Inactive , lsCustomers .PasswordCA , lsCustomers .Balance , lsCustomers .ExistingCredit FROM lsCustomers WHERE lsCustomers .CustomerName = @CustomerName END RETURN ' END |
[dbo].[sp_select_CustomerMessageDetail] |
Owner | dbo |
Parameters | @CustomerMessage varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerMessageDetail] @CustomerMessage varchar (50 ) AS BEGIN SELECT lsCustomerMessages .CustomerMessage , lsCustomerMessages .Inactive FROM lsCustomerMessages WHERE lsCustomerMessages .CustomerMessage = @CustomerMessage END RETURN ' END |
[dbo].[sp_select_CustomerPaymentDetail] |
Owner | dbo |
Parameters | @Reference nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerPaymentDetail] @Reference nvarchar (50 ) AS BEGIN SELECT Reference , Customer , PaymentAmount , Date , PaymentMethod , Memo , CheckNo , CreditCardNo , CardExpires , NameOnCard , CardZipCode , CardApproved , UndepFunds , DepositTo , Account , ApplyExistingCredits , Apply , TotalToApply , TotalApplied , UnappliedAmount , Deposit , PayAmount , ReceivedYN , ClearedYN , PostedYN FROM csCustomerPayments WHERE Reference = @Reference END RETURN ' END |
[dbo].[sp_select_CustomerPaymentInvoices] |
Owner | dbo |
Parameters | @CustomerName nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerPaymentInvoices] @CustomerName nvarchar (50 ) AS BEGIN SELECT csInvoices .InvoiceNo , csInvoices .Date , ''Invoice '' AS Type , STR (csInvoices .Total - csInvoices .PayAmount ,10 ,2 ) AS Amount , STR (csInvoices .PayAmount ,10 ,2 ) AS PayAmount FROM csInvoices WHERE csInvoices .PayAmount < csInvoices .Total AND csInvoices .PostedYN = 1 AND csInvoices .Customer = @CustomerName ORDER BY csInvoices .Date , csInvoices .InvoiceNo END RETURN ' END |
[dbo].[sp_select_CustomerPaymentItems] |
Owner | dbo |
Parameters | @Reference nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerPaymentItems] @Reference nvarchar (50 ) AS BEGIN SELECT csInvoices .InvoiceNo , csInvoices .Date , ''Invoice '' AS Type , STR (csInvoices .Total ,10 ,2 ) AS TotalAmount , STR (csCustomerPaymentDetails .textboxAmount ,10 ,2 ) AS Amount FROM csInvoices , csCustomerPaymentDetails WHERE csInvoices .PostedYN = 1 AND csCustomerPaymentDetails .Reference = @Reference AND csInvoices .InvoiceNo = csCustomerPaymentDetails .FID END RETURN ' END |
[dbo].[sp_select_CustomerShipToDetail] |
Owner | dbo |
Parameters | @CustomerName nvarchar (50), @ShipingAddressID int |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerShipToDetail] @CustomerName nvarchar (50 ), @ShipingAddressID int AS BEGIN SELECT lsCustomersShipTo .CustomerName , lsCustomersShipTo .ShipingAddressID , lsCustomersShipTo .ShippingAddress , lsCustomersShipTo .City , lsCustomersShipTo .State , lsCustomersShipTo .Zip , lsCustomersShipTo .Country FROM lsCustomersShipTo WHERE lsCustomersShipTo .CustomerName = @CustomerName AND lsCustomersShipTo .ShipingAddressID = @ShipingAddressID END RETURN ' END |
[dbo].[sp_select_CustomerShipToForCustomer] |
Owner | dbo |
Parameters | @CustomerName nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerShipToForCustomer] @CustomerName nvarchar (50 ) AS BEGIN SELECT lsCustomersShipTo .CustomerName , lsCustomersShipTo .ShipingAddressID , lsCustomersShipTo .ShippingAddress , lsCustomersShipTo .City , lsCustomersShipTo .State , lsCustomersShipTo .Zip , lsCustomersShipTo .Country FROM lsCustomersShipTo WHERE lsCustomersShipTo .CustomerName = @CustomerName END RETURN ' END |
[dbo].[sp_select_CustomerShipToForDropDown] |
Owner | dbo |
Parameters | @CustomerName varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerShipToForDropDown] @CustomerName varchar (50 ) AS BEGIN SELECT lsCustomersShipTo .ShipingAddressID , lsCustomersShipTo .ShippingAddress FROM lsCustomersShipTo WHERE lsCustomersShipTo .CustomerName = @CustomerName ORDER BY lsCustomersShipTo .ShipingAddressID END RETURN ' END |
[dbo].[sp_select_CustomerTypeDetail] |
Owner | dbo |
Parameters | @CustomerType nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerTypeDetail] @CustomerType nvarchar (50 ) AS BEGIN SELECT CustomerType , Inactive FROM lsCustomerTypes WHERE CustomerType = @CustomerType END RETURN ' END |
[dbo].[sp_select_EmployeeCommission] |
Owner | dbo |
Parameters | @EmpName nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_EmployeeCommission] @EmpName nvarchar (50 ) AS BEGIN SELECT Commissions .ID , Commissions .Date , Commissions .Amount FROM Commissions WHERE Commissions .PaidYN = 0 AND Commissions .EmpName = @EmpName END RETURN ' END |
[dbo].[sp_select_GeneralJournalDetail] |
Owner | dbo |
Parameters | @EntryNo nvarchar (20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_GeneralJournalDetail] @EntryNo nvarchar (20 ) AS BEGIN SELECT GeneralJournalDetail .No , GeneralJournalDetail .Account , GeneralJournalDetail .Memo , GeneralJournalDetail .Debit , GeneralJournalDetail .Credit FROM GeneralJournalDetail WHERE GeneralJournalDetail .EntryNo = @EntryNo END RETURN ' END |
[dbo].[sp_select_GeneralJournalEntryDetail] |
Owner | dbo |
Parameters | @EntryNo nvarchar (20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_GeneralJournalEntryDetail] @EntryNo nvarchar (20 ) AS BEGIN SELECT GeneralJournalEntry .Date , GeneralJournalEntry .EntryNo , GeneralJournalEntry .PostedYN FROM GeneralJournalEntry WHERE GeneralJournalEntry .EntryNo = @EntryNo END RETURN ' END |
[dbo].[sp_select_InventoryAdjustmentDetail] |
Owner | dbo |
Parameters | @RefNo nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_InventoryAdjustmentDetail] @RefNo nvarchar (50 ) AS BEGIN SELECT InventoryAdjustments .Date , InventoryAdjustments .AdjustmentAccount , InventoryAdjustments .Memo , InventoryAdjustments .Customer , InventoryAdjustments .RefNo , InventoryAdjustments .TotalValue , InventoryAdjustments .PostedYN FROM InventoryAdjustments WHERE InventoryAdjustments .RefNo = @RefNo END RETURN ' END |
[dbo].[sp_select_InventoryAdjustmentItem] |
Owner | dbo |
Parameters | @RefNo nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_InventoryAdjustmentItem] @RefNo nvarchar (50 ) AS BEGIN SELECT Item , Description , CurrentQty , CurrentValue , NewQty , NewValue , QtyDifference , ValueDifference FROM InventoryAdjustmentDetail WHERE RefNo = @RefNo END RETURN ' END |
[dbo].[sp_select_InvoiceDetail] |
Owner | dbo |
Parameters | @InvoiceNo nvarchar (20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_InvoiceDetail] @InvoiceNo nvarchar (20 ) AS BEGIN SELECT csInvoices .Date , csInvoices .Customer , csInvoices .ShipToID , csInvoices .Class , csInvoices .Memo , csInvoices .Term , csInvoices .InvoiceNo , csInvoices .DueDate , csInvoices .ShipVia , csInvoices .ShipDate , csInvoices .POID , csInvoices .CustomerMessage , csInvoices .TrackingNumber , csInvoices .Handling , csInvoices .Freight , csInvoices .Discount , csInvoices .SalesTax , csInvoices .Tax , csInvoices .TaxTotal , csInvoices .Total , csInvoices .TotalWeight , csInvoices .ShippingCharge , csInvoices .ApplyYN , csInvoices .PaidYN , csInvoices .PayAmount , csInvoices .PostedYN FROM csInvoices WHERE csInvoices .InvoiceNo = @InvoiceNo END RETURN ' END |
[dbo].[sp_select_InvoiceItems] |
Owner | dbo |
Parameters | @InvoiceNo nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_InvoiceItems] @InvoiceNo nvarchar (50 ) AS BEGIN SELECT InvoiceNo , No , Item , Description , Quantity , Rate , Amount , ItemWeight , Shipped , ShippedDate , TrackingNumber FROM csInvoiceItems WHERE InvoiceNo = @InvoiceNo END RETURN ' END |
[dbo].[sp_select_ItemCategoryDetail] |
Owner | dbo |
Parameters | @CategoryID nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_ItemCategoryDetail] @CategoryID nvarchar (50 ) AS BEGIN SELECT lsItemCategories .CategoryID , lsItemCategories .Description , lsItemCategories .PictureURL , lsItemCategories .familyID FROM lsItemCategories WHERE lsItemCategories .CategoryID = @CategoryID END RETURN ' END |
[dbo].[sp_select_ItemDetail] |
Owner | dbo |
Parameters | @ItemName nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_ItemDetail] @ItemName nvarchar (50 ) AS BEGIN SELECT lsItems .ItemName , lsItems .Subitem , lsItems .PurchaseDescription , lsItems .Cost , lsItems .COGSAccount , lsItems .PrefferedVendor , lsItems .IncomeAccount , lsItems .SalesPrice , lsItems .CategoryID , lsItems .ItemWeight , lsItems .SalesDescription , lsItems .AssetAccount , lsItems .QuantityOnHand , lsItems .TotalValue , lsItems .ReorderPoint , lsItems .PictureURL , lsItems .ItemName1 , lsItems .ItemType , lsItems .Taxable , lsItems .Inactive FROM lsItems WHERE lsItems .ItemName = @ItemName END RETURN ' END |
[dbo].[sp_select_ItemFamilyDetail] |
Owner | dbo |
Parameters | @FamilyID nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_ItemFamilyDetail] @FamilyID nvarchar (50 ) AS BEGIN SELECT lsItemFamilies .FamilyID , lsItemFamilies .Description , lsItemFamilies .PictureURL FROM lsItemFamilies WHERE lsItemFamilies .FamilyID = @FamilyID END RETURN ' END |
[dbo].[sp_select_OrderDetail] |
Owner | dbo |
Parameters | @OrderNo nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_OrderDetail] @OrderNo nvarchar (50 ) AS BEGIN SELECT csOrders .Date , csOrders .Customer , csOrders .ShipToID , csOrders .Class , csOrders .Memo , csOrders .Term , csOrders .OrderNo , csOrders .DueDate , csOrders .ShipVia , csOrders .ShipDate , csOrders .POID , csOrders .CustomerMessage , csOrders .Discount , csOrders .Freight , csOrders .SalesTax , csOrders .Tax , csOrders .TaxTotal , csOrders .Total , csOrders .TotalWeight , csOrders .ShippingCharge , csOrders .Handling , csOrders .ApplyYN , csOrders .Picked , csOrders .Billed , csOrders .Printed , csOrders .Shipped , csOrders .Backordered , csOrders .Invoiced , csOrders .TrackingNumber , csOrders .CCType , csOrders .CCName , csOrders .CCNumber , csOrders .CCExpDate , csOrders .ApprovalNumber , csOrders .PostedYN FROM csOrders WHERE csOrders .OrderNo = @OrderNo END RETURN ' END |
[dbo].[sp_select_OrderItems] |
Owner | dbo |
Parameters | @OrderNo nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_OrderItems] @OrderNo nvarchar (50 ) AS BEGIN SELECT csOrderItems .OrderNo , csOrderItems .No , csOrderItems .Item , csOrderItems .Description , csOrderItems .Quantity , csOrderItems .Ship , csOrderItems .Rate , csOrderItems .Amount , csOrderItems .ItemWeight , csOrderItems .ShipDate FROM csOrderItems WHERE csOrderItems .OrderNo = @OrderNo END RETURN ' END |
[dbo].[sp_select_PaymentMethodDetail] |
Owner | dbo |
Parameters | @PaymentMethod nvarchar (20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_PaymentMethodDetail] @PaymentMethod nvarchar (20 ) AS BEGIN SELECT lsPaymentMethods .PaymentMethod , lsPaymentMethods .ExternalAccess , lsPaymentMethods .Inactive FROM lsPaymentMethods WHERE lsPaymentMethods .PaymentMethod = @PaymentMethod END RETURN ' END |
[dbo].[sp_select_ProjectDetail] |
Owner | dbo |
Parameters | @ProjectName nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_ProjectDetail] @ProjectName nvarchar (50 ) AS BEGIN SELECT Customer , ProjectName , Description , ProjectManager , StartDate , EndDate , Inactive FROM lsProjects WHERE ProjectName = @ProjectName END RETURN ' END |
[dbo].[sp_select_PurchaseOrderDetail] |
Owner | dbo |
Parameters | @PORef nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_PurchaseOrderDetail] @PORef nvarchar (50 ) AS BEGIN SELECT vpPurchaseOrders .Vendor , vpPurchaseOrders .Class , vpPurchaseOrders .Memo , vpPurchaseOrders .BillTo , vpPurchaseOrders .Date , vpPurchaseOrders .DueDate , vpPurchaseOrders .PORef , vpPurchaseOrders .ShipTo , vpPurchaseOrders .ShipToD , vpPurchaseOrders .ReceiveBy , vpPurchaseOrders .ShipVia , vpPurchaseOrders .Term , vpPurchaseOrders .VendorInvoice , vpPurchaseOrders .VendorMessage , vpPurchaseOrders .TrackingNumber , vpPurchaseOrders .Discount , vpPurchaseOrders .Freight , vpPurchaseOrders .OtherCharges , vpPurchaseOrders .ShippingCharge , vpPurchaseOrders .Total , vpPurchaseOrders .ReceivedYN FROM vpPurchaseOrders WHERE vpPurchaseOrders .PORef = @PORef END RETURN ' END |
[dbo].[sp_select_PurchaseOrderItems] |
Owner | dbo |
Parameters | @PORef nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_PurchaseOrderItems] @PORef nvarchar (50 ) AS BEGIN SELECT vpPurchaseOrderDetails .Id , vpPurchaseOrderDetails .No , vpPurchaseOrderDetails .POref , vpPurchaseOrderDetails .Item , vpPurchaseOrderDetails .Description , vpPurchaseOrderDetails .Project , vpPurchaseOrderDetails .Quantity , vpPurchaseOrderDetails .Rate , vpPurchaseOrderDetails .Amount FROM vpPurchaseOrderDetails WHERE vpPurchaseOrderDetails .PORef = @PORef END RETURN ' END |
[dbo].[sp_select_ShippingMethodDetail] |
Owner | dbo |
Parameters | @ShippingMethod nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_ShippingMethodDetail] @ShippingMethod nvarchar (50 ) AS BEGIN SELECT lsShippingMethods .ShippingMethod , lsShippingMethods .AccountNo , lsShippingMethods .Inactive FROM lsShippingMethods WHERE lsShippingMethods .ShippingMethod = @ShippingMethod END RETURN ' END |
[dbo].[sp_select_TaxDetail] |
Owner | dbo |
Parameters | @TaxID nvarchar (10) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_TaxDetail] @TaxID nvarchar (10 ) AS BEGIN SELECT lsTaxes .TaxID , lsTaxes .Description , lsTaxes .TaxPercent , lsTaxes .Account , lsTaxes .Inactive FROM lsTaxes WHERE lsTaxes .TaxID = @TaxID END RETURN ' END |
[dbo].[sp_select_TaxGroupDetail] |
Owner | dbo |
Parameters | @TaxGroupID varchar (10) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_TaxGroupDetail] @TaxGroupID varchar (10 ) AS BEGIN SELECT lsTaxGroups .TaxGroupID , lsTaxGroups .TaxGroupName , lsTaxGroups .TotalPercent , lsTaxGroups .Inactive , lsTaxGroups .IsDefault FROM lsTaxGroups WHERE lsTaxGroups .TaxGroupID = @TaxGroupID END RETURN ' END |
[dbo].[sp_select_TermDetail] |
Owner | dbo |
Parameters | @Term varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_TermDetail] @Term varchar (50 ) AS BEGIN SELECT Term , Standard , DayTillNetDue , Discount , DaysTillDiscountExpires , DayOfMonthNetDue , DueNextMonthIfWithinDays , Discount1 , DayDiscountExpires , Inactive FROM lsTerms WHERE Term = @Term END RETURN ' END |
[dbo].[sp_select_TimeTrackingDetail] |
Owner | dbo |
Parameters | @Reference varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_TimeTrackingDetail] @Reference varchar (50 ) AS BEGIN SELECT TimeTracking .Customer , TimeTracking .ServiceItem , TimeTracking .Employee , TimeTracking .Class , TimeTracking .Date , TimeTracking .Duration , TimeTracking .Reference , TimeTracking .Memo FROM TimeTracking WHERE TimeTracking .Reference = @Reference END RETURN ' END |
[dbo].[sp_select_TransferFundDetail] |
Owner | dbo |
Parameters | @Reference nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_TransferFundDetail] @Reference nvarchar (50 ) AS BEGIN SELECT bTransferFunds .FromAccount , bTransferFunds .ToAccount , bTransferFunds .Reference , bTransferFunds .Date , bTransferFunds .Memo , bTransferFunds .Amount , bTransferFunds .PostedYN FROM bTransferFunds WHERE bTransferFunds .Reference = @Reference END RETURN ' END |
[dbo].[sp_select_UserDetail] |
Owner | dbo |
Parameters | @Username varchar (20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_UserDetail] @Username varchar (20 ) AS BEGIN SELECT Users .LoginID , Users .Username , Users .Password , Users .GroupLevel FROM Users WHERE Users .Username = @Username END RETURN ' END |
[dbo].[sp_select_VendorDetail] |
Owner | dbo |
Parameters | @VendorName nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_VendorDetail] @VendorName nvarchar (50 ) AS BEGIN SELECT lsVendors .VendorName , lsVendors .Company , lsVendors .MrMs , lsVendors .FirstName , lsVendors .MiddleInitial , lsVendors .LastName , lsVendors .Contact , lsVendors .Address1 , lsVendors .Address2 , lsVendors .City , lsVendors .State , lsVendors .Zip , lsVendors .Country , lsVendors .Phone , lsVendors .Fax , lsVendors .AltPhone , lsVendors .AltContact , lsVendors .PrintOnCheckAs , lsVendors .AccountNumber , lsVendors .DefaultExpenseAccount , lsVendors .VendorType , lsVendors .Term , lsVendors .PMID , lsVendors .CreditLimit , lsVendors .TaxID , lsVendors .Eligible , lsVendors .Email , lsVendors .OpeningBalance , lsVendors .OpeningBalanceDate , lsVendors .Balance , lsVendors .Inactive FROM lsVendors WHERE lsVendors .VendorName = @VendorName END RETURN ' END |
[dbo].[sp_select_VendorPaymentBills] |
Owner | dbo |
Parameters | @Vendor nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_VendorPaymentBills] @Vendor nvarchar (50 ) AS BEGIN SELECT vpBills .Reference , vpBills .DueDate , vpBills .Type , vpBills .VendorInvoice , vpBills .DueAmount FROM vpBills WHERE vpBills .DueAmount > 0 .01 AND vpBills .PostedYN = 1 AND vpBills .Vendor = @Vendor END RETURN ' END |
[dbo].[sp_select_VendorTypeDetail] |
Owner | dbo |
Parameters | @VendorType nvarchar (30) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_VendorTypeDetail] @VendorType nvarchar (30 ) AS BEGIN SELECT lsVendorTypes .VendorType , lsVendorTypes .Inactive FROM lsVendorTypes WHERE lsVendorTypes .VendorType = @VendorType END RETURN ' END |
[dbo].[sp_update_Accounts] |
Owner | dbo |
Parameters | @AccountNumber varchar (10), @AccountName varchar (50), @AccountType varchar (50), @AccountName1 varchar (50), @Subaccount bit, @Description varchar (50), @OpeningBalance money, @Date datetime, @Inactive bit, @BalanceType smallint |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_Accounts] @AccountNumber varchar (10 ), @AccountName varchar (50 ), @AccountType varchar (50 ), @AccountName1 varchar (50 ), @Subaccount bit , @Description varchar (50 ), @OpeningBalance money , @Date datetime , @Inactive bit , @BalanceType smallint AS BEGIN UPDATE lsAccounts SET lsAccounts .AccountNumber = @AccountNumber , lsAccounts .AccountType = @AccountType , lsAccounts .Subaccount = @Subaccount , lsAccounts .AccountName1 = @AccountName1 , lsAccounts .Description = @Description , lsAccounts .OpeningBalance = @OpeningBalance , lsAccounts .Date = @Date , lsAccounts .Inactive = @Inactive , lsAccounts .BalanceType = @BalanceType WHERE lsAccounts .AccountName = @AccountName END RETURN ' END |
[dbo].[sp_update_AutoShipOrders] |
Owner | dbo |
Parameters | @OrderNo varchar (50) |
Definition | |
Note | |
Called SPs | NewInvoiceShippedOrder |
DDL Code | CREATE PROC [dbo].[sp_update_AutoShipOrders] @OrderNo varchar (50 ) AS BEGIN UPDATE csOrders SET csOrders .Billed = 1 , csOrders .Picked = 1 , csOrders .Printed = 1 , csOrders .Shipped = 1 , csOrders .ShipDate = CONVERT (DATETIME , @@DBTS ) WHERE csOrders .OrderNo = @OrderNo END BEGIN EXEC NewInvoiceShippedOrder @OrderNo END RETURN ' END |
[dbo].[sp_update_BillOrders] |
Owner | dbo |
Parameters | @OrderNo varchar (50), @ApprovalNumber varchar (12) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_BillOrders] @OrderNo varchar (50 ), @ApprovalNumber varchar (12 ) AS BEGIN UPDATE csOrders SET csOrders .Billed = 1 , csOrders .ApprovalNumber = @ApprovalNumber WHERE csOrders .OrderNo = @OrderNo END RETURN ' END |
[dbo].[sp_update_Bills] |
Owner | dbo |
Parameters | @Date datetime, @Vendor varchar (50), @Reference varchar (20), @Term varchar (50), @DueDate datetime, @VendorInvoice nvarchar (20), @Freight money, @Discount money, @OtherCharges money, @Amount money, @Memo nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_Bills] @Date datetime , @Vendor varchar (50 ), @Reference varchar (20 ), @Term varchar (50 ), @DueDate datetime , @VendorInvoice nvarchar (20 ), @Freight money , @Discount money , @OtherCharges money , @Amount money , @Memo nvarchar (50 ) AS BEGIN UPDATE vpBills SET vpBills .Vendor = @Vendor , vpBills .Date = @Date , vpBills .DueDate = @DueDate , vpBills .Term = @Term , vpBills .Memo = @Memo , vpBills .VendorInvoice = @VendorInvoice , vpBills .Discount = @Discount , vpBills .Freight = @Freight , vpBills .OtherCharges = @OtherCharges , vpBills .Amount = @Amount WHERE vpBills .Reference = @Reference END RETURN ' END |
[dbo].[sp_update_Company] |
Owner | dbo |
Parameters | @CompanyName varchar (50) , @LegalName varchar (50) , @Address1 varchar (50) , @Address2 varchar (50) , @City varchar (50) , @State varchar (50) , @Zip varchar (10) , @Country varchar (50) , @Phone varchar (25) , @Fax varchar (25) , @email varchar (50) , @website varchar (50) , @FirstFiscalMonth varchar (50) , @PictureURL char (10) , @ConfirmDeletion bit , @UseAN bit , @DefaultCT int , @DefaultIT int , @DefaultSM varchar (50) , @AgingReportUse bit , @TaxFreight bit , @DefaultSalesTax varchar (50) , @ChargeHandling bit , @HandlingAsPercent bit , @HandlingAmount float , @CurrencySymbol varchar (10) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_Company] @CompanyName varchar (50 ) , @LegalName varchar (50 ) , @Address1 varchar (50 ) , @Address2 varchar (50 ) , @City varchar (50 ) , @State varchar (50 ) , @Zip varchar (10 ) , @Country varchar (50 ) , @Phone varchar (25 ) , @Fax varchar (25 ) , @email varchar (50 ) , @website varchar (50 ) , @FirstFiscalMonth varchar (50 ) , @PictureURL char (10 ) , @ConfirmDeletion bit , @UseAN bit , @DefaultCT int , @DefaultIT int , @DefaultSM varchar (50 ) , @AgingReportUse bit , @TaxFreight bit , @DefaultSalesTax varchar (50 ) , @ChargeHandling bit , @HandlingAsPercent bit , @HandlingAmount float , @CurrencySymbol varchar (10 ) AS BEGIN UPDATE Company SET CompanyName = @CompanyName , LegalName = @LegalName , Address1 = @Address1 , Address2 = @Address2 , City = @City , State = @State , Zip = @Zip , Country = @Country , Phone = @Phone , Fax = @Fax , email = @email , website = @website , FirstFiscalMonth = @FirstFiscalMonth , PictureURL = @PictureURL , ConfirmDeletion = @ConfirmDeletion , UseAN = @UseAN , DefaultCT = @DefaultCT , DefaultSM = @DefaultSM , DefaultIT = @DefaultIT , AgingReportUse = @AgingReportUse , TaxFreight = @TaxFreight , DefaultSalesTax = @DefaultSalesTax , ChargeHandling = @ChargeHandling , HandlingAsPercent = @HandlingAsPercent , HandlingAmount = @HandlingAmount , CurrencySymbol = @CurrencySymbol END RETURN ' END |
[dbo].[sp_update_CustomerPayments] |
Owner | dbo |
Parameters | @Reference varchar (50) , @Amount money |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_CustomerPayments] @Reference varchar (50 ) , @Amount money AS BEGIN UPDATE csCustomerPayments SET csCustomerPayments .PaymentAmount = PaymentAmount + @Amount , csCustomerPayments .TotalApplied = TotalApplied + @Amount , csCustomerPayments .PostedYN = 1 WHERE csCustomerPayments .Reference = @Reference END RETURN ' END |
[dbo].[sp_update_NewDocNumber] |
Owner | dbo |
Parameters | @Type varchar (50), @NewNo varchar(20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_NewDocNumber] @Type varchar (50 ), @NewNo varchar (20 ) AS BEGIN BEGIN TRANSACTION IF @Type= ''INV '' BEGIN UPDATE DocNumbers SET DocNumbers .Invoices = @NewNo END ELSE IF @Type= ''ORD '' BEGIN UPDATE DocNumbers SET DocNumbers .Orders = @NewNo END ELSE IF @Type= ''BP '' BEGIN UPDATE DocNumbers SET DocNumbers .BillPmt = @NewNo END ELSE IF @Type= ''CP '' BEGIN UPDATE DocNumbers SET DocNumbers .CustPmt = @NewNo END ELSE IF @Type= ''CC '' BEGIN UPDATE DocNumbers SET DocNumbers .CustCredits = @NewNo END ELSE IF @Type= ''CHECK '' BEGIN UPDATE DocNumbers SET DocNumbers .CheckNo = @NewNo END ELSE IF @Type= ''TF '' BEGIN UPDATE DocNumbers SET DocNumbers .Transfers = @NewNo END ELSE IF @Type= ''BILL '' BEGIN UPDATE DocNumbers SET DocNumbers .Vouchers = @NewNo END ELSE IF @Type= ''INVADJ '' BEGIN UPDATE DocNumbers SET DocNumbers .Adjustment = @NewNo END ELSE IF @Type= ''TT '' BEGIN UPDATE DocNumbers SET DocNumbers .TimeTracking = @NewNo END COMMIT TRANSACTION END RETURN ' END |
[dbo].[sp_update_PickOrders] |
Owner | dbo |
Parameters | @OrderNo varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_PickOrders] @OrderNo varchar (50 ) AS BEGIN UPDATE csOrders SET csOrders .Picked = 1 , csOrders .Printed = 1 WHERE csOrders .OrderNo = @OrderNo END RETURN ' END |
[dbo].[sp_update_PurchaseOrder] |
Owner | dbo |
Parameters | @PORef nvarchar (50), @DueDate datetime, @Term varchar (50), @VendorInvoice varchar (20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_PurchaseOrder] @PORef nvarchar (50 ), @DueDate datetime , @Term varchar (50 ), @VendorInvoice varchar (20 ) AS BEGIN UPDATE vpPurchaseOrders SET vpPurchaseOrders .DueDate = @DueDate , vpPurchaseOrders .Term = @Term , vpPurchaseOrders .VendorInvoice = @VendorInvoice WHERE vpPurchaseOrders .PORef = @PORef END RETURN ' END |
[dbo].[sp_update_PurchaseOrderDetail] |
Owner | dbo |
Parameters | @PORef nvarchar (50), @ReceivedQty float , @ID int |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_PurchaseOrderDetail] @PORef nvarchar (50 ), @ReceivedQty float , @ID int AS BEGIN UPDATE vpPurchaseOrderDetails SET vpPurchaseOrderDetails .ReceivedQty = @ReceivedQty WHERE vpPurchaseOrderDetails .PORef = @PORef AND vpPurchaseOrderDetails .ID = @ID END RETURN ' END |
[dbo].[sp_update_ShipOrders] |
Owner | dbo |
Parameters | @OrderNo varchar (50), @TrackingNumber varchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_ShipOrders] @OrderNo varchar (50 ), @TrackingNumber varchar (50 ) AS BEGIN UPDATE csOrders SET csOrders .Shipped = 1 , csOrders .ShipDate = CONVERT (DATETIME , @@DBTS ), csOrders .TrackingNumber = @TrackingNumber WHERE csOrders .OrderNo = @OrderNo END RETURN ' END |
[dbo].[sp_update_Terms] |
Owner | dbo |
Parameters | @OLDTerm varchar (50) , @Term varchar (50) , @Standard bit , @DayTillNetDue smallint , @Discount float , @DaysTillDiscountExpires smallint , @DayOfMonthNetDue smallint , @DueNextMonthIfWithinDays smallint , @Discount1 float , @DayDiscountExpires smallint , @Inactive bit |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_Terms] @OLDTerm varchar (50 ) , @Term varchar (50 ) , @Standard bit , @DayTillNetDue smallint , @Discount float , @DaysTillDiscountExpires smallint , @DayOfMonthNetDue smallint , @DueNextMonthIfWithinDays smallint , @Discount1 float , @DayDiscountExpires smallint , @Inactive bit AS BEGIN UPDATE [dbo].[lsTerms] SET lsTerms .Term = @Term , lsTerms .Standard = @Standard , lsTerms .DayTillNetDue = @DayTillNetDue , lsTerms .Discount = @Discount , lsTerms .DaysTillDiscountExpires = @DaysTillDiscountExpires , lsTerms .DayOfMonthNetDue = @DayOfMonthNetDue , lsTerms .DueNextMonthIfWithinDays = @DueNextMonthIfWithinDays , lsTerms .Discount1 = @Discount1 , lsTerms .DayDiscountExpires = @DayDiscountExpires , lsTerms .Inactive = @Inactive WHERE lsTerms .Term = @OLDTerm END RETURN ' END |
[dbo].[sp_update_TransferFund] |
Owner | dbo |
Parameters | @Reference nvarchar (50), @Date datetime , @Amount money , @Memo nvarchar (50) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_TransferFund] @Reference nvarchar (50 ), @Date datetime , @Amount money , @Memo nvarchar (50 ) AS BEGIN UPDATE bTransferFunds SET Date = @Date , Amount = @Amount , Memo = @Memo WHERE Reference = @Reference END RETURN ' END |
[dbo].[sp_update_Users] |
Owner | dbo |
Parameters | @UserName varchar (20), @Password varchar (20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_update_Users] @UserName varchar (20 ), @Password varchar (20 ) AS BEGIN UPDATE Users SET Password = @Password WHERE Username = @UserName END RETURN ' END |
[dbo].[SplitPO] |
Owner | dbo |
Parameters | @TransID varchar(50) |
Definition | |
Note | |
Called SPs | PostGLWorkDetail PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[SplitPO] @TransID varchar (50 ) AS 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 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 SELECT @a = SUM (Quantity-ReceivedQty ) FROM vpPurchaseOrderDetails WHERE vpPurchaseOrderDetails .PORef = @TransID IF @a=0 BEGIN -- The PO was fully received -- DELETE FROM GLWorkDetail -- Update the Received flag -- UPDATE vpPurchaseOrders SET ReceivedYN=1 WHERE vpPurchaseOrders .PORef = @TransID UPDATE lsVendors SET Balance = Balance + @Total WHERE VendorName = @Vendor INSERT INTO GeneralJournalEntry (Date , EntryNo , TrType , TrID , PostedYN , SystemGenerated ) VALUES (CONVERT (varchar , CURRENT_TIMESTAMP ), @IDD , 2 , @TransID , 1 , 1 ) SELECT @DiscAmount = (SELECT SUM (Amount ) FROM vpPurchaseOrderDetails WHERE PORef = @TransID ) * @Discount / 100 INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Freight Expense '', @Freight , 0 , ''Freight '', 10 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Accounts Payable '', 0 , @Total , ''AP '', 20 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Discounts '', 0 , @DiscAmount , ''Discount '', 30 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Other Expense '', @OtherCharges , 0 , ''Charge '', 40 ) 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 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 UPDATE lsItems SET QuantityOnHand = QuantityOnHand + @ReceivedQty WHERE ItemName = @Item INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , @AssetAccount , @Rate * @Quantity , 0 , ''INV Asset '', @BID ) SELECT @BID = @BID + 1 FETCH NEXT FROM Curs1 INTO @Item , @Quantity , @ReceivedQty , @Rate , @AssetAccount END CLOSE Curs1 DEALLOCATE Curs1 EXEC PostGLWorkDetail @IDD END ELSE BEGIN -- The PO must be split , the items were partially received -- DELETE FROM GLWorkDetail -- Insert a new row representing the new PO -- 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 -- INSERT INTO GeneralJournalEntry (Date , EntryNo , TrType , TrID , PostedYN , SystemGenerated ) VALUES (CONVERT (varchar , CURRENT_TIMESTAMP ), @IDD , 2 , @TransID , 1 , 1 ) 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 -- 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 ) UPDATE vpPurchaseOrderDetails SET Quantity = ReceivedQty , Amount = Rate * ReceivedQty WHERE ID=@ID 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 INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , @AssetAccount , @Rate * @ReceivedQty , 0 , ''INV Asset '', @BID ) SELECT @BID = @BID + 10 END UPDATE lsItems SET QuantityOnHand = QuantityOnHand + @ReceivedQty WHERE ItemName = @Item 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 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 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 INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Freight Expense '', @Freight , 0 , ''Freight '', @BID ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Accounts Payable '', 0 , @SumOld , ''AP '', @BID+10 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Discounts '', 0 , @DSC , ''Discount '', @BID+20 ) INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@IDD , ''Other Expense '', @OtherCharges , 0 , ''Charge '',@BID+30 ) 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 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 ' END |
[dbo].[UpdateBankRec] |
Owner | dbo |
Parameters | @ID varchar(50), @Type varchar(20) |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[UpdateBankRec] @ID varchar (50 ), @Type varchar (20 ) AS IF @Type= ''CustPmt '' UPDATE csCustomerPayments SET ClearedYN = 1 WHERE Reference = @ID ELSE IF @Type= ''VendPmt '' UPDATE vpBillPayments SET ClearedYN = 1 WHERE Reference = @ID ELSE IF @Type= ''TransferTo '' UPDATE bTransferFunds SET ClearedYN = 1 WHERE Reference = @ID ELSE IF @Type= ''TransferFrom '' UPDATE bTransferFunds SET ClearedYN = 1 WHERE Reference = @ID ' END |
[dbo].[voidCheck] |
Owner | dbo |
Parameters | @CheckID int |
Definition | |
Note | |
Called SPs | delBILL |
DDL Code | CREATE PROC [dbo].[voidCheck] @CheckID int AS BEGIN TRANSACTION -- voids a check by posting a "reversed" bill . The posting routine follows the postBill procedure except -- the credits are debits and viceversa DECLARE @CheckNo int SELECT @CheckNo = CheckNo FROM bChecks WHERE CheckID = @CheckID DECLARE @Amount int , @BillID varchar (50 ) DECLARE C CURSOR FOR SELECT Amount , BillID FROM bCheckDetails WHERE CheckID = @CheckID OPEN C FETCH NEXT FROM C INTO @Amount , @BillID WHILE @@FETCH_STATUS=0 BEGIN EXEC delBILL @BillID , @CheckNo , @Amount FETCH NEXT FROM C INTO @Amount , @BillID END CLOSE C DEALLOCATE C UPDATE bChecks SET VoidedYN = 1 WHERE CheckID = @CheckID COMMIT TRANSACTION ' END |
[dbo].[writeCheck] |
Owner | dbo |
Parameters | @AccNoTF int,@Account varchar(50), @Vendor varchar(50), @CheckNo |
Definition | |
Note | |
Called SPs | addBill postBill postBillPayment |
DDL Code | CREATE PROC [dbo].[writeCheck] @AccNoTF int ,@Account varchar (50 ), @Vendor varchar (50 ), @CheckNo AS varchar (20 ), @Date AS datetime , @Amount float , @Ref varchar (20 ), @Debit varchar (50 ), @NewCheck int AS BEGIN TRANSACTION -- writeCheck = addBill -> postBill -> postBillPayment DECLARE @NewNo varchar (50 ) SELECT @NewNo = CONVERT (varchar , CASE WHEN ((SELECT MAX (Vouchers ) + 1 FROM DocNumbers ) IS NULL ) THEN 1000 ELSE (SELECT MAX (Vouchers ) + 1 FROM DocNumbers ) END ) UPDATE DocNumbers SET Vouchers = @NewNo INSERT INTO vpBills (Date , Vendor , Reference , Type , DueDate , VendorInvoice , Freight , Discount , OtherCharges , Amount , PayAmount , DueAmount , PORef , PayYN , CheckID , PostedYN ) VALUES (@Date , @Vendor , @NewNo , ''VOUCHER '', @Date , @Ref , 0 , 0 , 0 ,@Amount ,0 , 0 , 0 , 0 , 0 ,1 ) INSERT INTO vpBillExpenses (Reference , Account , Amount , Memo , Project ) VALUES (@NewNo , @Debit , @Amount , '' '', '' '') EXEC addBill @NewNo EXEC postBill @NewNo EXEC postBillPayment @AccNoTF , @NewNo , @Account , @Amount , @CheckNo , @Date , @NewCheck , @Amount COMMIT TRANSACTION ' END |
addOpBal |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | PostGLWorkDetail |
DDL Code | CREATE PROC [dbo].[addOpBal] AS DECLARE @ident varchar (50 ), @AccID varchar (50 ), @AccType varchar (50 ), @OpBal float BEGIN TRANSACTION -- GetOpBalances returns the list of accounts and their opening balances as typed in the Enter -- Opening Balance form . DECLARE Curs CURSOR FOR SELECT AccountName , BalanceType , OpeningBalance FROM GetOpBalances WHERE Modified=1 -- gets a new document number for this transaction DECLARE @NewID int DECLARE @NewNumber varchar (50 ) SELECT @NewID = 1 SELECT @NewNumber = ''ACCOPBAL-1 '' OPEN Curs DECLARE @No int SELECT @No = 10 FETCH NEXT FROM Curs INTO @AccID , @AccType , @OpBal WHILE @@FETCH_STATUS = 0 BEGIN DELETE FROM GLWorkDetail WHILE EXISTS (SELECT EntryNo FROM GeneralJournalEntry WHERE EntryNo=@NewNumber ) BEGIN SELECT @NewID = @NewID + 1 SELECT @NewNumber = ''ACCOPBAL- '' + CONVERT (varchar ,@NewID ) END -- creates the GL header INSERT INTO GeneralJournalEntry (Date , EntryNo , SystemGenerated , PostedYN , TrType , TrID ) VALUES (CONVERT (varchar ,CURRENT_TIMESTAMP ,101 ), @NewNumber , 1 , 1 , 8 , @NewNumber ) -- for every record in GetOpBalances , post the transaction to GL IF @AccType=1 -- debit BEGIN INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@NewNumber , @AccID , 0 , @OpBal , ''Beg Bal Credit '', @No ) SELECT @No = @No + 10 INSERT INTO GLWorkDetail (GLID , Account , Debit , Credit , Memo , No ) VALUES (@NewNumber , ''Opening Balance '', @OpBal , 0 , ''Beg Bal Credit '', @No ) SELECT @No = @No + 10 END ELSE IF @AccType=2 -- credit BEGIN INSERT INTO GLWorkDetail (GLID , Account , Debit ,Credit ,Memo , No ) VALUES (@NewNumber ,@AccID ,@OpBal , 0 , ''Beg Bal Debit '', @No ) SELECT @No = @No + 10 INSERT INTO GLWorkDetail (GLID , Account , Debit ,Credit ,Memo , No ) VALUES (@NewNumber , ''Opening Balance '',0 ,@OpBal , ''Beg Bal Debit '', @No ) SELECT @No = @No + 10 END -- posts it to GL EXEC PostGLWorkDetail @NewNumber FETCH NEXT FROM Curs INTO @AccID , @AccType , @OpBal END CLOSE Curs DEALLOCATE Curs UPDATE lsAccounts SET Modified=0 COMMIT TRANSACTION ' END |
AutoShipOrders |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[AutoShipOrders] AS -- selects the orders ready for shipping SELECT csOrders .OrderNo , '' |
BillOrders |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[BillOrders] AS DECLARE @A float -- returns the list of orders ready to be billed , along with the HTML syntax for the fields SELECT csOrders .OrderNo , csOrders .Total , CONVERT (varchar , csOrders .Total ) AS Tot , csOrders .Billed , CASE csOrders .CCNumber WHEN NULL THEN '' '' ELSE csOrders .CCNumber END AS CCNumber , CONVERT (varchar , csOrders .CCExpDate , 101 ) AS CCExpDate , lsCustomers .Zip , ''" '' + CASE csOrders .ApprovalNumber WHEN NULL THEN '' '' ELSE csOrders .ApprovalNumber END+ ''"> '' AS ApprovalNumber , ''= checkbox id = OKautonumberingCode name = OKautonumberingCode> '' AS OK FROM csOrders INNER JOIN lsCustomers ON csOrders .Customer = lsCustomers .CustomerName WHERE csOrders .Billed = 0 AND Backordered = 0 ORDER BY Total DESC ' END |
ClosedPO |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[ClosedPO] AS -- returns the POs marked as closed (received ) SELECT PORef , CONVERT (varchar ,Total ) AS Total ,CONVERT (varchar ,Date ,101 ) AS Date ,Vendor FROM vpPurchaseOrders WHERE ReceivedYN=1 ' END |
GetAccounts |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[GetAccounts] AS -- retrieves a preformated Accounts list SELECT ''Edit '' AS Edit , AccountNumber , AccountName , lsAccountTypes .Description , (CASE BalanceType WHEN 1 THEN Balance * - 1 ELSE Balance END ) AS Balance FROM lsAccounts , lsAccountTypes WHERE lsAccounts .AccountType = lsAccountTypes .Description ORDER BY AccountNumber ' END |
GetGroupID |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[GetGroupID] AS BEGIN TRANSACTION -- generates a pseudo-random group id for checks (to use in Pay Multiple Vendors screen ) DECLARE @D AS datetime SELECT @D = CURRENT_TIMESTAMP SELECT CONVERT (varchar ,@D ,101 ) + CONVERT (VARCHAR , DatePart (hh ,@D )) + CONVERT (VARCHAR , DatePart (mi ,@D )) + CONVERT (VARCHAR , DatePart (ss ,@D )) + CONVERT (VARCHAR , DatePart (ms ,@D )) AS GroupID COMMIT TRANSACTION ' END |
getItems3 |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[getItems3] AS -- Optimized routine to load the item names list SELECT '' |
GetNewCheckID |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[GetNewCheckID] AS BEGIN TRANSACTION -- selects the @@IDENTITY as being the last check ID INSERT INTO bChecks (CheckNo ) VALUES (0 ) SELECT @@IDENTITY AS NewCID COMMIT TRANSACTION ' END |
GetNewEmpCheckID |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[GetNewEmpCheckID] AS BEGIN TRANSACTION -- selects the @@IDENTITY as being the last available CheckID INSERT INTO EmpChecks (CheckNo ) VALUES (0 ) SELECT @@IDENTITY AS NewCID COMMIT TRANSACTION ' END |
NewAutoShipOrders |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[NewAutoShipOrders] AS BEGIN SELECT csOrders .OrderNo , csOrders .Total , csOrders .Date , csOrders .Customer FROM csOrders WHERE csOrders .Billed = 0 AND csOrders .Backordered = 0 ORDER BY csOrders .Total DESC END RETURN ' END |
NewBillOrders |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[NewBillOrders] AS BEGIN SELECT csOrders .OrderNo , csOrders .Total , csOrders .Billed , csOrders .CCNumber , csOrders .CCExpDate , lsCustomers .Zip , csOrders .ApprovalNumber FROM csOrders INNER JOIN lsCustomers ON csOrders .Customer = lsCustomers .CustomerName WHERE csOrders .Billed = 0 AND csOrders .Backordered = 0 ORDER BY csOrders .Total DESC END RETURN ' END |
NewPickOrders |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[NewPickOrders] AS BEGIN -- retrieves a preformated list of orders for picking SELECT csOrders .OrderNo , csOrders .Total , csOrders .Customer AS CustomerName , csOrders .Date , csOrders .Billed FROM csOrders WHERE csOrders .Billed = 1 AND csOrders .Picked = 0 AND csOrders .Backordered = 0 ORDER BY Total DESC END RETURN ' END |
NewShipOrders |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[NewShipOrders] AS BEGIN SELECT csOrders .OrderNo , csOrders .Total , csOrders .Date , csOrders .TrackingNumber FROM csOrders WHERE csOrders .Billed = 1 AND csOrders .Picked = 1 AND csOrders .Printed = 1 AND csOrders .Backordered = 0 AND csOrders .Shipped = 0 ORDER BY Total DESC END RETURN ' END |
PickOrders |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[PickOrders] AS -- retrieves a preformated list of orders for picking SELECT csOrders .OrderNo , csOrders .Total , csOrders .Customer AS CustomerName , CONVERT (varchar , csOrders .Total ) AS Tot , CONVERT (varchar , csOrders .Date ,101 ) AS Date , csOrders .Billed , ''= checkbox id = OKautonumbering name = OKautonumbering> '' AS OK FROM csOrders WHERE csOrders .Billed = 1 AND csOrders .Picked = 0 AND Backordered = 0 ORDER BY Total DESC ' END |
ReceivePO |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[ReceivePO] AS -- selects the PO ''s partially or not received SELECT PORef , Total ,CONVERT (varchar ,Date ,101 ) AS Date , VendorName , ''"main" "receivepurchaseorders .asp?direction=idnumber&fields=PORef= ''+CONVERT (varchar ,PORef )+ ''">Receive</a> '' AS Receive FROM vpPurchaseOrders , lsVendors WHERE ReceivedYN=0 AND Vendor=VendorName ' END |
rptARRegister |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptARRegister] AS SELECT InvoiceNo AS TransID , CONVERT (varchar ,csInvoices .Date , 101 ) AS Date , InvoiceNo AS Number , ''INV '' AS Type , Customer , Memo AS Description , CASE WHEN (PayAmount=Total ) THEN ''Paid '' ELSE CONVERT (varchar ,DueDate ,101 ) END AS DueDate , STR (Total ,20 ,2 ) AS AmtChrg , STR (PayAmount ,10 ,2 ) AS AmtPaid , ''../receivables/createinvoices .asp?direction=idnumber '' AS Path FROM csInvoices WHERE PostedYN = 1 UNION SELECT GeneralJournalEntry .EntryNo AS TransID , CONVERT (varchar , GeneralJournalEntry .Date , 101 ) AS Date , GeneralJournalEntry .EntryNo AS Number , ''GENJRNL '' AS Type , '' '' AS Customer , CASE (SELECT COUNT (GeneralJournalDetail .Account ) FROM GeneralJournalDetail WHERE GeneralJournalEntry .EntryNo = GeneralJournalDetail .EntryNo ) WHEN 2 THEN (SELECT lsAccounts .AccountName FROM lsAccounts , GeneralJournalDetail WHERE GeneralJournalEntry .EntryNo = GeneralJournalDetail .EntryNo AND lsAccounts .AccountName = GeneralJournalDetail .Account AND GeneralJournalDetail .Account <> ''Accounts Receivable '') ELSE ''split '' END AS Account , GeneralJournalDetail .Memo , STR (GeneralJournalDetail .Credit ,20 ,2 ) AS AmtChrg , STR (GeneralJournalDetail .Debit ,20 ,2 ) AS AmtPaid , ''../ledger/makejournalentries .asp?direction=idnumber '' AS Path FROM GeneralJournalEntry , GeneralJournalDetail WHERE GeneralJournalEntry .EntryNo = GeneralJournalDetail .EntryNo AND GeneralJournalDetail .Account = ''Accounts Receivable '' AND PostedYN = 1 AND SystemGenerated = 0 ORDER BY DueDate ASC ' END |
rptCOA |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptCOA] AS SELECT AccountNumber , AccountName , AccountType AS TypeAccount , CASE BalanceType WHEN 1 THEN Balance * - 1 ELSE Balance END AS Balance FROM lsAccounts ORDER BY AccountNumber ,TypeAccount ,AccountName ' END |
rptItems |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptItems] AS SELECT ItemName , SalesDescription , CASE (ItemType ) WHEN 1 THEN ''Inventory Part '' WHEN 5 THEN ''Non Inventory Part '' WHEN 9 THEN ''Service '' END AS ItemType , QuantityOnHand ,SalesPrice FROM lsItems ORDER BY ItemName ' END |
rptOpenInvoices |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptOpenInvoices] AS SELECT InvoiceNo AS TransID , CONVERT (varchar ,Date ,101 ) AS Date , InvoiceNo , Customer , Memo , CONVERT (varchar ,DueDate ,101 ) AS DueDate ,Total , ''../receivables/createinvoices .asp?direction=idnumber '' AS Path FROM csInvoices WHERE PaidYN = 0 AND PostedYN = 1 ORDER BY Date ' END |
rptProjects |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptProjects] AS SELECT ProjectName , Customer AS CustomerName , ProjectManager , CONVERT (varchar , StartDate , 101 ) AS StartDate , CONVERT (varchar , EndDate , 101 ) AS EndDate FROM lsProjects ORDER BY ProjectName ' END |
rptTaxes |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[rptTaxes] AS SELECT TaxID ,lsTaxes .Description ,AccountName ,TaxPercent FROM lsTaxes ,lsAccounts WHERE lsTaxes .Account=lsAccounts .AccountName ' END |
ShipOrders |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[ShipOrders] AS -- selects the orders ready for shipping SELECT csOrders .OrderNo , csOrders .Total , CONVERT (varchar , csOrders .Total ) AS Tot , CONVERT (varchar , Date , 101 ) AS Date , ''"15" " '' + CASE WHEN (csOrders .TrackingNumber IS NULL ) THEN '' '' ELSE csOrders .TrackingNumber END+ ''"> '' AS TrackingNumber , ''= checkbox id = OKautonumbering name = OKautonumbering> '' AS OK FROM csOrders WHERE csOrders .Billed = 1 AND csOrders .Picked = 1 AND csOrders .Printed = 1 AND Backordered = 0 AND csOrders .Shipped = 0 ORDER BY Total DESC ' END |
sp_get_CompanyUseAccountNumber |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_get_CompanyUseAccountNumber] AS BEGIN SELECT TOP 1 ISNULL (Company .UseAN , 1 ) AS UseAN FROM Company END RETURN ' END |
sp_get_NewGroupID |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_get_NewGroupID] AS BEGIN BEGIN TRANSACTION -- generates a pseudo-random group id for checks (to use in Pay Multiple Vendors screen ) SET NOCOUNT ON DECLARE @D AS datetime SELECT @D = CURRENT_TIMESTAMP SELECT CONVERT (varchar ,@D ,101 ) + CONVERT (VARCHAR , DatePart (hh ,@D )) + CONVERT (VARCHAR , DatePart (mi ,@D )) + CONVERT (VARCHAR , DatePart (ss ,@D )) + CONVERT (VARCHAR , DatePart (ms ,@D )) AS GroupID COMMIT TRANSACTION END RETURN ' END |
sp_select_AccountTypes |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_AccountTypes] AS BEGIN SELECT lsAccountTypes .Description FROM lsAccountTypes ORDER BY lsAccountTypes .Description END RETURN ' END |
sp_select_BillPayments |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_BillPayments] AS BEGIN SELECT vpBillPayments .Date , vpBillPayments .Account , vpBillPayments .Amount , vpBillPayments .CheckNo , vpBillPayments .Memo FROM vpBillPayments ORDER BY vpBillPayments .Date END RETURN ' END |
sp_select_Bills |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Bills] AS BEGIN SELECT vpBills .Date , vpBills .Reference , vpBills .Amount , vpBills .Memo , vpBills .PostedYN FROM vpBills WHERE vpBills .Type = ''Voucher '' ORDER BY vpBills .Date END RETURN ' END |
sp_select_Classes |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Classes] AS BEGIN SELECT lsClasses .ClassName FROM lsClasses ORDER BY lsClasses .ClassName END RETURN ' END |
sp_select_Company |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Company] AS BEGIN SELECT TOP 1 Company .CompanyName , Company .CompanyName , Company .LegalName , Company .Address1 , Company .Address2 , Company .City , Company .State , Company .Zip , Company .Country , Company .Phone , Company .Fax , Company .email , Company .website , Company .FirstFiscalMonth , Company .PictureURL , Company .ConfirmDeletion , Company .UseAN , Company .DefaultCT , Company .DefaultIT , Company .DefaultSM , Company .AgingReportUse , Company .TaxFreight , Company .DefaultSalesTax , Company .ChargeHandling , Company .HandlingAsPercent , Company .HandlingAmount , Company .CurrencySymbol FROM Company END RETURN ' END |
sp_select_CustomerCredits |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerCredits] AS BEGIN SELECT csCustomerCredits .CreditNumber , csCustomerCredits .Date , csCustomerCredits .Total , csCustomerCredits .Memo FROM csCustomerCredits ORDER BY csCustomerCredits .Date END RETURN ' END |
sp_select_CustomerMessages |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerMessages] AS BEGIN SELECT lsCustomerMessages .CustomerMessage FROM lsCustomerMessages ORDER BY lsCustomerMessages .CustomerMessage END RETURN ' END |
sp_select_CustomerPayments |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerPayments] AS BEGIN SELECT csCustomerPayments .Reference , csCustomerPayments .Date , csCustomerPayments .PaymentAmount , csCustomerPayments .Memo FROM csCustomerPayments ORDER BY csCustomerPayments .Date END RETURN ' END |
sp_select_Customers |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Customers] AS BEGIN SELECT lsCustomers .Login , lsCustomers .Password , lsCustomers .CustomerName , lsCustomers .Company , lsCustomers .MrMs , lsCustomers .FirstName , lsCustomers .MiddleInitial , lsCustomers .LastName , lsCustomers .BillingAddress1 , lsCustomers .BillingAddress2 , lsCustomers .City , lsCustomers .State , lsCustomers .Zip , lsCustomers .Country , lsCustomers .Contact , lsCustomers .Phone , lsCustomers .Email , lsCustomers .Fax , lsCustomers .AltPhone , lsCustomers .AltContact , lsCustomers .Account , lsCustomers .CustomerType , lsCustomers .Term , lsCustomers .Rep , lsCustomers .CreditLimit , lsCustomers .Taxable , lsCustomers .TaxItem , lsCustomers .ResaleNumber , lsCustomers .OpeningBalance , lsCustomers .OpeningBalanceDate , lsCustomers .Inactive , lsCustomers .PasswordCA , lsCustomers .Balance , lsCustomers .ExistingCredit FROM lsCustomers ORDER BY lsCustomers .CustomerName END RETURN ' END |
sp_select_CustomersShipTo |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomersShipTo] AS BEGIN SELECT lsCustomersShipTo .CustomerName , lsCustomersShipTo .ShipingAddressID , lsCustomersShipTo .ShippingAddress , lsCustomersShipTo .City , lsCustomersShipTo .State , lsCustomersShipTo .Zip , lsCustomersShipTo .Country FROM lsCustomersShipTo ORDER BY lsCustomersShipTo .CustomerName , lsCustomersShipTo .ShipingAddressID END RETURN ' END |
sp_select_CustomerTypes |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_CustomerTypes] AS BEGIN SELECT lsCustomerTypes .CustomerType , lsCustomerTypes .Inactive FROM lsCustomerTypes ORDER BY lsCustomerTypes .CustomerType END RETURN ' END |
sp_select_DocNumbers |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_DocNumbers] AS BEGIN SELECT TOP 1 DocNumbers .CompanyID , DocNumbers .CheckNo , DocNumbers .Vouchers , DocNumbers .Orders , DocNumbers .BillPmt , DocNumbers .Invoices , DocNumbers .CustCredits , DocNumbers .CustPmt , DocNumbers .Adjustment , DocNumbers .TimeTracking , DocNumbers .Transfers FROM DocNumbers END RETURN ' END |
sp_select_GeneralJournalEntry |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_GeneralJournalEntry] AS BEGIN SELECT GeneralJournalEntry .Date , GeneralJournalEntry .EntryNo FROM GeneralJournalEntry ORDER BY GeneralJournalEntry .Date END RETURN ' END |
sp_select_InventoryAdjustments |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_InventoryAdjustments] AS BEGIN SELECT InventoryAdjustments .Date , InventoryAdjustments .RefNo , InventoryAdjustments .TotalValue , InventoryAdjustments .Memo FROM InventoryAdjustments ORDER BY InventoryAdjustments .Date , InventoryAdjustments .RefNo END RETURN ' END |
sp_select_Invoices |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Invoices] AS BEGIN SELECT csInvoices .Date , csInvoices .Customer , csInvoices .ShipToID , csInvoices .Class , csInvoices .Memo , csInvoices .Term , csInvoices .InvoiceNo , csInvoices .DueDate , csInvoices .ShipVia , csInvoices .ShipDate , csInvoices .POID , csInvoices .CustomerMessage , csInvoices .TrackingNumber , csInvoices .Handling , csInvoices .Freight , csInvoices .Discount , csInvoices .SalesTax , csInvoices .Tax , csInvoices .TaxTotal , csInvoices .Total , csInvoices .TotalWeight , csInvoices .ShippingCharge , csInvoices .ApplyYN , csInvoices .PaidYN , csInvoices .PayAmount , csInvoices .PostedYN FROM csInvoices ORDER BY csInvoices .InvoiceNo END RETURN ' END |
sp_select_InvoiceShippedOrders |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_InvoiceShippedOrders] AS BEGIN SELECT csOrders .OrderNo , csOrders .Total , csOrders .Date , csOrders .ShipDate FROM csOrders WHERE csOrders .Billed = 1 AND csOrders .Picked = 1 AND csOrders .Printed = 1 AND csOrders .Invoiced = 0 AND csOrders .Shipped = 1 ORDER BY csOrders .OrderNo END RETURN ' END |
sp_select_ItemCategories |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_ItemCategories] AS BEGIN SELECT lsItemCategories .CategoryID , lsItemCategories .Description , lsItemCategories .PictureURL , lsItemCategories .familyID FROM lsItemCategories ORDER BY lsItemCategories .CategoryID END RETURN ' END |
sp_select_ItemFamilies |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_ItemFamilies] AS BEGIN SELECT lsItemFamilies .FamilyID , lsItemFamilies .Description , lsItemFamilies .PictureURL FROM lsItemFamilies ORDER BY lsItemFamilies .FamilyID END RETURN ' END |
sp_select_Items |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Items] AS BEGIN SELECT lsItems .ItemName , lsItems .SalesDescription , lsItems .QuantityOnHand , lsItems .SalesPrice FROM lsItems WHERE lsItems .ItemType=1 ORDER BY lsItems .ItemName END RETURN ' END |
sp_select_Memorized |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Memorized] AS BEGIN SELECT lsMemorized .TrType , lsMemorized .Name , lsMemorized .TransID FROM lsMemorized ORDER BY lsMemorized .Name END RETURN ' END |
sp_select_Orders |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Orders] AS BEGIN SELECT csOrders .Date , csOrders .OrderNo , csOrders .Total , csOrders .Memo FROM csOrders WHERE csOrders .Picked=0 AND csOrders .Billed=0 AND csOrders .Printed=0 AND csOrders .Shipped=0 AND csOrders .Backordered=0 ORDER BY csOrders .Date , csOrders .OrderNo END RETURN ' END |
sp_select_PayMany |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_PayMany] AS BEGIN SELECT PayMany .Vendor , SUM (PayMany .Amount ) AS Amount FROM PayMany GROUP BY PayMany .Vendor ORDER BY PayMany .Vendor END RETURN ' END |
sp_select_PaymentMethods |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_PaymentMethods] AS BEGIN SELECT lsPaymentMethods .PaymentMethod FROM lsPaymentMethods ORDER BY lsPaymentMethods .PaymentMethod END RETURN ' END |
sp_select_Projects |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Projects] AS BEGIN SELECT lsProjects .Customer , lsProjects .ProjectName , lsProjects .Description , lsProjects .ProjectManager , lsProjects .StartDate , lsProjects .EndDate , lsProjects .Inactive FROM lsProjects ORDER BY lsProjects .ProjectName END RETURN ' END |
sp_select_PurchaseOrders |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_PurchaseOrders] AS BEGIN SELECT vpPurchaseOrders .PORef , vpPurchaseOrders .Total , vpPurchaseOrders .Date , vpPurchaseOrders .Vendor FROM vpPurchaseOrders WHERE vpPurchaseOrders .ReceivedYN = 0 END RETURN ' END |
sp_select_ShippingMethods |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_ShippingMethods] AS BEGIN SELECT lsShippingMethods .ShippingMethod , lsShippingMethods .AccountNo , lsShippingMethods .Inactive FROM lsShippingMethods ORDER BY lsShippingMethods .ShippingMethod END RETURN ' END |
sp_select_Taxes |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Taxes] AS BEGIN SELECT lsTaxes .TaxID , lsTaxes .Description , lsTaxes .TaxPercent , lsTaxes .Account , lsTaxes .Inactive FROM lsTaxes ORDER BY lsTaxes .TaxID END RETURN ' END |
sp_select_TaxGroups |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_TaxGroups] AS BEGIN SELECT lsTaxGroups .TaxGroupID , lsTaxGroups .TaxGroupName , lsTaxGroups .TotalPercent , lsTaxGroups .Inactive , lsTaxGroups .IsDefault FROM lsTaxGroups ORDER BY lsTaxGroups .TaxGroupID END RETURN ' END |
sp_select_Terms |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Terms] AS BEGIN SELECT lsTerms .Term , lsTerms .Standard , lsTerms .DayTillNetDue , lsTerms .Discount , lsTerms .DaysTillDiscountExpires , lsTerms .DayOfMonthNetDue , lsTerms .DueNextMonthIfWithinDays , lsTerms .Discount1 , lsTerms .DayDiscountExpires , lsTerms .Inactive FROM lsTerms ORDER BY lsTerms .Term END RETURN ' END |
sp_select_TimeTracking |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_TimeTracking] AS BEGIN SELECT TimeTracking .Reference , TimeTracking .Date , TimeTracking .Memo FROM TimeTracking ORDER BY TimeTracking .Date END RETURN ' END |
sp_select_TransferFunds |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_TransferFunds] AS BEGIN SELECT bTransferFunds .Reference , bTransferFunds .Date , bTransferFunds .Amount , bTransferFunds .Memo FROM bTransferFunds ORDER BY bTransferFunds .Date END RETURN ' END |
sp_select_Users |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Users] AS BEGIN SELECT Users .UserName , Users .Password FROM Users ORDER BY Users .UserName END RETURN ' END |
sp_select_Vendors |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_Vendors] AS BEGIN SELECT lsVendors .VendorName , lsVendors .Company , lsVendors .MrMs , lsVendors .FirstName , lsVendors .MiddleInitial , lsVendors .LastName , lsVendors .Contact , lsVendors .Address1 , lsVendors .Address2 , lsVendors .City , lsVendors .State , lsVendors .Zip , lsVendors .Country , lsVendors .Phone , lsVendors .Fax , lsVendors .AltPhone , lsVendors .AltContact , lsVendors .PrintOnCheckAs , lsVendors .AccountNumber , lsVendors .DefaultExpenseAccount , lsVendors .VendorType , lsVendors .Term , lsVendors .PMID , lsVendors .CreditLimit , lsVendors .TaxID , lsVendors .Eligible , lsVendors .Email , lsVendors .OpeningBalance , lsVendors .OpeningBalanceDate , lsVendors .Balance , lsVendors .Inactive FROM lsVendors ORDER BY lsVendors .VendorName END RETURN ' END |
sp_select_VendorTypes |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[sp_select_VendorTypes] AS BEGIN SELECT lsVendorTypes .VendorType , lsVendorTypes .Inactive FROM lsVendorTypes ORDER BY lsVendorTypes .VendorType END RETURN ' END |
vpPayMany |
Owner | dbo |
Parameters | |
Definition | |
Note | |
Called SPs | |
DDL Code | CREATE PROC [dbo].[vpPayMany] AS -- retrieves a consistent list of payments by vendor SELECT Vendor AS IID , Vendor , SUM (Amount ) AS Amount FROM PayMany GROUP BY Vendor ' END |