LP_CUSTOMER_RANK

Since LP_CUSTOMER_RANK is designed for custom code, I would suspect that something has changed with the custom code or data structures they rely on that is causing it to fail.  Since it is connected to a trigger on pretty much any update to a customer record, LP_CUSTOMER_RANK scripts should generally be designed to fail silently, so you should probably troubleshoot the code in question in isolation to the procedure.

This is assuming you haven't been able to conclude via logs or traces that LP_CUSTOMER_RANK absolutely isn't firing.  In that case I'd suggest a TASK ticket.

The plot thickens... or thins, depending on your point of view.

This is the statement that is stopping procedure from working. Is it needed? When would a customer number ever be null?

IF @customer_no is null
BEGIN
EXEC AP_SET_CONTEXT 'LockTrigger', 'N'
RETURN
END

Everything works as expected when I comment this block out. Thoughts? Will I cause database Armageddon if I leave it commented out? Obviously I'm doing this in Test, but it's hard to simulate reality in a Test system that's only got a few users in it at a time.

Thanks!

If commenting out that block is solving your problem, then that means that customer_no IS null whenever the procedure is failing for you!

If it wasn't null, that block wouldn't be executing anyway.

The LockTrigger context flag is used to bypass table triggers for some tables like T_ADDRESS, T_EADDRESS, T_PHONE and a few others. Any procedure or script that sets it to 'Y' should set it back to 'N' when it's done if there is any other work to be done in the database connection.

It's use here is a little confusing since out of the box in 12.5.1, (and as far as I can tell -- someone please correct me) LP_CUSTOMER_RANK is only called by the update trigger on T_CUSTOMER, and that trigger does not check LockTrigger at all. So LockTrigger here may be a red herring, and the real problem is why is your @customer_no variable NULL, causing the procedure to return early!?

If you upload or include the full SQL script I might be able to help further. You might also want to check the TG_CUSTOMER_UPDATE trigger, since that should be what calls LP_CUSTOMER_RANK with the populated @customer_no parameter.

Hi Nick,

Here's the full script. As with the majority of the custom procedures in our database, I inherited this - so it's always a voyage of discovery when something stops working.

Thank you for your help!

--Stacey

RETURN
EXEC AP_SET_CONTEXT 'LockTrigger', 'Y'

--this is the statement that appears to be stopping procedure from working. Is it needed? When would a customer number ever be null?
IF @customer_no is null
BEGIN
EXEC AP_SET_CONTEXT 'LockTrigger', 'N'
RETURN
END

declare @ranking int
--select @ranking = 0

declare @rank_type int
select @rank_type = 1 -- Other rank types can be added

select @ranking = CASE WHEN ISNULL(sum(1000 - b.rank), 0) < 0 
THEN 0
ELSE ISNULL(sum(1000 - b.rank), 0) END 
from vx_const_cust_active a
JOIN tr_constituency b ON a.constituency = b.id
where customer_no = @customer_no

--select @ranking

delete t_cust_rank
where customer_no = @customer_no and
rank_type = @rank_type

IF @ranking > 0 -- 0 is the default ranking so we don't need to write these records
insert t_cust_rank(customer_no, rank_type, rank)
select @customer_no, @rank_type, @ranking

declare @auto_address_keyword int
set @auto_address_keyword = 319

declare @update_address int
set @update_address = 1 -- assume we update

declare @original_street1 nvarchar(1024)
declare @original_street2 nvarchar(1024)
declare @original_street3 nvarchar(1024)
declare @original_city nvarchar(1024)
declare @new_street1 nvarchar(1024)
declare @new_street2 nvarchar(1024)
declare @new_street3 nvarchar(1024)
declare @new_city nvarchar(1024)
declare @address_no int

if exists ( select *
from tx_cust_keyword
where customer_no = @customer_no
and keyword_no = @auto_address_keyword
and key_value = 'No'
)
set @update_address = 0 -- no auto update

if @update_address = 1
begin -- get the street address and address number
select
@address_no = address_no,
@original_street1 = street1,
@original_street2 = street2,
@original_street3 = street3,
@original_city = city
from t_address
where customer_no = @customer_no and primary_ind = 'Y'

if @original_street1 is null -- null address, don't bother
set @update_address = 0
end

if @update_address = 1
begin -- compute new address
set @new_street1 = dbo.lf_titlecase (@original_street1, 'street')
set @new_street2 = dbo.lf_titlecase (@original_street2, 'street')
set @new_street3 = dbo.lf_titlecase (@original_street3, 'street')
set @new_city = dbo.lf_titlecase (@original_city, 'city')

-- if no change, no update
if @new_street1 = @original_street1 and @new_street2 = @original_street2 and @new_street3 = @original_street3 and @new_city = @original_city
set @update_address = 0
end

if @new_street1 is null
set @update_address = 0

if @update_address = 1 -- do we need to update?
begin
update t_address
set street1 = @new_street1,
street2 = @new_street2,
street3 = @new_street3,
city = @new_city
where address_no = @address_no
end

GO

Based on Caryl Jones' suggestions, I moved things around so that 

IF @customer_no is null
RETURN

is at the very beginning; followed by

EXEC AP_SET_CONTEXT 'LockTrigger', 'Y'

and 

EXEC AP_SET_CONTEXT 'LockTrigger', 'N' 

is at the very end of the procedure.

This seems to have solved the problem....

Now I need to go learn about triggers so I'm not fumbling next time this happens! Thank you all.

I think I remember seeing null customer numbers when orders were done at door to general public or via TNEW guest checkout. Please correct me if I'm wrong though.

That script looked like you had an uncommented RETURN statement right at the top! The procedure would have been exiting right there without running anything. (If you ever need to provide a script in the future, it's also a good idea to start with the ALTER PROCEDURE line so that the parameter definition is included.)

Glad your fix worked!

Oh yeah - I can see how that would be a problem! The mystery is how it got there...

I really appreciate everyone's help. It's priceless!