- Article Type: General
- Product: Aleph
- Product Version: 18.01
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?
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.**
purge, Z31, cash transactions
- Article last edited: 10/8/2013