Search This Blog

Tuesday, February 6, 2018

WF Comments in Fusion BIP Reports

SELECT txnh.module_identifier ProcessName,
  wft.creator Requestor,
  wft.assignees CurrentAssignee,
  wft.assigneddate AssignedDate,
  wft.title NotificationTitle,
  txnd.status TxnStatus,
  txnh.object ObjectName,  wfc.wfcomment
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,
  hcm_fusion_soainfra.WFTASK wft,
  HCM_FUSION_SOAINFRA.wfcomments wfc

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 wfc.taskid = wft.taskid

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

4 comments: