IRC_PERSON_TYPES

Details

Schema: FUSION
Object owner: IRC
Object type: VIEW

Columns

Name
SYSTEM_PERSON_TYPE MEANING DISPLAY_SEQUENCE ENABLED_FLAG TYPE INTERNAL_FLAG PRIORITY

Name

SYSTEM_PERSON_TYPE

MEANING

DISPLAY_SEQUENCE

ENABLED_FLAG

TYPE

INTERNAL_FLAG

PRIORITY

Query

SQL_Statement
WITH CWK_PROFILE_CHECK AS ( SELECT CASE WHEN EXISTS ( SELECT 1 FROM fnd_profile_options p JOIN fnd_profile_option_values pv ON p.profile_option_id = pv.profile_option_id WHERE p.profile_option_name = 'IRC_TREAT_CWK_AS_EXTERNAL' AND pv.level_value = 'SITE' AND p.application_id = pv.application_id AND pv.profile_option_value = 'Y' ) THEN 'N' ELSE 'Y' END is_internal_flag FROM dual ) SELECT flv.lookup_code system_person_type, flv.meaning, flv.display_sequence, CASE WHEN flv.lookup_code IN ('EMP', 'CWK', 'EX_EMP', 'EX_CWK', 'ORA_CANDIDATE') THEN 'Y' ELSE flv.enabled_flag END enabled_flag, CASE WHEN flv.lookup_code IN ('EMP', 'CWK', 'EX_EMP', 'EX_CWK', 'ORA_CANDIDATE') THEN 'STANDARD' ELSE 'EXTENDED' END type, CASE flv.lookup_code WHEN 'EMP' THEN 'Y' WHEN 'CWK' THEN c.is_internal_flag ELSE 'N' END internal_flag, CASE flv.lookup_code WHEN 'EMP' THEN 1 WHEN 'CWK' THEN 2 WHEN 'EX_EMP' THEN 3 WHEN 'EX_CWK' THEN 4 WHEN 'ORA_CANDIDATE' THEN 200 WHEN 'NONW' THEN 60 WHEN 'EX_NONW' THEN 10 WHEN 'NONW_PAID' THEN 64 WHEN 'NONW_UNPAID' THEN 68 WHEN 'PWK' THEN 20 WHEN 'EX_PWK' THEN 15 WHEN 'PRJ' THEN 110 WHEN 'PENSIONER' THEN 122 WHEN 'RETIREE' THEN 118 WHEN 'SPONSOR' THEN 140 WHEN 'PRTN' THEN 126 WHEN 'ORA_PER_INTERN' THEN 70 WHEN 'FR_TRAINEE' THEN 86 WHEN 'FR_COMP_DIRECTOR' THEN 84 WHEN 'DPNT' THEN 55 WHEN 'DE_FREELANCER' THEN 82 WHEN 'DE_CONSULTANT' THEN 80 WHEN 'BENNONWRKR' THEN 120 WHEN 'VOLUNTEER' THEN 99 WHEN 'CON' THEN 40 WHEN 'BNF' THEN 50 WHEN 'SRVNG_SPS' THEN 156 WHEN 'SRVNG_FMLY_MMBR' THEN 154 WHEN 'SRVNG_DPFM' THEN 150 WHEN 'SRVNG_DP' THEN 152 ELSE 199 END priority FROM fnd_lookup_values_vl flv CROSS JOIN cwk_profile_check c WHERE flv.lookup_type = 'ORA_IRC_PERSON_TYPE' AND created_by = 'SEED_DATA_FROM_APPLICATION'

SQL_Statement

WITH CWK_PROFILE_CHECK AS (

SELECT CASE

WHEN EXISTS (

SELECT 1

FROM fnd_profile_options p

JOIN fnd_profile_option_values pv

ON p.profile_option_id = pv.profile_option_id

WHERE p.profile_option_name = 'IRC_TREAT_CWK_AS_EXTERNAL'

AND pv.level_value = 'SITE'

AND p.application_id = pv.application_id

AND pv.profile_option_value = 'Y'

)

THEN 'N'

ELSE 'Y'

END is_internal_flag

FROM dual

)

SELECT

flv.lookup_code system_person_type,

flv.meaning,

flv.display_sequence,

CASE

WHEN flv.lookup_code IN ('EMP', 'CWK', 'EX_EMP', 'EX_CWK', 'ORA_CANDIDATE') THEN 'Y'

ELSE flv.enabled_flag

END enabled_flag,

CASE

WHEN flv.lookup_code IN ('EMP', 'CWK', 'EX_EMP', 'EX_CWK', 'ORA_CANDIDATE') THEN 'STANDARD'

ELSE 'EXTENDED'

END type,

CASE flv.lookup_code

WHEN 'EMP' THEN 'Y'

WHEN 'CWK' THEN c.is_internal_flag

ELSE 'N'

END internal_flag,

CASE flv.lookup_code

WHEN 'EMP' THEN 1

WHEN 'CWK' THEN 2

WHEN 'EX_EMP' THEN 3

WHEN 'EX_CWK' THEN 4

WHEN 'ORA_CANDIDATE' THEN 200

WHEN 'NONW' THEN 60

WHEN 'EX_NONW' THEN 10

WHEN 'NONW_PAID' THEN 64

WHEN 'NONW_UNPAID' THEN 68

WHEN 'PWK' THEN 20

WHEN 'EX_PWK' THEN 15

WHEN 'PRJ' THEN 110

WHEN 'PENSIONER' THEN 122

WHEN 'RETIREE' THEN 118

WHEN 'SPONSOR' THEN 140

WHEN 'PRTN' THEN 126

WHEN 'ORA_PER_INTERN' THEN 70

WHEN 'FR_TRAINEE' THEN 86

WHEN 'FR_COMP_DIRECTOR' THEN 84

WHEN 'DPNT' THEN 55

WHEN 'DE_FREELANCER' THEN 82

WHEN 'DE_CONSULTANT' THEN 80

WHEN 'BENNONWRKR' THEN 120

WHEN 'VOLUNTEER' THEN 99

WHEN 'CON' THEN 40

WHEN 'BNF' THEN 50

WHEN 'SRVNG_SPS' THEN 156

WHEN 'SRVNG_FMLY_MMBR' THEN 154

WHEN 'SRVNG_DPFM' THEN 150

WHEN 'SRVNG_DP' THEN 152

ELSE 199

END priority

FROM fnd_lookup_values_vl flv

CROSS JOIN cwk_profile_check c

WHERE flv.lookup_type = 'ORA_IRC_PERSON_TYPE'

AND created_by = 'SEED_DATA_FROM_APPLICATION'