Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table ps_item_sf (item_type, item_amt, applied_amt, scc_row_add_dttm) as
select '600000050010', 200, 100, to_date('03/19/2017 12:00:23PM', 'MM/DD/YYYY HH:MI:SSPM')
from dual;
1 rows affected

create table ps_item_type_tbl (item_type, descr, setid, effdt) as
select '600000050010', 'Something', 42, trunc(sysdate)
from dual
1 rows affected

SELECT A.ITEM_TYPE, B.DESCR, SUM( A.ITEM_AMT- A.APPLIED_AMT), TO_CHAR(TO_DATE(TO_CHAR(CAST((A.SCC_ROW_ADD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 'MM/DD/YYYY HH:MI:SSPM'), 'YYYY'), TO_CHAR(CAST((A.SCC_ROW_ADD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')
  FROM PS_ITEM_SF A, PS_ITEM_TYPE_TBL B
  WHERE ( B.ITEM_TYPE = A.ITEM_TYPE
    AND ( A.ITEM_TYPE IN ('600000050010','600000050020','600000050030')
     AND B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_TYPE_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.ITEM_TYPE = B_ED.ITEM_TYPE
          AND B_ED.EFFDT <= SYSDATE) ))
  GROUP BY  A.ITEM_TYPE,  B.DESCR,  TO_CHAR(TO_DATE( TO_CHAR(CAST((A.SCC_ROW_ADD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 'MM/DD/YYYY HH:MI:SSPM'), 'YYYY'),  A.SCC_ROW_ADD_DTTM
  HAVING ( SUM( A.ITEM_AMT- A.APPLIED_AMT) > 0)
  ORDER BY 1
ORA-01843: not a valid month

SELECT
  A.ITEM_TYPE,
  B.DESCR,
  SUM(A.ITEM_AMT - A.APPLIED_AMT),
  TO_CHAR(A.SCC_ROW_ADD_DTTM, 'YYYY'),
  TO_CHAR(CAST(A.SCC_ROW_ADD_DTTM AS TIMESTAMP), 'YYYY-MM-DD-HH24.MI.SS.FF')
FROM PS_ITEM_SF A
JOIN PS_ITEM_TYPE_TBL B
ON B.ITEM_TYPE = A.ITEM_TYPE
WHERE A.ITEM_TYPE IN ('600000050010', '600000050020', '600000050030')
AND B.EFFDT = (
  SELECT MAX(B_ED.EFFDT)
  FROM PS_ITEM_TYPE_TBL B_ED
  WHERE B.SETID = B_ED.SETID
  AND B.ITEM_TYPE = B_ED.ITEM_TYPE
  AND B_ED.EFFDT <= SYSDATE
)
GROUP BY A.ITEM_TYPE, B.DESCR, A.SCC_ROW_ADD_DTTM
HAVING SUM(A.ITEM_AMT - A.APPLIED_AMT) > 0
ORDER BY 1


ITEM_TYPE
DESCR
SUM(A.ITEM_AMT-A.APPLIED_AMT)
TO_CHAR(A.SCC_ROW_ADD_DTTM,'YYYY')
TO_CHAR(CAST(A.SCC_ROW_ADD_DTTMASTIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')


600000050010
Something
100
2017
2017-03-19-12.00.23.000000