-- @BPUTTKAMMER, 2023-02-10
-- 
-- https://www.brantputtkammer.com/
-- 
-- ***LOGIC TO GET PRIMARY ADDRESS, MINIMUM ORDINAL FOR THE PERSON ADDRESS LIST, CAN BE USED FOR PERSON CONTACT TABLE
-- NO FILTERS BY EXPIRATION, BAD, VERIFICATION DATES, ADDRESS FORMAT
-- UI FILTERS OUT EXPIRED ADDRESSES, NEED TO KEEP THE PAL.EXPIRATION_DATE, VERIFY WITH TYFONE THEY FOLLOW THE UI LOGIC
-- 

PrimaryAddress(personAddressLinkSerial,personSerial,addressLine1,addressLine2,city,state,postalCode,combinedAddress) AS (
    SELECT
        PERSON_ADDRESS_LINK_SERIAL, 
        PERSON_SERIAL, 
        COALESCE(ADDRESS.STREET || ' ', '') AS STREET,
        COALESCE(ADDRESS.ADDITIONAL_ADDRESS_LINE || ', ', '') AS ADDRESS_LINE_2,
        COALESCE(ADDRESS.CITY || ' ', '') AS CITY,
        COALESCE(ADDRESS.STATE, '') AS STATE,
        COALESCE(ADDRESS.POSTAL_CODE, '') AS POSTAL_CODE,
        COALESCE(ADDRESS.STREET || ' ', '') || COALESCE(ADDRESS.ADDITIONAL_ADDRESS_LINE || ', ', '') || 
        COALESCE(ADDRESS.CITY || ' ', '') || COALESCE(ADDRESS.STATE, '') || COALESCE(', ' || ADDRESS.POSTAL_CODE, '') AS COMBINED_ADDRESS 
    FROM
    (
        SELECT
            PAL.SERIAL AS PERSON_ADDRESS_LINK_SERIAL,
            PAL.PARENT_SERIAL AS PERSON_SERIAL,
            PAL.ADDRESS_SERIAL AS ADDRESS_SERIAL,
            COALESCE(PAL.ORDINAL, 0) AS PAL_ORDINAL,
            MIN(COALESCE(PAL.ORDINAL, 0)) OVER (PARTITION BY PAL.PARENT_SERIAL) AS MIN_PAL_ORDINAL
        FROM
            CORE.PERSON_ADDRESS_LINK AS PAL LEFT OUTER JOIN
            CORE.ENV AS ENV ON
                ENV.SERIAL > 0
        WHERE
            (PAL.EFFECTIVE_DATE IS NULL OR
            PAL.EFFECTIVE_DATE <= ENV.POSTING_DATE) AND
            (PAL.EXPIRATION_DATE IS NULL OR
            PAL.EXPIRATION_DATE >= ENV.POSTING_DATE) AND
            PAL.BAD_ADDRESS <> 'Y' AND
            PAL.CATEGORY <> 'V'
    ) LEFT OUTER JOIN
        CORE.ADDRESS AS ADDRESS ON
            ADDRESS_SERIAL = ADDRESS.SERIAL 
            --AND ADDRESS.ADDRESS_FORMAT = 'D' --IF YOU ONLY WANT DOMESTIC ADDRESSES
    WHERE
        PAL_ORDINAL = MIN_PAL_ORDINAL
)


-- ADDRESS AND ACCOUNT QUERY
    SELECT
        PERSON_ADDRESS_LINK_SERIAL, 
        PERSON_SERIAL, 
        ACCOUNT_NUMBER,
        COALESCE(ADDRESS.POSTAL_CODE, '') AS POSTAL_CODE,
        COALESCE(ADDRESS.STREET || ' ', '') || COALESCE(ADDRESS.ADDITIONAL_ADDRESS_LINE || ', ', '') || 
        COALESCE(ADDRESS.CITY || ' ', '') || COALESCE(ADDRESS.STATE, '') || COALESCE(', ' || ADDRESS.POSTAL_CODE, '') AS COMBINED_ADDRESS,
        PAL_LAST_FM_DATE
    FROM
    (
        SELECT
            PAL.SERIAL AS PERSON_ADDRESS_LINK_SERIAL,
            PAL.PARENT_SERIAL AS PERSON_SERIAL,
            PAL.ADDRESS_SERIAL AS ADDRESS_SERIAL,
            ACCOUNT.ACCOUNT_NUMBER AS ACCOUNT_NUMBER,
            COALESCE(PAL.ORDINAL, 0) AS PAL_ORDINAL,
            MIN(COALESCE(PAL.ORDINAL, 0)) OVER (PARTITION BY PAL.PARENT_SERIAL) AS MIN_PAL_ORDINAL,
            PAL.LAST_FM_DATE AS PAL_LAST_FM_DATE,
            ENV.POSTING_DATE AS ENV_POSTING_DATE
        FROM
            CORE.PERSON_ADDRESS_LINK AS PAL LEFT OUTER JOIN
            CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.PRIMARY_PERSON_SERIAL=PAL.PARENT_SERIAL LEFT OUTER JOIN
            CORE.ENV AS ENV ON ENV.SERIAL > 0
        WHERE
            (PAL.EFFECTIVE_DATE IS NULL OR
            PAL.EFFECTIVE_DATE <= ENV.POSTING_DATE) AND
            (PAL.EXPIRATION_DATE IS NULL OR
            PAL.EXPIRATION_DATE >= ENV.POSTING_DATE) AND
            PAL.BAD_ADDRESS <> 'Y' AND
            PAL.CATEGORY <> 'V'
    ) LEFT OUTER JOIN
        CORE.ADDRESS AS ADDRESS ON
            ADDRESS_SERIAL = ADDRESS.SERIAL 
            --AND ADDRESS.ADDRESS_FORMAT = 'D' --IF YOU ONLY WANT DOMESTIC ADDRESSES
    WHERE
        PAL_ORDINAL = MIN_PAL_ORDINAL
        AND PAL_LAST_FM_DATE >= (ENV_POSTING_DATE - 1 MONTH)
    ORDER BY
    	PAL_LAST_FM_DATE ASC
	--FETCH FIRST 10 ROWS ONLY
;


StaffAccounts(accountSerial,userSerial,userName,personSerial,personName) AS (
	SELECT 
		ACCOUNT.SERIAL AS ACCOUNT_SERIAL,
		USER.SERIAL AS USER_SERIAL,
		USER.USERNAME AS USERNAME,
		PERSON.SERIAL AS PERSON_SERIAL,
		PERSON.FIRST_NAME || ' ' || PERSON.LAST_NAME AS PERSON_NAME
	FROM 
		CORE.USER AS USER INNER JOIN 
		CORE.ROLE AS ROLE ON USER.ROLE_SERIAL=ROLE.SERIAL LEFT OUTER JOIN
		CORE.PERSON AS PERSON ON PERSON.SERIAL=USER.PERSON_SERIAL LEFT OUTER JOIN
		CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.PRIMARY_PERSON_SERIAL=PERSON.SERIAL AND ACCOUNT.CLOSE_DATE IS NULL
	WHERE 
		USER.CATEGORY IN ('P') 
		AND (UCASE(USER.EMAIL_ADDRESS) LIKE '%@UECU%' OR UCASE(USER.EMAIL_ADDRESS) LIKE '%@EXPEDITION%' OR ACCOUNT.ACCESS_RESTRICTION = 'E')
		AND USER.TERMINATION_DATE IS NULL
)





