0 Replies Latest reply on Jul 12, 2020 7:02 AM by 4162047

    HCM Workflow status is not matching between UI and Core TABLE

    4162047

      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