ITG Plus: Tips & Tricks

A definitive resource for for ITG developers. Check out the nitty gritties of ITG in simple words and learn them through simple examples.

My Photo
Name:
Location: India

A software professional from India with 16+ years of IT experience. Worked on diverse technologies from various vendors, such as Microsoft technologies, IBM groupware, Project and Portfolio Management, Database Systems, Configuration Management, Document Management etc. In my leisure time, I like to share ideas. Hence this blog. Find out more about me. Important note: Ideas and opinion expressed in this blog are strictly my personal opinion and in no way represent the viewpoint of my employer and vice-versa. All content here must be seen in my individual capacity.

Monday, November 07, 2005

Setting the search flag

Recently, I encountered a problem where I needed to make all the fields that were used in request types searchable. By default we don't make all fields searchable. We use our judgement, unless otherwise governed by a Requirement Specification Document, to mark the fields as searchable. Considering that there were many request types and number of fields per request type were large, I didn't think it was a great solution to do all the work manually. A little bit of thinking and I came up with this SQL procedure that would mark all the visible fields on a request type as searchable.





CREATE OR REPLACE PROCEDURE <procedure_name>IS
BEGIN
For i
in
(select PARAMETER_SET_FIELD_ID, DISPLAY_FLAG from knta_parameter_set_fields a where parameter_set_context_id
IN
(Select parameter_set_context_id
from knta_parameter_set_contexts
where context_value
IN
(SELECT TO_CHAR(REQUEST_TYPE_ID) FROM KCRT_REQUEST_TYPES WHERE REQUEST_TYPE_NAME IN('<Request_Type_Name>')
)
)
)

LOOP
update knta_parameter_set_fields set SEARCHABLE_FLAG = i.DISPLAY_FLAG WHERE
parameter_set_field_ID =
i.PARAMETER_SET_FIELD_ID
;
END
LOOP;
commit;




EXCEPTION

WHEN others
then
rollback;
dbms_output.put_line (
'Update of the Field Security Tables
Failed :'
sqlcode':'sqlerrm
);
END
<procedure_name>;
/


Notice that the name of the request type to work on is hardcoded in the procedure. One enhancement I would suggest is -- make the procedure accept the name of the request type to set the flag in. The script is straight-forward, so I will not include an explanation. Hopefully, you can use it.

- Ranjeet Rain

0 Comments:

Post a Comment

<< Home