eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HR_CERIDIAN_500_RESIDENT_TAX_V

Source

View Text - Preformatted


SELECT /*+ ORDERED */ pp.employee_number , greatest(lit.effective_start_date, sit.effective_start_date, pft.effective_start_date) , decode(trunc(HR_CERIDIAN.get_cer_extract_date), greatest(trunc(HR_CERIDIAN.get_cer_extract_date), pft.effective_end_date), pft.effective_end_date, to_date(NULL)) , pj.assignment_number , pj.primary_flag , pp.national_identifier , hou.name , decode (pft.filing_status_code, '01', 'S', '02', 'M', '03', 'S', '04', 'S', pft.filing_status_code) , HR_CERIDIAN.fica_futa_exempt(pft.medicare_tax_exempt, pft.ss_tax_exempt, pft.futa_tax_exempt) , nvl(pft.fit_exempt, 'N') , pft.withholding_allowances , decode(pft.fit_override_amount,0,null,pft.fit_override_amount)|| decode(pft.fit_override_rate,0,null,null,null,'%')|| decode(pft.fit_override_rate,0,null,pft.fit_override_rate)|| decode(pft.fit_additional_tax, 0,null,null,null,'$')|| decode(pft.fit_additional_tax, 0,null,pft.fit_additional_tax) , nvl(sit.sit_exempt, 'N') , sit.withholding_allowances , decode(sit.sit_override_amount,0,null,sit.sit_override_amount)|| decode(sit.sit_override_rate,0,null,null,null,'%')|| decode(sit.sit_override_rate,0,null,sit.sit_override_rate)|| decode(sit.sit_additional_tax,0,null,null,null,'$')|| decode(sit.sit_additional_tax,0,null,sit.sit_additional_tax) , nvl(lit.lit_exempt, 'N') , decode(lit.lit_override_amount,0,null,lit.lit_override_amount)|| decode(lit.lit_override_rate,0,null,null,null,'%')|| decode(lit.lit_override_rate,0,null,lit.lit_override_rate)|| decode(lit.lit_additional_tax,0,null,null,null,'$')|| decode(lit.lit_additional_tax,0,null,lit.lit_additional_tax) , nvl(pad.add_information17,pad.region_2) , lit.jurisdiction_code , cn1.city_name , cn2.city_name , pj.assignment_id , pp.business_group_id , pj.payroll_id , pp.person_id , greatest( decode( greatest(trunc(HR_CERIDIAN.get_cer_extract_date), pft.effective_end_date), trunc(HR_CERIDIAN.get_cer_extract_date), greatest(pft.effective_end_date, nvl(pft.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pft.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), pft.effective_start_date, decode( greatest(trunc(HR_CERIDIAN.get_cer_extract_date), lit.effective_end_date), trunc(HR_CERIDIAN.get_cer_extract_date), greatest(lit.effective_end_date, nvl(lit.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(lit.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), lit.effective_start_date, decode( greatest(trunc(HR_CERIDIAN.get_cer_extract_date), sit.effective_end_date), trunc(HR_CERIDIAN.get_cer_extract_date), greatest(sit.effective_end_date, nvl(sit.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(sit.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), sit.effective_start_date) last_update_date from per_all_people_f pp, per_addresses pad, per_periods_of_service pps, per_all_assignments_f pj, hr_soft_coding_keyflex hs, pay_us_emp_fed_tax_rules_f pft, pay_us_emp_state_tax_rules_f sit, pay_us_emp_city_tax_rules_f lit, pay_us_states pust, pay_us_counties c, pay_us_city_names cn1, pay_us_city_names cn2, hr_organization_units hou WHERE pp.person_id = pad.person_id and pj.assignment_type = 'E' and pps.person_id = pp.person_id and pps.period_of_service_id = pj.period_of_service_id and pft.assignment_id = pj.assignment_id and hs.soft_coding_keyflex_id = pj.soft_coding_keyflex_id and hs.segment1 = hou.organization_id and c.county_name = nvl(pad.add_information19,pad.region_1) and substr(lit.jurisdiction_code,4,3) = c.county_code and substr(lit.jurisdiction_code,1,2) = c.state_code and substr(lit.jurisdiction_code,8,4) <> '0000' and cn2.city_name = nvl(pad.add_information18,pad.town_or_city) and cn1.city_code = cn2.city_code and cn1.county_code = cn2.county_code and cn1.state_code = cn2.state_code and cn1.state_code = lit.state_code and cn1.county_code = lit.county_code and cn1.city_code = lit.city_code and ((cn1.primary_flag = 'Y') or (cn1.primary_flag = 'N' and cn1.city_code like 'U%')) and pad.style = 'US' and pj.primary_flag = 'Y' and pad.primary_flag = 'Y' and sit.assignment_id = pj.assignment_id and sit.state_code = pust.state_code and pust.state_abbrev = nvl(pad.add_information17,pad.region_2) and lit.assignment_id = pj.assignment_id and pp.employee_number is not null and trunc(HR_CERIDIAN.get_cer_extract_date) between pp.effective_start_date and pp.effective_end_date and trunc(HR_CERIDIAN.get_cer_extract_date) between pad.date_from and nvl(pad.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) and pj.effective_start_date = (select max(a2.effective_start_date) from per_all_assignments_f a2 where (a2.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and a2.assignment_type = 'E' and a2.person_id = pj.person_id and a2.assignment_id = pj.assignment_id and a2.primary_flag = 'Y')) and pft.effective_start_date = (select max(pft2.effective_start_date) from pay_us_emp_fed_tax_rules_f pft2 where (pft2.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and pft2.emp_fed_tax_rule_id = pft.emp_fed_tax_rule_id)) and sit.effective_start_date = (select max(sit2.effective_start_date) from pay_us_emp_state_tax_rules_f sit2 where (sit2.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and sit2.emp_state_tax_rule_id = sit.emp_state_tax_rule_id)) and lit.effective_start_date = (select max(lit2.effective_start_date) from pay_us_emp_city_tax_rules_f lit2 where (lit2.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and lit2.emp_city_tax_rule_id = lit.emp_city_tax_rule_id)) and pps.date_start = (select max(ps.date_start) from per_periods_of_service ps where (ps.date_start <= trunc(HR_CERIDIAN.get_cer_extract_date) and ps.person_id = pp.person_id))

View Text - HTML Formatted

SELECT /*+ ORDERED */ PP.EMPLOYEE_NUMBER
, GREATEST(LIT.EFFECTIVE_START_DATE
, SIT.EFFECTIVE_START_DATE
, PFT.EFFECTIVE_START_DATE)
, DECODE(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, PFT.EFFECTIVE_END_DATE)
, PFT.EFFECTIVE_END_DATE
, TO_DATE(NULL))
, PJ.ASSIGNMENT_NUMBER
, PJ.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, HOU.NAME
, DECODE (PFT.FILING_STATUS_CODE
, '01'
, 'S'
, '02'
, 'M'
, '03'
, 'S'
, '04'
, 'S'
, PFT.FILING_STATUS_CODE)
, HR_CERIDIAN.FICA_FUTA_EXEMPT(PFT.MEDICARE_TAX_EXEMPT
, PFT.SS_TAX_EXEMPT
, PFT.FUTA_TAX_EXEMPT)
, NVL(PFT.FIT_EXEMPT
, 'N')
, PFT.WITHHOLDING_ALLOWANCES
, DECODE(PFT.FIT_OVERRIDE_AMOUNT
, 0
, NULL
, PFT.FIT_OVERRIDE_AMOUNT)|| DECODE(PFT.FIT_OVERRIDE_RATE
, 0
, NULL
, NULL
, NULL
, '%')|| DECODE(PFT.FIT_OVERRIDE_RATE
, 0
, NULL
, PFT.FIT_OVERRIDE_RATE)|| DECODE(PFT.FIT_ADDITIONAL_TAX
, 0
, NULL
, NULL
, NULL
, '$')|| DECODE(PFT.FIT_ADDITIONAL_TAX
, 0
, NULL
, PFT.FIT_ADDITIONAL_TAX)
, NVL(SIT.SIT_EXEMPT
, 'N')
, SIT.WITHHOLDING_ALLOWANCES
, DECODE(SIT.SIT_OVERRIDE_AMOUNT
, 0
, NULL
, SIT.SIT_OVERRIDE_AMOUNT)|| DECODE(SIT.SIT_OVERRIDE_RATE
, 0
, NULL
, NULL
, NULL
, '%')|| DECODE(SIT.SIT_OVERRIDE_RATE
, 0
, NULL
, SIT.SIT_OVERRIDE_RATE)|| DECODE(SIT.SIT_ADDITIONAL_TAX
, 0
, NULL
, NULL
, NULL
, '$')|| DECODE(SIT.SIT_ADDITIONAL_TAX
, 0
, NULL
, SIT.SIT_ADDITIONAL_TAX)
, NVL(LIT.LIT_EXEMPT
, 'N')
, DECODE(LIT.LIT_OVERRIDE_AMOUNT
, 0
, NULL
, LIT.LIT_OVERRIDE_AMOUNT)|| DECODE(LIT.LIT_OVERRIDE_RATE
, 0
, NULL
, NULL
, NULL
, '%')|| DECODE(LIT.LIT_OVERRIDE_RATE
, 0
, NULL
, LIT.LIT_OVERRIDE_RATE)|| DECODE(LIT.LIT_ADDITIONAL_TAX
, 0
, NULL
, NULL
, NULL
, '$')|| DECODE(LIT.LIT_ADDITIONAL_TAX
, 0
, NULL
, LIT.LIT_ADDITIONAL_TAX)
, NVL(PAD.ADD_INFORMATION17
, PAD.REGION_2)
, LIT.JURISDICTION_CODE
, CN1.CITY_NAME
, CN2.CITY_NAME
, PJ.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, PJ.PAYROLL_ID
, PP.PERSON_ID
, GREATEST( DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, PFT.EFFECTIVE_END_DATE)
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(PFT.EFFECTIVE_END_DATE
, NVL(PFT.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PFT.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, PFT.EFFECTIVE_START_DATE
, DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, LIT.EFFECTIVE_END_DATE)
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(LIT.EFFECTIVE_END_DATE
, NVL(LIT.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(LIT.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, LIT.EFFECTIVE_START_DATE
, DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, SIT.EFFECTIVE_END_DATE)
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(SIT.EFFECTIVE_END_DATE
, NVL(SIT.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(SIT.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, SIT.EFFECTIVE_START_DATE) LAST_UPDATE_DATE
FROM PER_ALL_PEOPLE_F PP
, PER_ADDRESSES PAD
, PER_PERIODS_OF_SERVICE PPS
, PER_ALL_ASSIGNMENTS_F PJ
, HR_SOFT_CODING_KEYFLEX HS
, PAY_US_EMP_FED_TAX_RULES_F PFT
, PAY_US_EMP_STATE_TAX_RULES_F SIT
, PAY_US_EMP_CITY_TAX_RULES_F LIT
, PAY_US_STATES PUST
, PAY_US_COUNTIES C
, PAY_US_CITY_NAMES CN1
, PAY_US_CITY_NAMES CN2
, HR_ORGANIZATION_UNITS HOU
WHERE PP.PERSON_ID = PAD.PERSON_ID
AND PJ.ASSIGNMENT_TYPE = 'E'
AND PPS.PERSON_ID = PP.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID = PJ.PERIOD_OF_SERVICE_ID
AND PFT.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PJ.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND C.COUNTY_NAME = NVL(PAD.ADD_INFORMATION19
, PAD.REGION_1)
AND SUBSTR(LIT.JURISDICTION_CODE
, 4
, 3) = C.COUNTY_CODE
AND SUBSTR(LIT.JURISDICTION_CODE
, 1
, 2) = C.STATE_CODE
AND SUBSTR(LIT.JURISDICTION_CODE
, 8
, 4) <> '0000'
AND CN2.CITY_NAME = NVL(PAD.ADD_INFORMATION18
, PAD.TOWN_OR_CITY)
AND CN1.CITY_CODE = CN2.CITY_CODE
AND CN1.COUNTY_CODE = CN2.COUNTY_CODE
AND CN1.STATE_CODE = CN2.STATE_CODE
AND CN1.STATE_CODE = LIT.STATE_CODE
AND CN1.COUNTY_CODE = LIT.COUNTY_CODE
AND CN1.CITY_CODE = LIT.CITY_CODE
AND ((CN1.PRIMARY_FLAG = 'Y') OR (CN1.PRIMARY_FLAG = 'N'
AND CN1.CITY_CODE LIKE 'U%'))
AND PAD.STYLE = 'US'
AND PJ.PRIMARY_FLAG = 'Y'
AND PAD.PRIMARY_FLAG = 'Y'
AND SIT.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND SIT.STATE_CODE = PUST.STATE_CODE
AND PUST.STATE_ABBREV = NVL(PAD.ADD_INFORMATION17
, PAD.REGION_2)
AND LIT.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PAD.DATE_FROM
AND NVL(PAD.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND PJ.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.PERSON_ID = PJ.PERSON_ID
AND A2.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND A2.PRIMARY_FLAG = 'Y'))
AND PFT.EFFECTIVE_START_DATE = (SELECT MAX(PFT2.EFFECTIVE_START_DATE)
FROM PAY_US_EMP_FED_TAX_RULES_F PFT2
WHERE (PFT2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PFT2.EMP_FED_TAX_RULE_ID = PFT.EMP_FED_TAX_RULE_ID))
AND SIT.EFFECTIVE_START_DATE = (SELECT MAX(SIT2.EFFECTIVE_START_DATE)
FROM PAY_US_EMP_STATE_TAX_RULES_F SIT2
WHERE (SIT2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND SIT2.EMP_STATE_TAX_RULE_ID = SIT.EMP_STATE_TAX_RULE_ID))
AND LIT.EFFECTIVE_START_DATE = (SELECT MAX(LIT2.EFFECTIVE_START_DATE)
FROM PAY_US_EMP_CITY_TAX_RULES_F LIT2
WHERE (LIT2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND LIT2.EMP_CITY_TAX_RULE_ID = LIT.EMP_CITY_TAX_RULE_ID))
AND PPS.DATE_START = (SELECT MAX(PS.DATE_START)
FROM PER_PERIODS_OF_SERVICE PS
WHERE (PS.DATE_START <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PS.PERSON_ID = PP.PERSON_ID))