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.

Wam task notes

The note_desc in sa_work_order_task_notes is a long. To convert this to a varchar2 in a view, you need to create a function and pass the work order number, task and note type. then select the note desc into as long. You then need to convert it to a long raw and then a varchar2 with a statement like:


sys.utl_raw.CAST_TO_VARCHAR2(utl_raw.cast_to_raw(k));

This will bomb if the long is over 4K long, however, in this table that is not likely to be an issue.

MTW

WAM 1.9 and GIS

Hello,

After upgrading to WAM 1.9, the GIS interfaces stopped working. The reason is that the 1.9 patch overwrites the SV_GIS_ASSET_LAYOUT view, despite the documentation stating that this code will not be overwritten.

Lesson: Backup this view before upgrading.

Modifying the work order when is it created from a service request

We have a need to make the work order emergency if the service request is for a sewer backup. No other service request problems are emergencies. To do this we created a trigger on the table SA_WORK_ORDER_SERV_REQ_XREF to update the work type. Below is the trigger that does this.



CREATE OR REPLACE TRIGGER SYNERGEN.sift_service_req_copytowo
BEFORE INSERT OR UPDATE OF associated_sr_no
ON sa_work_order_serv_req_xref
FOR EACH ROW

DECLARE

v_plant sa_plant.plant%TYPE;
v_work_order_no sa_work_order.work_order_no%TYPE;
v_task_no sa_work_order_task.work_order_task_no%TYPE;
v_service_request_no sa_service_request.service_request_no%TYPE;
v_service_request_type sa_service_request.service_request_type%TYPE;
v_problem_code sa_service_request.problem_code%TYPE;
v_problem_description sa_service_request.problem_description%TYPE;
v_work_class sa_work_order.work_class%TYPE;
v_work_category sa_work_order.work_category%TYPE;
v_requested_date sa_service_request.requested_date%TYPE;
v_backlog_group sa_service_request.backlog_group%TYPE;
v_crew sa_service_request.crew%TYPE;
v_asset_record_type sa_asset.asset_record_type%TYPE;
v_asset_id sa_asset.asset_id%TYPE;
v_asset_desc sa_asset.asset_desc%TYPE;
v_account_no sa_asset.account_no%TYPE;
v_department sa_asset.department%TYPE;
v_area sa_asset.area%TYPE;
v_user sa_work_order_serv_req_xref.last_update_user%TYPE;
v_serv_req_asset_count NUMBER;
v_message VARCHAR2 (200);
dbms_activity VARCHAR2 (100);
e_length_too_long EXCEPTION;
-- pragma autonomous_transaction;
BEGIN
dbms_activity := 'SIFT_SERVICE_REQ_COPYTOWO trigger for Update of WO';
v_plant := :NEW.plant;
v_work_order_no := :NEW.work_order_no;
v_service_request_no := :NEW.associated_sr_no;
v_work_class := NULL;
v_work_category := NULL;
v_user := 'INTERFACE';
dbms_activity :=
'Query for Service Type and Problem Code for SR NO ['
|| v_service_request_no
|| ']';

BEGIN
SELECT service_request_type, problem_code, problem_description,
requested_date, backlog_group, work_order_no
INTO v_service_request_type, v_problem_code, v_problem_description,
v_requested_date, v_backlog_group, v_work_order_no
FROM sa_service_request
WHERE plant = v_plant AND service_request_no = v_service_request_no;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_service_request_type := NULL;
v_problem_code := NULL;
v_problem_description := NULL;
END;


dbms_activity := 'Set the work order to Emergency if the problem_code is BACK_UP';

if v_problem_code = 'BACK-UP' then
update sa_work_order set work_type = 'E' where plant = v_plant AND work_order_no = v_work_order_no;
end if;


EXCEPTION
WHEN OTHERS
THEN
v_message :=
SUBSTR (dbms_activity || ' ' || SQLCODE || ' ' || SQLERRM, 1, 200);
raise_application_error (-20001, v_message);
END;
/

Custom calls from custom menus

You can make a custom call from a custom menu. This can be a web call or a PL/SQL call.
First, setup the custom menu. Goto ‘Modules Administration Forms’, search for the form and click on it. Click Custom menus to the side and add a menu.



Set the menu title, and add a submenu line. The type should be CUSTOM and the Action should be a unique value. The title will show up under Submenu Title.
Next, you need to add your code to the srpi.pll module. You will need to download and install Forms 10g from Oracle (now called Developer Suite 10g).
Copy srpi.pll from the applications server. It should be in d:\synergen\INSTANCE\forms where instance is the name of your WAM environment.
Open it with Oracle forms:



