domain element value

Hi Tiago,

In the Software Factory, we have a function called get_dom_elemnt and based on the domain you supply, it'll return you all elements, their db value and their translation in English in our case.

However, this is simply a function we created ourselves. You can do the same for this situation.

1. Create a subroutine that returns a table
2. Create a subroutine parameter (if needed) to filter the list of elements that the function may return.
3. Create a SQL Control procedure for the subroutine
4. Create a template with some parameters that will be replaced when you generate the control procedure.

Our SQL code looks like this, you can modify it to your liking:

insert into #prog_object_item
(
    prog_object_id,
    prog_object_item_id,
    order_no,
    template_id
)
select
    'func_get_dom_elemnt',
    @control_proc_id,
    10,
    @control_proc_id

insert into #prog_object_item_parmtr
(
    prog_object_id,
    prog_object_item_id,
    parmtr_id,
    parmtr_value,
    order_no,
    no_line_when_empty
)
select
    'func_get_dom_elemnt',
    @control_proc_id,
    'ELEMENT_SET',
    '('''+ e.dom_id + ''',' +
     ''''+ e.elemnt_id + ''',' +  
     ''''+ e.db_value + ''',' +  
     ''''+ coalesce(replace(t.transl, '''', ''''''), '[' + e.elemnt_id + ']') + ''')',
    99 + row_number() over(order by e.dom_id, e.abs_order_no),
    0
from elemnt e
left join transl_object_transl t
  on t.model_id = e.model_id
 and t.branch_id = e.branch_id
 and t.type_of_object = 2 -- Dom element
 and t.transl_object_id = e.elemnt_id
 and t.appl_lang_id = 'en-US'
where e.model_id = @model_id
  and e.branch_id = @branch_id

And the corresponding template code:

-- All elements of a given domain
return
(
    select t.elemnt_id, t.db_value, t.transl
    from
    (
        values
            [ELEMENT_SET],
            ('-', '-', '-', '-') -- Dummy row
    ) as t(dom_id, elemnt_id, db_value, transl)
    where t.dom_id = @dom_id
      and t.dom_id <> '-'
)

Again, this is our implementation. You do not necessarily need all elements of all domains in this function, you can limit it to a couple, or even use a domain tag to determine which domains to include.

Hope this helps!