· 7 years ago · Oct 30, 2018, 05:26 PM
1-- table to store break details
2CREATE TABLE #PositionReconciliationCurrentBreaks
3(
4 TransactionKey INT,
5 BreakOriginationDate DATE,
6 BreakAge DECIMAL (18,0)
7);
8TRUNCATE TABLE #PositionReconciliationCurrentBreaks;
9
10WITH PreviousRecBreaks_CTE -- get breaks from previous rec
11AS
12(
13 SELECT
14 MAX(i.InvestmentID) AS InvestmentID,
15 PRT.PositionDate,
16 PRT.PositionReconciliationDetailKey,
17 MAX(PRT.PositionReconciliationTransactionKey) AS PositionReconciliationTransactionKey,
18 PRT.InvestmentKey,
19 MAX(PRT.GenevaInvestmentKey) AS GenevaInvestmentKey,
20 MAX(PRT.BrokerInvestmentKey) AS BrokerInvestmentKey,
21 PRT.PaymentCurrencyKey,
22 PRT.AccountKey,
23 CASE WHEN MAX(PRT.BreakOriginationDate) = '2100-01-01' THEN NULL ELSE MAX(PRT.BreakOriginationDate) END AS BreakOriginationDate,
24 MAX(PRT.BreakAge) AS BreakAge,
25 SUM(ISNULL(PRT.GenevaQuantity,0)) AS GenevaQuantity,
26 SUM(ISNULL(PRT.BrokerQuantity,0)) AS BrokerQuantity
27 FROM [Fund.Accounting.Model.PositionReconciliation].PositionReconciliationTransaction PRT -- get transactions from previous rec
28 INNER JOIN
29 [Fund.Accounting.Model.PositionReconciliation].PositionReconciliationDetail TRD -- check details to get Action on transaction
30 ON TRD.PositionReconciliationDetailKey = PRT.PositionReconciliationDetailKey -- join on Detail Key
31 inner join [Fund.Accounting.Model.TradeReconciliation].Investment i -- add in investmentID for debugging purpose
32 ON i.InvestmentKey = PRT.InvestmentKey
33 INNER JOIN
34 [Fund.Accounting.Model].[ReconciliationActions] RA -- actions
35 ON RA.ReconciliationActionID=TRD.ReconciliationActionID
36 AND (TRD.PositionCommentKey IS NOT NULL) --approved breaks must have a comment.
37 AND RA.Name <> 'Suppress' -- ensure not suppressed
38 AND RA.Name <> 'Auto Match' -- ensure not perfect match - we do want to include cross references with a qty diff
39 WHERE PRT.PositionReconciliationID = @PreviousPositionReconciliationId -- previous rec
40 GROUP BY -- added grouping for quantity breaks as x-refs will be 2 separate lines in PositionReconciliationTransaction tbl; We group to force this as 1 line
41 PRT.PositionDate,
42 PRT.PositionReconciliationDetailKey,
43 PRT.InvestmentKey,
44 PRT.PaymentCurrencyKey,
45 PRT.AccountKey
46),CurrentRecBreaks_CTE -- get breaks from current rec
47AS
48(
49 SELECT
50 MAX(i.InvestmentID) AS InvestmentID,
51 PRT.PositionDate,
52 PRT.PositionReconciliationDetailKey,
53 MAX(PRT.PositionReconciliationTransactionKey) AS PositionReconciliationTransactionKey,
54 PRT.InvestmentKey,
55 MAX(PRT.GenevaInvestmentKey) AS GenevaInvestmentKey,
56 MAX(PRT.BrokerInvestmentKey) AS BrokerInvestmentKey,
57 PRT.PaymentCurrencyKey,
58 PRT.AccountKey,
59 CASE WHEN MAX(PRT.BreakOriginationDate) = '2100-01-01' THEN NULL ELSE MAX(PRT.BreakOriginationDate) END AS BreakOriginationDate,
60 MAX(PRT.BreakAge) AS BreakAge,
61 SUM(ISNULL(PRT.GenevaQuantity,0)) AS GenevaQuantity,
62 SUM(ISNULL(PRT.BrokerQuantity,0)) AS BrokerQuantity
63 FROM [Fund.Accounting.Model.PositionReconciliation].PositionReconciliationTransaction PRT -- get transactions from previous rec
64 INNER JOIN
65 [Fund.Accounting.Model.PositionReconciliation].PositionReconciliationDetail TRD -- check details to get Action on transaction
66 ON TRD.PositionReconciliationDetailKey = PRT.PositionReconciliationDetailKey -- join on Detail Key
67 inner join [Fund.Accounting.Model.TradeReconciliation].Investment i -- add in investmentID for debugging purpose
68 ON i.InvestmentKey = PRT.InvestmentKey
69 LEFT JOIN
70 [Fund.Accounting.Model].[ReconciliationActions] RA -- actions
71 ON RA.ReconciliationActionID=TRD.ReconciliationActionID
72 AND RA.Name <> 'Suppress' -- ensure not suppressed
73 AND RA.Name <> 'Auto Match' -- ensure not perfect match - we do want to include cross references with a qty diff
74 WHERE PRT.PositionReconciliationID = @PositionReconciliationID -- current rec
75 GROUP BY -- added grouping for quantity breaks as x-refs will be 2 separate lines in PositionReconciliationTransaction tbl; We group to force this as 1 line
76 PRT.PositionDate,
77 PRT.PositionReconciliationDetailKey,
78 PRT.InvestmentKey,
79 PRT.PaymentCurrencyKey,
80 PRT.AccountKey
81)
82INSERT INTO #PositionReconciliationCurrentBreaks
83(
84 TransactionKey,
85 BreakOriginationDate,
86 BreakAge
87)
88SELECT -- get breaks in current rec that also appeared in previous rec
89 C.PositionReconciliationTransactionKey
90 ,ISNULL(P.BreakOriginationDate,@PreviousPositionReconciliationPeriod) --set ro previous date if it is first time presence.
91 ,CASE DATEDIFF(DAY, P.PositionDate, C.PositionDate)
92 WHEN 0 THEN 1 --set to 1 day old, if it is 1st time
93 ELSE ISNULL(P.BreakAge,0) + DATEDIFF(DAY, P.PositionDate, C.PositionDate) -- get no. of days difference between current rec and previous one
94 END AS BreakAge
95FROM PreviousRecBreaks_CTE P
96INNER JOIN
97 CurrentRecBreaks_CTE C
98 ON P.AccountKey = C.AccountKey
99 AND P.PaymentCurrencyKey=C.PaymentCurrencyKey
100 AND P.InvestmentKey=C.InvestmentKey
101 --AND (P.GenevaInvestmentKey=C.GenevaInvestmentKey
102 -- OR P.BrokerInvestmentKey=C.BrokerInvestmentKey)
103 AND P.GenevaQuantity=C.GenevaQuantity
104 AND P.BrokerQuantity=C.BrokerQuantity
105WHERE NOT EXISTS(
106 -- exclude breaks that are now perfect matches (or suppressed???)
107 SELECT
108 D.PositionReconciliationDetailKey
109 FROM
110 [Fund.Accounting.Model.PositionReconciliation].PositionReconciliationDetail D
111 INNER JOIN -- join on AggregatePositionReconciliationTransaction so we can get the Qty Diff value
112 [Fund.Accounting.Facade.PositionReconciliation].[AggregatePositionReconciliationTransaction] (@PositionReconciliationID,NULL,NULL) A
113 ON A.PositionReconciliationDetailKey = D.PositionReconciliationDetailKey
114 WHERE D.PositionReconciliationID = @PositionReconciliationID -- current rec
115 AND A.QuantityDifference = 0 -- perfect matches
116 AND D.PositionReconciliationDetailKey = C.PositionReconciliationDetailKey -- join details to transactions
117);