--GETS FEWER RESULTS BECAUSE OF THE NOT EXISTS, SEE OPTION B FOR BETTER LOGIC ESPECIALLY EXPIRED EMAILS
PrimaryEmailOptionA(personSerial,emailAddress,accountSerial,accountNumber,) AS (
    SELECT
        PERSON_CONTACT.PARENT_SERIAL AS PERSON_SERIAL,
        PERSON_CONTACT.VALUE AS EMAIL,
        ACCOUNT.SERIAL AS ACCOUNT_SERIAL,
        ACCOUNT.ACCOUNT_NUMBER AS ACCOUNT_NUMBER
    FROM
        CORE.ENV AS ENV CROSS JOIN
        CORE.PERSON_CONTACT AS PERSON_CONTACT LEFT OUTER JOIN
        CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.PRIMARY_PERSON_SERIAL=PERSON_CONTACT.PARENT_SERIAL
    WHERE
        PERSON_CONTACT.CATEGORY IN ('PE','BE') AND
        PERSON_CONTACT.BAD_CONTACT = 'N' AND
        NOT EXISTS (SELECT 1 
                    FROM
                        CORE.PERSON_CONTACT AS PERSON_CONTACT_INNER
                    WHERE
                        PERSON_CONTACT_INNER.PARENT_SERIAL = PERSON_CONTACT.PARENT_SERIAL AND
                        PERSON_CONTACT_INNER.ORDINAL > PERSON_CONTACT.ORDINAL AND
                        PERSON_CONTACT.CATEGORY IN ('PE','BE') AND
                        PERSON_CONTACT.BAD_CONTACT = 'N') 
)



--MIN GETS MORE RESULTS AND FINDS THE EMAIL HIGHEST UP IN THE ACCOUNT TREE, LOWEST ORDINAL
--OPTION B IS PREFERRED WAY TO FIND THE PRIMARY EMAIL ADDRESS
--COULD ALSO FIGURE IN LOGIC TO GRAB ACCOUNT.E_STMT_PERSON_CONTACT_SERIAL WHEN THE ORDINAL IS HIGHER THAN THE MIN ORDINAL
PrimaryEmailOptionB(personContactSerial,personSerial,emailAddress,accountNumber) AS (
    SELECT
        PERSON_CONTACT_SERIAL, 
        PERSON_SERIAL, 
        --PERSON_CONTACT_OUTER.CATEGORY,
        PERSON_CONTACT_OUTER.VALUE AS EMAIL_ADDRESS,
        --PERSON_CONTACT_OUTER.PARENT_SERIAL,
        ACCOUNT_NUMBER
        --PERSON_CONTACT_ORDINAL,
        --PERSON_CONTACT_OUTER.ORDINAL AS PC_OUTER_ORDINAL
    FROM
    (
        SELECT
            PERSON_CONTACT.SERIAL AS PERSON_CONTACT_SERIAL,
            PERSON_CONTACT.PARENT_SERIAL AS PERSON_SERIAL,
            COALESCE(PERSON_CONTACT.ORDINAL, 0) AS PERSON_CONTACT_ORDINAL,
            MIN(COALESCE(PERSON_CONTACT.ORDINAL, 0)) OVER (PARTITION BY PERSON_CONTACT.PARENT_SERIAL) AS MIN_PERSON_CONTACT_ORDINAL,
            ACCOUNT.ACCOUNT_NUMBER AS ACCOUNT_NUMBER
        FROM
            CORE.PERSON_CONTACT AS PERSON_CONTACT LEFT OUTER JOIN
            CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.PRIMARY_PERSON_SERIAL=PERSON_CONTACT.PARENT_SERIAL LEFT OUTER JOIN
            CORE.ENV AS ENV ON ENV.SERIAL > 0
        WHERE
            (PERSON_CONTACT.EXPIRATION_DATE IS NULL OR
            PERSON_CONTACT.EXPIRATION_DATE >= ENV.POSTING_DATE) AND
            PERSON_CONTACT.BAD_CONTACT <> 'Y' AND
            PERSON_CONTACT.CATEGORY IN ('PE','BE') 
    ) LEFT OUTER JOIN CORE.PERSON_CONTACT AS PERSON_CONTACT_OUTER ON PERSON_CONTACT_OUTER.SERIAL=PERSON_CONTACT_SERIAL
    WHERE
        PERSON_CONTACT_ORDINAL = MIN_PERSON_CONTACT_ORDINAL
	ORDER BY
		ACCOUNT_NUMBER ASC
)






--GETS FEWER RESULTS BECAUSE OF THE NOT EXISTS, SEE OPTION B FOR BETTER LOGIC ESPECIALLY EXPIRED EMAILS
PrimaryPhoneOptionA(personSerial,phoneNumber,accountSerial,accountNumber,) AS (
    SELECT
        PERSON_CONTACT.PARENT_SERIAL AS PERSON_SERIAL,
        PERSON_CONTACT.VALUE AS PHONE_NUMBER,
        ACCOUNT.SERIAL AS ACCOUNT_SERIAL,
        ACCOUNT.ACCOUNT_NUMBER AS ACCOUNT_NUMBER
    FROM
        CORE.ENV AS ENV CROSS JOIN
        CORE.PERSON_CONTACT AS PERSON_CONTACT LEFT OUTER JOIN
        CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.PRIMARY_PERSON_SERIAL=PERSON_CONTACT.PARENT_SERIAL
    WHERE
        PERSON_CONTACT.CATEGORY IN ('HP','BP','PC','BC') AND
        PERSON_CONTACT.BAD_CONTACT = 'N' AND
        NOT EXISTS (SELECT 1 
                    FROM
                        CORE.PERSON_CONTACT AS PERSON_CONTACT_INNER
                    WHERE
                        PERSON_CONTACT_INNER.PARENT_SERIAL = PERSON_CONTACT.PARENT_SERIAL AND
                        PERSON_CONTACT_INNER.ORDINAL > PERSON_CONTACT.ORDINAL AND
                        PERSON_CONTACT.CATEGORY IN ('HP','BP','PC','BC') AND
                        PERSON_CONTACT.BAD_CONTACT = 'N') 
)



