...

Issue

Tradefwd (tradefwdcancel) transaction is used in 2 situations:

...

The issue is cashdbo.cash_activity.performance_flow_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  performance_flow_descr 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”  value when it is used to close (situation #2).

...

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)

1. 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 Accounts

2. Date 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:BEGIN
   SELECT

   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)

   
   

3. Ensure procedure runs in the overnight and intraday cycle

4. clean up script required for existing records