SET SCHEMA JEMSHDP72;

WITH COSTS (COST_CODE) AS (SELECT AIBPCD FROM CCAFERP WHERE AICJST IN ('C', 'F')),

PAYMENTS(PAY_CASE_NUMBER, PAY_PAYMENT_DATE, PAY_RECEIPT_NUMBER, PAY_PAYMENT_AMOUNT)
AS (SELECT BECRCD, BEA6DT, BEDANB, BEAMVA FROM CCFTRCP
WHERE BEA6DT>=1100128 AND BEA6DT<=1100201 AND BED3DT=0),

EARLIEST_PAYMENT_DATE (EPD_CASE_NUMBER, EPD_PAYMENT_DATE) AS 
(SELECT PAY_CASE_NUMBER, MIN(PAY_PAYMENT_DATE) FROM PAYMENTS GROUP BY PAY_CASE_NUMBER),

CLEARANCE_PAYMENTS (CP_CASE_NUMBER, CP_PAYMENT_DATE, CP_RECEIPT_NUMBER, CP_PAYMENT_AMOUNT) 
AS (SELECT EPD_CASE_NUMBER, EPD_PAYMENT_DATE, PAY_RECEIPT_NUMBER, PAY_PAYMENT_AMOUNT
FROM EARLIEST_PAYMENT_DATE LEFT JOIN PAYMENTS ON EPD_CASE_NUMBER=PAY_CASE_NUMBER 
AND EPD_PAYMENT_DATE=PAY_PAYMENT_DATE),

CASES_CLEARED (CC_CASE_NUMBER, CC_PAYMENT_DATE, CC_MONETARY_PAID, CC_NONMONETARY_PAID) 
AS (SELECT CP_CASE_NUMBER, CP_PAYMENT_DATE, 
SUM(CASE WHEN CP_RECEIPT_NUMBER!=0 THEN CP_PAYMENT_AMOUNT ELSE 0 END),
SUM(CASE WHEN CP_RECEIPT_NUMBER=0 THEN CP_PAYMENT_AMOUNT ELSE 0 END) 
FROM CLEARANCE_PAYMENTS GROUP BY CP_CASE_NUMBER, CP_PAYMENT_DATE),

ALL_REFERRAL_PAYMENTS (ARP_CASE_NUMBER, ARP_PAYMENT_DATE, ARP_MONETARY_PAID, ARP_NONMONETARY_PAID) AS
(SELECT CASES_CLEARED.* FROM CASES_CLEARED 
LEFT JOIN CCFCRPP ON CC_CASE_NUMBER=BYCRCD WHERE BYX8CF='WARRUP'),

ARP_WITH_ASSESSED (ASS_CASE_NUMBER, ASS_PAYMENT_DATE, ASS_MONETARY_PAID, ASS_NONMONETARY_PAID, ASS_ASSESSED) AS
(SELECT ALL_REFERRAL_PAYMENTS.*, SUM(CACIVA) FROM ALL_REFERRAL_PAYMENTS 
LEFT JOIN CCECBCP ON ARP_CASE_NUMBER=CACRCD WHERE CABPCD IN (SELECT * FROM COSTS)
GROUP BY ARP_CASE_NUMBER, ARP_PAYMENT_DATE, ARP_MONETARY_PAID, ARP_NONMONETARY_PAID)

SELECT * FROM ARP_WITH_ASSESSED;