--MIN GETS MORE RESULTS AND FINDS THE PHONE_NUMBER HIGHEST UP IN THE ACCOUNT TREE, LOWEST ORDINAL
--OPTION B IS PREFERRED WAY TO FIND THE PRIMARY PHONE_NUMBER ADDRESS
--COULD ALSO FIGURE IN LOGIC TO GRAB ACCOUNT.E_STMT_PERSON_CONTACT_SERIAL WHEN THE ORDINAL IS HIGHER THAN THE MIN ORDINAL
--WATCH THE JOIN, IF YOU ARE ONLY LOOKING FOR THE PRIMARY IT WILL LIST OUT MULTIPLE ENTRIES BASED ON CTE COUNT
PrimaryPhoneOptionB(personContactSerial,personSerial,phoneNumber,accountNumber) AS (
    SELECT
        PERSON_CONTACT_SERIAL, 
        PERSON_SERIAL, 
        --PERSON_CONTACT_OUTER.CATEGORY,
        PERSON_CONTACT_OUTER.VALUE AS PHONE_NUMBER,
        --PERSON_CONTACT_OUTER.PARENT_SERIAL,
        ACCOUNT_NUMBER
        --PERSON_CONTACT_ORDINAL,
        --PERSON_CONTACT_OUTER.ORDINAL AS PC_OUTER_ORDINAL
    FROM
    (
        SELECT
            PERSON_CONTACT.SERIAL AS PERSON_CONTACT_SERIAL,
            PERSON_CONTACT.PARENT_SERIAL AS PERSON_SERIAL,
            COALESCE(PERSON_CONTACT.ORDINAL, 0) AS PERSON_CONTACT_ORDINAL,
            MIN(COALESCE(PERSON_CONTACT.ORDINAL, 0)) OVER (PARTITION BY PERSON_CONTACT.PARENT_SERIAL) AS MIN_PERSON_CONTACT_ORDINAL,
            ACCOUNT.ACCOUNT_NUMBER AS ACCOUNT_NUMBER
        FROM
            CORE.PERSON_CONTACT AS PERSON_CONTACT LEFT OUTER JOIN
            CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.PRIMARY_PERSON_SERIAL=PERSON_CONTACT.PARENT_SERIAL LEFT OUTER JOIN
            CORE.ENV AS ENV ON ENV.SERIAL > 0
        WHERE
            (PERSON_CONTACT.EXPIRATION_DATE IS NULL OR
            PERSON_CONTACT.EXPIRATION_DATE >= ENV.POSTING_DATE) AND
            PERSON_CONTACT.BAD_CONTACT <> 'Y' AND
            PERSON_CONTACT.CATEGORY IN ('HP','BP','PC','BC')
    ) LEFT OUTER JOIN CORE.PERSON_CONTACT AS PERSON_CONTACT_OUTER ON PERSON_CONTACT_OUTER.SERIAL=PERSON_CONTACT_SERIAL
    WHERE
        PERSON_CONTACT_ORDINAL = MIN_PERSON_CONTACT_ORDINAL
	ORDER BY
		ACCOUNT_NUMBER ASC
)



NSFFeeCount(accountSerial,feeCount) AS (  
	SELECT  
		ACCOUNT.SERIAL, COUNT(MONETARY.PARENT_SERIAL) AS TOTAL_COUNT  
	FROM   
		CORE.ENV AS ENV CROSS JOIN  
		CORE.SHARE AS SHARE INNER JOIN  
		CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.SERIAL=SHARE.PARENT_SERIAL LEFT OUTER JOIN  
		CORE.MONETARY AS MONETARY ON MONETARY.TARGET_ACCESS_KEY='A:' ||ACCOUNT.SERIAL || '/S:' || SHARE.SERIAL || '/' LEFT OUTER JOIN  
		CORE.MON_DETAIL AS MON_DETAIL ON MON_DETAIL.PARENT_SERIAL=MONETARY.SERIAL  
	WHERE   
		MON_DETAIL.CATEGORY IN ('FEC') AND  
		MON_DETAIL.CONTENTS_1 IN ('RTN') AND  
		MONETARY.DESCRIPTION IN ('NSF Return Fee') AND  
		MONETARY.POSTING_DATE >= (ENV.POSTING_DATE - 1 YEAR) AND  
		MONETARY.STATUS IN ('P')  
	GROUP BY   
		ACCOUNT.SERIAL  
	  
)




NSFFeeCountReversed(accountSerial,feeCount) AS (  
	SELECT  
		ACCOUNT.SERIAL, COUNT(MONETARY.PARENT_SERIAL) AS TOTAL_COUNT  
	FROM   
		CORE.ENV AS ENV CROSS JOIN  
		CORE.SHARE AS SHARE INNER JOIN  
		CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.SERIAL=SHARE.PARENT_SERIAL LEFT OUTER JOIN  
		CORE.MONETARY AS MONETARY ON MONETARY.TARGET_ACCESS_KEY='A:' ||ACCOUNT.SERIAL || '/S:' || SHARE.SERIAL || '/' LEFT OUTER JOIN  
		CORE.MON_DETAIL AS MON_DETAIL ON MON_DETAIL.PARENT_SERIAL=MONETARY.SERIAL  
	WHERE   
		MON_DETAIL.CATEGORY IN ('FEC') AND  
		MON_DETAIL.CONTENTS_1 IN ('RTN') AND  
		UCASE(MONETARY.DESCRIPTION) LIKE ('%REVERSE%') AND  
		MONETARY.POSTING_DATE >= (ENV.POSTING_DATE - 1 YEAR) AND  
		MONETARY.STATUS IN ('P')  
	GROUP BY   
		ACCOUNT.SERIAL  
	  
)




ReturnedItemFeeCount(accountSerial,feeCount) AS (  
	SELECT  
		ACCOUNT.SERIAL, COUNT(MONETARY.PARENT_SERIAL) AS TOTAL_COUNT  
	FROM   
		CORE.ENV AS ENV CROSS JOIN  
		CORE.SHARE AS SHARE INNER JOIN  
		CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.SERIAL=SHARE.PARENT_SERIAL LEFT OUTER JOIN  
		CORE.MONETARY AS MONETARY ON MONETARY.TARGET_ACCESS_KEY='A:' ||ACCOUNT.SERIAL || '/S:' || SHARE.SERIAL || '/' LEFT OUTER JOIN  
		CORE.MON_DETAIL AS MON_DETAIL ON MON_DETAIL.PARENT_SERIAL=MONETARY.SERIAL  
	WHERE   
		MON_DETAIL.CATEGORY IN ('FEC') AND  
		MON_DETAIL.CONTENTS_1 IN ('') AND  
		MONETARY.DESCRIPTION IN ('Deposited Item Returned') AND  
		MONETARY.POSTING_DATE >= (ENV.POSTING_DATE - 1 YEAR) AND  
		MONETARY.STATUS IN ('P')  
	GROUP BY   
		ACCOUNT.SERIAL  
	  
)