Double-click on SAPI_TRIGGER




Add your code to this proc:




The trigger name is WHEN-CUSTOM-MENU-ACTION- and your custom menu name. In this case I am calling a stored procedure named mark.
You should be able to read a value from the underlining form using the name_in function. More information can be found at http://download.oracle.com/otn_hosted_doc/forms/forms/A73074_01.pdf
Click the icon in the upper left corner to compile.
After making the change save your work and close the procedure body. Then select the menu Program, Compile Module. This will produce the file srpi.plx. Stop your oc4j services on the WAM server and copy this file to the WAM server. Restart the services and your custom menu should now be there and work.

Custom Reports

Writing customized reports for WAM


Custom reports can be added and integrated into Oracle Utilities Work and Asset Management (WAM) without changing any of the vendor code. This post explains how to add an Oracle custom report and integrate it into the application.

WAM uses a defined parameter form to call each report. The parameter form builds the ‘where clause’ and the ‘order by’ parameters and passes them to the report. The report then appends them to the SQL statement when running the report. If the report is called from a custom menu (defined in the Module Administration – Forms function), then the menu will pass a ‘where clause’ (consisting of the primary key equaling the current record) into the report. All of this functionality resides in the libraries srpt.pll (for the reports) and srpi.pll (for the form).

When the report is run, the form saves the parameters into the table sv_report_queue. This gives a log of what each user has ran, as well as the status of the report.

Setup of development environment

Before building customized reports for WAM, you need to install Oracle 10g Developer. The system is written using the latest verson, so, while other versions of the report developer may work, it is best to use the latest version.

1 – Install Latest version forms and reports (10 not 6)
2 – Create a directory for forms and reports development (j:\spl)
3 – Create a shortcut for the report developer and form developer.
4 – For each shortcut, change the ‘start in’ to the directory you created in #2.
5 – Copy all forms and reports you will work on, as well as srpt.pll, srpi.pll and rptmstr.fmb found on the CMMS server to the directory you created in #2.

It is very important that all your forms and reports development be done in one directory. If you use multiple directories, you will need to create a shortcut for the Forms and Reports developer for each one. You will also need to copy srpt.pll, spri.pll and rptmstr.fmb to the new directory. If you don’t, the libraries and other files will not attach correctly.

Developing reports with Oracle Reports

When developing a custom report, you can start with an existing seeded report, or another custom report. By doing that, you get all the parameter and other information needed to run the report. You can do this be copying the existing report to the new custom report.

Before starting to change the report, you first want to make sure that it will compile. Do this by going to file/connect and then connect to the CMMS database. Then go to Program/Compile/All to compile the report.

The first thing to change is to update the filename of the new report in two places inside the report developer. First, change the report ID parameter default value to the new name. Secondly, change the name in the top level of the navigator.

You then can start to customize the report. To change the SQL for the report go under ‘Data Model’ in the navigator. To change the layout, go under ‘Paper Layout’ to change it.

For the report to run from the form, you will need to make sure that each parameter in the parameter form is a valid column in your SQL statement in the report. If the where clause or order by are invalid, you get the generic error FRM-41214 ‘Unable to run report’. Please see the section under forms for more on the parameters.

You can run the report from your desktop to test your changes.

NOTE: It is very easy within Oracle Reports to invalidate the Report especially when doing the ‘Paper Layout’. If you do this, you may not be able to undo or recover the invalidated version. You will need to restore a backup. To deal with this, create a backup directory and make FREQUENT backups of the report you are working on. You can rename them name_v1.rdf, name_v2.rdf, etc. to keep multiple backups. I would recommend you backup at least every half hour if you are working on a report.

That being said, 10g is much more stable than report 6, which EBS uses.

If you do the steps above, the following about attaching the library will not apply.

If you get errors about SRPT.PLL missing when loading the report, then you will need to attach SRPT.PLL You do this by selecting ‘Attached Libraries’ and clicking on the green plus sign and choosing SRPT.PLL. Say ‘YES’ to the question about removing the path.

Compiling and uploading

When your report is complete, you will need to compile and export the report. To compile, select program – compile – all from the menu. To export your report, select file – export. Export the report as a .REP with the same name as the RDF. You will need to move the REP to the server in order for CMMS to run the report.

If the report does not run from the application, you will get the FRM-41214 error. To see the actual error being returned by the report, you will need to run it directly from the browser. The URL to use is:

