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_ADP_FED_TAX_V

Source

View Text - Preformatted


SELECT ref.company_code_equivalent company_code_equivalent , hou.name gre_name , hs.segment1 gre_id , pasg.organization_id organization_id , pp.employee_number employee_number , pp.business_group_id business_group_id , pp.person_id person_id , decode(ptp.number_per_fiscal_year, 52,'W',24,'S',26,'B', 12,'M', ptp.number_per_fiscal_year) , pasg.assignment_id assignment_id , pasg.assignment_number assignment_number , decode(pasg.primary_flag, 'Y',0,pasg.assignment_sequence) assignment_sequence , pasg.primary_flag primary_flag , pft.effective_start_date effective_start_date , pft.effective_end_date effective_end_date , hrl.meaning filing_status , pft.filing_status_code filing_status_code , TO_NUMBER(pft.withholding_allowances) allowances , TO_NUMBER(pft.fit_additional_tax) additional_tax , hrl2.meaning eic_filing_status , pft.fit_exempt fit_exempt_flag , pft.medicare_tax_exempt medicare_exempt_flag , pft.futa_tax_exempt futa_exempt_flag , pft.ss_tax_exempt ss_exempt_flag , TO_NUMBER(pft.fit_override_rate) override_rate , TO_NUMBER(pft.fit_override_amount) override_amount , TO_NUMBER(pft.supp_tax_override_rate) supp_override_rate , pft.statutory_employee statutory_employee_flag , pft.cumulative_taxation cumulative_taxation_flag , nvl(pft.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')) greatest_last_update_date from hr_adp_emp_ref_v ref , hr_locations_all hl , per_addresses pad , per_all_people_f pp , per_periods_of_service pps , hr_all_organization_units hou , hr_soft_coding_keyflex hs , hr_lookups hrl , hr_lookups hrl2 , per_time_period_types ptp , pay_payrolls_x ppr , pay_us_emp_fed_tax_rules_f pft , per_all_assignments_f pasg WHERE pp.employee_number is not null and pasg.assignment_id = ref.assignment_id and pasg.rowid = ref.asg_rowid and pp.person_id = pad.person_id and pad.style = 'US' and pad.primary_flag = 'Y' and trunc(hr_adp.get_adp_extract_date) between pad.date_from and nvl(pad.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) and trunc(hr_adp.get_adp_extract_date) between pp.effective_start_date and pp.effective_end_date and pps.person_id = pasg.person_id and pps.period_of_service_id= pasg.period_of_service_id and hs.soft_coding_keyflex_id = pasg.soft_coding_keyflex_id and hs.segment1 = hou.organization_id and pasg.person_id = pp.person_id and pasg.location_id = hl.location_id and ppr.period_type = ptp.period_type and ppr.payroll_id = pasg.payroll_id and pasg.assignment_id = pft.assignment_id and pasg.assignment_type = 'E' and pasg.effective_start_date = (select max(a2.effective_start_date) from per_all_assignments_f a2 where (a2.effective_start_date <= trunc(hr_adp.get_adp_extract_date) and a2.assignment_id = pasg.assignment_id)) 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_adp.get_adp_extract_date) and pft2.emp_fed_tax_rule_id = pft.emp_fed_tax_rule_id)) and pft.filing_status_code = hrl.lookup_code and hrl.lookup_type = 'US_FIT_FILING_STATUS' and pft.eic_filing_status_code = hrl2.lookup_code and hrl2.lookup_type = 'US_EIC_FILING_STATUS'

View Text - HTML Formatted

SELECT REF.COMPANY_CODE_EQUIVALENT COMPANY_CODE_EQUIVALENT
, HOU.NAME GRE_NAME
, HS.SEGMENT1 GRE_ID
, PASG.ORGANIZATION_ID ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PP.PERSON_ID PERSON_ID
, DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, PASG.ASSIGNMENT_ID ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE) ASSIGNMENT_SEQUENCE
, PASG.PRIMARY_FLAG PRIMARY_FLAG
, PFT.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PFT.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, HRL.MEANING FILING_STATUS
, PFT.FILING_STATUS_CODE FILING_STATUS_CODE
, TO_NUMBER(PFT.WITHHOLDING_ALLOWANCES) ALLOWANCES
, TO_NUMBER(PFT.FIT_ADDITIONAL_TAX) ADDITIONAL_TAX
, HRL2.MEANING EIC_FILING_STATUS
, PFT.FIT_EXEMPT FIT_EXEMPT_FLAG
, PFT.MEDICARE_TAX_EXEMPT MEDICARE_EXEMPT_FLAG
, PFT.FUTA_TAX_EXEMPT FUTA_EXEMPT_FLAG
, PFT.SS_TAX_EXEMPT SS_EXEMPT_FLAG
, TO_NUMBER(PFT.FIT_OVERRIDE_RATE) OVERRIDE_RATE
, TO_NUMBER(PFT.FIT_OVERRIDE_AMOUNT) OVERRIDE_AMOUNT
, TO_NUMBER(PFT.SUPP_TAX_OVERRIDE_RATE) SUPP_OVERRIDE_RATE
, PFT.STATUTORY_EMPLOYEE STATUTORY_EMPLOYEE_FLAG
, PFT.CUMULATIVE_TAXATION CUMULATIVE_TAXATION_FLAG
, NVL(PFT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) GREATEST_LAST_UPDATE_DATE
FROM HR_ADP_EMP_REF_V REF
, HR_LOCATIONS_ALL HL
, PER_ADDRESSES PAD
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE PPS
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, HR_LOOKUPS HRL
, HR_LOOKUPS HRL2
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, PAY_US_EMP_FED_TAX_RULES_F PFT
, PER_ALL_ASSIGNMENTS_F PASG
WHERE PP.EMPLOYEE_NUMBER IS NOT NULL
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PASG.ROWID = REF.ASG_ROWID
AND PP.PERSON_ID = PAD.PERSON_ID
AND PAD.STYLE = 'US'
AND PAD.PRIMARY_FLAG = 'Y'
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PAD.DATE_FROM
AND NVL(PAD.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PPS.PERSON_ID = PASG.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID= PASG.PERIOD_OF_SERVICE_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PASG.PERSON_ID = PP.PERSON_ID
AND PASG.LOCATION_ID = HL.LOCATION_ID
AND PPR.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PPR.PAYROLL_ID = PASG.PAYROLL_ID
AND PASG.ASSIGNMENT_ID = PFT.ASSIGNMENT_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND A2.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID))
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_ADP.GET_ADP_EXTRACT_DATE)
AND PFT2.EMP_FED_TAX_RULE_ID = PFT.EMP_FED_TAX_RULE_ID))
AND PFT.FILING_STATUS_CODE = HRL.LOOKUP_CODE
AND HRL.LOOKUP_TYPE = 'US_FIT_FILING_STATUS'
AND PFT.EIC_FILING_STATUS_CODE = HRL2.LOOKUP_CODE
AND HRL2.LOOKUP_TYPE = 'US_EIC_FILING_STATUS'