Clarity PPM: Invalid Transactions Not Being Processed by the Post Transactions to Financials Job

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

There are invalid transactions in the Administration > Project Management > Invalid Transactions page.

The Post Transactions to Financial job runs and completes but it does not process the XOG financial transactions.
 

Environment

Release:
Component: ODENV

Cause

When a transaction XOG occurs, there are 2 tables that are populated:
a. IMP_TRANSACTIONIMPORT
b. PAC_IMP_TRANSIMPORT_HEADER

The XML should contain a groupId value set which propagates to the following table columns:
a. PAC_IMP_TRANSIMPORT_HEADER.id 
b. IMP_TRANSACTIONIMPORT.pac_imp_header_id

Any error found while running Post Transactions To Financials job (PTF) will set the following fields on the PAC_IMP_TRANSIMPORT_HEADER table:
a. PAC_IMP_TRANSIMPORT_HEADER.errorcode
b. PAC_IMP_TRANSIMPORT_HEADER.importstatus

Any further attempts to run the PTF job in an effort to reprocess the transaction errors will be ignored by the job, therefore
it will not process any transactions with the same groupId from the IMP_TRANSACTIONIMPORT table.

This is the reason we are still seeing the "New Transaction" status even after running PTF job. 

 

Resolution

The resolution is to find the related transaction(s) causing the failure and attempt to fix them. If it is not fixed, then any attempt to XOG in any new transactions with the same groupId, none of the transactions will get processed by the PTF job. 

In summary, if PAC_IMP_TRANSIMPORT_HEADER did not have any errors for the certain groupId, the issue would not have occurred. 

Please contact the Support team to review the transactions as the DBA team will need to be provided queries to reset the import status on the Invalid Transactions table.

Steps to Resolve:

0. Backup table IMP_TRANSACTIONIMPORT

1. Review transactions:

select * IMP_TRANSACTIONIMPORT 
where transdate between '<date1>' and '<date2>';

2. Review columns: importstatus and pac_imp_header_id.

3. Analyze and use one or more of the following queries. Review with Support team.

--Set transactions to New
update IMP_TRANSACTIONIMPORT 
set importstatus = 'N', 
pac_imp_header_id = 0 
where pac_imp_header_id = ???
and transdate between '<date1>' and '<date2>';
commit;

--Set transactions to Hold
update IMP_TRANSACTIONIMPORT 
set importstatus = 'H', 
pac_imp_header_id = 0 
where pac_imp_header_id = ???
and transdate between '<date1>' and '<date2>';
commit;

--Ignore rejected transactions
update IMP_TRANSACTIONIMPORT 
set importstatus = 'H', 
pac_imp_header_id = 0 
where errorcode not in ('<ERROR LIST>') ;
commit;

4. . Run PTF job in specific date ranges, targeting specific financial transactions.

Additional Information

Reference KB000049034 - Clarity PPM: Jobs stuck in Waiting or Scheduled status