http://SERVER:PORT/reports/rwservlet?report=D:\SYNERGEN\CMMST\forms\c_rpt000.rep&destype=cache&desformat=html&userid=student15/password@cmmst

Where c_rpt000.rep is your report and the userid information is your login and password to the database. This will return the actual error that caused the report not to run.

Parameter Forms

The parameter form is the screen that shows up when you run the report, and is what calls the report. The form uses the field names and data to build a ‘where clause’ and ‘order by clause’ and sends them to the report as parameters.

A single form file (FMX) can store multiple parameter forms. Each parameter screen is a separate canvas. You can use rptspl1.fmb (comment on this and I will send) as an example of a parameter form. You can either add parameter forms to rptspl1.fmb or create a new parameter form.

To create a new parameter canvas, do the following:

1 – Go to the data blocks section and create a new data block. Do not use the wizard.

2 – Ctrl-click and drag each of the items under the REPORT_TEMPLATE data block to the new data block. Make sure to subclass B_RUN_REPORT and copy the others.

3 – Add any parameters you need to the data block.

4 – Create a new window object. Name it the same as the form

5 – Create a new canvas using the wizard. Choose the correct data block. When you get to the list of fields, choose them all but the plant. Select the B_RUN_REPORT parameter and make sure that it is a push button and has a size.

6 – Arrange the parameter form so it looks good.

7 – Open the property pallet for the canvas and make sure the window is the same as created in #4.

8 – Save, compile the PL/SQL and compile the module.

Field naming conventions

The libraries use the field names on the form as column names on the form, and to identify the order by field. You need to name the fields in the data block and canvas as they are in the database. The field names can be prefixed to identify the operator to use. Below are the prefixes you can use

Prefix on field name
Operator

No prefix
=

OPER_
Drop down list of operators

ORDER_BY_TEXT
The Order by clause

LE_
<=

GE_
>=

EQ_
=

LIST1_
Like

For OPER_ the data is the operator, you need to have another field for the parameter. The OPER_ field type needs to be a pop list.

For Date fields (end in _DATE) the library will set it to get the whole days for LE_ and GE_. So, if you enter 9/1/2007 as a start date and 9/30/07 as the end date, the system will add the code: Date_col >= 9/1/2007 and Date_col < 10/1/07. This way, it gets the whole last day.

To test these, use the dummy report (c_rpt000.rdf) (comment and I will send to you) that does not process the parameters, but prints them on the first page of the report. This way you can make sure your parameters are correct.

You cannot run the form on your desktop; you will need to load it to the test server.

Reports Admin

Report ID: This is the report (RDF) Name.

Select Block: This is the Canvas name in the parameter form.

Location: This is the Parameter Form (FMX) Name. The LOV is in Code table 95.

Type: Does not seem to make any difference. Set it to Listing for now.

Group: This is used to help find things in the responsibility form. Choose the most appropriate.

Custom Menus and Reports

Reports can be put on custom menus and ran using the ‘Use Primekeys in where clause’. This skips the parameter form and runs the report using the information contained in the form that calls the report. In order to use the primekeys and bring up a parameter form, you would have to create a custom version of srpi.pll and use it with the form.

To make this work:

1 - Make sure that the from clause of the select statement of the report contains the table that is being viewed in the module.

2 – Register the report.

3 – Create a custom menu for the form. Go to ‘Module administration – Forms’. Find the form you want to add the menu to, click on it, click on custom menu. Add the menu name in the ‘menu title’ field. Enter a sequence number (use 10, 20, etc.). Enter the submenu name into ‘Submenu Title’. Click the ‘display’ box. Choose ‘Report’ for the ‘type’ and enter the report name for the Action. Set ‘Use Primekeys in where clause’. Save your work.

4 – Grant access to the custom menu for the responsibilities that are to run it. Goto Responsibilities, Select the responsibility, choose Custom Menu for the type and add the form to the list.

5 – Test the report. It is helpful to put the raw generated where clause in report to validate that the where clause is passed correctly.

Upgrade to 1.8.1.1

We just upgraded to 1.8.1.1 and found the following issues:

1 - Users cannot reset their passwords (We already have received a patch for this and are testing it.
2 - Mousetrap issue (cannot use mouse to change the focus of the cursor. This is resolved by uninstalling Jinitiator and allowing the system to re-install it.
3 - Advanced edit on the home page does not work unless a form is already open
4 - The help screen is broken (Oracle has a fix for this)

All in all, no major issues.