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, lk_all_conxt.lookup_code 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, ctx.context_code subcomponent_code, ctx.name subcomponent_name, null display_sequence from hcm_lookups lk, pay_report_blocks_vl blk, per_ext_definitions_vl def, fnd_df_adfbc_usages adf, fnd_df_table_usages tu, fnd_df_flexfields_vl flex, fnd_df_contexts_vl ctx, fnd_lookup_values_vl lookupvalues, (select prop.report_block_id,MAX(DECODE(prop1.property_name, 'DF_CONTEXT_CODE_NEG_REGEXP', prop1.property_value)) AS DF_CONTEXT_CODE_NEG_REGEXP, MAX(DECODE(prop1.property_name, 'DF_CONTEXT_CODE_POS_REGEXP', prop1.property_value)) AS DF_CONTEXT_CODE_POS_REGEXP, MAX(DECODE(prop1.property_name, 'EF_CATEGORY_CODE_POS_REGEXP', prop1.property_value)) AS EF_CATEGORY_CODE_POS_REGEXP, MAX(DECODE(prop1.property_name, 'EF_CATEGORY_CODE_NEG_REGEXP', prop1.property_value)) AS EF_CATEGORY_CODE_NEG_REGEXP, MAX(DECODE(prop1.property_name, 'DF_PACKAGE_NAME', prop1.property_value)) AS DF_PACKAGE_NAME from hry_pi_config_properties prop, hry_pi_config_properties prop1 where prop.property_name like 'DF\_TYPE%' escape '\' and prop.property_value in ('DDF', 'DFF') and prop.last_updated_by in ('0', 'SEED_DATA_FROM_APPLICATION') and prop1.report_block_id=prop.report_block_id and prop1.property_name in ('DF_CONTEXT_CODE_NEG_REGEXP','DF_CONTEXT_CODE_POS_REGEXP','EF_CATEGORY_CODE_POS_REGEXP', 'EF_CATEGORY_CODE_NEG_REGEXP','DF_PACKAGE_NAME') group by prop.report_block_id ) prop where lk.lookup_type = 'ORA_HRY_GPI_FEATURE_OPERATION' and def.attribute23 = lookupvalues.lookup_code(+) and lk.lookup_code in ('CREATE_FLEX', 'DELETE_FLEX') and blk.report_block_id = prop.report_block_id and def.ext_definition_id = blk.ext_definition_id and ctx.enabled_flag='Y' and adf.package_name = prop.DF_PACKAGE_NAME and (DF_CONTEXT_CODE_POS_REGEXP is null or regexp_like(ctx.context_code, DF_CONTEXT_CODE_POS_REGEXP) or ctx.context_code=rtrim(ltrim(DF_CONTEXT_CODE_POS_REGEXP,'^'),'$') ) and (DF_CONTEXT_CODE_NEG_REGEXP is null or NOT regexp_like(ctx.context_code, DF_CONTEXT_CODE_NEG_REGEXP)) and tu.table_name= adf.table_name and tu.table_usage_code = adf.table_usage_code and flex.application_id = tu.application_id and flex.descriptive_flexfield_code = tu.descriptive_flexfield_code and flex.application_id = ctx.application_id and flex.descriptive_flexfield_code = ctx.descriptive_flexfield_code /* and decode(lk.lookup_code,'CREATE_FLEX','Y','N')= ( select NVL(max('N'),'Y') from pay_report_records_f rec, hry_pi_config_properties prop1 where prop1.report_record_id=rec.report_record_id and prop1.property_name='DF_CONTEXT_CODE' and prop1.ext_definition_id=def.ext_definition_id and rec.creator_id=blk.report_block_id and prop1.property_value=ctx.context_code ) and decode(lk.lookup_code,'CREATE_FLEX',(select NVL(max('Y'),'N') from fnd_df_segments_vl seg where seg.context_code=ctx.context_code and seg.enabled_flag = 'Y' and rownum=1),'Y')= 'Y' */ 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, ctx.context_code subcomponent_code, ctx.name subcomponent_name, null display_sequence from hcm_lookups lk, pay_report_blocks_vl blk, per_ext_definitions_vl def, fnd_df_adfbc_usages adf, fnd_df_table_usages tu, fnd_df_flexfields_vl flex, fnd_ef_categories_vl cat, fnd_ef_category_contexts usg, fnd_df_contexts_vl ctx, fnd_lookup_values_vl lookupvalues, (select prop.report_block_id,MAX(DECODE(prop1.property_name, 'DF_CONTEXT_CODE_NEG_REGEXP', prop1.property_value)) AS DF_CONTEXT_CODE_NEG_REGEXP, MAX(DECODE(prop1.property_name, 'DF_CONTEXT_CODE_POS_REGEXP', prop1.property_value)) AS DF_CONTEXT_CODE_POS_REGEXP, MAX(DECODE(prop1.property_name, 'EF_CATEGORY_CODE_POS_REGEXP', prop1.property_value)) AS EF_CATEGORY_CODE_POS_REGEXP, MAX(DECODE(prop1.property_name, 'EF_CATEGORY_CODE_NEG_REGEXP', prop1.property_value)) AS EF_CATEGORY_CODE_NEG_REGEXP, MAX(DECODE(prop1.property_name, 'DF_PACKAGE_NAME', prop1.property_value)) AS DF_PACKAGE_NAME from hry_pi_config_properties prop, hry_pi_config_properties prop1 where prop.property_name like 'DF\_TYPE%' escape '\' and prop.property_value in ('EFF_TEMPLATE') and prop.last_updated_by in ('0', 'SEED_DATA_FROM_APPLICATION') and prop1.report_block_id=prop.report_block_id and prop1.property_name in ('DF_CONTEXT_CODE_NEG_REGEXP','DF_CONTEXT_CODE_POS_REGEXP','EF_CATEGORY_CODE_POS_REGEXP', 'EF_CATEGORY_CODE_NEG_REGEXP','DF_PACKAGE_NAME') group by prop.report_block_id ) prop where lk.lookup_type = 'ORA_HRY_GPI_FEATURE_OPERATION' and def.attribute23 = lookupvalues.lookup_code(+) and lk.lookup_code in ('CREATE_FLEX', 'DELETE_FLEX') and blk.report_block_id = prop.report_block_id and def.ext_definition_id = blk.ext_definition_id and ctx.enabled_flag='Y' and adf.package_name = prop.DF_PACKAGE_NAME and tu.table_name= adf.table_name and tu.table_usage_code = adf.table_usage_code and flex.application_id = tu.application_id and flex.descriptive_flexfield_code = tu.descriptive_flexfield_code and (EF_CATEGORY_CODE_POS_REGEXP is null or regexp_like(cat.category_code, EF_CATEGORY_CODE_POS_REGEXP)) and (EF_CATEGORY_CODE_NEG_REGEXP is null or NOT regexp_like(cat.category_code, EF_CATEGORY_CODE_NEG_REGEXP)) and cat.enabled_flag = 'Y' and usg.category_code = cat.category_code and usg.application_id = flex.application_id and usg.descriptive_flexfield_code = flex.descriptive_flexfield_code and (DF_CONTEXT_CODE_POS_REGEXP is null or regexp_like(usg.context_code, DF_CONTEXT_CODE_POS_REGEXP)) and (DF_CONTEXT_CODE_NEG_REGEXP is null or NOT regexp_like(usg.context_code, DF_CONTEXT_CODE_NEG_REGEXP)) and ctx.application_id = usg.application_id and ctx.descriptive_flexfield_code = usg.descriptive_flexfield_code and ctx.context_code = usg.context_code 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,

lk_all_conxt.lookup_code 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,

ctx.context_code subcomponent_code,

ctx.name subcomponent_name,

null display_sequence

from hcm_lookups lk,

pay_report_blocks_vl blk,

per_ext_definitions_vl def,

fnd_df_adfbc_usages adf,

fnd_df_table_usages tu,

fnd_df_flexfields_vl flex,

fnd_df_contexts_vl ctx,

fnd_lookup_values_vl lookupvalues,

(select prop.report_block_id,MAX(DECODE(prop1.property_name, 'DF_CONTEXT_CODE_NEG_REGEXP', prop1.property_value)) AS DF_CONTEXT_CODE_NEG_REGEXP,

MAX(DECODE(prop1.property_name, 'DF_CONTEXT_CODE_POS_REGEXP', prop1.property_value)) AS DF_CONTEXT_CODE_POS_REGEXP,

MAX(DECODE(prop1.property_name, 'EF_CATEGORY_CODE_POS_REGEXP', prop1.property_value)) AS EF_CATEGORY_CODE_POS_REGEXP,

MAX(DECODE(prop1.property_name, 'EF_CATEGORY_CODE_NEG_REGEXP', prop1.property_value)) AS EF_CATEGORY_CODE_NEG_REGEXP,

MAX(DECODE(prop1.property_name, 'DF_PACKAGE_NAME', prop1.property_value)) AS DF_PACKAGE_NAME

from hry_pi_config_properties prop, hry_pi_config_properties prop1

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

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

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

and prop1.report_block_id=prop.report_block_id

and prop1.property_name in ('DF_CONTEXT_CODE_NEG_REGEXP','DF_CONTEXT_CODE_POS_REGEXP','EF_CATEGORY_CODE_POS_REGEXP',

'EF_CATEGORY_CODE_NEG_REGEXP','DF_PACKAGE_NAME')

group by prop.report_block_id ) prop

where lk.lookup_type = 'ORA_HRY_GPI_FEATURE_OPERATION'

and def.attribute23 = lookupvalues.lookup_code(+)

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

and blk.report_block_id = prop.report_block_id

and def.ext_definition_id = blk.ext_definition_id

and ctx.enabled_flag='Y'

and adf.package_name = prop.DF_PACKAGE_NAME

and (DF_CONTEXT_CODE_POS_REGEXP is null or regexp_like(ctx.context_code, DF_CONTEXT_CODE_POS_REGEXP)

or ctx.context_code=rtrim(ltrim(DF_CONTEXT_CODE_POS_REGEXP,'^'),'$') )

and (DF_CONTEXT_CODE_NEG_REGEXP is null or NOT regexp_like(ctx.context_code, DF_CONTEXT_CODE_NEG_REGEXP))

and tu.table_name= adf.table_name

and tu.table_usage_code = adf.table_usage_code

and flex.application_id = tu.application_id

and flex.descriptive_flexfield_code = tu.descriptive_flexfield_code

and flex.application_id = ctx.application_id

and flex.descriptive_flexfield_code = ctx.descriptive_flexfield_code

/* and decode(lk.lookup_code,'CREATE_FLEX','Y','N')=

( select NVL(max('N'),'Y') from pay_report_records_f rec,

hry_pi_config_properties prop1 where

prop1.report_record_id=rec.report_record_id

and prop1.property_name='DF_CONTEXT_CODE' and prop1.ext_definition_id=def.ext_definition_id

and rec.creator_id=blk.report_block_id and prop1.property_value=ctx.context_code

) and decode(lk.lookup_code,'CREATE_FLEX',(select NVL(max('Y'),'N')

from fnd_df_segments_vl seg

where seg.context_code=ctx.context_code and seg.enabled_flag = 'Y' and rownum=1),'Y')= 'Y' */

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,

ctx.context_code subcomponent_code,

ctx.name subcomponent_name,

null display_sequence

from hcm_lookups lk,

pay_report_blocks_vl blk,

per_ext_definitions_vl def,

fnd_df_adfbc_usages adf,

fnd_df_table_usages tu,

fnd_df_flexfields_vl flex,

fnd_ef_categories_vl cat,

fnd_ef_category_contexts usg,

fnd_df_contexts_vl ctx,

fnd_lookup_values_vl lookupvalues,

(select prop.report_block_id,MAX(DECODE(prop1.property_name, 'DF_CONTEXT_CODE_NEG_REGEXP', prop1.property_value)) AS DF_CONTEXT_CODE_NEG_REGEXP,

MAX(DECODE(prop1.property_name, 'DF_CONTEXT_CODE_POS_REGEXP', prop1.property_value)) AS DF_CONTEXT_CODE_POS_REGEXP,

MAX(DECODE(prop1.property_name, 'EF_CATEGORY_CODE_POS_REGEXP', prop1.property_value)) AS EF_CATEGORY_CODE_POS_REGEXP,

MAX(DECODE(prop1.property_name, 'EF_CATEGORY_CODE_NEG_REGEXP', prop1.property_value)) AS EF_CATEGORY_CODE_NEG_REGEXP,

MAX(DECODE(prop1.property_name, 'DF_PACKAGE_NAME', prop1.property_value)) AS DF_PACKAGE_NAME

from hry_pi_config_properties prop, hry_pi_config_properties prop1

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

and prop.property_value in ('EFF_TEMPLATE')

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

and prop1.report_block_id=prop.report_block_id

and prop1.property_name in ('DF_CONTEXT_CODE_NEG_REGEXP','DF_CONTEXT_CODE_POS_REGEXP','EF_CATEGORY_CODE_POS_REGEXP',

'EF_CATEGORY_CODE_NEG_REGEXP','DF_PACKAGE_NAME')

group by prop.report_block_id ) prop

where lk.lookup_type = 'ORA_HRY_GPI_FEATURE_OPERATION'

and def.attribute23 = lookupvalues.lookup_code(+)

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

and blk.report_block_id = prop.report_block_id

and def.ext_definition_id = blk.ext_definition_id

and ctx.enabled_flag='Y'

and adf.package_name = prop.DF_PACKAGE_NAME

and tu.table_name= adf.table_name

and tu.table_usage_code = adf.table_usage_code

and flex.application_id = tu.application_id

and flex.descriptive_flexfield_code = tu.descriptive_flexfield_code

and (EF_CATEGORY_CODE_POS_REGEXP is null or regexp_like(cat.category_code, EF_CATEGORY_CODE_POS_REGEXP))

and (EF_CATEGORY_CODE_NEG_REGEXP is null or NOT regexp_like(cat.category_code, EF_CATEGORY_CODE_NEG_REGEXP))

and cat.enabled_flag = 'Y'

and usg.category_code = cat.category_code

and usg.application_id = flex.application_id

and usg.descriptive_flexfield_code = flex.descriptive_flexfield_code

and (DF_CONTEXT_CODE_POS_REGEXP is null or regexp_like(usg.context_code, DF_CONTEXT_CODE_POS_REGEXP))

and (DF_CONTEXT_CODE_NEG_REGEXP is null or NOT regexp_like(usg.context_code, DF_CONTEXT_CODE_NEG_REGEXP))

and ctx.application_id = usg.application_id

and ctx.descriptive_flexfield_code = usg.descriptive_flexfield_code

and ctx.context_code = usg.context_code

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

)