Hi Everyone,
Please find below is the query to get person calculation cards (Ex: Tax withhold)
SELECT
card_def.display_name,
papf.person_number
FROM
pay_dir_card_definitions_tl card_def,
pay_dir_cards_f card,
per_all_People_f papf,
PER_ALL_ASSIGNMENTS_f paaf,
PAY_DIR_CARD_COMPONENTS_F pdcc,
PAY_DIR_CARD_COMP_DEFS_F pdccdf,
PAY_DIR_REP_CARD_USAGES_F pdrcu,
PAY_DIR_COMP_DETAILS_F PDCDF
-- PAY_DIR_REP_CARD_USAGES_F_ PDCDFF
WHERE card.dir_card_definition_id = card_def.dir_card_definition_id
AND pdcc.dir_card_id = card.dir_card_id
and pdcc.DIR_CARD_COMP_ID = pdrcu.DIR_CARD_COMP_ID
AND paaf.person_id = papf.person_id
AND card_def.LANGUAGE = 'US'
and paaf.assignment_type = 'E'
and paaf.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
and paaf.PRIMARY_FLAG = 'Y'
and paaf.EFFECTIVE_LATEST_CHANGE = 'Y'
AND upper(card_def.display_name) in (upper('Tax Withholding'))
AND SYSDATE BETWEEN card.effective_start_date AND card.effective_end_date
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND SYSDATE BETWEEN pdcc.effective_start_date AND pdcc.effective_end_date
AND SYSDATE BETWEEN pdrcu.effective_start_date AND pdrcu.effective_end_date
AND SYSDATE BETWEEN pdccdf.effective_start_date AND pdccdf.effective_end_date
AND pdccdf.DIR_CARD_COMP_DEF_ID = pdcc.DIR_CARD_COMP_DEF_ID
and pdccdf.DIR_CARD_DEFINITION_ID = card_def.DIR_CARD_DEFINITION_ID
and PDCC.DIR_CARD_COMP_DEF_ID = PDCCDF.DIR_CARD_COMP_DEF_ID
AND PDCDF.DIR_CARD_COMP_ID = PDCC.DIR_CARD_COMP_ID
AND PDCDF.PERSON_ID = PAPF.PERSON_ID
AND SYSDATE BETWEEN PDCDF.effective_start_date AND PDCDF.effective_end_date
--Below lines are to get last updated date if employee has more than one record---
-- and pdrcu.LAST_UPDATE_DATE = (select max(pdrcu1.LAST_UPDATE_DATE)
-- from PAY_DIR_REP_CARD_USAGES_F pdrcu1
-- where pdrcu.DIR_REP_CARD_USAGE_ID = pdrcu1.DIR_REP_CARD_USAGE_ID
-- AND SYSDATE BETWEEN pdrcu1.effective_start_date AND pdrcu1.effective_end_date
-- and rownum = 1)