USP_SALESORDER_DISTRIBUTEITEMFEES

CREATE procedure dbo.USP_SALESORDER_DISTRIBUTEITEMFEES
(
    @ORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null
)
as begin

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if @CURRENTDATE is null
        set @CURRENTDATE = getdate();

    -- SALESORDERITEM records for fees have QUANTITY = 1 and PRICE = (ticket quantity * fee amount),

    -- so we'll need to divide their price by the number of tickets to get the per-ticket amount.

    -- Also: if some subset of these tickets with fees were refunded before 2014 S1, the fees went with them.

    -- We need to set the REFUNDEDAMOUNT to the full per-ticket fee amount in those cases.

    insert into dbo.SALESORDERITEMTICKETFEE
        (ID, SALESORDERITEMFEEID, TICKETID, AMOUNT, REFUNDEDAMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select
        newid(),
        FEE.ID,
        TICKET.ID,
        FEEITEM.PRICE / TICKETITEM.QUANTITY, -- Divide fee by original quantity for per-ticket amount

        case
            when TICKET.ISREFUNDED = 0 then 0.0
            else FEEITEM.PRICE / TICKETITEM.QUANTITY
        end,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from dbo.SALESORDERITEM FEEITEM
    inner join dbo.SALESORDERITEMFEE FEE on FEE.ID = FEEITEM.ID
    inner join dbo.SALESORDERITEM TICKETITEM on TICKETITEM.ID = FEE.SALESORDERITEMID
    inner join dbo.TICKET on TICKET.SALESORDERITEMTICKETID = TICKETITEM.ID
    left join dbo.SALESORDERITEMTICKETFEE TICKETFEE on TICKETFEE.SALESORDERITEMFEEID = FEE.ID
    where FEEITEM.SALESORDERID = @ORDERID
        and TICKETFEE.ID is null;  -- Only distribute fees that haven't been yet


end