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.