Hi,
Did a bit of a documentation trawl on this one but couldn't easily find the answer I'm looking for:
Where would I go to look up the particular schedule that has been used on an order? So in other words, is there a table that links up orders with the schedule id from TR_BILLING_SCHEDULE?
Cheers,
Matt
Hi Matt,
This data is stored in T_ORDER_SCHEDULE.
From the Table Structures doc:
The T_ORDER_SCHEDULE table is the record of the payment plan on for the order.
1.1. Columns:
1.1.1. order_no int NOT NULL
1.1.2. order_sch_no int NOT NULL
1.1.3. sch_type int NOT NULL (always 1 for order schedules; 2 indicates schedule for an invoice in which case order_no represents a payment_no)
1.1.4. amt_due money NULL
1.1.5. due_dt datetime NULL
1.1.6. billing_type int NULL (from tr_billing_type)
1.1.7. mir_lock int NOT NULL
1.1.8. created_by varchar (8) NULL
1.1.9. create_dt datetime NULL
1.1.10. create_loc varchar (16) NULL
Cheers
Kim
Thanks for that, Kim. I came across that table already, and it will show you a list of the orders with schedules, but what I was curious was whether there was a link between that and the actual name of the Payment Plan in TR_BILLING_SCHEDULE.
So, for instance, if id = 9 in TR_BILLING_SCHEDULE is called Four Monthly Payments, is there something that says you used id = 9 when generating the order.
In T_ORDER_SCHEDULE, the only thing they seem to have is sch_type, but that only has a value of 1 or 2. So I'm not sure if Tessitura actually stores that particular schedule. Is it possible they just use the schedule to calculate the payments and then make a record of the payments rather than the payment itself?
Matt,
There is no link between TR_BILLING_SCHEDULE and T_ORDER_SCHEDULE. The billing schedules only act as templates for ease of entry and are not attached to the order.
Best,
Anna
Ah, that's what I was afraid you would say ... Thus why our custom report that looks at payment plans is so long and involved ... Anyway, thanks for your help everyone.
Matthew what kind of custom report do you currently have? I am trying to get something here that helps us keep track of payment plans. Would you mind sharing?