Asset Vs. Work Order Costs

Asset Vs. Work Order Costs


We have had an issue where work order costs have not matched Asset Costs. In one case we had a 40,000 work order that was not reflected on an asset. Although we have not come up with a root cause, I believe that the issue was caused by someone updating the system using scripts.

Work order costs can be found in the view SV1_WORK_ORDER_TASK_COST. These roll to SA_ACCOUNT_LOG and then to SA_ASSET_LOG.
We had to match the work order to the account log by Work Order, Task, Expense Code and Asset. The query to check this is:


select a.expense_code, a.WORK_ORDER_NO, a.WORK_ORDER_TASK_NO, a.asset_id,
    sum (a.transaction_amount) trans_amt,
    min(transaction_id) transaction_id,
    tc.actual_amount, tc.actual_amount - sum (a.transaction_amount) diff
from sa_account_log a,
   (select tc.work_order_no, tc.work_order_task_no, ttt.asset_id, tc.expense_code, sum(tc.actual_amount) actual_amount from
     sv_1_work_order_task_cost tc, sa_work_order_task ttt
where ttt.work_order_no = tc.work_order_no
    and ttt.work_order_task_no = tc.work_order_task_no
    and (tc.work_order_no, tc.work_order_task_no) not in (select ta.work_order_no,  ta.work_order_task_no
from sa_work_order_task_asset_list ta
where ta.work_order_no = tc.work_order_no
   and ta.work_order_task_no = tc.work_order_task_no)
group by tc.work_order_no, tc.work_order_task_no, ttt.asset_id, expense_code
UNION
select tc.work_order_no, tc.work_order_task_no, ttt.asset_id, tc.expense_code, sum(round(actual_amount* percent_split / 100, 2)) actual_amount from
    sv_1_work_order_task_cost tc, sa_work_order_task_asset_list ttt
where ttt.work_order_no = tc.work_order_no
and ttt.work_order_task_no = tc.work_order_task_no
group by tc.work_order_no, tc.work_order_task_no, ttt.asset_id, tc.expense_code
) tc,
sa_work_order_task t where 1 = 1
  and a.work_order_no = tc.work_order_no
  and a.work_order_task_no = tc.work_order_task_no
  and a.expense_code = tc.expense_code
  and a.asset_id = tc.asset_id
  and a.work_order_task_no = t.work_order_task_no
  and a.work_order_no = t.work_order_no
  and t.task_status = 'CLOSED'
group by a.expense_code, a.asset_id, a.WORK_ORDER_NO, a.WORK_ORDER_TASK_NO, a.asset_id, tc.actual_amount
having abs(tc.ACTUAL_AMOUNT - sum (a.transaction_amount)) > 10;

The having clause gets rid of rounding issues from the task asset list that I was unable to duplicate. These are hard to fix, since you cannot determine which line from the work order went to which period and year. We had 452 issues which were all inventory and caused by an interface.

We chose to update one SA_ACCOUNT_LOG row to make them balance. We had to not adjust 10 rows because these are used by SA_ACCOUNT_LOG so the adjustment did not work.

The Comparison between SA_ACCOUNT_LOG and SA_ASSET_COST was much easier. Compare by Period:

select l.expense_code, l.period_year, l.period_month, l.asset_id, sum(transaction_amount),
   direct_actual_amount
from sa_account_log l, sa_asset_cost a where 1 =1
   and a.asset_id = l.asset_id
   and a.expense_code = l.expense_code
   and a.period_year = l.period_year
   and a.period_month = l.period_month
group by l.expense_code, l.period_year, l.period_month, l.asset_id, direct_actual_amount
  having sum(transaction_amount) <> direct_actual_amount

Look for missing rows in the SA_ASSET_COST table.

select a.expense_code, a.period_year, a.period_month, a.asset_id, transaction_amount
from sa_account_log a where 1=1
   and (a.expense_code, a.period_year, a.period_month, a.asset_id) not in (select a.expense_code,  a.period_year, a.period_month, a.asset_id from sa_asset_cost l
where a.asset_id = l.asset_id
   and a.expense_code = l.expense_code
   and a.period_year = l.period_year
   and a.period_month = l.period_month)

Look for missing rows on the SA_ACCOUNT_LOG table.

select a.expense_code, a.period_year, a.period_month, a.asset_id, direct_actual_amount
from sa_asset_cost a where 1 =1
  and nvl(direct_actual_amount, 0) <>0
  and (a.expense_code, a.period_year, a.period_month, a.asset_id) not in (select l.expense_code, l.period_year, l.period_month, l.asset_id from sa_account_log l
where a.asset_id = l.asset_id
and a.expense_code = l.expense_code
and a.period_year = l.period_year
and a.period_month = l.period_month)

You also would want to compare without period, since the system seems to post some entries to a different period between the two tables.

Once we finish validating the procedure that corrects our system and run it in production I will post it.