Forum Stats

  • 3,838,667 Users
  • 2,262,392 Discussions
  • 7,900,730 Comments

Discussions

HCM Workflow status is not matching between UI and Core TABLE

User_3PRBP
User_3PRBP Member Posts: 1 Red Ribbon

I'm trying to create a report which will display the HCM transactions current status and the Approval details by using the below query. when run the query some of the transaction getting the mismatch status between TAC UI and from the report. UI level status is Approved by someone. but HRC_TXN_DATA table level still it is showing as pending.

HRC_TXN_HEADER.transaction id is not associated with the identificationkey of  wftask or fnd_bpm_task_b. So not able to get the exact status and the approval details.

when I check the HRC_TXN_HEADER.object_id it is available in wftask.identificationkey. but object_id is not a primary key to HRC_TXN_HEADER . so getting duplicate and incorrect result.

--link with wftask

SELECT pap.person_number, txnh.transaction_id, txnd.submitted_date,

       txnh.module_identifier, txnd.status, wf.outcome, wf.identificationkey,

       wf.outcome, wf.enddate, wf.assigneesdisplayname, wf.creator,

       wf.assigneddate

  FROM per_all_people_f pap,

       hrc_txn_header txnh,

       hrc_txn_data txnd,

       fa_fusion_soainfra.wftask wf

WHERE pap.person_id = txnh.subject_id

   AND txnh.transaction_id = txnd.transaction_id

   AND SYSDATE BETWEEN pap.effective_start_date AND pap.effective_end_date

   AND TO_CHAR (txnh.transaction_id) = wft.identificationkey(+)

   AND txnh.transaction_id in (300000067133064,300000088572245,300000094784804)

  

--link with fnd_bpm_task_b

SELECT pap.person_number, txnh.transaction_id, txnd.submitted_date,

       txnh.module_identifier, txnd.status,

       fbt.identification_key identificationkey, fbt.outcome_code outcome,

       fbt.completed_date enddate, fbt.completed_by assigneesdisplayname,

       fbt.initiated_by creator, fbt.initiated_date assigneddate

  FROM per_all_people_f pap,

       hrc_txn_header txnh,

       hrc_txn_data txnd,

       fnd_bpm_task_b fbt

WHERE pap.person_id = txnh.subject_id

   AND txnh.transaction_id = txnd.transaction_id

   AND SYSDATE BETWEEN pap.effective_start_date AND pap.effective_end_date

   AND TO_CHAR (txnh.transaction_id) = fbt.identification_key(+)

   AND txnh.transaction_id in (300000067133064,300000088572245,300000094784804)

Thanks in Advance,

Murugesu.V