Trying to write a procedure that can tell the difference between a renewed package and a new. The first given is that T_Order.Accepting_Rollovers = 'Y'.
I start running into issues where customers have one package say "Dance" that is rolled over and then add a "Jazz". The count should be 1 renewal and 1 new.
Now I can compare the dates between t_lineitem.create_dt and T_order.create_dt and if they match say down to the minute then one can assume that that lineitem is a rollover. Issue with this logic is if the customer rolled over a dance and a jazz where the rollovers for each package were done separately but added to the same order. The two dates would be at least several minutes apart.
Found the tx_ro_log table which gave me some hope where I can join on li_seq_no except that if the customer has 1 dance package and decides to add another. We would go to the original lineitem and change the seats to 2 which would make the new one look like a renewal.
Anybody else have a better way of figuring this out?
Thanks for the help.
I built a report, and change how I looked at the create date between Lineitem and order from the minute to several hours. The chance of us selling a subscription within a couple of hours of doing a rollover I thought was slim. I had one area that bit me. That if I had to cancel a rollover due to a setup issue. When I did the rollover again, the time fell outside of my range so they got counted as new. I had to hard code the time difference for that one package compared to the rest.
With that being said, we decided to use price types this year to determine New vs Renew and will be scrapping the old report. I think with price types, it will be easier to report on the packages.
Marty
Thanks for the info. We are toying around with building a report based off the log table like you were initially thinking.