1. Tables and Views for Sales, Fusion Service, and Field Service
  2. FFS_REP_TYPE_LIST_V

FFS_REP_TYPE_LIST_V

Details

  • Schema: FUSION

  • Object owner: FFS

  • Object type: VIEW

Query

SQL_Statement

WITH lang_translation AS (

SELECT

ln.code,

ln.language_id,

t.text,

t.translation_id,

t.translation_table

FROM

ffs_translation t

JOIN ffs_language ln

ON ln.language_id = t.language_id

WHERE

ln.active = 1

)

SELECT

pt.type_id,

lt.code,

pt.label,

lt.text,

substr('provider',1,200) AS type_name,

null AS activity_group

FROM

ffs_resource_type pt,

lang_translation lt

WHERE

lt.translation_table = 'provider_type.type_id'

AND pt.type_id = lt.translation_id

UNION ALL

SELECT

l.lookup_id AS type_id,

lt.code,

l.lookup_label AS label,

lt.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,

lang_translation lt

WHERE

lt.translation_table = 'lookup.lid.' || l.lookup_type

AND to_char(l.lookup_id) = lt.translation_id

AND l.lookup_type IN (3,5,7)

UNION ALL

SELECT

at.type_id,

lt.code,

at.label,

lt.text,

'activity' AS type_name,

(

SELECT

text

FROM

(SELECT

tg.text,

ROW_NUMBER() OVER(ORDER BY tg.language_id DESC) row_num

FROM

ffs_translation tg,

ffs_activity_type ag

WHERE

tg.translation_table = 'activity_type.type_id'

AND at.group_id = ag.type_id

AND ag.type_id = tg.translation_id

AND tg.language_id IN (1,lt.language_id)

) src

WHERE

row_num = 1

) AS activity_group

FROM

ffs_activity_type at,

lang_translation lt

WHERE

lt.translation_table = 'activity_type.type_id'

AND at.group_id != 0

AND to_char(at.type_id) = lt.translation_id

UNION ALL

SELECT

it.type_id,

lt.code,

it.label,

lt.text,

'inventory' AS type_name,

'' AS activity_group

FROM

ffs_inventory_type it,

lang_translation lt

WHERE

lt.translation_table = 'inventory_type.type_id'

AND to_char(it.type_id) = lt.translation_id

UNION ALL

SELECT

ws.work_skill_id AS type_id,

lt.code,

ws.label,

lt.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,

lang_translation lt

WHERE

lt.translation_table = 'work_skill.work_skill_id'

AND to_char(ws.work_skill_id) = lt.translation_id

AND ws.type is not null

UNION ALL

SELECT

1 AS type_id,

lt.code,

lt.translation_id AS label,

lt.text,

'trigger' AS type_name,

'' AS activity_group

FROM

lang_translation lt

WHERE

lt.translation_table = 'trigger_label'