FFS_REP_TYPE_LIST

Details

Schema: FUSION
Object owner: FFS
Object type: VIEW

Query

SQL_Statement
with lang_translation as ( select ln.code, ln.language_id, ln.language_code from ffs_language ln where ln.active = 1 ) select pt.type_id, ln.code, pt.label, ffs_translation_pkg.get_translation( p_table => 'FFS_RESOURCE_TYPE', p_column => 'NAME', p_language => ln.language_code, p_pk1_value => pt.type_id ) as text, substr( 'provider', 1, 200 ) as type_name, null as activity_group from ffs_resource_type pt cross join lang_translation ln union all select l.lookup_id as type_id, ln.code, l.lookup_label as label, ffs_translation_pkg.get_translation( p_tid => to_char(l.lookup_id), p_t_lid => ln.language_id, p_ttable => 'lookup.lid.' \|\| l.lookup_type ) as text, substr( case l.lookup_type when 5 then 'request' when 7 then 'non_working_reason' end, 1, 200 ) as type_name, null as activity_group from ffs_lookup l cross join lang_translation ln where l.lookup_type in ( 3, 5, 7 ) union all select at.type_id, ln.code, at.label, ffs_translation_pkg.get_translation( p_table => 'FFS_ACTIVITY_TYPE', p_column => 'NAME', p_language => ln.language_code, p_pk1_value => at.type_id ) as text, 'activity' as type_name, ffs_translation_pkg.get_translation( p_table => 'FFS_ACTIVITY_TYPE', p_column => 'NAME', p_language => ln.language_code, p_pk1_value => at.group_id ) as activity_group from ffs_activity_type at cross join lang_translation ln where at.group_id != 0 union all select it.type_id, ln.code, it.label, ffs_translation_pkg.get_translation( p_table => 'FFS_INVENTORY_TYPE', p_column => 'NAME', p_language => ln.language_code, p_pk1_value => it.type_id ) as text, 'inventory' as type_name, '' as activity_group from ffs_inventory_type it cross join lang_translation ln union all select ws.work_skill_id as type_id, ln.code, ws.label, ffs_translation_pkg.get_translation( p_table => 'FFS_WORK_SKILL', p_column => 'NAME', p_language => ln.language_code, p_pk1_value => ws.work_skill_id ) as text, case when ws.type != 'capacity_category' then 'work_skill' else 'capacity_category' end as type_name, '' as activity_group from ffs_work_skill ws cross join lang_translation ln where ws.type is not null union all select 1 as type_id, ln.code, tr.translation_id as label, ffs_translation_pkg.get_translation( p_tid => tr.translation_id, p_t_lid => ln.language_id, p_ttable => 'trigger_label' ) as text, 'trigger' as type_name, '' as activity_group from ( select distinct t.translation_id from ffs_translation t where t.translation_table = 'trigger_label' ) tr cross join lang_translation ln

SQL_Statement

with lang_translation as (

select ln.code,

ln.language_id,

ln.language_code

from ffs_language ln

where ln.active = 1

)

select pt.type_id,

ln.code,

pt.label,

ffs_translation_pkg.get_translation(

p_table => 'FFS_RESOURCE_TYPE',

p_column => 'NAME',

p_language => ln.language_code,

p_pk1_value => pt.type_id

) as text,

substr(

'provider',

1,

200

) as type_name,

null as activity_group

from ffs_resource_type pt

cross join lang_translation ln

union all

select l.lookup_id as type_id,

ln.code,

l.lookup_label as label,

ffs_translation_pkg.get_translation(

p_tid => to_char(l.lookup_id),

p_t_lid => ln.language_id,

p_ttable => 'lookup.lid.' || l.lookup_type

) as text,

substr(

case l.lookup_type

when 5 then

'request'

when 7 then

'non_working_reason'

end,

1,

200

) as type_name,

null as activity_group

from ffs_lookup l

cross join lang_translation ln

where l.lookup_type in ( 3,

5,

7 )

union all

select at.type_id,

ln.code,

at.label,

ffs_translation_pkg.get_translation(

p_table => 'FFS_ACTIVITY_TYPE',

p_column => 'NAME',

p_language => ln.language_code,

p_pk1_value => at.type_id

) as text,

'activity' as type_name,

ffs_translation_pkg.get_translation(

p_table => 'FFS_ACTIVITY_TYPE',

p_column => 'NAME',

p_language => ln.language_code,

p_pk1_value => at.group_id

) as activity_group

from ffs_activity_type at

cross join lang_translation ln

where at.group_id != 0

union all

select it.type_id,

ln.code,

it.label,

ffs_translation_pkg.get_translation(

p_table => 'FFS_INVENTORY_TYPE',

p_column => 'NAME',

p_language => ln.language_code,

p_pk1_value => it.type_id

) as text,

'inventory' as type_name,

'' as activity_group

from ffs_inventory_type it

cross join lang_translation ln

union all

select ws.work_skill_id as type_id,

ln.code,

ws.label,

ffs_translation_pkg.get_translation(

p_table => 'FFS_WORK_SKILL',

p_column => 'NAME',

p_language => ln.language_code,

p_pk1_value => ws.work_skill_id

) as text,

case

when ws.type != 'capacity_category' then

'work_skill'

else

'capacity_category'

end as type_name,

'' as activity_group

from ffs_work_skill ws

cross join lang_translation ln

where ws.type is not null

union all

select 1 as type_id,

ln.code,

tr.translation_id as label,

ffs_translation_pkg.get_translation(

p_tid => tr.translation_id,

p_t_lid => ln.language_id,

p_ttable => 'trigger_label'

) as text,

'trigger' as type_name,

'' as activity_group

from (

select distinct t.translation_id

from ffs_translation t

where t.translation_table = 'trigger_label'

) tr

cross join lang_translation ln