Retrieving a list of fields

One of the first things I wanted to do when i was learning ITG configuration was: I wanted a list of all the fields that a request type had, along with its properties. You bet its much easy to debug an object if you have all the property values etc associated with it. Given that ITG doesn't feature a full scale development environment, it would be kinda cool if you can have a list of fields along with its attribute in an Excel sheet, right? Well, I didn't really get down to creating an Excel sheet to fetch the details, but I did write this query that serves as a Q&D (quick and dirty) tool for my purpose. Just type in this query in SQL runner right from within Workbench and replace the request type name with the one you want.

SELECT   f.section_id "Section ID", s.section_name "Section name",
   f.prompt "Field prompt", f.description "Description",
   f.parameter_token "Token", v.validation_name "Associated validation",
         f.enabled_flag "Is enabled", f.display_flag "Is displayed",
   f.searchable_flag "Is searchable", f.required_flag "Is required",
   f.parameter_set_field_id,
         f.ROW_NUMBER "Row", f.column_number "Column",
         f.batch_number "Batch", f.parameter_column_number "Parameter"
    FROM knta_parameter_set_fields f, knta_validations v, knta_sections s
   WHERE f.parameter_set_context_id IN (
            SELECT parameter_set_context_id PCS_ID
              FROM knta_parameter_set_contexts PSC
             WHERE context_value IN (
                      SELECT TO_CHAR (request_type_id)
                        FROM kcrt_request_types
                       WHERE request_type_name IN
                                          ('&REQUEST_TYPE_NAME_HERE')
                      UNION
                      SELECT TO_CHAR (request_header_type_id)
                        FROM kcrt_request_types
                       WHERE request_type_name IN
                                          ('&REQUEST_TYPE_NAME_HERE')))
     AND f.validation_id = v.validation_id
     AND f.section_id = s.section_id
ORDER BY 1, 12, 13

As you can see, its not a lot of work. A rather simple query. In fact, if you study the KNTA_Parameter_Set_Fields table, you will find it rather interesting. I did some analysis and was surprised to know how simple it was to do various things I would have liked to do.

-- Ranjeet Rain