-------------------------------------------------------------------------------
--Description   :5. 2004.3.8 changes since 2004.3.7.sql
--Purpose       :Customer Payment Allocation Tables fixes
--Functionality :1. Assign AS_Transactions_Transactions.Amount = AS_Transactions.Total_Amount
--              :        WHERE Sys_AW_Transaction_Types_ID = 7 (POS Payments)
--              :2. Assign AS_Transaction_Payments.Amount = AS_Transactions.Total_Amount
--              :        WHERE Sys_AW_Transaction_Types_ID = 7 (POS Payments)
--              :           OR Sys_AW_Transaction_Types_ID = 21 (BOF Payments)
--              :           OR Sys_AW_Transaction_Types_ID = 22 (EFT Payments)
--Date          :2008/05/29
--Authors       :TB
--2004.3.8      :31/05/08 - Replaced errorneous ABS(@TotalAmount) insert into
--              :           AS_Transactions_Transactions with -@TotalAmount to insert
--              :           the SAME value as inserted in AS_Transactions for
--              :           TranType 7 (POS Payments). ABS(@TotalAmount) inserted
--              :           the SAME SIGNED value for POSITIVE AND NEGATIVE payments !!!
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- 1. AS_Transactions_Transactions - ALL POS PAYMENTS were POSITIVE AMOUNTS
-------------------------------------------------------------------------------

SET NOCOUNT ON

BEGIN TRANSACTION

    UPDATE b
    SET b.Amount = CASE WHEN a.Total_Amount < 0                -- POS PAYMENT
                        THEN ROUND(ABS(b.Amount) * -1, 2)      -- THEN PAYMENT S/B NEGATIVE
                        ELSE ROUND(ABS(b.Amount), 2)           -- ELSE PAYMENT S/B POSITIVE
                   END
    FROM AS_Transactions a
        INNER JOIN AS_Transactions_Transactions b
            ON b.Destination_AS_Transactions_ID = a.AS_Transactions_ID
    WHERE  a.Sys_AW_Transaction_Types_ID = 7

COMMIT TRANSACTION

-------------------------------------------------------------------------------
-- 2. AS_Transaction_Payments - EVERY PAYMENT AMOUNT is the OPPOSITE of what it SHOULD BE
-------------------------------------------------------------------------------

BEGIN TRANSACTION

    UPDATE b
    SET b.Amount = CASE WHEN a.Total_Amount < 0                -- ALL PAYMENTS
                        THEN ROUND(ABS(b.Amount) * -1, 2)      -- THEN PAYMENT S/B NEGATIVE
                        ELSE ROUND(ABS(b.Amount), 2)           -- ELSE PAYMENT S/B POSITIVE
                   END
    FROM AS_Transactions a
        INNER JOIN AS_Transaction_Payments b
            ON b.AS_Transactions_ID = a.AS_Transactions_ID
    WHERE (a.Sys_AW_Transaction_Types_ID = 7 OR
           a.Sys_AW_Transaction_Types_ID = 21 OR
           a.Sys_AW_Transaction_Types_ID = 22)

COMMIT TRANSACTION

-------------------------------------------------------------------------------