Hi everyone,
I’m adding some logic to our LP_CUSTOMER_RANK procedure (for rankings) however I can’t seem to get the rankings to be added when it’s the current transaction that would give them the rank.
Example: A non-member purchases a membership, so I want them to get the appropriate Member ranking as soon as they finalize the purchase of the membership. However, the ranking is only added if I make subsequent updates to the account (anything that would trigger LP_CUSTOMER_RANK).
I am basing my ranking logic off of the following SQL:
If @customer_no in
(
select distinct customer_no
from TX_CUST_MEMBERSHIP
where memb_org_no = '1' --PT Partners
and current_status = '2' --active
)
Begin
…….Rest of procedure here…..
It would appear that the membership transaction has not been committed to TX_CUST_MEMBERSHIP and therefore the customer_no is not included in the list that should be updated.
I feel like I have missed something really simple here, any advice much appreciated!
Have a great weekend all,
Natasha
I have a job that runs the customer ranking procedure every hour. But that doesn’t help in the situation you are describing. We added code to our site to check the cart for a membership and if it is there to give the appropriate discount.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Natasha Purkiss Sent: Friday, November 22, 2013 6:11 PM To: Gloria Ormsby Subject: [Tessitura Technical Forum] Effect of current transaction on LP_CUSTOMER_RANK
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Thanks Gloria - we are planning on what you suggested re: the website, but I was hoping that phone and in person transactions could also be instant.
If anyone has managed to accomplish this, please let me know!
Hi Natasha
Does your membership have an associated constituency?
Adding the constituency updates t_customer.last_activity_dt, which should trigger LP_CUSTOMER_RANK to run, in case that's the problem.
Ken
Hi Ken,
Yep, we have a constituency that is added when a membership is purchased, so I switched the IF statement to look at TX_CONST_CUST instead of TX_CUST_MEMBERSHIP. It did recognize the customer as a member this time (hooray!), however because I still need to refer to the specific membership level in TX_CUST_MEMBERSHIP to determine the rank value, it still won’t update when the current transaction is the one that would make it eligible for the rank.
I thought that LP_CUSTOMER_RANK was triggered by any activity on the account, is this not the case? I might run a trace to see the order in which things happen.
Thanks for your help,
It looks like LP_CUSTOMER_RANK is called both before and after data is inserted into TX_CUST_MEMBERSHIP so I'm a little baffled. I think I might put in a ticket on this one.