Hi,
Does any one can assit me with finding n solution for this error message. It is occured in P6 Glaboal schedule services - Publish Enterprise Summaries services.
"2016-11-11 15:19:52:[TASK]:[PxEpsSpread]: [4] CORE(SQL): Errors occurred when executing following statement: DECLARE vupdate_date varchar2(20); msg varchar(2000); vspid number; vtotal pls_integer := 0; vupd pls_integer := 0; vins pls_integer := 0; i_start pls_integer; i_end pls_integer; vsec number; vrate number; start_time timestamp; end_time timestamp; vmsg varchar2(1000); vmsg_cnt pls_integer := 0; df varchar2(22) := 'yyyy-mm-dd hh24:mi:ss'; vparent_wbs_id projwbs.parent_wbs_id%type; vobs_id obs.obs_id%type; vobs_name obs.obs_name%type; BEGIN vupdate_date := :update_date; vspid := sys_context('USERENV', 'SID') ; i_start := dbms_utility.get_time; execute immediate 'select current_timestamp from dual' into start_time; for erec in (SELECT distinct eh.parentobjectid FROM project p, projwbs pw, epshierarchy eh WHERE p.project_flag='Y' and p.proj_id = pw.proj_id and pw.proj_node_flag ='Y' and eh.childobjectid = pw.parent_wbs_id and p.orig_proj_id IS NULL and p.px_last_update_date > to_date(vupdate_date,'yyyy-mm-dd hh24:mi:ss') ) LOOP select parent_wbs_id, pw.obs_id, o.obs_name into vparent_wbs_id, vobs_id, vobs_name from projwbs pw, obs o where pw.obs_id = o.obs_id and pw.wbs_id = erec.parentobjectid; DELETE /*+ INDEX(EPSSPREAD NDX_EPSSPREAD_OBJID) */ FROM EPSSPREAD where ObjectID = erec.parentobjectid; INSERT INTO EpsSpread (OBJECTID ,ID ,NAME ,PARENTOBJECTID ,PROJECTOBJECTID ,OBSOBJECTID ,OBSNAME ,STARTDATE ,ENDDATE ,ACTUALLABORUNITS ,ACTUALNONLABORUNITS ,ATCOMPLETIONLABORUNITS ,ATCOMPLETIONNONLABORUNITS ,BASELINEPLANNEDLABORUNITS ,BASELINEPLANNEDNONLABORUNITS ,EARNEDVALUELABORUNITS ,ESTIMATEATCOMPLETIONLABORUNITS ,ESTIMATETOCOMPLETELABORUNITS ,PLANNEDVALUELABORUNITS ,REMAININGLABORUNITS ,REMAININGNONLABORUNITS ,ACTUALCOST ,ACTUALEXPENSECOST ,ACTUALLABORCOST ,ACTUALMATERIALCOST ,ACTUALNONLABORCOST ,ACTUALTOTALCOST ,ATCOMPLETIONEXPENSECOST ,ATCOMPLETIONLABORCOST ,ATCOMPLETIONMATERIALCOST ,ATCOMPLETIONNONLABORCOST ,ATCOMPLETIONTOTALCOST ,BASELINEPLANNEDEXPENSECOST ,BASELINEPLANNEDLABORCOST ,BASELINEPLANNEDMATERIALCOST ,BASELINEPLANNEDNONLABORCOST ,BASELINEPLANNEDTOTALCOST ,EARNEDVALUECOST ,ESTIMATEATCOMPLETIONCOST ,ESTIMATETOCOMPLETECOST ,PLANNEDVALUECOST ,REMAININGEXPENSECOST ,REMAININGLABORCOST ,REMAININGMATERIALCOST ,REMAININGNONLABORCOST ,REMAININGTOTALCOST ) SELECT a.objectid, a.id, a.name, a.parentobjectid, a.projectobjectid, a.obsobjectid, a.obsname, a.startdate, a.enddate, a.ACTUALLABORUNITS, a.ACTUALNONLABORUNITS, a.ATCOMPLETIONLABORUNITS, a.ATCOMPLETIONNONLABORUNITS, a.BASELINEPLANNEDLABORUNITS, a.BASELINEPLANNEDNONLABORUNITS, a.EARNEDVALUELABORUNITS, a.ESTIMATEATCOMPLETIONLABORUNITS, a.ESTIMATETOCOMPLETELABORUNITS, a.PLANNEDVALUELABORUNITS, a.REMAININGLABORUNITS, a.REMAININGNONLABORUNITS, a.ACTUALCOST, a.ACTUALEXPENSECOST, a.ACTUALLABORCOST, a.ACTUALMATERIALCOST, a.ACTUALNONLABORCOST, a.ACTUALTOTALCOST, a.ATCOMPLETIONEXPENSECOST, a.ATCOMPLETIONLABORCOST, a.ATCOMPLETIONMATERIALCOST, a.ATCOMPLETIONNONLABORCOST, a.ATCOMPLETIONTOTALCOST, a.BASELINEPLANNEDEXPENSECOST, a.BASELINEPLANNEDLABORCOST, a.BASELINEPLANNEDMATERIALCOST, a.BASELINEPLANNEDNONLABORCOST, a.BASELINEPLANNEDTOTALCOST, a.EARNEDVALUECOST, a.ESTIMATEATCOMPLETIONCOST, a.ESTIMATETOCOMPLETECOST, a.PLANNEDVALUECOST, a.REMAININGEXPENSECOST, a.REMAININGLABORCOST, a.REMAININGMATERIALCOST, a.REMAININGNONLABORCOST, a.REMAININGTOTALCOST FROM (select /*+ INDEX(EH NDX_EPSHIER_PARENTOBJ) INDEX(PS NDX_PROJSPREAD_PARENTEPSID) */ eh.parentObjectid objectid, eh.parentid ID, eh.parentname NAME, vparent_wbs_id ParentObjectId, eh.parentprojectid ProjectObjectId, vobs_id ObsObjectId, vobs_name ObsName, ps.startdate,ps.enddate, SUM(ps.ACTUALLABORUNITS) ACTUALLABORUNITS, SUM(ps.ACTUALNONLABORUNITS) ACTUALNONLABORUNITS, SUM(ps.ATCOMPLETIONLABORUNITS) ATCOMPLETIONLABORUNITS, SUM(ps.ATCOMPLETIONNONLABORUNITS) ATCOMPLETIONNONLABORUNITS, SUM(ps.BASELINEPLANNEDLABORUNITS) BASELINEPLANNEDLABORUNITS, SUM(ps.BASELINEPLANNEDNONLABORUNITS) BASELINEPLANNEDNONLABORUNITS, SUM(ps.EARNEDVALUELABORUNITS) EARNEDVALUELABORUNITS, SUM(ps.ESTIMATEATCOMPLETIONLABORUNITS) ESTIMATEATCOMPLETIONLABORUNITS, SUM(ps.ESTIMATETOCOMPLETELABORUNITS) ESTIMATETOCOMPLETELABORUNITS, SUM(ps.PLANNEDVALUELABORUNITS) PLANNEDVALUELABORUNITS, SUM(ps.REMAININGLABORUNITS) REMAININGLABORUNITS, SUM(ps.REMAININGNONLABORUNITS) REMAININGNONLABORUNITS, SUM(ps.ACTUALCOST) ACTUALCOST, SUM(ps.ACTUALEXPENSECOST) ACTUALEXPENSECOST, SUM(ps.ACTUALLABORCOST) ACTUALLABORCOST, SUM(ps.ACTUALMATERIALCOST) ACTUALMATERIALCOST, SUM(ps.ACTUALNONLABORCOST) ACTUALNONLABORCOST, SUM(ps.ACTUALTOTALCOST) ACTUALTOTALCOST, SUM(ps.ATCOMPLETIONEXPENSECOST) ATCOMPLETIONEXPENSECOST, SUM(ps.ATCOMPLETIONLABORCOST) ATCOMPLETIONLABORCOST, SUM(ps.ATCOMPLETIONMATERIALCOST) ATCOMPLETIONMATERIALCOST, SUM(ps.ATCOMPLETIONNONLABORCOST) ATCOMPLETIONNONLABORCOST, SUM(ps.ATCOMPLETIONTOTALCOST) ATCOMPLETIONTOTALCOST, SUM(ps.BASELINEPLANNEDEXPENSECOST) BASELINEPLANNEDEXPENSECOST, SUM(ps.BASELINEPLANNEDLABORCOST) BASELINEPLANNEDLABORCOST, SUM(ps.BASELINEPLANNEDMATERIALCOST) BASELINEPLANNEDMATERIALCOST, SUM(ps.BASELINEPLANNEDNONLABORCOST) BASELINEPLANNEDNONLABORCOST, SUM(ps.BASELINEPLANNEDTOTALCOST) BASELINEPLANNEDTOTALCOST, SUM(ps.EARNEDVALUECOST) EARNEDVALUECOST, SUM(ps.ESTIMATEATCOMPLETIONCOST) ESTIMATEATCOMPLETIONCOST, SUM(ps.ESTIMATETOCOMPLETECOST) ESTIMATETOCOMPLETECOST, SUM(ps.PLANNEDVALUECOST) PLANNEDVALUECOST, SUM(ps.REMAININGEXPENSECOST) REMAININGEXPENSECOST, SUM(ps.REMAININGLABORCOST) REMAININGLABORCOST, SUM(ps.REMAININGMATERIALCOST) REMAININGMATERIALCOST, SUM(ps.REMAININGNONLABORCOST) REMAININGNONLABORCOST, SUM(ps.REMAININGTOTALCOST) REMAININGTOTALCOST FROM projectspread ps,epshierarchy eh WHERE eh.childobjectid = ps.parentepsobjectid AND eh.parentobjectid = erec.parentobjectid group by eh.parentObjectid, eh.parentid, eh.parentname, vparent_wbs_id, eh.parentprojectid, vobs_id, vobs_name, ps.startdate, ps.enddate ) a; vins := vins+SQL%ROWCOUNT; commit; END LOOP; vtotal := vins; i_end := dbms_utility.get_time; execute immediate 'select current_timestamp from dual' into end_time; vsec := round(( (i_end-i_start) * 0.01), 3); if vsec > 0 then vrate := round( vtotal / vsec , 3); else vrate := 0; end if; vmsg := 'Success[true],' || ' Start[' || to_char(start_time, df) || '], End[' || to_char(end_time, df) || '], ' || ' Elapsed[' ||"
Thanks & Regards,
Ramanathan