HRY_GPI_FEATURES_V

Details

  • Schema: FUSION

  • Object owner: HRY

  • Object type: VIEW

Columns

Name

OPERATION

FEATURE_CODE

FEATURE_NAME

COMPONENT_CODE

COMPONENT_NAME

SUBCOMPONENT_CODE

SUBCOMPONENT_NAME

DISPLAY_SEQUENCE

Query

SQL_Statement

select OPERATION,

FEATURE_CODE,

FEATURE_NAME,

COMPONENT_CODE,

COMPONENT_NAME,

SUBCOMPONENT_CODE,

SUBCOMPONENT_NAME,

DISPLAY_SEQUENCE

from (

select lk.lookup_code operation,

flx.descriptive_flexfield_code feature_code,

flx.name feature_name,

ctx.context_code component_code,

ctx.name component_name,

seg.segment_code subcomponent_code,

seg.name subcomponent_name,

seg.sequence_number display_sequence

from hcm_lookups lk,

fnd_df_flexfields_vl flx,

fnd_df_contexts_vl ctx,

fnd_df_segments_vl seg

where lk.lookup_type = 'ORA_HRY_GPI_FEATURE_OPERATION'

and lk.lookup_code in ('Y', 'N')

and flx.application_id = 800

and flx.descriptive_flexfield_code in ('PER_PERSON_EIT_EFF', 'PER_ASSIGNMENT_EIT_EFF','PER_JOBS_EIT_EFF')

and ctx.application_id = flx.application_id

and ctx.descriptive_flexfield_code = flx.descriptive_flexfield_code

and ctx.context_code in ('ORA_HRY_GPI_WFN_EMP_INFO', 'ORA_HRY_GPI_WFN_PAYROLL_INFO', 'ORA_GPY_ASG_DTLS', 'ORA_GPY_PER_DTLS','ORA_HRY_GPI_WFN_JOB_INFO','ORA_HRY_GPI_WFN_EMP_LOV_INFO','ORA_HRY_GPI_WFN_PAY_LOV_INFO','ORA_HRY_GPI_WFN_JOB_LOV_INFO')

and seg.application_id = ctx.application_id

and seg.descriptive_flexfield_code = ctx.descriptive_flexfield_code

and seg.context_code = ctx.context_code

and seg.enabled_flag != lk.lookup_code

union all

select lk.lookup_code operation,

flx.descriptive_flexfield_code feature_code,

flx.name feature_name,

ctx.context_code component_code,

ctx.name component_name,

'ALL' subcomponent_code,

'All' subcomponent_name,

-100 display_sequence

from hcm_lookups lk,

fnd_df_flexfields_vl flx,

fnd_df_contexts_vl ctx

where lk.lookup_type = 'ORA_HRY_GPI_FEATURE_OPERATION'

and lk.lookup_code in ('Y', 'N')

and flx.application_id = 800

and flx.descriptive_flexfield_code in ('PER_PERSON_EIT_EFF', 'PER_ASSIGNMENT_EIT_EFF','PER_JOBS_EIT_EFF')

and ctx.application_id = flx.application_id

and ctx.descriptive_flexfield_code = flx.descriptive_flexfield_code

and ctx.context_code in ('ORA_HRY_GPI_WFN_EMP_INFO', 'ORA_HRY_GPI_WFN_PAYROLL_INFO', 'ORA_GPY_ASG_DTLS', 'ORA_GPY_PER_DTLS','ORA_HRY_GPI_WFN_JOB_INFO','ORA_HRY_GPI_WFN_EMP_LOV_INFO','ORA_HRY_GPI_WFN_PAY_LOV_INFO','ORA_HRY_GPI_WFN_JOB_LOV_INFO')

union all

select lk.lookup_code operation,

to_char(def.ext_definition_id) feature_code,

nvl(lookupvalues.meaning,def.definition_name) feature_name,

to_char(blk.report_block_id) component_code,

blk.block_name component_name,

prop.property_name subcomponent_code,

lk_all_conxt.meaning subcomponent_name,

-100 display_sequence

from hcm_lookups lk,

hry_pi_config_properties prop,

pay_report_blocks_vl blk,

per_ext_definitions_vl def,

hcm_lookups lk_all_conxt,

fnd_lookup_values_vl lookupvalues

where lk.lookup_type = 'ORA_HRY_GPI_FEATURE_OPERATION'

and lk_all_conxt.lookup_type = 'ORA_HRY_GPI_OPTIONS'

and lk_all_conxt.lookup_code = 'ORA_HRY_ALL_CONTEXTS'

and def.attribute23 = lookupvalues.lookup_code(+)

and lk.lookup_code in ('CREATE_FLEX', 'DELETE_FLEX')

and prop.property_name like 'DF\_TYPE%' escape '\'

and prop.property_value in ('EFF_TEMPLATE', 'DDF', 'DFF')

and prop.last_updated_by in ('0', 'SEED_DATA_FROM_APPLICATION')

and blk.report_block_id = prop.report_block_id

and def.ext_definition_id = prop.ext_definition_id

union all

select lk.lookup_code operation,

to_char(def.ext_definition_id) feature_code,

nvl(lookupvalues.meaning,def.definition_name) feature_name,

to_char(blk.report_block_id) component_code,

blk.block_name component_name,

prop.property_name subcomponent_code,

flx.name subcomponent_name,

null display_sequence

from hcm_lookups lk,

hry_pi_config_properties prop,

pay_report_blocks_vl blk,

per_ext_definitions_vl def,

hry_pi_config_properties prop2,

fnd_df_adfbc_usages adf,

fnd_df_table_usages tu,

fnd_df_contexts_vl flx,

fnd_lookup_values_vl lookupvalues

where 1=2 and lk.lookup_type = 'ORA_HRY_GPI_FEATURE_OPERATION'

and def.attribute23 = lookupvalues.lookup_code(+)

and lk.lookup_code in ('CREATE_FLEX', 'DELETE_FLEX')

and prop.property_name like 'DF\_TYPE%' escape '\'

and prop.property_value in ('EFF_TEMPLATE', 'DDF', 'DFF')

and prop.last_updated_by in ('0', 'SEED_DATA_FROM_APPLICATION')

and blk.report_block_id = prop.report_block_id

and def.ext_definition_id = prop.ext_definition_id

and prop2.ext_definition_id = def.ext_definition_id

and prop2.report_block_id = blk.report_block_id

and prop2.report_record_id is null

and prop2.ext_data_element_id is null

and prop2.property_name = 'DF_PACKAGE_NAME' || regexp_replace(prop.property_name, '^DF_TYPE(\d*)$', '\1')

and adf.package_name = prop2.property_value

and tu.table_name= adf.table_name

and tu.table_usage_code = adf.table_usage_code

and flx.application_id = tu.application_id

and flx.descriptive_flexfield_code = tu.descriptive_flexfield_code

and (

lk.lookup_code = 'CREATE_FLEX'

or (

prop.property_value = 'EFF_TEMPLATE'

and exists(

select null

from pay_report_blocks blk2

where blk2.creator_id = blk.report_block_id

and blk2.creator_type = 'REPORT_BLOCK')

)

or (

prop.property_value != 'EFF_TEMPLATE'

and exists(

select null

from pay_report_records_f rec2,

hry_pi_config_properties prop3,

hry_pi_config_properties prop4

where rec2.creator_id = blk.report_block_id

and rec2.creator_type = 'REPORT_BLOCK'

and prop3.ext_definition_id = blk.ext_definition_id

and prop3.report_block_id = rec2.report_block_id

and prop3.report_record_id = rec2.report_record_id

and prop3.ext_data_element_id is null

and prop3.property_name = 'DF_CONTEXT_CODE'

and prop4.ext_definition_id(+) = blk.ext_definition_id

and prop4.report_block_id(+) = rec2.report_block_id

and prop4.report_record_id(+) = rec2.report_record_id

and prop4.ext_data_element_id(+) is null

and prop4.property_name(+) = 'DF_CONFIG_SUFFIX'

and nvl(prop4.property_value, '$$') = nvl(regexp_replace(prop.property_name, '^DF_TYPE(\d*)$', '\1'), '$$'))

)

)

union all

select 'XSD_GENERATE' operation,

'ALL' feature_code,

'ALL' feature_name,

'NA' component_code,

'Not Applicable' component_name,

'NA' subcomponent_code,

'Not Applicable' subcomponent_name,

-100 display_sequence

from hcm_lookups

WHERE lookup_type = 'ORA_HRY_GPI_FEATURE_OPERATION'

AND Rownum=1

union all

select 'XSD_GENERATE' operation,

to_char(rule_property_id) feature_code,

payload_rule_name feature_name,

'NA' component_code,

'Not Applicable' component_name,

'NA' subcomponent_code,

'Not Applicable' subcomponent_name,

null display_sequence

from hry_pi_payload_rules where parent_rule_property_id is null

and enterprise_id!=0

)