Is there a straightforward way by which I can determine whether a specific kind of item (say a package) was wholly or in part exchanged vs refunded? Let's say I care about Package A. One customer has this order:
Package A
Perf A1
Perf A2
Performance B
Fee C
They are refunded for Perf A1, and have Perf A2 exchanged for a ticket to Perf A3 and some money on account. My usual method of screening returns out of my queries (where sli_status not in (4,7,8,13)) will remove all T_SUB_LINEITEM rows for Package A, but I want to be able to tell the difference between the above order, and a second order:
Package A (Perf A1, Perf A2)
Where the customer is refunded for both Perfs A1 and A2 (no exchanges, no money transferred to an On Account payment type).
The problem is that this query would then wind up in an SP running in LP_CUSTOMER_RANK, so if it is too involved and slow then I'll have to find some other method of making this distinction.