HRY_GPI_FEATURES

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

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 )

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

)