PAY_CIR_CARD_COMPONENTS_DR_VL

Details

• Schema: FUSION

• Object owner: PAY

• Object type: VIEW

Query

SQL_Statement

SELECT EFFECTIVE_START_DATE , EFFECTIVE_END_DATE , LOGICAL_EFFECTIVE_START_DATE , LOGICAL_EFFECTIVE_END_DATE , DIR_CARD_COMP_ID, DIR_CARD_COMP_ID DIR_CARD_COMP_DR_ID, PARENT_DIR_CARD_COMP_ID, DIR_CARD_COMP_DEF_ID, GROUP_DIR_CARD_COMP_DEF_ID, PARENT_DIR_CARD_COMP_DEF_ID, DEDUCTION_GROUP_ID, BASE_DEDUCTION_GROUP_NAME, DEDUCTION_GROUP_NAME, 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, pay_deduction_card_service.get_component_discriminator_date_range(BASE_COMPONENT_NAME,LOGICAL_EFFECTIVE_START_DATE,DIR_CARD_COMP_DEF_ID,CONTEXT1,CONTEXT2,CONTEXT3,CONTEXT4,CONTEXT5,CONTEXT6) AS COMPONENT_DISCRIMINATOR FROM ( SELECT TO_DATE(substr((a.COLUMN_VALUE).getstringval(),0,instr((a.COLUMN_VALUE).getstringval(),'|')-1),'YYYY-MM-DD')EFFECTIVE_START_DATE , TO_DATE(substr((a.COLUMN_VALUE).getstringval(),instr((a.COLUMN_VALUE).getstringval(),'|')+1,length((a.COLUMN_VALUE).getstringval())),'YYYY-MM-DD') EFFECTIVE_END_DATE , TO_DATE(substr((b.COLUMN_VALUE).getstringval(),0,instr((b.COLUMN_VALUE).getstringval(),'|')-1),'YYYY-MM-DD') LOGICAL_EFFECTIVE_START_DATE , TO_DATE(substr((b.COLUMN_VALUE).getstringval(),instr((b.COLUMN_VALUE).getstringval(),'|')+1,length((b.COLUMN_VALUE).getstringval())),'YYYY-MM-DD') LOGICAL_EFFECTIVE_END_DATE , DIR_CARD_COMP_ID, PARENT_DIR_CARD_COMP_ID, DIR_CARD_COMP_DEF_ID, GROUP_DIR_CARD_COMP_DEF_ID, PARENT_DIR_CARD_COMP_DEF_ID, DEDUCTION_GROUP_ID, BASE_DEDUCTION_GROUP_NAME, DEDUCTION_GROUP_NAME, 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 FROM ( SELECT EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, DIR_CARD_COMP_ID, PARENT_DIR_CARD_COMP_ID, DIR_CARD_COMP_DEF_ID, GROUP_DIR_CARD_COMP_DEF_ID, PARENT_DIR_CARD_COMP_DEF_ID, DEDUCTION_GROUP_ID, BASE_DEDUCTION_GROUP_NAME, DEDUCTION_GROUP_NAME, 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, PAY_DEDUCTION_CARD_SERVICE.get_component_logical_dates(dir_card_comp_id,dir_card_comp_def_id, effective_start_date,effective_end_date) logical_dates, pay_deduction_card_service.get_component_def_logical_dates(DIR_CARD_COMP_DEF_ID,CONTEXT1,CONTEXT2,CONTEXT3,CONTEXT4,CONTEXT5,CONTEXT6) compdef_logical_dates FROM( select min(pdccf.effective_start_date) effective_start_date, max(pdccf.effective_end_date) effective_end_date, pdccf.dir_card_comp_id, pdccf.parent_dir_card_comp_id, pdccf.dir_card_comp_def_id, NVL((select max(group_dir_card_comp_def_id) from pay_dir_comp_flex_rships_f where dir_card_comp_def_id=pdccf.DIR_CARD_COMP_DEF_ID),pdccf.DIR_CARD_COMP_DEF_ID) group_dir_card_comp_def_id, (select max(parent_dir_card_comp_def_id) from pay_dir_comp_flex_rships_f where dir_card_comp_def_id=pdccf.DIR_CARD_COMP_DEF_ID) parent_dir_card_comp_def_id, pdccf.deduction_group_id, pdgv.base_name base_deduction_group_name, pdgv.name deduction_group_name, 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 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_deduction_groups_vl pdgv, PAY_DIR_CARD_LEVELS_F pdclf 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 pdgv.base_deduction_group_id=pdccdf.deduction_group_id and pdgv.legislation_code=pldgv.legislation_code and (pdccdf.legislation_code=pldgv.legislation_code or pdccdf.legislative_data_group_id=pldgv.legislative_data_group_id) AND pdccf.effective_start_date between pdcf.effective_start_date and pdcf.effective_end_date AND pdccf.effective_start_date between pdccdf.effective_start_date and pdccdf.effective_end_date AND pdccf.effective_start_date between pdclf.effective_start_date and pdclf.effective_end_date GROUP BY pdccf.dir_card_comp_id, pdccf.parent_dir_card_comp_id, pdccf.dir_card_comp_def_id, pdccf.deduction_group_id, pdgv.base_name, pdgv.name, pdccdf.component_name , pdccdf.base_component_name, pdccf.context_value1 , pdccf.context_value2 , pdccf.context_value3 , pdccf.context_value4 , pdccf.context_value5 , pdccf.context_value6 , pdccf.component_sequence, pdcf.dir_card_id, pdcf.effective_start_date, pdcf.effective_end_date, pldgv.name, pldgv.legislative_data_group_id, pdcdv.display_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), pdcdv.base_display_name ) ),XMLTABLE (logical_dates) a ,XMLTABLE(compdef_logical_dates) b)