Asset Suite ver 6 Oracle Sequence PAYMENT_REF creating gaps

Oracle Sequence PAYMENT_REF is not always generating numbers in order. Gaps are occurring without an apparent pattern. Some gaps are occurring during the processing day so it does not appears to be a database bounce issue.

Currently it is not thought to be causing application integrity problems, however users have noticed it and don't like it suggesting that it might turn into an audit problem.

The following SQL rearranges the Asset Suite Payment Authorization Number back into the original source number.

select substr(paymt_ref_no,6,1) || substr(paymt_ref_no,5,1) || substr(paymt_ref_no,4,1) ||
substr(paymt_ref_no,3,1) || substr(paymt_ref_no,2,1) || substr(paymt_ref_no,1,1)
,ap.*
from tidapmst ap
order by 1 desc

SQL results show that problem started with new database after Asset Suite ver 6 go-live.

Solution:

Remove cache from Oracle Sequence PAYMENT_REF.

‹ Slow response with first online interaction of the day
By webmaster | | read more | add new comment

Reply