ReturnedItemFeeCountReversed(accountSerial,feeCount) AS (  
	SELECT  
		ACCOUNT.SERIAL, COUNT(MONETARY.PARENT_SERIAL) AS TOTAL_COUNT  
	FROM   
		CORE.ENV AS ENV CROSS JOIN  
		CORE.SHARE AS SHARE INNER JOIN  
		CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.SERIAL=SHARE.PARENT_SERIAL LEFT OUTER JOIN  
		CORE.MONETARY AS MONETARY ON MONETARY.TARGET_ACCESS_KEY='A:' ||ACCOUNT.SERIAL || '/S:' || SHARE.SERIAL || '/' LEFT OUTER JOIN  
		CORE.MON_DETAIL AS MON_DETAIL ON MON_DETAIL.PARENT_SERIAL=MONETARY.SERIAL  
	WHERE   
		MON_DETAIL.CATEGORY IN ('FEC') AND  
		MON_DETAIL.CONTENTS_1 IN ('') AND  
		UCASE(MONETARY.DESCRIPTION) LIKE ('%REVERSE%') AND  
		UCASE(MONETARY.DESCRIPTION) LIKE ('%DEPOSITED%') AND  
		MONETARY.POSTING_DATE >= (ENV.POSTING_DATE - 1 YEAR) AND  
		MONETARY.STATUS IN ('P')  
	GROUP BY   
		ACCOUNT.SERIAL  
	  
)



-- FEE SQL, GROUPED BY DESCRIPTION
SELECT
	COUNT(UNIQUE TRANSACTION_SERIAL) AS TOTALS,
	SUM(TOTAL_FEES) AS TOTAL_FEES,
	MONETARY_DESCRIPTION
FROM (
	SELECT  
		MONETARY.PRINCIPAL AS TOTAL_FEES,
		MONETARY.PARENT_SERIAL AS TRANSACTION_SERIAL,
		(TO_CHAR(MONETARY.POSTING_DATE, 'YYYY-MM')) AS FEE_MONTH,
		MONETARY.DESCRIPTION AS MONETARY_DESCRIPTION
	FROM   
		CORE.ENV AS ENV CROSS JOIN  
		--CORE.SHARE AS SHARE INNER JOIN  
		CORE.ACCOUNT AS ACCOUNT LEFT OUTER JOIN  
		--CORE.MONETARY AS MONETARY ON MONETARY.TARGET_ACCESS_KEY='A:' ||ACCOUNT.SERIAL || '/S:' || SHARE.SERIAL || '/' LEFT OUTER JOIN  
		CORE.MONETARY AS MONETARY ON ACCOUNT.SERIAL=REGEXP_SUBSTR(MONETARY.TARGET_ACCESS_KEY, 'A:(\d*)/', 1, 1, '', 1) 
		  AND MONETARY.POSTING_DATE >= (ENV.POSTING_DATE - 1 YEAR) AND MONETARY.STATUS IN ('P')  LEFT OUTER JOIN  
		CORE.MON_DETAIL AS MON_DETAIL ON MON_DETAIL.PARENT_SERIAL=MONETARY.SERIAL  
	WHERE   
		(MON_DETAIL.CATEGORY IN ('FEC') AND  
		--MON_DETAIL.CONTENTS_1 IN ('RTN') AND  
		-- DESCRIPTION WILL CONTAIN REVERSED IF THE FEE WAS REVERSED
		UCASE(MONETARY.DESCRIPTION) NOT LIKE ('%REVERSEXXX%'))
		OR (MONETARY.SOURCE IN ('F') AND
		-- DESCRIPTION WILL CONTAIN REVERSED IF THE FEE WAS REVERSED
		UCASE(MONETARY.DESCRIPTION) NOT LIKE ('%REVERSEXXX%'))
		AND  
		MONETARY.STATUS IN ('P') 
	GROUP BY   
		MONETARY.DESCRIPTION,
		MONETARY.PARENT_SERIAL,
		MONETARY.PRINCIPAL,
		(TO_CHAR(MONETARY.POSTING_DATE, 'YYYY-MM'))
) a
GROUP BY
	MONETARY_DESCRIPTION
;

-- GET ACCOUNT DETAILS FOR SPECIFIC FEES
	SELECT  
		ACCOUNT.ACCOUNT_NUMBER,
		'S ' || SHARE.ID AS SHARE_ID,
		MONETARY.PRINCIPAL AS TOTAL_FEES,
		MONETARY.PARENT_SERIAL AS TRANSACTION_SERIAL,
		MONETARY.POSTING_DATE AS POSTING_DATE,
		--(TO_CHAR(MONETARY.POSTING_DATE, 'YYYY-MM')) AS FEE_MONTH,
		MONETARY.DESCRIPTION AS MONETARY_DESCRIPTION,
		USER.USERNAME AS USERNAME,
		MONETARY.TARGET_ACCESS_KEY AS TARGET_ACCESS_KEY
	FROM   
		CORE.ENV AS ENV CROSS JOIN  
		CORE.ACCOUNT AS ACCOUNT LEFT OUTER JOIN  
		CORE.MONETARY AS MONETARY ON ACCOUNT.SERIAL=REGEXP_SUBSTR(MONETARY.TARGET_ACCESS_KEY, 'A:(\d*)/', 1, 1, '', 1) 
		  AND MONETARY.POSTING_DATE >= (ENV.POSTING_DATE - 1 MONTH) AND MONETARY.STATUS IN ('P')  LEFT OUTER JOIN  
		CORE.MON_DETAIL AS MON_DETAIL ON MON_DETAIL.PARENT_SERIAL=MONETARY.SERIAL  LEFT OUTER JOIN
		CORE.TRANSACTION AS TRANSACTION ON TRANSACTION.SERIAL=MONETARY.PARENT_SERIAL LEFT OUTER JOIN
		CORE.USER AS USER ON TRANSACTION.USER_SERIAL=USER.SERIAL LEFT OUTER JOIN
		CORE.SHARE AS SHARE ON SHARE.SERIAL=REGEXP_SUBSTR(MONETARY.TARGET_ACCESS_KEY, 'S:(\d*)/', 1, 1, '', 1) 
	WHERE   
		(MON_DETAIL.CATEGORY IN ('FEC') AND  
		--MON_DETAIL.CONTENTS_1 IN ('RTN') AND  
		-- DESCRIPTION WILL CONTAIN REVERSED IF THE FEE WAS REVERSED
		UCASE(MONETARY.DESCRIPTION) NOT LIKE ('%REVERSE%')
		AND UCASE(MONETARY.DESCRIPTION) LIKE ('%SKIP%'))
		OR (MONETARY.SOURCE IN ('F') AND
		-- DESCRIPTION WILL CONTAIN REVERSED IF THE FEE WAS REVERSED
		UCASE(MONETARY.DESCRIPTION) NOT LIKE ('%REVERSE%')
		AND UCASE(MONETARY.DESCRIPTION) LIKE ('%SKIP%'))
		AND  
		MONETARY.STATUS IN ('P') 
	ORDER BY
		MONETARY.POSTING_DATE ASC, ACCOUNT.ACCOUNT_NUMBER ASC
