...
Issue
Tradefwd (tradefwdcancel) transaction is used in 2 situations:
To open a forward
To close a forward prior to settlement date.
The issue is cashdbo.cash_activity.performance_flow_descr descr gets populated based on the trans_type with either a value of “positive”, “negative” or “no flow”. Currently, for trans_type tradefwd (tradefwdcancel) . the , the performance_flow_descr is is set to “POSITIVE” . This is appropriate for situation #1 when a forward is opened. However, if the tradefwd is posted to close the forward, the performance_flow_descr should be “NEGATIVE”.
...
Solution
To identify or distinguish when a tradefwd (tradefwd cancel) is used to close a forward from when it is used to open, and then change the performance_flow_descr to “NEGATIVE” “NEGATIVE” value when it is used to close (situation #2).
The field that identifies this situation is tradesdbo.trade.record_type. For transactions with a transaction type or code tradefwd (tradefwdcancel), and tradesdbo.trade.record_type value of “180” or “280, set cashdbo.performance_flow_descr to “ NEGATIVE”.
This change affects cash activity records. Performance Flow Desc Update exporter (Eagle Pace) seems to lend itself to this sort of logic. In reviewing the procedure , it highlighted some other changes related to data logic, parameters, and when it should be run, that I will include here as well.
...
Request
Modify the Performance Flow Desc Update Exporter (PFD) (Eagle Pace)
For transactions with tradesdbo.trade.record_type of 180 or 280 and transaction type of tradefwd or tradefwdcancel change the cashdbo.performance_flow_descr to “NEGATIVE”
Below is a query that identifies records for reference.Code Block language sql SELECT C.* , T.RECORD_TYPE
...
FROM CASHDBO.CASH_ACTIVITY C, TRADESDBO.TRADE T
...
WHERE C.TRANS_TYPE IN ('TRADEFWD', 'TRADEFWDCANCEL')
...
AND T.RECORD_TYPE IN ('180', '280')
...
AND T.POSITION_ID = C.POSITION_ID
...
AND T.SECURITY_ALIAS = C.SECURITY_ALIAS
...
AND C.SRC_INTFC_INST = '14'
Source: STARDIRECT
Entities: Should impact both Mutual Fund and Institutional AccountsDate Logic: The date logic in this exporter should mirror that logic recently updated in the Cash Performance exporter procedure (cpf_eagle_pace_exp_sp). Sql statement included below for reference. Mark Tedesco can coordinate on the date logic to ensure it aligns with recent changes. New date logic:
Code Block language sql BEGIN
...
SELECT CURR_DT
...
INTO L_Post_Date
...
FROM RULESDBO.PROCESS_CENTER
...
WHERE PROCESS_CENTER_DESC = 'PRODUCTION PROCESS CENTER';
...
EXCEPTION
...
WHEN No_Data_Found THEN
...
L_Post_Date := NULL;
...
END;
...
END IF;
...
--Default to SYSDATE if Process Center date not fonund
...
IF (L_Post_Date IS NULL) THEN
...
L_Post_Date := TRUNC(SYSDATE);
...
END IF;
...
BEGIN
...
SELECT Max(business_date) INTO L_Prev_Buisness_date
...
FROM PACE_MASTERDBO.BUSINESS_CALENDAR
...
WHERE business_date < L_Post_Date
...
AND DAILY_FLAG = '1';
...
END;
...
L_Post_Date := L_Prev_Buisness_date;
...
/////////
...
WHERE (CA.POST_DATE >= L_Post_Date)
Ensure procedure runs in the overnight and intraday cycle
clean up script required for existing records