PIA Setup
Data Archive Manager
PeopleTools \ Data Archive Manager \ Homepage
- Create a Archive Record following Oracle Documentation and your Application Engine.
- Define an Archive Object identifying the Archiving table and the history record from step 1
- Create a Public Query to identify the rows you want archived
- Define your Archive Template using the Archive Object from step 2
- Add your Query for the Selective Archiving Query
- Add Archive Selection AE Process Post AE Program MCM_ARCH_BAT
- Create Run Controls
- My Archive example I name it MCM_ARCHIVE
- Using your new Archive Template, Selection process type
- Set the selective query to your query added in the template
- Create your delete run control with the same name adding "_DELETE" in my example it us named MCM_ARCHIVE_DELETE
- Using your new Archive Template, Delete process type
- Note: You need to run the full MCM_ARCHIVE process and delete once manually in order to create a delete run control because Batch Number is a required field.
Jobs Setup
PeopleTools \ Process Scheduler \ JobsCreate a job and add 2 application process instances of PSARCHIVE.
PeopleTools \ Process Scheduler \ Schedule JobSet Definitions
You can now add the Job you created to a existing Jobset or a new Jobset.
- Set the first instance to your ARCHIVE run control
- Set the second instance to the _DELETE run control
Running the Job
Now when this Jobset is run the job will first execute the PSARCHIVE step copying the selection criteria data to the history table. Because of the Archive Template post AE task an App Engine will run updated your batch ID in the second Run Control to the same batch ID run in the first process. The job then executes the second process to delete the batch from the archiving table.Record and Application Engine Setup
- Create a Archive Record following Oracle Documentation
- Create your Application Engine as defined below. In my example it was named MCM_ARCH_BAT
Your Application Engine needs 2 actions. The first one is a standard SQL Init step that loads your state record. I used the delivered record ARCH_RPT_AET as a state record because it already contained the fields I needed.
%Select(ARCH_RPT_AET.OPRID,ARCH_RPT_AET.RUN_CNTL_ID,ARCH_RPT_AET.PSARCH_ID) SELECT OPRID , RUN_CNTL_ID , PSARCH_ID FROM PSARCHRUNCNTL WHERE OPRID = %OperatorId AND RUN_CNTL_ID = %RunControl
The Second is a PeopleCode Action to update the Delete version of the run Control.
/* Description: The AppEngine will be used as in a Archive Template to update a specific Delete Run Control */ Local number &batchNumber; Local string &DeleteRunControl; Local Record &Rec; /* What process is currently running */ MessageBox(0, "", 0, 0, "AET:%1, %2, %3", ARCH_RPT_AET.PROCESS_INSTANCE, ARCH_RPT_AET.RUN_CNTL_ID, ARCH_RPT_AET.PSARCH_ID); /* What Batch ID was created for this Archive Process */ Local String &qry = "SELECT PSARCH_BATCHNUM FROM PSARCHBATCH WHERE PROCESS_INSTANCE = :1"; SQLExec(&qry, ARCH_RPT_AET.PROCESS_INSTANCE, &batchNumber); MessageBox(0, "", 0, 0, "This Batch ID: %1", &batchNumber); /* Check that the matching Delete Run Control exists */ &DeleteRunControl = ARCH_RPT_AET.RUN_CNTL_ID | "_DELETE"; &Rec = CreateRecord(Record.PSARCHRUNCNTL); &Rec.OPRID.Value = ARCH_RPT_AET.OPRID; &Rec.RUN_CNTL_ID.Value = &DeleteRunControl; &Rec.PSARCH_ID.Value = ARCH_RPT_AET.PSARCH_ID; If &Rec.SelectByKey() Then /* Delete Run Control Found */ If &Rec.PSARCH_PROCESS.Value = "D" Then
/* Set batch value */ &Rec.PSARCH_BATCHNUM.Value = &batchNumber; If Not &Rec.Update() Then MessageBox(0, "", 0, 0, "Error updating %1", &DeleteRunControl); End-If; Else MessageBox(0, "", 0, 0, "Your matching run control is not setup as Delete"); End-If; Else MessageBox(0, "", 0, 0, "You do not have a matching run control for the DELETE step"); MessageBox(0, "", 0, 0, "Missing Run Control %1, ARCHIVE ID %2 for user %3" , &DeleteRunControl, ARCH_RPT_AET.PSARCH_ID, ARCH_RPT_AET.OPRID); End-If;