Purging z31 cash transactions

• Article Type: General
• Product: Aleph
• Product Version: 18.01

Description:
We want to delete old, paid cash transactions, but need to keep non-Lost transactions less than 6 months old and Lost transactions less than 2 years old.

Could the p_cir_32 "scrub" job do this?

Resolution:
Unlike the z35, z36h, and z37h, from which the user ID might be removed and they would still have some use, z31s without a user ID are meaningless. Nevertheless, the p_cir_32 job, new in 16.02, does scrub the user_id from the z31 -- but it doesn't distinguish Lost from non-Lost transactions.

The following SQL could be used to delete non-Lost paid (or waived or transferred*) transactions more than 6 months old:

SQL>delete from z31 where Z31_DATE <= TO_CHAR(SYSDATE - 180, 'YYYYMMDD') and (Z31_STATUS = 'C' or Z31_STATUS = 'W' or Z31_STATUS = 'T') and (Z31_TYPE NOT in (40, 41, 42, 0040, 0041, 0042));

And this to delete paid (or waived or transferred*) Lost transactions more than 2 years old:

SQL>delete from z31 where Z31_DATE <= TO_CHAR(SYSDATE - 730, 'YYYYMMDD') and (Z31_STATUS = 'C' or Z31_STATUS = 'W' or Z31_STATUS = 'T') and (Z31_TYPE in (40, 41, 42, 0040, 0041, 0042));

* T = Transactions transferred to the university Accounting office in cases where they do the money-handling. The p_cash_09 job does the export.

**Note: Before doing either of these commands you need to back up the z31 table, using p_file_03.**