FinancialModelling/Files/AR Scripts/06_ARTransactions.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
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
DROP TABLE IF EXISTS ARTransaction;

CREATE TABLE ARTransaction
(
    ARTransactionID INT IDENTITY(1,1)
  , InvoiceID INT
  , CustomerID INT
  , DocumentNo varchar(50)
  , PostedDate DATE
  , TransactionType VARCHAR(30)
  , PostedType VARCHAR(10)
  , CurrencyCode CHAR(3)

  , AmountLocal DECIMAL(18,2)      -- real transaction currency
  , FXRate      DECIMAL(18,6)      -- rate used to derive EUR
  , AmountEUR   DECIMAL(18,2)      -- stored reporting value
  , CONSTRAINT PK_ARTransaction PRIMARY KEY (ARTransactionID)
);


-- 1. Invoice
INSERT INTO ARTransaction
SELECT
    i.InvoiceID
  , i.CustomerID
  , i.InvoiceNo
  , i.InvoiceDate
  , 'INVOICE'
  , 'AR'
  , i.CurrencyCode
  , i.AmountLocal
  , i.FXRate
  , i.AmountLocal * i.FXRate
FROM Invoice i;

--2/ payments and Allocation
INSERT INTO ARTransaction
SELECT
    i.InvoiceID
  , i.CustomerID
  , p.PaymentNo
  , p.PaymentDate
  , 'PAYMENT'
  , 'AR'
  , p.CurrencyCode
  , -pa.AppliedLocal
  , p.FXRate
  , -pa.AppliedLocal * p.FXRate   -- 🔥 CRITICAL FIX (payment rate)
FROM PaymentAllocation pa
JOIN Payment p ON p.PaymentID = pa.PaymentID
JOIN Invoice i ON i.InvoiceID = pa.InvoiceID;

--Credit Notes
INSERT INTO ARTransaction
SELECT
    c.InvoiceID
  , i.CustomerID
  , 'CR-' + convert (varchar, c.InvoiceID)
  , c.CreditDate
  , 'CREDIT_NOTE'
  , 'AR'
  , i.CurrencyCode
  , -c.AmountLocal
  , c.FXRate
  , -c.AmountLocal * c.FXRate
FROM CreditNote c
JOIN Invoice i ON i.InvoiceID = c.InvoiceID;

--Bad Debt

INSERT INTO ARTransaction
SELECT
    b.InvoiceID
  , i.CustomerID
  , 'JNBD-' + convert (varchar,b.InvoiceID)
  , b.WriteOffDate
  , 'BAD_DEBT'
  , 'AR'
  , i.CurrencyCode
  , -b.AmountLocal
  , b.FXRate
  , -b.AmountLocal * b.FXRate
FROM BadDebt b
JOIN Invoice i ON i.InvoiceID = b.InvoiceID;


--FX
INSERT INTO ARTransaction
SELECT
    r.InvoiceID
  , i.CustomerID
   , 'JNFXR1-' + convert (varchar,r.InvoiceID)
  , r.TransactionDate
  , 'FX_REVAL'
  , 'AR'
  , r.CurrencyCode
  , null
  , null
  , r.UnrealisedEUR
FROM FXRevaluation r
JOIN Invoice i ON i.InvoiceID = r.InvoiceID
WHERE r.TransactionType = 'REVAL';

INSERT INTO ARTransaction
SELECT
    r.InvoiceID
  , i.CustomerID
   , 'JNFXR2-' + convert (varchar,r.InvoiceID)
  , r.TransactionDate
  , 'FX_REVERSAL'
  , 'AR'
  , r.CurrencyCode
  , null
  , null
  , - r.UnrealisedEUR
FROM FXRevaluation r
JOIN Invoice i ON i.InvoiceID = r.InvoiceID
WHERE r.TransactionType = 'REVERSAL';


--Realised FX Gains
INSERT INTO ARTransaction
SELECT
    i.InvoiceID
  , i.CustomerID
  , 'JNFX-' + convert (varchar,i.InvoiceID)
  , p.PaymentDate
  , 'FX_REALISED'
  , 'AR'
  , p.CurrencyCode
  , null
  , null                                         -- already EUR value
  , pa.AppliedLocal * (p.FXRate - i.FXRate)   -- FX difference
FROM PaymentAllocation pa
JOIN Payment p ON p.PaymentID = pa.PaymentID
JOIN Invoice i ON i.InvoiceID = pa.InvoiceID
WHERE ABS(p.FXRate - i.FXRate) > 0.000001;