Changing Expiration Date in Report Archive

Is it not better to use the “Set Default Life Time” option in the report definition window for each report. So, you can set deferent life time for different reports. Don’t know whether you have tried this already.

Thanks,

Hi Thushitha,

as mentioned in my first post we are aware of this option. But this would save many documents, that we do not need to be saved.

In the past what I’ve done with a customer is to set up a report rule with conditions to export them to an FTP and the file naming represents the order_no and date. In that case, the burden is not in the Application / Database, but outside in an FTP folder. This may be an alternative to look at.

This raises the question (for me, anyway) of how many documents “do not need to be saved”. You mention preliminary invoices and test prints in your original post… Test prints should mostly occur in your TEST environment, but I am not sure about “preliminary invoices”. Do you have a feel for what proportion of documents are of this unneeded (to be archived) variety?

I recently developed a client-side printing mechanism that generates Purchase Orders every time someone wants to generate a PO and attach it to an email. This is going to potentially generate hundreds (or even thousands) of extra documents over time, but each one is only around 50-60K. An extra thousand documents is only 50-60 MB or so.

In other words, are you sure that setting the expiration by report type isn’t close enough? Do the extra documents really hurt anything? I like your solution, and the archive area is relatively simply (in terms of the data model), but like you, I would worry that something might go amiss down the line.

Thanks,

Joe Kaufman

Hi Joe,

take this example:

This is the print out of one single invoice report. Due to Report Distribution Groups the report has 27 entries in the archive. Yes, it’s only one result key, hence one report. But for the archiving purpose we only need one single entry (t2.sender=t.user_name is my SQL query in first post).

Another example are the test print outs. I didn’t mean that we are testing development changes on PROD environment. Like you said, that’s what we use TEST for. It’s more like users are checking the data on the documents before actually printing it. In these cases, we want only the documents that have a check mark in the "Printed" field.’ field tagged. And from those also only the latest print out.

Here is the SQL code for the Delivery note:

update archive_distribution_tab t
set t.expire_date = t.expire_date+100
where exists (select *
             from archive_tab t2
             where
             t2.result_key in (select max(t3.result_key)
                              from archive_tab t3
                              where t3.report_id in ('SHIPMENT_DELIVERY_NOTE_REP')
                                and t3.sender=t.user_name
                              group by t3.notes)
             and t2.result_key=t.result_key)
      and trunc(t.expire_date) < to_date('31.08.2021','DD.MM.YYYY')
      and t.printed = 1

So yes, setting all reports to max lifetime is possible. We are just trying to minimize storage space consumption with data, that is not really needed.

ig_ wrote:

sutekh137 wrote:

ig_ wrote:

Hi Thushitha,

 

as mentioned in my first post we are aware of this option. But this would save many documents, that we do not need to be saved.

 

This raises the question (for me, anyway) of how many documents “do not need to be saved”. You mention preliminary invoices and test prints in your original post… Test prints should mostly occur in your TEST environment, but I am not sure about “preliminary invoices”. Do you have a feel for what proportion of documents are of this unneeded (to be archived) variety?

I recently developed a client-side printing mechanism that generates Purchase Orders every time someone wants to generate a PO and attach it to an email. This is going to potentially generate hundreds (or even thousands) of extra documents over time, but each one is only around 50-60K. An extra thousand documents is only 50-60 MB or so.

In other words, are you sure that setting the expiration by report type isn’t close enough? Do the extra documents really hurt anything? I like your solution, and the archive area is relatively simply (in terms of the data model), but like you, I would worry that something might go amiss down the line.

 

Thanks,

Joe Kaufman

Hi Joe,

 

take this example:

This is the print out of one single invoice report. Due to Report Distribution Groups the report has 27 entries in the archive. Yes, it’s only one result key, hence one report. But for the archiving purpose we only need one single entry (t2.sender=t.user_name is my SQL query in first post).

 

Another example are the test print outs. I didn’t mean that we are testing development changes on PROD environment. Like you said, that’s what we use TEST for. It’s more like users are checking the data on the documents before actually printing it. In these cases, we want only the documents that have a check mark in the "Printed" field.’ field tagged. And from those also only the latest print out.

Here is the SQL code for the Delivery note:

update archive_distribution_tab t
set t.expire_date = t.expire_date+100
where exists (select *
             from archive_tab t2
             where
             t2.result_key in (select max(t3.result_key)
                              from archive_tab t3
                              where t3.report_id in ('SHIPMENT_DELIVERY_NOTE_REP')
                                and t3.sender=t.user_name
                              group by t3.notes)
             and t2.result_key=t.result_key)
      and trunc(t.expire_date) < to_date('31.08.2021','DD.MM.YYYY')
      and t.printed = 1

 

So yes, setting all reports to max lifetime is possible. We are just trying to minimize storage space consumption with data, that is not really needed.

Am I right in saying, though, that one result key means only one actual file store as a LOB? Extra data records, while cluttering, should not take up much space (we could calculate the bytes taken per records, I suppose).

But I know what you mean about various printouts being preliminary or parts of “test” processes, even in PROD, and those are definitely going to take up space. At the end of the day, I do not see what could go wrong with your process, since that expiration date only resides in one spot as far as I can tell, and the data model is relatively simple. If it works for cleanup, it should keep working on into the future.

One thing I might suggest is that you may want to use API methods instead of a direct hit against the table. I tend to go that way so that code is more IFS-native (we are in Apps10). For example, the Archive_Distribution_API has a method Set_Expire_Date() that performs an update like you list above. Yeah, it is one update at a time and you would need to pull the data you want and increment the expire date to use, but when it comes to writes I try to always use IFS API methods in case they are doing something else under the hood. In looking at the code for Set_Expire_Date() I do not see anything very interesting (which is why your update code is probably safe), but you may way to consider it anyway.

Thanks,

Joe Kaufman

Hi Joe,

appreciate your input.

My guess is that one result key is one stored file , yes. In this case it’s true that storage space can be ignored. Here it’s more about having a better overall overview in the report archive looking for reports connected to a specific order.

Everything you say about using the APIs is absolutely true. That’s also our normal approach. But just like you did, I analyzed the method and saw it was really basic and decided to go for directly for the change of the table. With thousands of reports to change a week, that’s way faster.

All great points! Be sure to let us know if the manual change of expiration dates ever bites you in the but down the road.  *smile*  I like this idea and will keep it in my back pocket in case we decide we need to do the same sort of thing based on some criteria.

Have a great weekend!

Thanks,

Joe Kaufman