Clarity PPM: Invalid Transactions Message:

book

Article ID: 143151

calendar_today

Updated On:

Products

STARTER PACK-CLARITY PPM Clarity PPM SaaS

Issue/Introduction

In the Administration >Project Managemen t> Invalid Transactions page, there are financial transactions with a rejected message:
No rate was found for this transaction

Cause

The rate matrix is not set up properly or does not contain a matching row that satisfies the project/resource combination.

Environment

All Clarity versions

Resolution

Run the following queries to determine if the project/resource rate was generated.
Note: The queries are in Oracle

--1 Get resource ID
select* from cmn_sec_users

--2 Get project ID
select * from inv_investments

--3 Get Invalid Transaction record information
select
imp.job_id, IMP.SOURCEMODULE, IMP.EXTERNALBATCHID tsid, IMP.APPLY_TO_EXTERNAL_ID,
TO_CHAR(imp.importdate, 'MM-DD-YYYY HH24:MI:SS Dy') PTFjob,
imp.transdate, imp.quantity, imp.importstatus, imp.errorcode,
err.description, err.lookup_code, imp.project_code, inv.id, inv.name,
IMP.RESOURCE_CODE, SRMR.LAST_NAME, SRMR.FIRST_NAME, IMP.ROLE_CODE,
pacp.status, pacp.type_, pacp.locationid, pacp.departcode,
IMP.INPUT_TYPE, IMP.CHARGE_CODE, imp.fatalerror
from
IMP_TRANSACTIONIMPORT IMP,
imp_transactionimporterrors err,
INV_INVESTMENTS INV,
pac_mnt_projects pacp,
SRM_RESOURCES SRMR
WHERE srmr.unique_name = imp.RESOURCE_CODE
AND INV.CODE = IMP.PROJECT_CODE
and inv.id = pacp.id
AND ERR.ERRORCODE = IMP.ERRORCODE
and srmr.unique_name = 'aaa'
and imp.project_code = 'bbb'
   order by IMP.EXTERNALBATCHID
   
--4 Determine if rate exists for project/resource
SELECT
i.id, i.name, i.code,
PROJECT_ID, RESOURCE_ID, TASK_ID, TEAM_ID, TASK_ROLE_ID, CHARGE_CODE, from_date, to_date, last_modified_date, bill_rate, cost_rate
FROM
NBI_PROJ_RES_RATES_AND_COSTS r,
INV_INVESTMENTS i
where 1=1
and r.project_id = i.id
and PROJECT_ID = xxx
and resource_id = yyy
order by RESOURCE_ID

If a matching row does not exist in the table of rates (NBI_PROJ_RES_RATES_AND_COSTS), which is generated by the Rate Matrix Extraction job,
the rate matrix needs to be reconfigured or modified to have the rates generated for the Post Timesheets job to generate future transaction rates.

The Post Transactions to Financials job uses the live matrix to generate rates, therefore the Filter button in the Administration > Finance > Manage Matrix > [Selected Matrix]
must exist in order for the job to fully process the invalid transaction.