CALCULATION CARD in (FEDERAL,STATE)

 

/* Formatted on 10-09-2024 11:32:45 (QP5 v5.149.1003.31008) */

SELECT DISTINCT (papf.person_number),

                papf.person_id,

                ppnf.full_name person_name,

                pdcd.base_display_name calculation_card_name,

                pdcf.effective_start_date,

                pdcf.effective_end_date,

                pdcdt.DISPLAY_NAME,

                pdcdf.DIR_COMP_FLEX_ID,

                pdcdf.CREATED_BY,

                pdcdf.CREATION_DATE,

                pdcdf.DIR_INFORMATION_CATEGORY,

                pdcd.DIR_CARD_DEFINITION_ID,

                pldg.LEGISLATIVE_DATA_GROUP_ID,

                pdccf.DIR_CARD_COMP_ID,

                pdcdf.DIR_INFORMATION_CHAR1,

                pdcdf.DIR_INFORMATION_CHAR2,

                pdcdf.DIR_INFORMATION_CHAR3,

                pdcdf.DIR_INFORMATION_CHAR4,

                pdcdf.DIR_INFORMATION_CHAR5,

                pdcdf.DIR_INFORMATION_CHAR6,

                pdcdf.DIR_INFORMATION_CHAR7,

                pdcdf.DIR_INFORMATION_CHAR8,

                pdcdf.DIR_INFORMATION_CHAR9,

                pdcdf.DIR_INFORMATION_CHAR10,

                pdcdf.DIR_INFORMATION_CHAR11,

                pdcdf.DIR_INFORMATION_CHAR12,

                pdcdf.DIR_INFORMATION_CHAR13,

                pdcdf.DIR_INFORMATION_CHAR14,

                pdcdf.DIR_INFORMATION_CHAR15,

                pdcdf.DIR_INFORMATION_CHAR16,

                pdcdf.DIR_INFORMATION_CHAR17,

                pdcdf.DIR_INFORMATION_CHAR18,

                pdcdf.DIR_INFORMATION_CHAR19,

                pdcdf.DIR_INFORMATION_CHAR20,

                pdcdf.DIR_INFORMATION_CHAR21,

                pdcdf.DIR_INFORMATION_CHAR22,

                pdcdf.DIR_INFORMATION_CHAR23,

                pdcdf.DIR_INFORMATION_CHAR24,

                pdcdf.DIR_INFORMATION_CHAR25

  FROM pay_dir_card_definitions pdcd,

       pay_dir_cards_f pdcf,

       pay_dir_card_components_f pdccf,

       pay_dir_comp_details_f pdcdf,

       per_person_names_f ppnf,

       per_all_people_f papf,

       PER_LEGISLATIVE_DATA_GROUPS pldg,

       PAY_DIR_CARD_DEFINITIONS_TL pdcdt

 WHERE     pdcd.dir_card_definition_id = pdcf.dir_card_definition_id

       AND pdcf.dir_card_definition_id = pdcdt.dir_card_definition_id

       AND pdcd.dir_card_definition_id = pdcdt.dir_card_definition_id

       --AND pdcf.effective_start_date AND pdcf.effective_end_date

       AND pdcf.dir_card_id = pdccf.dir_card_id

       AND TRUNC (SYSDATE) BETWEEN pdccf.effective_start_date

                               AND pdccf.effective_end_date

       AND pdccf.dir_card_comp_id = pdcdf.dir_card_comp_id

       AND TRUNC (SYSDATE) BETWEEN pdcdf.effective_start_date

                               AND pdcdf.effective_end_date

       AND pdcdf.person_id = papf.person_id

       --AND ppnf.effective_start_date AND ppnf.effective_end_date

       AND ppnf.name_type = 'GLOBAL'

       --AND pldg.LANGUAGE = 'US'

       AND pldg.legislative_data_group_id = pdcf.legislative_data_group_id

       AND ppnf.person_id = papf.person_id

       AND TRUNC (SYSDATE) BETWEEN pdcf.effective_start_date

                               AND pdcf.effective_end_date

       --AND BETWEEN papf.effective_start_date AND papf.effective_end_date

       AND PAPF.PERSON_NUMBER = '279'

       AND pdcdf.DIR_INFORMATION_CATEGORY IN

              ('HRX_US_WTH_STATE_UNEMPLOYMENT',

               'HRX_US_WTH_FEDERAL_UNEMPLOYMENT')