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 |