;


LastActivityDate(accountSerial,date,rowNum,totalAccounts,storedAccessKey) AS (
	SELECT
		SERIAL,
		MAX(LAST_ACTIVITY_DATE) AS LAST_ACTIVITY_DATE,
		MAX(ROW_NUM) AS ROW_NUM,
		MAX(TOTAL_SHARE_LOAN) AS TOTAL_SHARE_LOAN,
		MAX(STORED_ACCESS_KEY) AS STORED_ACCESS_KEY
	FROM
	(
		--SELECT SERIAL, LAST_ACTIVITY_DATE, ROW_NUM, TOTAL_ACCTS, STORED_ACCESS_KEY
		SELECT *
			FROM (
				SELECT
					    ACCOUNT.SERIAL,
					    MAX(COALESCE(SHARE.LAST_ACTIVITY_DATE, DATE('1776-07-04'))) AS LAST_ACTIVITY_DATE,
					    ROW_NUMBER() OVER(PARTITION BY ACCOUNT.SERIAL ORDER BY SHARE.LAST_ACTIVITY_DATE DESC) AS ROW_NUM,
					    COUNT(*) OVER (PARTITION BY ACCOUNT.SERIAL) AS TOTAL_SHARE_LOAN,
					    SHARE.STORED_ACCESS_KEY AS STORED_ACCESS_KEY
					FROM
					    CORE.ENV AS ENV CROSS JOIN 
						CORE.ACCOUNT AS ACCOUNT INNER JOIN
					    CORE.SHARE AS SHARE ON ACCOUNT.SERIAL = SHARE.PARENT_SERIAL 
					WHERE 
						SHARE.CLOSE_DATE IS NULL
						--AND SHARE.LAST_ACTIVITY_DATE IS NULL
						AND ACCOUNT.CLOSE_DATE IS NULL
					GROUP BY
					    ACCOUNT.SERIAL, SHARE.STORED_ACCESS_KEY, SHARE.LAST_ACTIVITY_DATE
					ORDER BY 
						ACCOUNT.SERIAL ASC
			)
			WHERE ROW_NUM = 1
		    
		UNION ALL
		
		SELECT *
			FROM (
				SELECT
					    ACCOUNT.SERIAL,
					    MAX(COALESCE(LOAN.LAST_ACTIVITY_DATE, DATE('1776-07-04'))) AS LAST_ACTIVITY_DATE,
					    ROW_NUMBER() OVER(PARTITION BY ACCOUNT.SERIAL ORDER BY LOAN.LAST_ACTIVITY_DATE DESC) AS ROW_NUM,
					    COUNT(*) OVER (PARTITION BY ACCOUNT.SERIAL) AS TOTAL_SHARE_LOAN,
					    LOAN.STORED_ACCESS_KEY AS STORED_ACCESS_KEY
					FROM
					    CORE.ENV AS ENV CROSS JOIN 
						CORE.ACCOUNT AS ACCOUNT INNER JOIN
					    CORE.LOAN AS LOAN ON ACCOUNT.SERIAL = LOAN.PARENT_SERIAL 
					WHERE 
						LOAN.CLOSE_DATE IS NULL
						--AND LOAN.LAST_ACTIVITY_DATE IS NULL
						AND ACCOUNT.CLOSE_DATE IS NULL
					GROUP BY
					    ACCOUNT.SERIAL, LOAN.STORED_ACCESS_KEY, LOAN.LAST_ACTIVITY_DATE
					ORDER BY 
						ACCOUNT.SERIAL ASC
			)
			WHERE ROW_NUM = 1
	)    
	GROUP BY
		SERIAL
	ORDER BY
		SERIAL
)





DormancyStatus(accountSerial,lastActivity) AS (  
    SELECT  
        PARENT_SERIAL AS ACCOUNT_SERIAL  
        , MAX(LAST_ACTIVITY_DATE) AS LAST_ACTIVITY_DATE  
    FROM  
        (  
            SELECT  
                SHARE.PARENT_SERIAL  
                , SHARE.LAST_ACTIVITY_DATE,  
                'S ' || SHARE.ID AS ID  
            FROM  
                CORE.SHARE AS SHARE INNER JOIN  
                CORE.SH_TYPE AS SH_TYPE ON SH_TYPE.SERIAL=SHARE.TYPE_SERIAL  
            WHERE  
            	SHARE.CLOSE_DATE IS NULL  
            	AND SH_TYPE.CATEGORY NOT IN ('C')  
            	AND SHARE.CHARGE_OFF_DATE IS NULL  
            UNION  
            SELECT  
                LOAN.PARENT_SERIAL  
                , LOAN.LAST_ACTIVITY_DATE,  
                'L ' || LOAN.ID AS ID  
            FROM  
                CORE.LOAN AS LOAN INNER JOIN  
                CORE.LN_TYPE AS LN_TYPE ON LN_TYPE.SERIAL=LOAN.TYPE_SERIAL  
            WHERE  
            	LOAN.CLOSE_DATE IS NULL  
            	AND LN_TYPE.CATEGORY NOT IN ('CC')  
            	AND LOAN.CHARGE_OFF_DATE IS NULL  
        )  
    GROUP BY  
        PARENT_SERIAL  
          
)




