PAY_CIR_CARDS_VL

Details

• Schema: FUSION

• Object owner: PAY

• Object type: VIEW

Columns

Name
EFFECTIVE_START_DATE EFFECTIVE_END_DATE DIR_CARD_ID 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 CARD_DISCRIMINATOR ORGANIZATION_ID PAYROLL_RELATIONSHIP_ID PERSON_ID

Query

SQL_Statement

select effective_start_date, effective_end_date, dir_card_id, name legislative_data_group_name, legislative_data_group_id, display_name dir_card_definition_name, dir_card_definition_id, source_id, source_type, card_sequence, LEVEL_CODE, decode(LEVEL_CODE,'HCM_TRU','TRU','HCM_PSU','PSU',LEVEL_CODE) card_level, base_display_name dir_card_definition_base_name, legislation_code||'_'||base_display_name card_discriminator, CASE WHEN LEVEL_CODE IN ('PSU', 'TRU' , 'HCM_TRU' , 'HCM_PSU') THEN SOURCE_ID END AS ORGANIZATION_ID, CASE WHEN LEVEL_CODE IN ('PREL') THEN SOURCE_ID END AS PAYROLL_RELATIONSHIP_ID, CASE WHEN LEVEL_CODE IN ('PREL') THEN (SELECT PERSON_ID FROM PAY_PAY_RELATIONSHIPS_DN pprdn WHERE PAYROLL_RELATIONSHIP_ID=SOURCE_ID AND EFFECTIVE_START_DATE BETWEEN pprdn.START_DATE and pprdn.END_DATE) END AS PERSON_ID from ( select min(pdcf.effective_start_date) effective_start_date, max(pdcf.effective_end_date) effective_end_date, pdcf.dir_card_id, 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, pdcdv.base_display_name, pldgv.legislation_code from pay_dir_cards_f pdcf, pay_dir_card_definitions_vl pdcdv, PAY_DIR_CARD_LEVELS_F pdclf, per_legislative_data_groups_vl pldgv where pdcdv.dir_card_definition_id=pdcf.dir_card_definition_id and pldgv.legislative_data_group_id=pdcf.legislative_data_group_id AND pdclf.LEVEL_CODE=pdcf.SOURCE_TYPE AND pdclf.DIR_CARD_DEFINITION_ID=pdcf.DIR_CARD_DEFINITION_ID AND pdcf.effective_start_date between pdclf.effective_start_date and pdclf.effective_end_date group by pdcf.dir_card_id, 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, pdcdv.base_display_name, pldgv.legislation_code)