This article describes the SQL Functions, Stored Procedures, and Views that are automatically added to the Control database.
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.
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.
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
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.
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.
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
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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
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
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
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
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
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.
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
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
Parameters
[none]
Result
Int
Description
This stored procedure executes 'DBREINDEX' on every table in the database.
Examples
Example 1:
sp_RebuildIndexes
Notes
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
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
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
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
Contributor: Gregory Schroeder, Cyrious Software
Date: 2/2/2012
Version: Control 4.x