Peoplesoft Data Archive Manager - Delete using selective query

Solution 201060969: Cannot use Selective Query to Delete
 
SPECIFIC TO:  Enterprise, PeopleTools 8.44 and higher
 
ISSUE:
 
    When attempting to use Data Archive Manager to Delete data from online  tables the following occurs...
  
    EXPECTED BEHAVIOR
    -----------------------
    Expect to be able to use a Selective Query
 
    ACTUAL BEHAVIOR
    -----------------------
    No queries are displayed in the drop down for Selective Query
   
    STEPS
    -----------------------
    By following these steps the issue can be reproduced:
    1. PeopleTools -> Archive Data To History
    2. Process Type: Delete
    3. Select Selective Query radio button
 
See that there is nothing for drop down in Selective Query.
Clicking on Save yields the following error message:
Please define a Selective Restoring Query for this Process. (142,163)
A Selective restoring query must be defined for this batch job.
 
According to Data Archive Manager PeopleBooks, 'Each of the queries to be used by the Data Archive Manager must be defined as either an Archive type or as a Restore type. '
According to Query PeopleBooks there are only 4 types of queries - User, Process, Role and Archive.
 
Restore is not a valid query type.
 
SOLUTION:
 
A 'Restore' query is an Archive query based on the history table.
 
The following will delete the rows in the on-line tables that match the last batch number selected by the Selection process.
 
To implement the solution, the following steps needs to be done:
 
Please try the following in a test environment.
          
1. Navigate to  PeopleTools -> Data Archive Manager -> Home Page -> Query Manager
2. Create New Query
3. Add the Record name of the history table of the base table.
4. Add an expression for MAX(A.PSARCH_BATCHNUM), Use as Field
5. Save As
    Query: <give the 'Restore' query a name>
    Query Type: Archive
    Owner: Public
    OK
6. Navigate to  PeopleTools -> Data Archive Manager -> Home Page -> Manage Archive Templates
7. Select your Archive Template
8. Select the check box for 'Template Allows Selective Restoring of Data From History
9. Add your new Restore query to the Selective Restoring Queries section
10. Save
11. Navigate to  PeopleTools -> Data Archive Manager -> Home Page -> Archive Data to History
12. Create a new run control
13. Process Type: Delete
14. Selective Query: Restore query name
15. Save
16. Run.

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63: