SM Entities

TABLE OF CONTENTS

• Example SM Entity Creation
• Identifying When an Entity is Needed

The Service management module in Vista doesn't use all of the same design patterns as the majority of the other modules in Vista. This is due to it being made more recently than everything else. One of the peculiarities of its design is the join table that links several of the primary SM tables to things like Standard Customer charges and standard service site charges. The table is called vSMEntity.  

Example SM Entity Creation

At the time of writing, as best as I can tell, the vSMEntity table acts as an abstraction layer for several different tables, allowing multiple tables to join exclusively to entities rather than have foreign keys for more than one SM table.

It is critical to the functioning of many of the service management forms. In addition, simply inserting SM Entities using SQL isn't sufficient. You have to create them using the function vspSMEntityCreate.

For example, if I wished to add a standard charge to a service site using SQL. I must first create an entity corresponding to the service site, then link the standard item to the entity.

Here is a piece of code used on an implementation to create standard charges on a service site.

BEGIN TRANSACTION

    DECLARE @SMCo NVARCHAR(50)
    DECLARE @ServiceSite NVARCHAR(50)
    DECLARE @StandardItem NVARCHAR(50)


    DECLARE db_cursor CURSOR FOR  
    SELECT ss.SMCo, 
           ss.ServiceSite, 
           si.StandardItem 
    FROM vSMServiceSite ss
    CROSS JOIN vSMStandardItem si
    LEFT JOIN vSMEntity se 
    ON se.CustGroup = ss.CustGroup AND se.Customer = ss.Customer
        LEFT JOIN vSMStandardItemDefault sidf 
    ON sidf.EntitySeq = se.EntitySeq
    WHERE si.StandardItem = sidf.StandardItem
    ORDER BY ss.ServiceSite


    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @SMCo, @ServiceSite, @StandardItem   
    WHILE @@FETCH_STATUS = 0   
    BEGIN   


        DECLARE @p20 INT


        -- This is a magic function. If you don't use it, the forms will explode.
        exec vspSMEntityCreate @SMEntityType=2,@SMCo=101,@CustGroup=NULL,@Customer=NULL,@ServiceSite=@ServiceSite,@RateTemplate=NULL,@EffectiveDate=NULL,@StandardItem=NULL,@WorkOrder=NULL,@WorkOrderScope=NULL,@Agreement=NULL,@AgreementRevision=NULL,@AgreementService=NULL,@WorkOrderQuote=NULL,@WorkOrderQuoteScope=NULL,@StandardTask=NULL,@WorkScope=NULL,@Class=NULL,@Maintenance=NULL,@EntitySeq=@p20 output


       INSERT INTO vSMStandardItemDefault([SMCo],[EntitySeq],[StandardItem],[CostQuantity],[PriceQuantity]) VALUES('101',@p20,@StandardItem,'1.000','1.000')




        FETCH NEXT FROM db_cursor INTO @SMCo, @ServiceSite, @StandardItem   
    END   


    CLOSE db_cursor   
    DEALLOCATE db_cursor
    --SELECT * FROM vSMEntity WHERE Type=2
ROLLBACK TRANSACTION

First, a cursor is opened on a query which generates the correct combination of service sites and standard items based on the standard charges on the customers (created earlier). 

using a while loop to iterate through each row of the table, exec vspSMEntityCreate is called once for each combination, and a row is inserted into the standard charges table (vSMStandardItemDefault) for that combination, using the EntitySeq generated by the call to vspSMEntityCreate.

Note that the vspSMEntityCreate function will be called multiple times if there is more than one standard charge for the service site. This is totally fine, because vspSMEntityCreate only creates a new entity if one does not exist for the supplied service site. if one already exists, it returns the EntitySeq of the existing entity.

after the standard charge is created, the cursor loops again until it has created all the standard charges.

Identifying When an Entity is Needed

Tables that join onto any of the following tables will likely join using an entity sequence rather than a direct foreign key. 

vSMCustomers, vSMServiceSite, vSMRateTemplate, vSMStandardItem, vSMWorkOrder, VSMWorkOrderScope, vSMAgreement, vSMAgreementService, vSMWorkOrderQuote, vSMWorkOrderQuoteScope, vSMStandardTask, vSMWorkScope

You can tell if this is the case by checking for the foreign key SMCo, EntitySeq on the "many" side of the relationship.