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;
/