PAY_CIR_COMP_DETAILS_VL

Details

• Schema: FUSION

• Object owner: PAY

• Object type: VIEW

Columns

Name

EFFECTIVE_START_DATE EFFECTIVE_END_DATE OVERRIDE_FLEX_ID COMPONENT_EFFECTIVE_START_DATE COMPONENT_EFFECTIVE_END_DATE DIR_CARD_COMP_ID DIR_CARD_COMP_DEF_ID DIR_CARD_COMP_DEF_NAME BASE_COMPONENT_NAME CONTEXT1 CONTEXT2 CONTEXT3 CONTEXT4 CONTEXT5 CONTEXT6 COMPONENT_SEQUENCE DIR_CARD_ID CARD_EFFECTIVE_START_DATE CARD_EFFECTIVE_END_DATE LEGISLATIVE_DATA_GROUP_NAME LEGISLATIVE_DATA_GROUP_ID DIR_CARD_DEFINITION_NAME DIR_CARD_DEFINITION_ID SOURCE_ID SOURCE_TYPE CARD_SEQUENCE LEVEL_CODE CARD_LEVEL DIR_CARD_DEFINITION_BASE_NAME ALLOW_OVERRIDES_ID VALUE_DEFN_ID BASE_VALUE_DEN_NAME VALUE_DEFN_NAME DIR_OVERRIDE_USAGE_START_DATE DIR_OVERRIDE_USAGE_END_DATE DIR_OVERRIDE_USAGE_ID RANGE_ITEM_ID PARENT_VALUE_DEFN_ID VALUE_GROUP_ID CALC_TYPE_ID TYPE DIR_COMP_FLEX_ID DIR_FLEX_USAGE_ID FLEX_CONTEXT_CODE FLEX_CONTEXT_NAME FLEX_SEGEMENT_CODE FLEX_SEGMENT_IDENTIFIER FLEX_DB_COLUMN_NAME OVERRIDE_FLEX_TYPE DISPLAY_SEQUENCE NAME VALUE

Query

SQL_Statement

