Order Date Frozen after upgrading to 8.0

Hi Brain,

On "custom data" tab in "order details" there is "Renewal order dt" field.

this field matches up custom_7 in t_order table.

we have a sql job to update the special order date.

(Is there anybody can post a picture in the post?

please show me if you can)

DECLARE @order_dt datetime,
        @customer_no INT,
         @order_no INT

DECLARE cur0 CURSOR for

select distinct custom_7, customer_no, order_no from T_ORDER
where order_dt > '2008-09-29 00:00:00.000' and custom_7 is not NULL

open cur0
fetch next from cur0 into @order_dt, @customer_no, @order_no

WHILE @@FETCH_STATUS = 0
BEGIN

Update T_ORDER
set order_dt = @order_dt
where customer_no = @customer_no
and order_no = @order_no

 fetch next from cur0 into @order_dt, @customer_no, @order_no
END

close cur0
deallocate cur0

Thanks Ben,

Does this run as is, or does it need modification? (I know enough SQL to get myself in trouble) What exactly will it do? Reset the order dates?

Brian,

I will respond as it is a four day weekend in Australia.

The Melbourne Theatre Company has a fairly specific way of handling special seating needs and VIP ticketing exceptions on the web as they are using auto seating to seat the web subscription requests.  As they implemented this in 7.5 the premise was based on artificially updating the order dates over the web for the orders which should be filtered out of the auto seating process. 

Once the bug was fixed for the order date in v8.0 and the operators could not update the order date they implemented an additional custom order date field (custom_7 in T_ORDER).  Once the special needs and VIP orders have been seated the operator updates the custom order date and the script Ben provided is scheduled to run regularly to update the orders.

If you have a process in place which requires updating the order date subsequent to the order being placed I would recommend contacting support for advice as to how to address this issue as it is not a a standard use.  The support staff can assist in evaluating the best way to proceed. 

Best,

Anna

Anna E. Wessely

Manager Asia Pacific/Senior Application Specialist

Tessitura Network, Inc

+1 888 643 5778 x 308

awessely@tessituranetwork.com

www.tessituranetwork.com

Nothing magic.

I created a stored procedure attached with this post. it has two parameters.

it only do one thing:update the order_dt with customer_7.

you can set @myDate ='2009-04-22'

set @dayDifference=180 (about six month).

then it will only process last six month orders.

If you are not sure what you are doing, as Anna said ask network for help, also you can send me an email.

Have fun.

:-):-):-)

USE

[impresario]

procedure LP_UPDATE_ORDER_DATE

@myDate

datetime,

@dayDifference

int

as

declare @dateForwards datetime

set @dateForwards=@myDate-@dayDifference

DECLARE @order_dt datetime

DECLARE @order_no INT

DECLARE cur0 CURSOR for

---pick up orders with certain order_dt and customer_7 has value.

select custom_7, order_no from T_ORDER

where order_dt > @dateForwards and custom_7 is not NULL

open cur0

fetch next from cur0 into @order_dt, @order_no

WHILE @@FETCH_STATUS = 0

BEGIN

---update t_order table set order_dt = customer_7

Update T_ORDER

set order_dt = @order_dt

where order_no = @order_no

fetch next from cur0 into @order_dt, @order_no

END

close cur0

deallocate cur0

Nothing magic.

I created a stored procedure attached with this post. it has two parameters.

it only do one thing:update the order_dt with customer_7.

you can set @myDate ='2009-04-22'

set @dayDifference=180 (about six month).

then it will only process last six month orders.

If you are not sure what you are doing, as Anna said ask network for help, also you can send me an email.

Have fun.

:-):-):-)

USE

[impresario]

procedure LP_UPDATE_ORDER_DATE

@myDate

datetime,

@dayDifference

int

as

declare @dateForwards datetime

set @dateForwards=@myDate-@dayDifference

DECLARE @order_dt datetime

DECLARE @order_no INT

DECLARE cur0 CURSOR for

---pick up orders with certain order_dt and customer_7 has value.

select custom_7, order_no from T_ORDER

where order_dt > @dateForwards and custom_7 is not NULL

open cur0

fetch next from cur0 into @order_dt, @order_no

WHILE @@FETCH_STATUS = 0

BEGIN

---update t_order table set order_dt = customer_7

Update T_ORDER

set order_dt = @order_dt

where order_no = @order_no

fetch next from cur0 into @order_dt, @order_no

END

close cur0

deallocate cur0