SELECT A.ACCTNO,
    A.IUBC,
    MIN (A.ACCT_DT),
    NULL AS RPT_ACCT_NO,
    A.SID,
    A.FTC,
    CASE
      WHEN MAX (C.FTC_GEN_FL) = 'N'
      THEN SUM (
        CASE
          WHEN A.ACCT_DT = ACTRL.PRR_VAL_EFF_DT
          THEN A.TRANS_AMT
          ELSE 0
        END)
      ELSE 0
    END AS O_BALANCES,
    CASE
      WHEN MAX (C.FTC_GEN_FL) = 'N'
      THEN SUM (
        CASE
          WHEN A.ACCT_DT = TEMP.END_DATE
          THEN A.TRANS_AMT
          ELSE 0
        END)
      ELSE 0
    END                             AS CL_BL,
  
    MAX (B.OFS)    AS F_SH_OUT,
      MAX (B.OPEN_NAV)                AS NAV,
    CASE
      WHEN MAX (C.FTC_GEN_FL) = 'N'
      THEN ( SUM (
        CASE
          WHEN A.ACCT_DT = TEMP.END_DATE
          THEN A.TRANS_AMT
          ELSE 0.00
        END) - SUM (
        CASE
          WHEN A.ACCT_DT = ACTRL.PRR_VAL_EFF_DT
          THEN A.TRANS_AMT
          ELSE 0.00
        END))
      WHEN MAX (C.FTC_GEN_FL) = 'Y'
      THEN SUM (
        CASE
          WHEN A.ACCT_DT >= TEMP.STRT_DT
          THEN A.TRANS_AMT
          ELSE 0
        END)
    END                    AS NET_ACTIVITY,
     'BALANCE' AS TP,
    4                      AS SRT,
    MAX (TEMP.RPT_ACCT_NO) AS TRPT_ACCT_NO,
    MAX (TEMP.STRT_DT)    AS TSTRT_DT,
      MAX (TEMP.STR_COL5)    AS TTRAN_TYPE,
    MAX (C.RPT_CAT_LVL_1_NM),
    MAX (C.RPT_CAT_LVL_2_NM),
    MAX (C.BS_SORT),
    MAX (C.FD_TRANS_RPT_NM_1),
    MAX (C.RPT_CAT_LVL_3_NM),
    MAX (C.FD_TRANS_RPT_NM_2),
    MAX (C.NAV_SORT)
  FROM TRANSACTIONS A
  JOIN (	select '5037' ACCTNO , '5037'  UAN,  'C5'  BC, 
TO_DATE('2015-03-01','YYYY-MM-DD')  STRT_DT, TO_DATE('2015-03-31','YYYY-MM-DD')  END_DATE from dual
select '5037'  IAN RPT_ACCT_NO from dual) TEMP
  ON A.IUBC = TEMP.BC
  AND A.ACCTNO     = TEMP.UAN
  JOIN CATEGORY C
  ON A.CODE         = C.FTC
  AND C.FD_TRANS_RPT_NM     = 'ALL'
  AND C.IUBC = 'XB'
  JOIN
    (SELECT TRANS.IUBC,
      TRANS.ACCT_DT,
      TRANS.ACCTNO,
      TRANS.SID,
            SUM (
      CASE
        WHEN TRANS.CODE     = 'SO'
        AND TRANS.ACCT_DT = ACTRL.PRR_VAL_EFF_DT
        THEN TRANS.TRANS_AMT
        ELSE 0
      END) AS OFS,
      SUM (
      CASE
        WHEN TRANS.CODE     = 'NV'
        AND TRANS.ACCT_DT = ACTRL.PRR_VAL_EFF_DT
        THEN TRANS.TRANS_AMT
        ELSE 0
      END) AS OPEN_NAV,
        FROM TRANSACTIONS TRANS,
      (	select '5037' ACCTNO , '5037'  UAN,  'C5'  BC, 
TO_DATE('2015-03-01','YYYY-MM-DD')  STRT_DT, TO_DATE('2015-03-31','YYYY-MM-DD')  END_DATE from dual
select '5037'  IAN RPT_ACCT_NO from dual) TEMP,
      ACCT_CTRL ACTRL
    WHERE TRANS.IUBC = BC
    AND TRANS.ACCT_DT     = ACTRL.PRR_VAL_EFF_DT
    AND TRANS.ACCTNO        = TEMP.UAN
    AND TRANS.CODE         IN ('AB', 'SO', 'NV', 'N8', 'UO')
    AND TRANS.SID             < TEMP.STR_COL4
    AND ACTRL.IUBC  = TRANS.IUBC
    AND ACTRL.ACCTNO       = TRANS.ACCTNO
    AND ACTRL.AEFFDT     = TEMP.STRT_DT
    GROUP BY TRANS.IUBC,
      TRANS.ACCT_DT,
      TRANS.ACCTNO,
      TRANS.SID
    ) B
  ON A.IUBC = B.IUBC
  AND B.ACCTNO     = A.ACCTNO
  AND A.SID          = B.SID
  JOIN ACCT_CTRL ACTRL
  ON ACTRL.IUBC = A.IUBC
  AND A.ACCT_DT BETWEEN ACTRL.PRR_VAL_EFF_DT AND TEMP.END_DATE
  AND ACTRL.ACCTNO   = A.ACCTNO
  AND ACTRL.AEFFDT = TEMP.STRT_DT
  JOIN
    (SELECT ACCTNO,
      MIN (CTRL.AEFFDT) AS AEFFDT
    FROM ACCT_CTRL CTRL
    JOIN (	select '5037' ACCTNO , '5037'  UAN,  'C5'  BC, 
TO_DATE('2015-03-01','YYYY-MM-DD')  STRT_DT, TO_DATE('2015-03-31','YYYY-MM-DD')  END_DATE from dual
select '5037'  IAN RPT_ACCT_NO from dual) TEMP
    ON CTRL.AEFFDT     = TEMP.STRT_DT
    AND CTRL.ACCTNO      = TEMP.UAN
    AND CTRL.IUBC = BC
    GROUP BY ACCTNO
    ) CAT ON ACTRL.ACCTNO = CAT.ACCTNO
  AND ACTRL.AEFFDT        = CAT.AEFFDT
  GROUP BY A.IUBC,
    A.ACCTNO,
    A.SID,
    A.FTC

  FROM TRANSACTIONS A
  JOIN (	select '5037' ACCTNO , '5037'  UAN,  'C5'  BC, 
TO_DATE('2015-03-01','YYYY-MM-DD')  STRT_DT, TO_DATE('2015-03-31','YYYY-MM-DD')  END_DATE from dual
select '5037'  IAN RPT_ACCT_NO from dual) TEMP