[SOLVED] Shipment Track & Trace and Transmission Purges

[ianlo]

Hi all,

I would like to find out if anyone has the following setup?

1) Whenever a transmission purge is executed, the shipment events in the Shipment Track and Trace screens are also purged.

2) This is expected and I have been told that I should use domain.no_status_purge = TRUE to just purge the XML CLOB and not the IE_SHIPMENT_STATUS tables.

3) However, I am receiving approximately 3000 messages per day and I need to manage the tablespace growth and I must keep my shipments for at the least 60 days. It is not sufficient for me to just purge the CLOBs alone. I need to manage the rate of growth of the transmissions without impacting shipment track and trace and purging the shipment events along with the transmissions.

I have also noticed that there is a SS_STATUS_HISTORY table where all the events are stored. Why isn't Shipment Track and Trace using this table?

Is there anyway to workaround this problem?

Thanks for any help!

[sprabhakar]

Hi ilano,
The Oracle provided transmission purge package does not provide this facility. However, this can be implemented through custom stored procedures. You can write an custom stored procedure and purge the CLOB data and data in i_transmission & related tables ( the list of tables depend on the version of OTM you are using).

When you later purge the shipments, the shipment events will be left out as the Oracle Shipment purge does not remove the shipment events associated with the shipment. This can be avoided by capturing the various i_transaction_no's from the ss_status_history table for those shipment's. You can then write an custom stored procedure to purge the shipment events and related information.

If you require more details, post a question and I will help you.

S.Prabhakar