List of Stored Procedures & Their Functions

I'm afraid none such exists, nor is likely to.  Doing so would imply some sort of contract about the format and behavior of the stored procedures, and Tessitura routinely changes those.  The REST API is where they will be creating a documented interface for end users to build custom functionality.

Full disclosure: I continue to do almost all of my customization work in SQL.  I'll do some digging to try and figure out how Tessitura handles a problem with their stored procedures at times, but I rarely use them, as it's easier to keep track of changes in tables than in everything else.

There are a handful of convenience procedures and functions specifically for end users, mostly for things like finding the best mailing address.  I think they may be in the standard documentation somewhere.

Gawain,

I appreciate your help, if could track the procedure down, please let me know.  I too like to work in SQL, but, like you said, documentation for TNEW-facing SQL ranges from poor to non-existent. However, it's really the only way you can provide custom functions without developing a full API site.  Kind of puts us in an awkward position.

I could be wrong, but doesn't TNEW make use of the SOAP API?  Maybe I could look through the documentation on SOAP too see if there is anything worthwhile.

At any rate, keep me posted, and thank you!

Unknown said:

I could be wrong, but doesn't TNEW make use of the SOAP API?  Maybe I could look through the documentation on SOAP too see if there is anything worthwhile.

That might be a good place to start, although to be sure TNEW is aggressively abandoning the SOAP API.

As Tessitura's primary ecommerce solution, we're excited that TNEW v7 is using more REST API requests than SOAP, and we will continue to expand REST usage across the application. As a result, it's very important that organizations have their REST API in order prior to a v7 installation or upgrade. This means making sure that you have working REST credentials and proper security permissions for the web user to carry out its functions.

http://www.tessituranetwork.com/network/About/Network%20News/Articles/December%202016%20Web%20Products%20Update.aspx?utm_source=WebProductsUpdate&utm_medium=email&utm_campaign=December2016#rest

Cody,

My guess is that if TNEW (we here at NIDA use TNEW) is using the SOAP API for account management, the stored procedure is most likely to be WP_MAINTAIN_CONSTITUENT.  As it's a Tess supplied SP it is definitely not recommended that you edit it.

Can I ask what you'd like the information for?  Maybe there's a setting in the various system tables that would help more?

Martin

Yes,

I believe that was the procedure I found as well.  I need to do some testing, however.  I will keep you all updated if I find something out.

The "Edit Your Account Information" page allows a user to change their constituent information, for example, changing their street address.  However, instead of creating a new address and deactivating the old one on record, it overwrites the existing address.  Because my organization is in a consortium, we have some rules about keeping old information, so we just want to create a record of the change that was made.  The idea would be to capture the information being changed and create a CSI containing that info so our development team can make a record of the change.

Personally, I don't see any problem with the overwriting changes, but rules are rules and until they change I need to make this system work.

I totally understand the desire to keep a record of prior addresses: this can tell you a lot about a customer.  We have this issue with our NCOA process.  The standard Tessitura NCOA process also overwrites the current address.  With ours I create a new address (if one is supplied) and then inactivate the old addresses.  I also flag them using a special Contact Point Category with Contact Point Purposes like "NCOA Move" and "NCOA Delete".  I think this is superior to a CSI in that you can search for them, and they appear very clearly in the customer record.

Doing it in this case is definitely challenging, though.  Tessitura does provide a mechanism for firing off "triggers" from customer record updates (LP_CUSTOMER_RANK), but I'm pretty sure that fires after the fact, and therefore is useless to you for this purpose.  You could, perhaps, try to reconstruct the old address from T_AUDIT_DETAIL.  Or you could keep a shadow custom table of addresses, updated whenever an address is changed or added.

Ironically, the REST API is probably exactly what you want here, with an interceptor on address updates.

Sorry, TA_AUDIT_TRAIL, not T_AUDIT_DETAIL.

Hi Cody,

The UpdateAddress method of the Soap API has a parameter to save a copy of the address when called. TNEW has a site control named "SaveAddressCopyOnChange" which gets used for that parameter. This may or may not help your situation.

-Sam

Hah!  Or we could read the documentation.  Thanks Sam!  Do you have any details on how the old address is saved, though?  Is it just an inactive address, or set to a specific type?

Unknown said:

Is it just an inactive address, or set to a specific type?

TNEW saves a copy just as it is, including type.

I opened a support ticket open about this once (back in 2014) re: the fact that when "SaveAddressCopyOnChange" is set to True a perfect dupe of each new constituent address is saved, including when a new address is updated from initial, dummy values to a real address, i.e. you wind up with the new address + an inactive dupe of that. I have a sproc that looks for these and deletes them daily...