LoanDelinquency(accountSerial,loanSerial,delinquentDays) AS (  
	SELECT   
		ACCOUNT_SERIAL,LOAN_SERIAL,DQ_DAYS  
	FROM (  
		SELECT  
		  ACCOUNT.SERIAL AS ACCOUNT_SERIAL,   
		  LOAN.SERIAL AS LOAN_SERIAL,   
		  ACCOUNT.ACCOUNT_NUMBER || ' L ' || LOAN.ID AS ACCT,  
		  PERSON.LAST_NAME || COALESCE(',' || PERSON.FIRST_NAME, '') AS PRIMARY_NAME,  
		  LN_TYPE.DESCRIPTION AS LN_TYPE_DESCRIPTION,  
		  LN_TYPE.SERIAL,   
		  LOAN.BALANCE AS BALANCE,  
		  COALESCE(PAYMENT_CHANGE.PRIOR_PAYMENT_AMOUNT, LOAN.PAYMENT_AMOUNT) AS PAYMENT_AMOUNT,  
		  COALESCE(LN_INT_RATE_CHANGE.PRIOR_INTEREST_RATE, LOAN.INTEREST_RATE) AS INTEREST_RATE,  
		  LOAN.PAYMENT_DUE_DATE AS PAYMENT_DUE_DATE,  
		  DAYS(ENV.POSTING_DATE) - DAYS(LOAN.PAYMENT_DUE_DATE) AS DQ_DAYS, 
		  DAYS(ENV.POSTING_DATE) - DAYS(COALESCE(LOAN.PAYMENT_DUE_DATE,ENV.POSTING_DATE)) AS DQ_DAYS_COAL
		FROM  
		  CORE.ENV AS ENV CROSS JOIN  
		  CORE.LOAN AS LOAN INNER JOIN  
		  CORE.LN_TYPE AS LN_TYPE ON LOAN.TYPE_SERIAL=LN_TYPE.SERIAL INNER JOIN  
		  CORE.ACCOUNT AS ACCOUNT ON LOAN.PARENT_SERIAL=ACCOUNT.SERIAL INNER JOIN  
		  CORE.PERSON AS PERSON ON ACCOUNT.PRIMARY_PERSON_SERIAL=PERSON.SERIAL LEFT OUTER JOIN  
		  CORE.PAYMENT_CHANGE AS PAYMENT_CHANGE ON PAYMENT_CHANGE.PARENT_SERIAL=LOAN.SERIAL AND PAYMENT_CHANGE.PAYMENT_DUE_DATE>LOAN.PAYMENT_DUE_DATE LEFT OUTER JOIN  
		  CORE.LN_INT_RATE_CHANGE AS LN_INT_RATE_CHANGE ON LN_INT_RATE_CHANGE.PARENT_SERIAL=LOAN.SERIAL AND LN_INT_RATE_CHANGE.EFFECTIVE_DATE>ENV.POSTING_DATE  
		WHERE  
		  LOAN.CLOSE_DATE IS NULL AND  
		  LOAN.CHARGE_OFF_DATE IS NULL AND  
		  LOAN.BALANCE + LOAN.INTEREST_UNPAID>0.00 AND  
		  LN_TYPE.SERIAL NOT IN (103,107,108,109) AND   
		  LOAN.PAYMENT_DUE_DATE<ENV.POSTING_DATE AND  
		  NOT EXISTS (SELECT 1 FROM CORE.PAYMENT_CHANGE AS EARLIER_PAYMENT_CHANGE  
		                       WHERE EARLIER_PAYMENT_CHANGE.PARENT_SERIAL=LOAN.SERIAL AND  
		                             EARLIER_PAYMENT_CHANGE.PAYMENT_DUE_DATE>LOAN.PAYMENT_DUE_DATE AND  
		                             EARLIER_PAYMENT_CHANGE.PAYMENT_DUE_DATE<PAYMENT_CHANGE.PAYMENT_DUE_DATE) AND  
		  NOT EXISTS (SELECT 1 FROM CORE.LN_INT_RATE_CHANGE AS EARLIER_LN_INT_RATE_CHANGE  
		                       WHERE EARLIER_LN_INT_RATE_CHANGE.PARENT_SERIAL=LOAN.SERIAL AND  
		                             EARLIER_LN_INT_RATE_CHANGE.EFFECTIVE_DATE>ENV.POSTING_DATE AND  
		                             EARLIER_LN_INT_RATE_CHANGE.EFFECTIVE_DATE<EARLIER_LN_INT_RATE_CHANGE.EFFECTIVE_DATE)  
		ORDER BY  
		  ACCT  
	)  
	WHERE  
		DQ_DAYS > 10  
		  
)






-- FM ACTION HISTORY SEARCH
-- KEEP FOR RESEARCH
SELECT
	TABLE_NAME,
	COLUMN_NAME,
	ACCOUNT_NUMBER,
	LAST_NAME,
	SHARE_ID,
	SHARE_OPEN_DATE,
	LOAN_ID,
	LOAN_OPEN_DATE,
	USERNAME,
	OPERATION,
	LEFT(OLD_CONTENTS,20) AS OLD_CONTENTS,
	LEFT(NEW_CONTENTS,20) AS NEW_CONTENTS,
	POSTING_DATE,
	ENV_POSTING_DATE,
	TRANSACTION_SERIAL,
	TARGET_ACCESS_KEY
FROM (
	SELECT 
		FM_ACTION.TABLE_NAME AS TABLE_NAME, 
		FM_FIELD.COLUMN_NAME AS COLUMN_NAME,
		ACCOUNT.ACCOUNT_NUMBER AS ACCOUNT_NUMBER,
		PERSON.LAST_NAME AS LAST_NAME,
		'S ' || SHARE.ID AS SHARE_ID,
		SHARE.OPEN_DATE AS SHARE_OPEN_DATE,
		'L ' || LOAN.ID AS LOAN_ID,
		LOAN.OPEN_DATE AS LOAN_OPEN_DATE,
		ACCOUNT.SERIAL AS ACCOUNT_SERIAL,
		CHANGE_USER.USERNAME AS USERNAME, TRANSACTION.SERIAL AS TRANSACTION_SERIAL, 
		CASE WHEN FM_ACTION.OPERATION = 'U' THEN 'UPDATE' WHEN FM_ACTION.OPERATION = 'I' THEN 'INSERT' WHEN FM_ACTION.OPERATION = 'D' THEN 'DELETE' ELSE FM_ACTION.OPERATION END AS OPERATION, 
		FM_FIELD.OLD_CONTENTS AS OLD_CONTENTS, FM_FIELD.NEW_CONTENTS AS NEW_CONTENTS,
		(TRANSACTION.POSTING_TIME + CURRENT TIMEZONE) AS POSTING_TIME, 
		TRANSACTION.POSTING_DATE AS POSTING_DATE,
		FM_ACTION.TARGET_ACCESS_KEY AS TARGET_ACCESS_KEY,
		FM_ACTION.TARGET_SERIAL AS TARGET_SERIAL,
		FM_ACTION.TARGET_PARENT_SERIAL AS TARGET_PARENT_SERIAL,
		ENV.POSTING_DATE AS ENV_POSTING_DATE
	FROM
		CORE.ENV AS ENV CROSS JOIN
		--INNER JOIN CORE.FM_ACTION AS FM_ACTION ON FM_ACTION.TABLE_NAME IN ('INTERACTION') INNER JOIN
		CORE.FM_ACTION AS FM_ACTION INNER JOIN
		CORE.FM_FIELD AS FM_FIELD ON FM_ACTION.SERIAL=FM_FIELD.PARENT_SERIAL INNER JOIN
		CORE.TRANSACTION AS TRANSACTION ON FM_ACTION.PARENT_SERIAL=TRANSACTION.SERIAL INNER JOIN
		CORE.USER CHANGE_USER ON TRANSACTION.USER_SERIAL=CHANGE_USER.SERIAL LEFT OUTER JOIN
		--CORE.INTERACTION AS INTERACTION ON INTERACTION.SERIAL=REGEXP_SUBSTR(FM_ACTION.TARGET_ACCESS_KEY, 'INTR:(\d*)/', 1, 1, '', 1) LEFT OUTER JOIN
		CORE.PERSON AS PERSON ON PERSON.SERIAL=REGEXP_SUBSTR(FM_ACTION.TARGET_ACCESS_KEY, 'P:(\d*)/', 1, 1, '', 1) LEFT OUTER JOIN
		CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.SERIAL=REGEXP_SUBSTR(FM_ACTION.TARGET_ACCESS_KEY, 'A:(\d*)/', 1, 1, '', 1) LEFT OUTER JOIN
		CORE.SHARE AS SHARE ON SHARE.SERIAL=REGEXP_SUBSTR(FM_ACTION.TARGET_ACCESS_KEY, 'S:(\d*)/', 1, 1, '', 1) LEFT OUTER JOIN
		CORE.LOAN AS LOAN ON LOAN.SERIAL=REGEXP_SUBSTR(FM_ACTION.TARGET_ACCESS_KEY, 'L:(\d*)/', 1, 1, '', 1)
	WHERE 
		TRANSACTION.POSTING_DATE BETWEEN ((ENV.POSTING_DATE +1 DAY - DAY(ENV.POSTING_DATE) DAYS) - 10 DAYS) AND ENV.POSTING_DATE
		AND CHANGE_USER.USERNAME NOT IN ('Standard BatchXXX')
		AND FM_FIELD.COLUMN_NAME IN ('ORIGINAL_AMOUNT','ORIGINAL_DATE','OPENING_BALANCE')
		AND FM_ACTION.TABLE_NAME IN ('MONETARY','SHARE')
		AND CHANGE_USER.CATEGORY NOT IN ('P')
	) a
