How to get Audit Report using CURL CMD from Oracle HCM Cloud:

Roles required:
Audit Access for Cloud Access Security Broker
Internal Auditor (Optional)
IT Auditor (Optional)

This SQL will list all the VO object that are enabled for Audit.

Select * from FND_AUDIT_WEBAPP_AM where WEBAPP = 'hcmCore';


curl -i -k --user <UserName>:<Pwd> -H "Content-Type: application/json" -X POST --data @GetAuditData.json https://<HCM Cloud Host Name>/fscmRestApi/fndAuditRESTService/audittrail/getaudithistory > OutputFileName.txt

Json File Sample content:
{
    "fromDate": "2019-01-08",
    "toDate": "2019-01-12",
    "product": "hcmCore",
    "businessObjectType": "oracle.apps.hcm.people.core.uiModel.view.ManagePersonVO",
 "includeAttributes":"false",
 "attributeDetailMode":"true",
    "includeChildObjects":"true",
 "includeImpersonator":"true",
 "timeZone":"US Pacific Time"
}

{
    "fromDate": "2018-10-10",
    "toDate": "2018-11-06",
    "product": "hcmCoreSetup",
 "businessObjectType": "oracle.apps.hcm.workStructures.grades.uiModel.view.GradeVO",
 "includeAttributes":"false",
 "attributeDetailMode":"true",
    "includeChildObjects":"true",
 "includeImpersonator":"true",
 "timeZone":"US Pacific Time"
}

curl -i -k -u <UserName>:<Pwd> -X GET https://<HCM Cloud Host Name>/hcmRestApi/scim/Users > OutputFileName.txt

 

How to fetch last run date of a BI Report in HCM Cloud.

You can do this in two ways, either get the processing start date of the previous run (you will have to modify the below Sql a little bit for this approach) or have a effective date parameter in your report and use below query to fetch the value from previous run.

SELECT TO_DATE(SUBSTR(PP.VALUE, INSTR(PP.VALUE, '"', 1, 3) + 1, 19), 'MM-DD-YYYY HH24:MI:SS')  LAST_RUN_DATE
FROM fusion_ora_ess.request_history_view rh
 ,fusion_ora_ess.request_property_view rp
 ,fusion_ora_ess.request_property_view pp
WHERE 1 = 1
 AND rp.requestid = rh.requestid
 AND rp.value LIKE '/Custom/My Report folder/../../MyReport.xdo'
 AND pp.requestid = rh.requestid
 AND rh.processstart = (
  SELECT max(rhs.processstart)
  FROM fusion_ora_ess.request_history_view rhs
   ,fusion_ora_ess.request_property_view rps
  WHERE 1 = 1
   AND rps.requestid = rhs.requestid
   AND rps.value = rp.value
   AND rhs.executable_status = 'SUCCEEDED'
  )
 AND pp.NAME = 'report_params'
 AND rh.executable_status = 'SUCCEEDED'
 ;

Oracle HCM Cloud Approval Transactions Query

You can use below SQL as a reference to build your report to fetch the approval transactions per your need.

SELECT A.*
FROM
(
SELECT txnh.module_identifier ProcessName,
  wft.creator Requestor,
  wft.assignees CurrentAssignee,
  wft.assigneddate AssignedDate,
  wft.title NotificationTitle,
  txnd.status TxnStatus,
  txnh.object ObjectName
FROM fusion.per_all_people_f dp,
  fusion.per_person_names_f_v n,
  fusion.per_all_assignments_m asg,
  fusion.hrc_txn_header txnh,
  fusion.hrc_txn_data txnd,
  fa_fusion_soainfra.WFTASK wft
WHERE dp.person_id             =n.person_id
AND asg.person_id              =n.person_id
AND LENGTH(asg.assignment_type)=1
AND asg.assignment_id          =txnh.object_id
AND wft.identificationkey      =TO_CHAR(txnh.transaction_id)
AND txnh.object                ='PER_ALL_ASSIGNMENTS_M'
AND txnh.transaction_id        =txnd.transaction_id
AND sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.effective_latest_change='Y'
AND sysdate BETWEEN dp.effective_start_date AND dp.effective_end_date
AND sysdate BETWEEN n.effective_start_date AND n.effective_end_date
UNION
SELECT txnh.module_identifier ProcessName,
  wft.creator Requestor,
  wft.assignees CurrentAssignee,
  wft.assigneddate AssignedDate,
  wft.title NotificationTitle,
  txnd.status TxnStatus,
  txnh.object ObjectName
FROM fusion.per_all_people_f dp,
  fusion.per_person_names_f_v n,
  fusion.per_all_assignments_m asg,
  fusion.hrc_txn_header txnh,
  fusion.hrc_txn_data txnd,
  fa_fusion_soainfra.WFTASK wft
WHERE dp.person_id             =n.person_id
AND asg.person_id              =n.person_id
AND LENGTH(asg.assignment_type)=1
AND asg.period_of_service_id   =txnh.object_id
AND wft.identificationkey      =TO_CHAR(txnh.transaction_id)
AND txnh.object                ='PER_PERIODS_OF_SERVICE'
AND txnh.transaction_id        =txnd.transaction_id
AND sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.effective_latest_change='Y'
AND sysdate BETWEEN dp.effective_start_date AND dp.effective_end_date
AND sysdate BETWEEN n.effective_start_date AND n.effective_end_date
UNION
SELECT txnh.module_identifier ProcessName,
  wft.creator Requestor,
  wft.assignees CurrentAssignee,
  wft.assigneddate AssignedDate,
  wft.title NotificationTitle,
  txnd.status TxnStatus,
  txnh.object ObjectName
FROM fusion.hrc_txn_header txnh,
  fusion.hrc_txn_data txnd,
  fa_fusion_soainfra.WFTASK wft
WHERE wft.identificationkey =TO_CHAR(txnh.transaction_id)
AND txnh.object             ='PER_ALL_PEOPLE_F'
AND txnh.transaction_id     =txnd.transaction_id
) A
order by A.TxnStatus