The Time Slicing job is not processing all slice requests

Document ID : KB000055199
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:
The Time Slicing job is not processing all slice requests.

The following error gets generated in the bg-ca.log file.


Error in PRJ_BLB_SLICES_INS_SP: ORA-01438: value larger than specified precision allows for this column


Solution:  
This error indicates that somebody typed in a value with too many digits right of the decimal place. Either in the assignment data (Actuals, Estimates, Baselines), Resource Availability, Allocations or Time Entries such as 1.5555555555. Or Someone entered in massive amount of hours such as 99999999999. The Time Slicing job cannot process this number because the database column is not set up to store data at that precision.

In order to tell which of the above fields has the error. Pause the reoccurring Time Slicing Job and run a one time Time Slicing job. Then wait until the error occurs. Run the query below to determine what data element contains the problem record.

SELECT 'Assignment' Slice_Object,Count(*),SLICE_STATUS
FROM prassignment
WHERE SLICE_STATUS = 2
GROUP BY SLICE_STATUS
UNION SELECT 'Allocation' Slice_Object,Count(*),SLICE_STATUS
FROM prteam
WHERE SLICE_STATUS = 2
GROUP BY SLICE_STATUS
UNION SELECT 'Availability' Slice_Object,Count(*),SLICE_STATUS
FROM prj_resources
WHERE SLICE_STATUS = 2
GROUP BY SLICE_STATUS
UNION SELECT 'Timeentries' Slice_Object,Count(*),SLICE_STATUS
FROM prtimeentry
WHERE SLICE_STATUS = 2
GROUP BY SLICE_STATUS

The data element returned with a slice status of 2 contains the problem record. A slice status of 2 means that the Time Slicing job was processing that record at the time of the error.

Once you know what element contains the problem record you need to track down the specific record itself within the application user interface. The queries below will assist in identifying the problem record. It returns the Sum field multiplied by a million. Then you'll need to scan the results and look for a record that has values in the lower digits. (In the one's ten's or hundreds places.) Or a very very large number. Then manually repair the records in the application UI.

To find the record in Task Assignment
SELECT 'Assignment' Slice_Object, p.name project, t.prname task, r.full_name resource_name,
practsum * 1000000 Actuals, prestsum * 1000000 ETC, prbasesum * 1000000 Baseline
FROM prassignment a, prtask t, srm_projects p, srm_resources r
WHERE a.prtaskid = t.prid and t.prprojectid = p.id and a.prresourceid = r.id and a.slice_status = 2

To find the record in Team Allocation
SELECT 'Allocation' Slice_Object, t.prid, p.id, p.name project, r.full_name resource_name, prallocsum * 1000000
FROM prteam t, srm_projects p, srm_resources r
WHERE t.prprojectid = p.id and t.prresourceid = r.id and t.slice_status = 2

To find the record in Time Entries
SELECT r.full_name resource_name, tp.prstart Timeperiod_start, te.practsum*1000000 FROM prtimeentry te, prtimesheet ts, prtimeperiod tp, srm_resources r
WHERE te.prtimesheetid = ts.prid and ts.prtimeperiodid = tp.prid and
ts.prresourceid = r.id and te.slice_status = 2

There is no sum field for availability so the records will need manual inspection in the application UI. The query below with return availability records of slicing records in the 'processing' state, ordered by the modification time in descending order.

To find the record in Resource Availability
SELECT 'Availability' Slice_Object, r.full_name resource_name, PRMODTIME
FROM prj_resources pr, srm_resources r
WHERE pr.prid = r.id and pr.slice_status = 2
order by PRMODTIME desc