ORDER BY
	POSTING_DATE ASC
;



WITH FMHistory(fmActionSerial,transactionSerial,targetAccessKey) AS (
	SELECT
		FM_ACTION_SERIAL,
		TRANSACTION_SERIAL,
		TARGET_ACCESS_KEY
	FROM (
		SELECT 
			FM_ACTION.SERIAL AS FM_ACTION_SERIAL, 
			CHANGE_USER.USERNAME AS USERNAME, TRANSACTION.SERIAL AS TRANSACTION_SERIAL, 
			FM_ACTION.TARGET_ACCESS_KEY AS TARGET_ACCESS_KEY,
			FM_ACTION.TARGET_SERIAL AS TARGET_SERIAL,
			FM_ACTION.TARGET_PARENT_SERIAL AS TARGET_PARENT_SERIAL,
			ENV.POSTING_DATE AS ENV_POSTING_DATE
		FROM
			CORE.ENV AS ENV CROSS JOIN
			CORE.FM_ACTION AS FM_ACTION INNER JOIN
			CORE.FM_FIELD AS FM_FIELD ON FM_ACTION.SERIAL=FM_FIELD.PARENT_SERIAL INNER JOIN
			CORE.TRANSACTION AS TRANSACTION ON FM_ACTION.PARENT_SERIAL=TRANSACTION.SERIAL INNER JOIN
			CORE.USER CHANGE_USER ON TRANSACTION.USER_SERIAL=CHANGE_USER.SERIAL 
		WHERE 
			--TRANSACTION.POSTING_DATE BETWEEN ((ENV.POSTING_DATE +1 DAY - DAY(ENV.POSTING_DATE) DAYS) - 10 DAYS) AND ENV.POSTING_DATE AND
			--AND CHANGE_USER.USERNAME NOT IN ('Standard BatchXXX')
			FM_FIELD.COLUMN_NAME IN ('ORIGINAL_AMOUNT','ORIGINAL_DATE','OPENING_BALANCE')
			AND FM_ACTION.TABLE_NAME IN ('MONETARY','SHARE')
			--AND CHANGE_USER.CATEGORY NOT IN ('P')
		) a
	ORDER BY
		FM_ACTION_SERIAL ASC
)


-- TABLES THAT UPDATE/USER THE PERSON TABLE REFERENCE
	SELECT
		--FM_ACTION_SERIAL,
		COUNT(UNIQUE TRANSACTION_SERIAL) AS TOTALS,
		--POSTING_DATE,
		TABLE_NAME,
		--CHANGE_USER,
		MAX(TARGET_ACCESS_KEY) AS TAK
	FROM (
		SELECT 
			FM_ACTION.SERIAL AS FM_ACTION_SERIAL, 
			CHANGE_USER.USERNAME AS USERNAME, TRANSACTION.SERIAL AS TRANSACTION_SERIAL, 
			FM_ACTION.TARGET_ACCESS_KEY AS TARGET_ACCESS_KEY,
			FM_ACTION.TABLE_NAME AS TABLE_NAME,
			FM_ACTION.POSTING_DATE AS POSTING_DATE,
			FM_ACTION.TARGET_SERIAL AS TARGET_SERIAL,
			FM_ACTION.TARGET_PARENT_SERIAL AS TARGET_PARENT_SERIAL,
			ENV.POSTING_DATE AS ENV_POSTING_DATE,
			CHANGE_USER.USERNAME AS CHANGE_USER
		FROM
			CORE.ENV AS ENV CROSS JOIN
			CORE.FM_ACTION AS FM_ACTION INNER JOIN
			CORE.FM_FIELD AS FM_FIELD ON FM_ACTION.SERIAL=FM_FIELD.PARENT_SERIAL INNER JOIN
			CORE.TRANSACTION AS TRANSACTION ON FM_ACTION.PARENT_SERIAL=TRANSACTION.SERIAL INNER JOIN
			CORE.USER CHANGE_USER ON TRANSACTION.USER_SERIAL=CHANGE_USER.SERIAL 
		WHERE 
			TRANSACTION.POSTING_DATE BETWEEN ((ENV.POSTING_DATE +1 DAY - DAY(ENV.POSTING_DATE) DAYS) - 10 MONTHS) AND ENV.POSTING_DATE AND
			--AND CHANGE_USER.USERNAME NOT IN ('Standard BatchXXX')
			--FM_FIELD.COLUMN_NAME IN ('ORIGINAL_AMOUNT','ORIGINAL_DATE','OPENING_BALANCE')
			--AND FM_ACTION.TABLE_NAME IN ('MONETARY','SHARE')
			CHANGE_USER.CATEGORY IN ('P')
			AND (FM_ACTION.TARGET_ACCESS_KEY LIKE 'P:%' OR FM_ACTION.TARGET_ACCESS_KEY LIKE '/P:%')
		) a
	GROUP BY
		TABLE_NAME
	ORDER BY
		TABLE_NAME ASC
;


