FinancialModelling/Files/AR Scripts/05_FX_Revaiulation.sql at main · ProdataSQL/FinancialModelling · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
/* =========================
   DROP + CREATE TABLE
   ========================= */

DROP TABLE IF EXISTS FXRevaluation;

CREATE TABLE FXRevaluation
(
    EntryID INT IDENTITY(1,1)
  , InvoiceID INT
  , TransactionDate DATE
  , TransactionType VARCHAR(20)   -- REVAL / REVERSAL
  , CurrencyCode CHAR(3)
  , OpenLocal DECIMAL(18,2)
  , FXRate_Invoice DECIMAL(18,6)
  , FXRate_Current DECIMAL(18,6)
  , UnrealisedEUR DECIMAL(18,2)
);


/* =========================
   INSERT REVALUATION (MONTH-END)
   ========================= */

;WITH MonthEnd AS
(
    SELECT DISTINCT EOMONTH(InvoiceDate) AS RevalDate
    FROM Invoice
)
, Paid AS
(
    SELECT
        pa.InvoiceID
      , p.PaymentDate
      , SUM(pa.AppliedLocal) AS PaidLocal
    FROM PaymentAllocation pa
    JOIN Payment p
        ON p.PaymentID = pa.PaymentID
    GROUP BY
        pa.InvoiceID
      , p.PaymentDate
)
, OpenBal AS
(
    SELECT
        i.InvoiceID
      , i.CurrencyCode
      , i.FXRate AS FXRate_Invoice
      , m.RevalDate
      , i.AmountLocal
        - ISNULL(SUM(CASE
            WHEN p.PaymentDate <= m.RevalDate THEN p.PaidLocal
            ELSE 0 END), 0) AS OpenLocal
    FROM Invoice i
    CROSS JOIN MonthEnd m
    LEFT JOIN Paid p
        ON i.InvoiceID = p.InvoiceID
    WHERE i.InvoiceDate <= m.RevalDate
    GROUP BY
        i.InvoiceID
      , i.CurrencyCode
      , i.FXRate
      , i.AmountLocal
      , m.RevalDate
)

INSERT INTO FXRevaluation
(
    InvoiceID
  , TransactionDate
  , TransactionType
  , CurrencyCode
  , OpenLocal
  , FXRate_Invoice
  , FXRate_Current
  , UnrealisedEUR
)
SELECT
    o.InvoiceID
  , o.RevalDate
  , 'REVAL'
  , o.CurrencyCode
  , o.OpenLocal
  , o.FXRate_Invoice
  , fx.EURRate
  , (o.OpenLocal * fx.EURRate)
    - (o.OpenLocal * o.FXRate_Invoice)
FROM OpenBal o
JOIN FXRate fx
    ON fx.RateDate = o.RevalDate
   AND fx.CurrencyCode = o.CurrencyCode
WHERE o.OpenLocal > 0;


/* =========================
   INSERT REVERSAL (NEXT DAY)
   ========================= */

INSERT INTO FXRevaluation
(
    InvoiceID
  , TransactionDate
  , TransactionType
  , CurrencyCode
  , OpenLocal
  , FXRate_Invoice
  , FXRate_Current
  , UnrealisedEUR
)
SELECT
    InvoiceID
  , DATEADD(DAY, 1, TransactionDate)
  , 'REVERSAL'
  , CurrencyCode
  , OpenLocal
  , FXRate_Invoice
  , FXRate_Current
  , -UnrealisedEUR
FROM FXRevaluation
WHERE TransactionType = 'REVAL';