This article describes the SQL Functions, Stored Procedures, and Views that are automatically added to the Control database.
Functions
BillIDFromNumber
**Parameters**
@BillNumber: Integer
**Result**
Integer
**Description**
This function returns the ID of the record of a Bill or Vendor Credit Memo with the given Bill Number.
**Examples**
Example 1: Returns the ID from the record of Bill Number 1000
SELECT dbo.BillIDFromNumber(1000)
Example 2: Returns the line items from Bill Number 1000
SELECT * FROM VendorTransDetail WHERE TransHeaderID = dbo.BillIDFromNumber(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
BillFromNumber
**Parameters**
@BillNumber: Integer
**Result**
TransHeader Table Record
**Description**
This function returns the TransHeader table record of a Bill or Vendor Credit Memo with the given Bill Number.
**Examples**
Example 1: Returns the record of Bill Number 1000
SELECT * FROM BillFromNumber(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
CurrentEarnings
**Parameters**
@StartDate: DateTime
@DivisionID: Integer
**Result**
Float
**Description**
This function returns the amount of money earned since the last yearly closeout up to the "StartDate". The beginning of the year is used if no closeout is found.
**Examples**
Example 1:
SELECT dbo.CurrentEarnings('02/02/2012', 1)
**Notes**
EstimateIDFromNumber
**Parameters**
@EstimateNumber: Integer
**Result**
Integer
**Description**
This function returns the ID of the record of an Estimate with the given Estimate Number.
**Examples**
Example 1: Returns the ID from the record of Estimate Number 1000
SELECT dbo.EstimateIDFromNumber(1000)
Example 2: Returns the line items from Estimate Number 1000
SELECT * FROM TransDetail WHERE TransHeaderID = dbo.EstimateIDFromNumber(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
EstimateFromNumber
**Parameters**
@EstimateNumber: Integer
**Result**
TransHeader Table Record
**Description**
This function returns the TransHeader table record of an Estimate with the given Estimate Number.
**Examples**
Example 1: Returns the record of Estimate Number 1000
SELECT * FROM EstimateFromNumber(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
FirstDayNextMonth
**Parameters**
@CurrentDate: DateTime
**Result**
DateTime
**Description**
This function returns the date of the first day of the month that follows the parameter.
**Examples**
Example 1: Returns the DateTime: (2012-03-01 00:00:00.000).
SELECT dbo.FirstDayNextMonth('02/02/2012')
Example 2: Returns the DateTime: (1984-07-01 00:00:00.000).
SELECT dbo.FirstDayNextMonth('06/27/1984 12:11:10:999')
**Notes**
GetAllPartUDFs
**Parameters**
[none]
**Result**
Table
**Description**
This function returns a table containing all of the part UDFs with columns: PartID, VariableName, and UDFValue.
**Examples**
Example 1:
SELECT * FROM GetAllPartUDFs()
**Notes**
LedgerFromOrderNumber
**Parameters**
@OrderNumber: Integer
**Result**
Ledger Table Records
**Description**
This function returns the Ledger table records of an Order, Service Ticket, or Credit Memo with the given Order Number.
**Examples**
Example 1: Returns all of the ledger record of Order Number 1000
SELECT * FROM LedgerFromOrderNumber(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
MultipleQuantityGridHeader
**Parameters**
@TransDetailID: Integer
**Result**
Table
**Description**
This function returns a single row table containing the multiple-quantity grid header information for the referenced TransDetailID. If the referenced TransDetailID does not exist or has no grid stored with it, a NULL table is returned.
**Examples**
Example 1:
SELECT * FROM MultipleQuantityGridHeader(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
MultipleQuantityGridRows
**Parameters**
@TransDetailID: Integer
**Result**
Table
**Description**
This function returns a table containing all of the rows for the multiple-quantity grid header referenced TransDetailID. If the referenced TransDetailID does not exist or has no grid stored with it, a NULL table is returned.
**Examples**
Example 1:
SELECT * FROM MultipleQuantityGridRows(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
OrderIDFromNumber
**Parameters**
@OrderNumber: Integer
**Result**
Integer
**Description**
This function returns the ID of the record of an Order, Service Ticket, or Credit Memo with the given Order Number.
**Examples**
Example 1: Returns the ID from the record of Order Number 1000
SELECT dbo.OrderIDFromNumber(1000)
Example 2: Returns the line items from Order Number 1000
SELECT * FROM TransDetail WHERE TransHeaderID = dbo.OrderIDFromNumber(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
OrderFromNumber
**Parameters**
@OrderNumber: Integer
**Result**
TransHeader Table Record
**Description**
This function returns the TransHeader table record of an Order, Service Ticket, or Credit Memo with the given Order Number.
**Examples**
Example 1: Returns the record of Order Number 1000
SELECT * FROM OrderFromNumber(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
PurchaseOrderIDFromNumber
**Parameters**
@PurchaseOrderNumber: Integer
**Result**
Integer
**Description**
This function returns the ID of the record of a Purchase Order with the given Purchase Order Number.
**Examples**
Example 1: Returns the ID from the record of Purchase Order Number 1000
SELECT dbo.PurchaseOrderIDFromNumber(1000)
Example 2: Returns the line items from Purchase Order Number 1000
SELECT * FROM VendorTransDetail WHERE TransHeaderID = dbo.PurchaseOrderIDFromNumber(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
PurchaseOrderFromNumber
**Parameters**
@PurchaseOrderNumber: Integer
**Result**
TransHeader Table Record
**Description**
This function returns the TransHeader table record of a Purchase Order with the given Purchase Order Number.
**Examples**
Example 1: Returns the record of Purchase Order Number 1000
SELECT * FROM PurchaseOrderFromNumber(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
ReceivingDocumentIDFromNumber
**Parameters**
@ReceivingDocumentNumber: Integer
**Result**
Integer
**Description**
This function returns the ID of the record of a Receiving Document with the given Receiving Document Number.
**Examples**
Example 1: Returns the ID from the record of Receiving Document Number 1000
SELECT dbo.ReceivingDocumentIDFromNumber(1000)
Example 2: Returns the line items from Receiving Document Number 1000
SELECT * FROM VendorTransDetail WHERE TransHeaderID = dbo.ReceivingDocumentIDFromNumber(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
ReceivingDocumentFromNumber
**Parameters**
@ReceivingDocumentNumber: Integer
**Result**
TransHeader Table Record
**Description**
This function returns the TransHeader table record of a Receiving Document with the given Receiving Document Number.
**Examples**
Example 1: Returns the record of Receiving Document Number 1000
SELECT * FROM ReceivingDocumentFromNumber(1000)
**Notes**
This function was included as of the 04.60.1202.0101 version release.
RetainedEarnings
**Parameters**
@StartDate: DateTime
@DivisionID: Integer
**Result**
Float
**Description**
This function returns the amount of money retained from the last yearly closeout up to the "StartDate". The beginning of the year is used if no closeout is found.
**Examples**
Example 1:
SELECT dbo.RetainedEarnings('02/02/2012', 1)
**Notes**
ShipmentDetailsForShipment
**Parameters**
@ShipmentID: Integer
@TransHeaderID: Integer
**Result**
Table
**Description**
This function returns the shipment details in a table format. If the ShipmentID or the TransHeaderID do not exist a NULL table is returned.
**Examples**
Example 1:
SELECT * FROM ShipmentDetailsForShipment(1000, 1000)
**Notes**
ShipmentItemsTable
**Parameters**
@ShipmentID: Integer
**Result**
Table
**Description**
This function returns the table format of the ShipLineItemsXML field. If no shipment exist for the ShipmentID, then a NULL table is returned.
**Examples**
Example 1:
SELECT * FROM ShipmentItemsTable(1000)
**Notes**
[[http://control.cyriouswiki.com/message/view/home/43186522#43459254|http://control.cyriouswiki.com/message/view/home/43186522#43459254]]
TaxItemsTable
**Parameters**
@list: nText
**Result**
Table
**Description**
This function returns a table with the break down of the tax information for each order where @list is string of order ID numbers.
**Examples**
Example 1:
SELECT * FROM TaxItemsTable('10100, 10101, 10102')
**Notes**
TaxItemsTableFromTransDetail
**Parameters**
@TransDetailID: Integer
**Result**
Table
**Description**
This function returns a single row table with the break down of the line item tax information.
**Examples**
Example 1:
SELECT * FROM TaxItemsTableFromTransDetail(1000)
**Notes**
TextToXML
**Parameters**
@InputText: VarChar(4096)
**Result**
VarChar(4096)
**Description**
This function returns the given text in a XML format where any ASCII characters that are not allowed in XML have been converted accordingly. If the supplied text is null or does not need formatting, the text is returned as is.
**Examples**
Example 1:
SELECT dbo.TextToXML('A String')
**Notes**
TransactionNumberFromID
**Parameters**
@ID: Integer
**Result**
VARCHAR(100)
**Description**
This function returns the transaction number of the transaction with the ID that is passed along with type of transaction.
**Examples**
Some examples of values returned are "Order: 100", "Estimate: 2345", "Bill: 15".
SELECT dbo.TransactionNumberFromID(TransactionID), * FROM Journal
**Notes**
This function was included as of the 05.50.1022.0101 version release.
Units
**Parameters**
[none]
**Result**
Table
**Description**
This function returns a table containing all of the Control units along the their appropriate conversion as well as their abbreviations.
**Examples**
Example 1:
SELECT * FROM Units()
**Notes**
XMLToText
**Parameters**
@InputText: VarChar(4096)
**Result**
VarChar(4096)
**Description**
This function returns the given XML formatted text in a non-XML format where any ASCII characters that are not allowed in XML have been converted accordingly. If the supplied text is null or does not need formatting, the text is returned as is.
**Examples**
Example 1:
SELECT dbo.XMLToText('A String')
**Notes**
Stored Procedures
sp_RebuildIndexes
**Parameters**
[none]
**Result**
Int
**Description**
This stored procedure executes 'DBREINDEX' on every table in the database.
**Examples**
Example 1:
sp_RebuildIndexes
**Notes**
Views
cv_ProductTree
**Columns**
Row: BigInt, FormattedName: nVarChar(4000), NodeID: Int, IsActive: Bit, IsCategory: Int, Depth: Int, NodeName: nVarChar(50), ParentID: Int,
LevelName: nVarChar(50), Level2Name: nVarChar(50), Level3Name: nVarChar(50), Level4Name: nVarChar(50), Level5Name: nVarChar(50),
ProductCode: nVarChar(50), Level1ID: Int, Level2ID: Int, Level3ID: Int, Level4ID: Int, Level5ID: Int, ProductID: Int, FormattedPath: nVarChar(306)
**Description**
This view shows the products and their categories with the names indented in a tree like manner.
**Examples**
Example 1:
SELECT * FROM cv_ProductTree Example
**Notes**
GL
**Columns**
ID: Int, StoreID: Int, ClassTypeID: Int, ModifiedByUser: nVarChar(25), ModifiedByComputer: nVarChar(25), ModifiedDate: DateTime, SeqID: Int,
IsSystem: Bit, IsActive: Bit, EntryDateTime: DateTime, Amount: Decimal(18,4), Classification: Int, IsTaxable: Bit, GroupID: Int, GLAccountID: Int,
GLAccountClassTypeID: Int, AccountID: Int, AccountClassTypeID: Int, TransactionID: Int, TransactionClassTypeID: Int, TransDetailID: Int,
TransDetailClassTypeID: Int, GoodsItemID: Int, GoodsItemsClassTypeID: Int, Description: nVarChar(50), DivisionID: Int, Notes: Text, IsModified: Bit,
IsUser: Bit, TaxClassID: Int, Quantity: Float, PartID: Int, PartClassTypeID: Int, JournalID: Int, JournalClassTypeID: Int, Reconciled: Bit,
ReconciliationDateTime: DateTime, ReconciliationID: Int, ReconciliationClassTypeID: Int, ProcessedDivisionID: Int, GLClassificationType: Int,
GLClassTypename: VarChar(50), TranspartID: Int, TransPartClassTypeID: Int, StationID: Int, PayrollID: Int, PayrollClassTypeID: Int, DepositJournalID: Int,
EntryType: Int, EmployeeID: Int, OffBalanceSheet: Bit, WarehouseID: Int, InventoryID: Int.
**Description**
This view represents the general ledger entries.
**Examples**
Example 1:
SELECT * FROM GL
**Notes**
ShipmentDetail
**Columns**
ShipmentID: Int, ShipmentNumber: VarChar(100), TransHeaderID: Int, OrderNumber: Int, LineItemID: Int, LineItemNumber: VarChar(15),
Description: VarChar(MAX), Quantity: Float, TotalLineQuantity: Float, ShippedPrice: Float, IsValidTax: Bit, TaxesClacPrice: Float,
TaxesOverriddenPrice: Float, TaxesIsOverridden: Bit, TaxItems: XML(.), EstimateNumber: Int, TransactionType: Int
**Description**
This view represents the ShipmentDetail entries.
**Examples**
Example 1:
SELECT * FROM ShipmentDetail
**Notes**
ShipperInfoForShipments
**Columns**
OrderNumber: VarChar(100), InsuredValue: Float, CompanyName: nVarChar(50), Description: Text, FirstName: nVarChar(25), LastName: nVarChar(25),
FullName: nVarChar(51), Email: nVarChar(50), Phone: nVarChar(75), StreetAddress1: nVarChar(40), StreetAddress2: nVarChar(40), City: nVarChar(25),
State: nVarChar(25), County: nVarChar(25), PostalCode: nVarChar(25), Country: nVarChar(25), TransactionID: Int, AccountID: Int, BareOrderNumber: Int,
Weight: Int, SalesPersonName: nVarChar(51), SPEmailAddress: nVarChar(51), ShipmentNotification: Int, DeliveryNotification: Int,
FedExServiceType: VarChar(30), FedExPackageType: VarChar(2), BillTransportationTo: VarChar(30), ActualOrderNumber: Int
**Description**
This view represents the information of the shipper.
**Examples**
Example 1:
SELECT * FROM ShipperInfoForShipments
**Notes**
Source
Contributor: Gregory Schroeder, Cyrious Software
Date: 2/2/2012
Version: Control 4.x
You could leave a comment if you were logged in.