• Replies 7 replies
  • Subscribers 336 subscribers
  • Views 175 views
  • Users 0 members are here
Related

Finding Exchanges

Gawain Lavers
$organization

Is there a good way of finding exchanges in orders?  Since we allow exchanges in order and also for returns (for certain customers) to be held on account for future orders, I assume I'll also have to track money on account payments as well, but I don't know how to detect that an exchange has happened in an order.

Parents
  • $organization

    To find the orders with returns held on account, you could use the payment method and sub line item status to find some of the orders you are looking for.

    Something like:

    USE impresario
    GO
    ;
    DECLARE @Begin_dt DATETIME = '2019-01-06 00:00:00'
    DECLARE @End_dt DATETIME = '2019-01-07 00:00:00'
    ;
    SELECT DISTINCT SLI.order_no, SLI.sli_status, P.pmt_method, P.pmt_amt
    FROM T_PAYMENT AS P
    INNER JOIN T_TRANSACTION AS T ON T.transaction_no = P.transaction_no
    INNER JOIN T_ORDER AS O ON O.order_no = T.order_no
    INNER JOIN T_SUB_LINEITEM AS SLI ON SLI.order_no = O.order_no
    WHERE P.pmt_method IN (52,107)
    --Your On Account payment method(s).
    AND P.pmt_amt < 0
    AND SLI.sli_status IN (4)
    --status of 4 = returned.
    AND O.order_dt BETWEEN @Begin_dt AND @End_dt
    ;

    Or maybe find a few examples and PORD a few of the order_no's to help develop a strategy?

    It does not sound like a fun project, most likely a bunch of different situations to factor in.

Reply
  • $organization

    To find the orders with returns held on account, you could use the payment method and sub line item status to find some of the orders you are looking for.

    Something like:

    USE impresario
    GO
    ;
    DECLARE @Begin_dt DATETIME = '2019-01-06 00:00:00'
    DECLARE @End_dt DATETIME = '2019-01-07 00:00:00'
    ;
    SELECT DISTINCT SLI.order_no, SLI.sli_status, P.pmt_method, P.pmt_amt
    FROM T_PAYMENT AS P
    INNER JOIN T_TRANSACTION AS T ON T.transaction_no = P.transaction_no
    INNER JOIN T_ORDER AS O ON O.order_no = T.order_no
    INNER JOIN T_SUB_LINEITEM AS SLI ON SLI.order_no = O.order_no
    WHERE P.pmt_method IN (52,107)
    --Your On Account payment method(s).
    AND P.pmt_amt < 0
    AND SLI.sli_status IN (4)
    --status of 4 = returned.
    AND O.order_dt BETWEEN @Begin_dt AND @End_dt
    ;

    Or maybe find a few examples and PORD a few of the order_no's to help develop a strategy?

    It does not sound like a fun project, most likely a bunch of different situations to factor in.

Children
No Data