My Oracle Support Banner

Publish Resource Role Assignment Spread Job Fails on an Microsoft SQL Server Database With Error: "PxRsrcRoleAssignmentSpread ... Errors occurred when executing following statement ..." (Doc ID 2179687.1)

Last updated on MAY 21, 2018

Applies to:

Primavera P6 Enterprise Project Portfolio Management - Version 16.1.2.0 and later
Primavera P6 Enterprise Project Portfolio Management Cloud Service - Version 16.1.2.0 and later
Information in this document applies to any platform.

Symptoms

When attempting to run the Publish Resource Role Assignment Spread job using an MSSQL Server database, the following error occurs:

ERROR

In the /services/JT_*/ text logs :

<TIMESTAMP>:[TASK]:[PxRsrcRoleAssignmentSpread]: [9] POST(SQL): Task started[<TIMESTAMP>]
SEVERE: <TIMESTAMP>:[TASK]:[PxRsrcRoleAssignmentSpread]: [7] POST(SQL): Errors occurred when executing following statement:
insert into rsrcroleasgnmentspread(
rsrc_id,spread_start_date,spread_end_date,
plan_qty,staffed_act_qty,unstaffed_act_qty,act_this_per_qty,total_period_qty,
staffed_remain_qty,unstaffed_remain_qty,staffed_total_qty,unstaffed_total_qty,
plan_cost,act_this_period_cost,total_period_cost,staffed_act_cost,unstaffed_act_cost,
staffed_remain_cost,unstaffed_remain_cost,staffed_total_cost,unstaffed_total_cost,rolledup_record)
select rsrc_id,spread_start_date,spread_end_date,pq,saq,uaq,atpq,tpq,srq,urq,stq,utq,pc,atpc,tpc,sac,uac,src,urc,stc,utc,'Y'
from
(select rsrc_id,spread_start_date,spread_end_date,
sum(plan_qty) pq,sum(staffed_act_qty) saq,sum(unstaffed_act_qty) uaq,sum(act_this_per_qty) atpq,sum(total_period_qty) tpq,
sum(staffed_remain_qty) srq,sum(unstaffed_remain_qty) urq,sum(staffed_total_qty) stq,sum(unstaffed_total_qty) utq,
sum(plan_cost) pc,sum(act_this_period_cost) atpc,sum(total_period_cost) tpc,sum(staffed_act_cost) sac,sum(unstaffed_act_cost) uac,
sum(staffed_remain_cost) src,sum(unstaffed_remain_cost) urc,sum(staffed_total_cost) stc,sum(unstaffed_total_cost) utc
from rsrcroleasgnmentspread
where rsrc_id is not null and rolledup_record ='N'
group by rsrc_id,spread_start_date,spread_end_date order by spread_start_date)
SEVERE: <TIMESTAMP>:[TASK]:[PxRsrcRoleAssignmentSpread]: [7] POST(SQL): The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
com.microsoft.sqlserver.jdbc.SQLServerException: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:320)
at com.primavera.infr.db.PPreparedStatement.execute(Unknown Source)
at com.primavera.px.common.PxServiceSqlTaskImpl.a(Unknown Source)
at com.primavera.px.common.PxServiceSqlTaskImpl.a(Unknown Source)
at com.primavera.px.common.PxServiceSqlTaskImpl.executeTask(Unknown Source)
at com.primavera.px.common.PxAbstractServiceTask.execute(Unknown Source)
at com.primavera.px.common.PxAbstractServiceTask.execute(Unknown Source)
at com.primavera.infr.threadpool.ThreadPoolImpl.run(Unknown Source)
at java.lang.Thread.run(Thread.java:745)
<TIMESTAMP>:[TASK]:[PxRsrcRoleAssignmentSpread]: [7] POST(SQL): Task completed[<TIMESTAMP>]

On some occasions, the following error will appear in the WebAccessLog HTML file :

sep-30-2016 11:30:31 391, CEST [ACTIVE] ExecuteThread: '11' for queue: 'weblogic.kernel.Default (self-tuning)' ERROR /rest/admin/serviceStatus/load (User Id: 91 DB Id: 1) com.primavera.bo.rules.calc.JobServiceCE ?:? Error parsing job_data blob for job 'PX Resource Role Assignment Spread 4786'. It will be treated as empty list. Bad job data was: SL_Taskrsrc

com.primavera.PhoenixException: The input to the Decryptor was invalid.
at com.primavera.common.b.b(Unknown Source)
at com.primavera.common.b.a(Unknown Source)
at com.primavera.infr.blobutils.BlobPropertyList.<init>(Unknown Source)
at com.primavera.bo.rules.calc.JobServiceCE.parseJobData(Unknown Source)
at com.primavera.bo.rules.calc.JobServiceCE.calc_JobDataParsed(Unknown Source)
at com.primavera.bo.schema.dispatch.JobServiceCEDispatch.dispatch(Unknown Source)
at com.primavera.bo.rules.calc.JobServiceCE.dispatch(Unknown Source)
at com.primavera.bo.base.CalculateEngineImpl.getObjects(Unknown Source)
at com.primavera.bo.base.CalculateEngineImpl.getObjects(Unknown Source)

STEPS

The issue can be reproduced at will with the following steps:

  1. Enable publication
  2. Publish a project containing resource and/or role data
  3. Check the publication logs
  4. Notice the error

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.