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

[[http://control.cyriouswiki.com/message/view/home/43186522#43459254|http://control.cyriouswiki.com/message/view/home/43186522#43459254]]

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

You could leave a comment if you were logged in.