Control Standard SQL Functions and Views

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

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

See Also