Introduction
Many a times we have to create some sample data into the application either for Client Demo (CRP) or even for sanity testing (to check that basic application works as expected) after any major upgrade or patching activity. We do create dummy employees for such purposes. However, once the purpose is served there might be a need to ensure such records are completely removed from the system.
This kind of functionality is only available in Non-Production Environments only and until release 11 this was achieved by a Diagnostic Test named ‘HCM Delete Object’ (do refer article here for complete details).Starting Release 11 there is a new diagnostic test ‘Purge Person’ which is enabled into Test and Stage Environments post you raise an explicit Service Request in My Oracle Support (MOS).
Worked Example
Before we start with a worked out example on using the ‘Purge Person’ data, let us try to identify a person record which we would like to be deleted (purged).
We would search for the person number from the person management screen (Person Number ‘5’ for this example)
Next, we need to navigate to the Diagnostic Framework
Once we click on ‘Run Diagnostic Tests’ this would take us to the Diagnostic Dashboard:
Next we need to search for ‘Purge Person’ Test Name in the Search For Tests option under the Diagnostic Dashboard (this would only show up if you have raised an SR and got the script registered for your specific environment)
We need to click on the checkbox (adjacent to the test name) and click on ‘Add to Run’ button.
Next we need to pass parameters to the test (you need to click on the icon under Input Status and a new dialog button would be opened). We would need to populate the following details:
Purge Person: Input Parameters |
||||
Value Required |
Display Name |
Include |
New Value |
Default Value |
|
Additional Criteria |
|
|
|
Checked |
Commit |
Checked (Non-Editable) |
N |
N |
|
Person ID |
|
|
|
|
Person Number |
Checked |
5 |
|
The screen when populated when look as below:
Note: We are using the ‘Commit’ as ‘N' ( I guess some of you with a EBS technical background having a little idea of employee and assignment APIs could relate this with p_validate parameter which used to hold Boolean value as ‘False’ and ‘True’. Like in EBS we used p_validate as ‘True’ to ensure that all validations are checked but no changes are made to the database the same is achieved here by using Commit as ‘N’)
Once we have populated all the details we should click on Ok. This would take us to the Diagnostic Dashboard page where we would need to click on ‘Run’ button.
Once we click on OK we could see the diagnostic test results.
Inference / Conclusion
From the above execution we could find the SQL used:
SELECT DISTINCT(person.person_id),person.person_number FROM per_all_people_f person WHERE (person.person_number in ('5')) AND person.created_by not in ('SEED_DATA_FROM_APPLICATION') AND NOT EXISTS (select 1 from hrc_loader_batch_key_map where object_name = 'PERSON_VO' and target_id = person.person_id and (OWNER='EHR2HR' or OWNER='HRC_SQLLOADER')) AND NOT EXISTS (SELECT 1 FROM pay_payroll_rel_actions pra WHERE pra.payroll_relationship_id IN (SELECT DISTINCT payroll_relationship_id FROM PAY_PAY_RELATIONSHIPS_DN WHERE person_id = person.person_id))
And also that the Skipping Operation was actually skipped as the commit is set to ‘N’.
Once we have verified the report and have confirmed that we need to execute the program for this data set and this person record we may set the COMMIT flag as ‘Y’ and resubmit the same.
Comments
attention-grabb ing article like yours. It's pretty worth enough for me.
In my view, if all site owners and bloggers made excellent content material as you probably did, the net will be a lot more helpful than ever
before.
RSS feed for comments to this post