How to identify the 'MISSING' hours in the posted timesheets which are stuck with PRRMEXPORTED = 0

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

When Post Timesheet job runs, it looks for timesheets which are in Approved status and will process them from timesheet tables to imp_transactionimport table(Invalid Transaction List).

But for various reasons like a Project not financially enabled or Project that is not approved and so on, the timesheet entries going into the financial module by the 'Post Timesheet' job however, the status of the timesheet is marked 'Posted'.

Even though these timesheets are in Posted status, the actuals haven't entered into the Invalid Transaction List for the Post Transaction to Financials job to process.

This will bring a lot of confusion to PMO/Application administrators and raises the alarm stating - "My timesheet is in Posted status but I don't see the actuals hours in Invalid Transaction List or waiting for Post to WIP or in WIP table."

Resolution

• As of now,we don't have a way to find these invalid timesheets records from the other than developing a query and running them from the DB.
• A PMO/Application administrator who is not so experienced in database queries will find it difficult to pull the data. 
• Attached is the Invalid Timesheet Query.txt and Invalid Timesheet.xml which can be used to find these invalid timesheets and the reason why they are invalid.
• The XOG XML of the portlet is from v13.2 so it may have the compatibility issue with lower versions, however the query can be executed directly to pull the report out of the database.

Note: This is an initial query/portlet and can be used as an example to expand further and suggestions are welcome.