Purge Order, Purge Shipments : HOW does Purge work, selection - sort - eligibility criteria logic

Resolving The Problem

Order Purge works off Task Q

-----Adding another where codition: AND HOLD_FLAG <> 'Y' --------

SELECT /*YANTRA*/ YFS_TASK_Q.TASK_Q_KEY FROM YFS_TASK_Q YFS_TASK_Q WHERE TRANSACTION_KEY = 'PURGE' AND AVAILABLE_DATE <= SYSDATE AND HOLD_FLAG <> 'Y' ORDER BY TRANSACTION_KEY, TASK_Q_KEY, AVAILABLE_DATE <br>SELECT criteria is where Transaction_key=PURGE and AVAILABLE_DATE <=SYSDATE<br>SORT criteria is by Task_Q_Key and then by Available_date<br>ELIGIBILTY - PaymentStatus should be PAID or NOT_APPLICABLE, YFS_ORDER_RELEASE_STATUS should have all records with ModifyTS less than sysdate-PurgeLeadDays<br><br>In a worst case scenario if you need to manipulate database to push some records ahead in the Purge order, tweak these fields.<br><br>ShipmentPurge does NOT work off TaskQ<br><br>WHERE YFS_SHIPMENT.MODIFYTS < '20041211000000' <br>AND YFS_SHIPMENT.ENTERPRISE_CODE ='DEFAULT' <br>AND YFS_SHIPMENT.SHIPMENT_CLOSED_FLAG = 'Y' <br>AND YFS_SHIPMENT.DOCUMENT_TYPE='0001' <br>AND NOT EXISTS ( SELECT C.ORDER_HEADER_KEY <br> FROM YFS_CONTAINER_DETAILS B, <br> YFS_ORDER_HEADER C <br>WHERE B.SHIPMENT_KEY = YFS_SHIPMENT.SHIPMENT_KEY <br>AND C.ORDER_HEADER_KEY = B.ORDER_HEADER_KEY ) <br>ORDER BY YFS_SHIPMENT.SHIPMENT_KEY<br><br>SELECT criteria is where Shipment.ShipmentClosedFlag=Y and Shipment.ModifyTS <=SYSDATE-leaddays, and No Active Order<br>SORT criteria is by Shipment_Key (Dont touch)<br>ELIGIBILTY - same as SELECT criteria<br><br>ShipmentPurge is NOT a taskQ based Purge. It uses a not-so-elegant NOTEXISTS query, which could cause a very large crossproduct join for Oracle.<br>It will purge only shipments whose corresponding orders are already purged.<br>We need to help Oracle get lucky with the records that it purges<br>and make the outer query be a success and the inner NOT EXISTS query return no rows.<br><br>For this we can run the order purge with a tighter window say 89 days than the shipment purge say 90 days.<br><br>(refer soln 15735 about purging orders before purging shipment)<br>Purge Shipments also expects the Shipment_CLOSED_Flag to be ='Y'<br>Some administrators realize very late in their implementaion that they ought to be running shipmentClose, and they run this agent, this basically resets their ModifyTS to syssdate. prompting them to manipulate database to tweak modifyTS only.<br>This is OK, but needs to be done with some strategy.<br>Do NOT BLINDLY reset ALL records to an old sysdate - 365 or so.<br>Instead do it in 'sweeps'<br>the SQL that checks where Shipment.modifyTS <20050101 and NOT EXISTS ...<br>will get overwhelmed with qualifying Shipment records, which it then has to whittle down after negative checking existense of Orders.<br>if this SQL got only say 3 months of data at a time, it would be much faster.<br><br>For this you should make sure Shipment purge runs in sweeps, where you first set shipmentpurge criteria to say 365 days<br>and purge out all 1 yr old shipments<br>then set it to 270 days and purge out 9 months old shipments then 180 days then 90 days and so on.<br>this way we can be assured older records in the db which meet all the WHERE criteria get picked first.<br><br>In case of timestamp manipulated shipments<br>1. Execute below sql and set purge retention time to 360 days .<br>update YFS_SHIPMENT set MODIFYTS = sysdate - 365<br>where SHIPMENT_KEY < '20040917' and SHIPMENT_CLOSED_FLAG = 'Y'<br><br>run purge<br><br>2. Execute below sql and set purge retention time to 270 days .<br>update YFS_SHIPMENT set MODIFYTS = sysdate - 272<br>where SHIPMENT_KEY < '20041212' and SHIPMENT_CLOSED_FLAG = 'Y'<br><br>run purge<br><br>3. Execute below sql and set purge retention time to 180 days .<br>update YFS_SHIPMENT set MODIFYTS = sysdate - 182<br>where SHIPMENT_KEY < '20050319' and SHIPMENT_CLOSED_FLAG = 'Y'<br><br>run purge <br><br>4. Execute below sql and set purge retention time to 90 days .