set schema jemshdp82;

WITH
GUILTY_DISPOSITIONS (CASE_TYPE, DISP_CODE, JUDGMENT_CODE) AS 
(SELECT AFBDCD, AFBFCD, 'G' FROM CCADPRP WHERE AFBDCD IN ('BC', 'CR', 'TR') AND AFJQCD='Y'),
NOT_GUILTY_DISPOSITIONS (CASE_TYPE, DISP_CODE, JUDGMENT_CODE) AS 
(SELECT AFBDCD, AFBFCD, 'NG' FROM CCADPRP WHERE AFBDCD IN ('BC', 'CR', 'TR') AND AFJQCD='N'),
DISMISSAL_DISPOSITIONS (CASE_TYPE, DISP_CODE, JUDGMENT_CODE) AS
(SELECT AFBDCD, AFBFCD, 'DIS' FROM CCADPRP LEFT JOIN CCBEDDP ON AFBDCD=S4BDCD AND AFBFCD=S4BFCD
WHERE AFBDCD IN ('BC', 'CR', 'TR') AND S4OWTT='DISMISSAL'),
ALL_DISPOSITIONS (CASE_TYPE, DISP_CODE, JUDGMENT_CODE) AS
(SELECT * FROM GUILTY_DISPOSITIONS UNION SELECT * FROM NOT_GUILTY_DISPOSITIONS UNION SELECT * FROM DISMISSAL_DISPOSITIONS),

WILL_NOT_BE_FIXED (CASE_NUMBER, CASE_TYPE, DISP_CODE, DISP_DESC, PLEA_CODE, JUDGMENT_DATE) AS
(SELECT BBCRCD, BBBDCD, BBBFCD, AFANTX, BBBHCD, HCGPL.CONVDATE(BBKNDT) FROM CCECOCP
LEFT JOIN ALL_DISPOSITIONS ON CASE_TYPE=BBBDCD AND DISP_CODE=BBBFCD
LEFT JOIN CCADPRP ON AFBDCD=BBBDCD AND AFBFCD=BBBFCD
WHERE BBKNDT!=0 AND JUDGMENT_CODE IS NULL AND BBBHCD!='G')
SELECT * FROM WILL_NOT_BE_FIXED;