-- ATM FEES ARE INCLUDED IN MONETARY.PRINCIPAL TRANSACTION, HAVE TO USE LOGIC TO PULL OUT FEE PORTION
SELECT 
		REWARD_TYPE, TOTAL_PRINCIPAL, 0 AS CALCULATED_CASH_REWARDS, CASH_REWARDS, TRAN_COUNT, ACCOUNT_NUMBER, SHARE_ID, REWARD_MONTH, SHARE_TYPE_SERIAL
	FROM (
		SELECT 
			'ATM REFUND' AS REWARD_TYPE, (SUM(MONETARY.PRINCIPAL)*-1) AS TOTAL_PRINCIPAL, 
			CASE WHEN SUM(MOD(((MONETARY.PRINCIPAL)*-1),5)) > 20 THEN 20 ELSE SUM(MOD(((MONETARY.PRINCIPAL)*-1),5)) END AS CASH_REWARDS,
			COUNT(MONETARY.SOURCE) AS TRAN_COUNT, (ACCOUNT.ACCOUNT_NUMBER) AS ACCOUNT_NUMBER, SHARE.ID AS SHARE_ID, SH_TYPE.CODE AS ACCLEV,
			(TO_CHAR(MONETARY.POSTING_DATE, 'YYYY-MM')) AS REWARD_MONTH, SHARE.TYPE_SERIAL AS SHARE_TYPE_SERIAL
		FROM 
			CORE.ENV AS ENV CROSS JOIN 
			CORE.MONETARY AS MONETARY INNER JOIN 
			CORE.SHARE AS SHARE ON MONETARY.TARGET_CATEGORY='S' AND MONETARY.TARGET_SERIAL=SHARE.SERIAL INNER JOIN 
			CORE.SH_TYPE AS SH_TYPE ON SHARE.TYPE_SERIAL=SH_TYPE.SERIAL INNER JOIN 
			CORE.ACCOUNT AS ACCOUNT ON SHARE.PARENT_SERIAL=ACCOUNT.SERIAL LEFT OUTER JOIN 
			CORE.LOGIN AS LOGIN ON LOGIN.ACCOUNT_SERIAL=ACCOUNT.SERIAL 
		WHERE 
			SHARE.SERIAL IN (
				SELECT 
					SHARE.SERIAL 
				FROM 
					CORE.ENV AS ENV CROSS JOIN 
					CORE.MONETARY AS MONETARY INNER JOIN 
					CORE.SHARE AS SHARE ON MONETARY.TARGET_CATEGORY='S' AND MONETARY.TARGET_SERIAL=SHARE.SERIAL INNER JOIN 
					CORE.SH_TYPE AS SH_TYPE ON SHARE.TYPE_SERIAL=SH_TYPE.SERIAL INNER JOIN 
					CORE.ACCOUNT AS ACCOUNT ON SHARE.PARENT_SERIAL=ACCOUNT.SERIAL 
				WHERE 
					MONETARY.POSTING_DATE BETWEEN ((ENV.POSTING_DATE +1 DAY - DAY(ENV.POSTING_DATE) DAYS)) AND ENV.POSTING_DATE AND MONETARY.CATEGORY = 'W' 
					AND MONETARY.SOURCE IN ('C','O') AND ACCOUNT.E_STMT_OPTION = 'E' 
					AND SHARE.CLOSE_DATE IS NULL
					GROUP BY SHARE.SERIAL 
					HAVING COUNT(*) > 0 
					ORDER BY SHARE.SERIAL ASC
		)
			AND MONETARY.POSTING_DATE BETWEEN ((ENV.POSTING_DATE +1 DAY - DAY(ENV.POSTING_DATE) DAYS)) AND ENV.POSTING_DATE
			AND MONETARY.POSTING_DATE BETWEEN ((ENV.POSTING_DATE +1 DAY - DAY(ENV.POSTING_DATE) DAYS)) AND ENV.POSTING_DATE
			AND MONETARY.CATEGORY = 'W' AND MONETARY.SOURCE IN ('A') AND MONETARY.PRINCIPAL <> 0
		GROUP BY 
			ACCOUNT.ACCOUNT_NUMBER, SHARE.ID, SH_TYPE.CODE, TO_CHAR(MONETARY.POSTING_DATE, 'YYYY-MM'), SHARE.TYPE_SERIAL 
	) tmpa
	WHERE 
		CASH_REWARDS > 0 
;


-- INTEGER(FLOOR((ENV.POSTING_DATE - COALESCE(PERSON.BIRTH_DATE, ENV.POSTING_DATE))/10000)) AS AGE_YEARS, 
SELECT
	ACCOUNT.ACCOUNT_NUMBER,
	LEFT(PERSON.LAST_NAME,25) AS PERSON_LAST_NAME, 
	COALESCE(PERSON.FIRST_NAME || ' ', '') || PERSON.LAST_NAME AS FULL_NAME,
	INTEGER(FLOOR((ENV.POSTING_DATE - COALESCE(PERSON.BIRTH_DATE, ENV.POSTING_DATE))/10000)) AS AGE_YEARS,
	PERSON.BIRTH_DATE
FROM   
	CORE.ENV AS ENV CROSS JOIN
	CORE.PERSON AS PERSON INNER JOIN
	CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.PRIMARY_PERSON_SERIAL=PERSON.SERIAL AND ACCOUNT.CLOSE_DATE IS NULL
WHERE
	ACCOUNT.ACCOUNT_NUMBER IN ('0000099956','0000099993','0000099944')
;

-- EMPLOYEE QUERY
-- MULTIPLE WAYS TO FIND EMPLOYEES, SEARCHING EMAIL AND SEARCHING ACCESS_RESTRICTION
SELECT 
	USER.USERNAME AS KEYSTONE_USER, 
	PERSON.FIRST_NAME || ' ' || PERSON.LAST_NAME AS FULL_NAME,
	ACCOUNT.ACCOUNT_NUMBER AS ACCOUNT_NUMBER,
	ACCOUNT.OPEN_DATE AS ACCOUNT_OPEN_DATE,
	USER.HIRE_DATE AS HIRE_DATE,
	INTEGER(FLOOR((CURRENT DATE - (USER.HIRE_DATE + 7 DAYS))/10000)) AS YEARS_EMPLOYED,
	MONTH(USER.HIRE_DATE) || '-' || DAY(USER.HIRE_DATE) AS HIRE_MONTH_DAY,
	MONTH(PERSON.BIRTH_DATE) || '-' || DAY(PERSON.BIRTH_DATE) AS BIRTH_MONTH_DAY
FROM 
	CORE.USER AS USER INNER JOIN 
	CORE.ROLE AS ROLE ON USER.ROLE_SERIAL=ROLE.SERIAL LEFT OUTER JOIN
	CORE.PERSON AS PERSON ON PERSON.SERIAL=USER.PERSON_SERIAL LEFT OUTER JOIN
	CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.PRIMARY_PERSON_SERIAL=PERSON.SERIAL AND ACCOUNT.CLOSE_DATE IS NULL
WHERE 
	USER.CATEGORY IN ('P') 
	AND 
	  (UCASE(USER.EMAIL_ADDRESS) LIKE '%@UECU%' OR UCASE(USER.EMAIL_ADDRESS) LIKE '%@EXPE%' OR ACCOUNT.ACCESS_RESTRICTION='E')
	AND USER.TERMINATION_DATE IS NULL
ORDER BY 
	USER.HIRE_DATE ASC
;