select prif.effective_start_date, prif.effective_end_date, prif.range_item_id override_flex_id, pdccf.effective_start_date component_effective_start_date, pdccf.effective_end_date component_effective_end_date, pdccf.dir_card_comp_id, pdccf.dir_card_comp_def_id, pdccdf.component_name dir_card_comp_def_name, pdccdf.base_component_name, pdccf.context_value1 Context1, pdccf.context_value2 Context2, pdccf.context_value3 Context3, pdccf.context_value4 Context4, pdccf.context_value5 Context5, pdccf.context_value6 Context6, pdccf.component_sequence, pdcf.dir_card_id, pdcf.effective_start_date card_effective_start_date, pdcf.effective_end_date card_effective_end_date, pldgv.name legislative_data_group_name, pldgv.legislative_data_group_id, pdcdv.display_name dir_card_definition_name, pdcf.dir_card_definition_id, pdcf.source_id, pdcf.source_type, pdcf.card_sequence, pdclf.LEVEL_CODE, decode(pdclf.LEVEL_CODE,'HCM_TRU','TRU','HCM_PSU','PSU',pdclf.LEVEL_CODE) card_level, pdcdv.base_display_name dir_card_definition_base_name, paov.allow_overrides_id, pvdv.value_defn_id, pvdv.base_name BASE_VALUE_DEN_NAME, pvdv.name VALUE_DEFN_NAME, pdouf.effective_start_date DIR_OVERRIDE_USAGE_START_DATE, pdouf.effective_end_date DIR_OVERRIDE_USAGE_END_DATE, pdouf.dir_override_usage_id, prif.range_item_id, paov.value_defn_id parent_value_defn_id, pvdv.value_group_id, pvdv.calc_type_id, paov.type, null dir_comp_flex_id, null dir_flex_usage_id, null Flex_context_code, null flex_context_name, null flex_segement_code, null flex_segment_identifier, null flex_db_column_name, 'OVERRIDE' override_flex_type, pdouf.display_sequence, paov.prompt name, prif.value1 value from pay_dir_cards_f pdcf, pay_dir_card_definitions_vl pdcdv, per_legislative_data_groups_vl pldgv, pay_dir_card_components_f pdccf, pay_dir_card_comp_defs_vl pdccdf, PAY_DIR_CARD_LEVELS_F pdclf, pay_dir_override_usages_f pdouf, pay_allow_overrides_vl paov, pay_value_definitions_vl pvdv, pay_range_items_f prif where pdcf.dir_card_id = pdccf.dir_card_id and pdcf.legislative_data_group_id=pldgv.legislative_data_group_id and pdccdf.DIR_CARD_DEFINITION_ID=pdcf.DIR_CARD_DEFINITION_ID and pdcf.DIR_CARD_DEFINITION_ID= pdclf.DIR_CARD_DEFINITION_ID and pdcdv.DIR_CARD_DEFINITION_ID=pdclf.DIR_CARD_DEFINITION_ID and pdcdv.DIR_CARD_DEFINITION_ID=pdccdf.DIR_CARD_DEFINITION_ID and pdccf.dir_card_comp_def_id=pdccdf.dir_card_comp_def_id and pdclf.LEVEL_CODE=pdcf.SOURCE_TYPE and (pdccdf.legislation_code=pldgv.legislation_code or pdccdf.legislative_data_group_id=pldgv.legislative_data_group_id) and pdouf.dir_card_comp_def_id = pdccdf.dir_card_comp_def_id and pdouf.allow_overrides_id=paov.allow_overrides_id and paov.value_defn_id=pvdv.parent_value_defn_id and prif.value_defn_id=pvdv.value_defn_id and prif.source_id=pvdv.source_id and pvdv.source_id=pdccf.dir_card_comp_id and prif.source_type='PDCC' and pvdv.source_type='PDCC' and pvdv.dir_override_usage_id=pdouf.dir_override_usage_id and nvl(pdouf.context_value1,nvl(pdccf.context_value1,'0'))||'-'|| nvl(pdouf.context_value2,nvl(pdccf.context_value2,'0'))||'-'|| nvl(pdouf.context_value3,nvl(pdccf.context_value3,'0'))||'-'|| nvl(pdouf.context_value4,nvl(pdccf.context_value4,'0'))||'-'|| nvl(pdouf.context_value5,nvl(pdccf.context_value5,'0'))||'-'|| nvl(pdouf.context_value6,nvl(pdccf.context_value6,'0')) = nvl(pdccf.context_value1,'0')||'-'|| nvl(pdccf.context_value2,'0')||'-'|| nvl(pdccf.context_value3,'0')||'-'|| nvl(pdccf.context_value4,'0')||'-'|| nvl(pdccf.context_value5,'0')||'-'|| nvl(pdccf.context_value6,'0') AND prif.effective_start_date between pdclf.effective_start_date and pdclf.effective_end_date AND prif.effective_start_date between pdouf.effective_start_date and pdouf.effective_end_date AND prif.effective_start_date between paov.effective_start_date and paov.effective_end_date AND prif.effective_start_date between pvdv.effective_start_date and pvdv.effective_end_date AND prif.effective_start_date between pdcf.effective_start_date and pdcf.effective_end_date AND prif.effective_start_date between pdccf.effective_start_date and pdccf.effective_end_date AND prif.effective_start_date between pdccdf.effective_start_date and pdccdf.effective_end_date union select pdcdf.effective_start_date, pdcdf.effective_end_date, pdcdf.dir_comp_detail_id override_flex_id, pdccf.effective_start_date component_effective_start_date, pdccf.effective_end_date component_effective_end_date, pdccf.dir_card_comp_id, pdccf.dir_card_comp_def_id, pdccdf.component_name dir_card_comp_def_name, pdccdf.base_component_name, pdccf.context_value1 Context1, pdccf.context_value2 Context2, pdccf.context_value3 Context3, pdccf.context_value4 Context4, pdccf.context_value5 Context5, pdccf.context_value6 Context6, pdccf.component_sequence, pdcf.dir_card_id, pdcf.effective_start_date card_effective_start_date, pdcf.effective_end_date card_effective_end_date, pldgv.name legislative_data_group_name, pldgv.legislative_data_group_id, pdcdv.display_name dir_card_definition_name, pdcf.dir_card_definition_id, pdcf.source_id, pdcf.source_type, pdcf.card_sequence, pdclf.LEVEL_CODE, decode(pdclf.LEVEL_CODE,'HCM_TRU','TRU','HCM_PSU','PSU',pdclf.LEVEL_CODE) card_level, pdcdv.base_display_name dir_card_definition_base_name, null allow_overrides_id, null value_defn_id, null value_defn_base_name, null value_defn_NAME, null DIR_OVERRIDE_USAGE_START_DATE, null DIR_OVERRIDE_USAGE_END_DATE, null dir_override_usage_id, null range_item_id, null parent_value_defn_id, null value_group_id, null calc_type_id, null type, pdcff.dir_comp_flex_id, pdfuf.dir_flex_usage_id, fdsv.context_code Flex_context_code, fdcv.name flex_context_name, fdsv.segment_code flex_segement_code, fdsv.segment_identifier flex_segment_identifier, fdsv.column_name flex_db_column_name, 'FLEX' override_flex_type, fdsv.sequence_number display_sequence, fdsv.prompt name, decode(fdsv.column_name,'DIR_INFORMATION_CHAR1',pdcdf.DIR_INFORMATION_CHAR1, decode(fdsv.column_name,'DIR_INFORMATION_CHAR2',pdcdf.DIR_INFORMATION_CHAR2, decode(fdsv.column_name,'DIR_INFORMATION_CHAR3',pdcdf.DIR_INFORMATION_CHAR3, decode(fdsv.column_name,'DIR_INFORMATION_CHAR4',pdcdf.DIR_INFORMATION_CHAR4, decode(fdsv.column_name,'DIR_INFORMATION_CHAR5',pdcdf.DIR_INFORMATION_CHAR5, decode(fdsv.column_name,'DIR_INFORMATION_CHAR6',pdcdf.DIR_INFORMATION_CHAR6, decode(fdsv.column_name,'DIR_INFORMATION_CHAR7',pdcdf.DIR_INFORMATION_CHAR7, decode(fdsv.column_name,'DIR_INFORMATION_CHAR8',pdcdf.DIR_INFORMATION_CHAR8, decode(fdsv.column_name,'DIR_INFORMATION_CHAR9',pdcdf.DIR_INFORMATION_CHAR9, decode(fdsv.column_name,'DIR_INFORMATION_CHAR10',pdcdf.DIR_INFORMATION_CHAR10, decode(fdsv.column_name,'DIR_INFORMATION_CHAR11',pdcdf.DIR_INFORMATION_CHAR11, decode(fdsv.column_name,'DIR_INFORMATION_CHAR12',pdcdf.DIR_INFORMATION_CHAR12, decode(fdsv.column_name,'DIR_INFORMATION_CHAR13',pdcdf.DIR_INFORMATION_CHAR13, decode(fdsv.column_name,'DIR_INFORMATION_CHAR14',pdcdf.DIR_INFORMATION_CHAR14, decode(fdsv.column_name,'DIR_INFORMATION_CHAR15',pdcdf.DIR_INFORMATION_CHAR15, decode(fdsv.column_name,'DIR_INFORMATION_CHAR16',pdcdf.DIR_INFORMATION_CHAR16, decode(fdsv.column_name,'DIR_INFORMATION_CHAR17',pdcdf.DIR_INFORMATION_CHAR17, decode(fdsv.column_name,'DIR_INFORMATION_CHAR18',pdcdf.DIR_INFORMATION_CHAR18, decode(fdsv.column_name,'DIR_INFORMATION_CHAR19',pdcdf.DIR_INFORMATION_CHAR19, decode(fdsv.column_name,'DIR_INFORMATION_CHAR20',pdcdf.DIR_INFORMATION_CHAR20, decode(fdsv.column_name,'DIR_INFORMATION_CHAR21',pdcdf.DIR_INFORMATION_CHAR21, decode(fdsv.column_name,'DIR_INFORMATION_CHAR22',pdcdf.DIR_INFORMATION_CHAR22, decode(fdsv.column_name,'DIR_INFORMATION_CHAR23',pdcdf.DIR_INFORMATION_CHAR23, decode(fdsv.column_name,'DIR_INFORMATION_CHAR24',pdcdf.DIR_INFORMATION_CHAR24, decode(fdsv.column_name,'DIR_INFORMATION_CHAR25',pdcdf.DIR_INFORMATION_CHAR25, decode(fdsv.column_name,'DIR_INFORMATION_CHAR26',pdcdf.DIR_INFORMATION_CHAR26, decode(fdsv.column_name,'DIR_INFORMATION_CHAR27',pdcdf.DIR_INFORMATION_CHAR27, decode(fdsv.column_name,'DIR_INFORMATION_CHAR28',pdcdf.DIR_INFORMATION_CHAR28, decode(fdsv.column_name,'DIR_INFORMATION_CHAR29',pdcdf.DIR_INFORMATION_CHAR29, decode(fdsv.column_name,'DIR_INFORMATION_CHAR30',pdcdf.DIR_INFORMATION_CHAR30, decode(fdsv.column_name,'DIR_INFORMATION_NUMBER1',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER1), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER2',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER2), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER3',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER3), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER4',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER4), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER5',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER5), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER6',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER6), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER7',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER7), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER8',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER8), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER9',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER9), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER10',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER10), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER11',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER11), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER12',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER12), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER13',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER13), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER14',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER14), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER15',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER15), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER16',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER16), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER17',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER17), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER18',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER18), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER19',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER19), decode(fdsv.column_name,'DIR_INFORMATION_NUMBER20',TO_CHAR(pdcdf.DIR_INFORMATION_NUMBER20), decode(fdsv.column_name,'DIR_INFORMATION_DATE1',TO_CHAR(pdcdf.DIR_INFORMATION_DATE1), decode(fdsv.column_name,'DIR_INFORMATION_DATE2',TO_CHAR(pdcdf.DIR_INFORMATION_DATE2), decode(fdsv.column_name,'DIR_INFORMATION_DATE3',TO_CHAR(pdcdf.DIR_INFORMATION_DATE3), decode(fdsv.column_name,'DIR_INFORMATION_DATE4',TO_CHAR(pdcdf.DIR_INFORMATION_DATE4), decode(fdsv.column_name,'DIR_INFORMATION_DATE5',TO_CHAR(pdcdf.DIR_INFORMATION_DATE5), decode(fdsv.column_name,'DIR_INFORMATION_DATE6',TO_CHAR(pdcdf.DIR_INFORMATION_DATE6), decode(fdsv.column_name,'DIR_INFORMATION_DATE7',TO_CHAR(pdcdf.DIR_INFORMATION_DATE7), decode(fdsv.column_name,'DIR_INFORMATION_DATE8',TO_CHAR(pdcdf.DIR_INFORMATION_DATE8), decode(fdsv.column_name,'DIR_INFORMATION_DATE9',TO_CHAR(pdcdf.DIR_INFORMATION_DATE9), decode(fdsv.column_name,'DIR_INFORMATION_DATE10',TO_CHAR(pdcdf.DIR_INFORMATION_DATE10), decode(fdsv.column_name,'DIR_INFORMATION_DATE11',TO_CHAR(pdcdf.DIR_INFORMATION_DATE11), decode(fdsv.column_name,'DIR_INFORMATION_DATE12',TO_CHAR(pdcdf.DIR_INFORMATION_DATE12), decode(fdsv.column_name,'DIR_INFORMATION_DATE13',TO_CHAR(pdcdf.DIR_INFORMATION_DATE13), decode(fdsv.column_name,'DIR_INFORMATION_DATE14',TO_CHAR(pdcdf.DIR_INFORMATION_DATE14), decode(fdsv.column_name,'DIR_INFORMATION_DATE15',TO_CHAR(pdcdf.DIR_INFORMATION_DATE15)) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) value from pay_dir_cards_f pdcf, pay_dir_card_definitions_vl pdcdv, per_legislative_data_groups_vl pldgv, pay_dir_card_components_f pdccf, pay_dir_card_comp_defs_vl pdccdf, PAY_DIR_CARD_LEVELS_F pdclf, pay_dir_comp_flexs_f pdcff, pay_dir_comp_flex_rships_f pdcfrf, pay_dir_flex_usages_f pdfuf, pay_dir_comp_details_f pdcdf, fnd_df_contexts_vl fdcv, fnd_df_segments_vl fdsv where pdcf.dir_card_id = pdccf.dir_card_id and pdcf.legislative_data_group_id=pldgv.legislative_data_group_id and pdccdf.DIR_CARD_DEFINITION_ID=pdcf.DIR_CARD_DEFINITION_ID and pdcf.DIR_CARD_DEFINITION_ID= pdclf.DIR_CARD_DEFINITION_ID and pdcdv.DIR_CARD_DEFINITION_ID=pdclf.DIR_CARD_DEFINITION_ID and pdcdv.DIR_CARD_DEFINITION_ID=pdccdf.DIR_CARD_DEFINITION_ID and pdccf.dir_card_comp_def_id=pdccdf.dir_card_comp_def_id and pdclf.LEVEL_CODE=pdcf.SOURCE_TYPE and (pdccdf.legislation_code=pldgv.legislation_code or pdccdf.legislative_data_group_id=pldgv.legislative_data_group_id) AND pdcff.dir_card_comp_def_id=pdccdf.dir_card_comp_def_id AND pdcff.dir_comp_flex_id = pdcfrf.dir_comp_flex_id (+) AND pdcff.dir_comp_flex_id = pdfuf.dir_comp_flex_id (+) AND pdcff.flex_context = pdcdf.dir_information_category AND pdcff.dir_card_comp_def_id = pdcfrf.dir_card_comp_def_id AND fdcv.application_id = fdsv.application_id AND fdcv.context_code = pdcff.flex_context AND fdcv.descriptive_flexfield_code = fdsv.descriptive_flexfield_code AND fdcv.context_code = fdsv.context_code AND fdsv.descriptive_flexfield_code = 'Deduction Developer DF' AND fdsv.APPLICATION_ID='801' AND fdsv.enabled_flag = 'Y' AND fdcv.enabled_flag = 'Y' and pdcdf.dir_card_comp_id= pdccf.dir_card_comp_id and pdcff.dir_card_comp_def_id=pdccf.dir_Card_comp_def_id and nvl(pdfuf.context_value1,nvl(pdccf.context_value1,'0'))||'-'|| nvl(pdfuf.context_value2,nvl(pdccf.context_value2,'0'))||'-'|| nvl(pdfuf.context_value3,nvl(pdccf.context_value3,'0'))||'-'|| nvl(pdfuf.context_value4,nvl(pdccf.context_value4,'0'))||'-'|| nvl(pdfuf.context_value5,nvl(pdccf.context_value5,'0'))||'-'|| nvl(pdfuf.context_value6,nvl(pdccf.context_value6,'0')) = nvl(pdccf.context_value1,'0')||'-'|| nvl(pdccf.context_value2,'0')||'-'|| nvl(pdccf.context_value3,'0')||'-'|| nvl(pdccf.context_value4,'0')||'-'|| nvl(pdccf.context_value5,'0')||'-'|| nvl(pdccf.context_value6,'0') AND pdcdf.effective_start_date between pdclf.effective_start_date and pdclf.effective_end_date AND pdcdf.effective_start_date BETWEEN pdcff.effective_start_date AND pdcff.effective_end_date AND pdcdf.effective_start_date BETWEEN pdccf.effective_start_date AND pdccf.effective_end_date AND pdcdf.effective_start_date BETWEEN pdcf.effective_start_date AND pdcf.effective_end_date AND pdcdf.effective_start_date BETWEEN pdccdf.effective_start_date AND pdccdf.effective_end_date AND pdcdf.effective_start_date BETWEEN pdfuf.effective_start_date(+) AND pdfuf.effective_end_date(+) AND pdcdf.effective_start_date BETWEEN pdcfrf.effective_start_date(+) AND pdcfrf.effective_end_date(+)