VIEW: APPS.HR_ADP_STATE_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 , sit.effective_start_date effective_start_date , sit.effective_end_date effective_end_date , 'Y' resident_flag , 'N' work_flag , pust.state_abbrev state_abbrev , hrl.meaning filing_status , sit.filing_status_code filing_status_code , sit.withholding_allowances allowances , sit.secondary_wa secondary_allowances , TO_NUMBER(sit.sit_additional_tax) additional_tax , sit.sit_exempt sit_exempt_flag , sit.sui_exempt sui_exempt_flag , sit.sdi_exempt sdi_exempt_flag , decode(sit.wc_exempt,'Y','Y','N') wc_exempt_flag , sit.state_non_resident_cert state_non_resident_cert , sit.sui_wage_base_override_amount sui_wage_base_override_amount , TO_NUMBER(sit.sit_override_rate) override_rate , TO_NUMBER(sit.sit_override_amount) override_amount , TO_NUMBER(sit.supp_tax_override_rate) supp_override_rate , sit.additional_wa_amount exemption_amount , sit.sit_optional_calc_ind optional_calculation , nvl(pstx.sit_exists,'N') sit_exists ,nvl(sit.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')) greatest_last_update_date from hr_adp_emp_ref_v ref , per_all_people_f pp , per_addresses pad , per_periods_of_service pps , per_time_period_types ptp , pay_payrolls_x ppr , hr_all_organization_units hou , hr_soft_coding_keyflex hs , pay_us_states pust , hr_lookups hrl , pay_us_emp_state_tax_rules_f sit , per_all_assignments_f pasg , pay_us_state_tax_info_f pstx , pay_state_rules psr WHERE pp.employee_number is not null and pasg.assignment_id = ref.assignment_id and pasg.rowid = ref.asg_rowid and pps.person_id = pasg.person_id and pps.period_of_service_id= pasg.period_of_service_id and pp.person_id = pad.person_id and pad.style = 'US' and pad.primary_flag = 'Y' and ppr.period_type = ptp.period_type and ppr.payroll_id = pasg.payroll_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.assignment_id = sit.assignment_id and pasg.assignment_type = 'E' and trunc(hr_adp.get_adp_extract_date) between pad.date_from and nvl(pad.date_to,to_date('4712/12/31', 'YYYY/MM/DD')) and trunc(hr_adp.get_adp_extract_date) between pp.effective_start_date and pp.effective_end_date 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 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_adp.get_adp_extract_date) and sit2.emp_state_tax_rule_id = sit.emp_state_tax_rule_id)) and to_number(sit.filing_status_code) = to_number(hrl.lookup_code) and psr.fs_lookup_type = hrl.lookup_type and psr.state_code = pust.state_abbrev and sit.state_code = pust.state_code and nvl(pad.add_information17, pad.region_2) = pust.state_abbrev and sit.state_code = pstx.state_code (+) and hr_adp.get_adp_extract_date between pstx.effective_start_date (+) and pstx.effective_end_date (+) and decode(pust.state_abbrev, nvl(pad.add_information17, pad.region_2), 'Y', 'N') = 'Y' union 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 , sit.effective_start_date effective_start_date , sit.effective_end_date effective_end_date , 'N' resident_flag , 'Y' work_flag , pust.state_abbrev state_abbrev , hrl.meaning filing_status , sit.filing_status_code filing_status_code , sit.withholding_allowances allowances , sit.secondary_wa secondary_allowances , TO_NUMBER(sit.sit_additional_tax) additional_tax , sit.sit_exempt sit_exempt_flag , sit.sui_exempt sui_exempt_flag , sit.sdi_exempt sdi_exempt_flag , decode(sit.wc_exempt,'Y','Y','N') wc_exempt_flag , sit.state_non_resident_cert state_non_resident_cert , sit.sui_wage_base_override_amount sui_wage_base_override_amount , TO_NUMBER(sit.sit_override_rate) override_rate , TO_NUMBER(sit.sit_override_amount) override_amount , TO_NUMBER(sit.supp_tax_override_rate) supp_override_rate , sit.additional_wa_amount exemption_amount , sit.sit_optional_calc_ind optional_calculation , nvl(pstx.sit_exists,'N') sit_exists , nvl(sit.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')) greatest_last_update_date from hr_adp_emp_ref_v ref , per_all_people_f pp , per_periods_of_service pps , per_time_period_types ptp , pay_payrolls_x ppr , hr_all_organization_units hou , hr_locations_all hl , hr_soft_coding_keyflex hs , pay_us_states pust , hr_lookups hrl , pay_us_emp_state_tax_rules_f sit , per_all_assignments_f pasg , pay_us_state_tax_info_f pstx , pay_state_rules psr where pp.employee_number is not null and pasg.assignment_id = ref.assignment_id and pasg.rowid = ref.asg_rowid and pps.person_id = pasg.person_id and pps.period_of_service_id= pasg.period_of_service_id and ppr.period_type = ptp.period_type and ppr.payroll_id = pasg.payroll_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.assignment_id = sit.assignment_id and pasg.assignment_type = 'E' and hl.location_id = nvl(hs.segment18,pasg.location_id) and hl.style = 'US' and trunc(hr_adp.get_adp_extract_date) between pp.effective_start_date and pp.effective_end_date 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 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_adp.get_adp_extract_date) and sit2.emp_state_tax_rule_id = sit.emp_state_tax_rule_id)) and to_number(sit.filing_status_code) = to_number(hrl.lookup_code) and psr.fs_lookup_type = hrl.lookup_type and psr.state_code = pust.state_abbrev and sit.state_code = pust.state_code and sit.state_code = pstx.state_code (+) and hr_adp.get_adp_extract_date between pstx.effective_start_date (+) and pstx.effective_end_date (+) and nvl(hl.loc_information17, hl.region_2) = pust.state_abbrev and decode(pust.state_abbrev, nvl(hl.loc_information17, hl.region_2), 'Y', 'N') = 'Y'

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
, SIT.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, SIT.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 'Y' RESIDENT_FLAG
, 'N' WORK_FLAG
, PUST.STATE_ABBREV STATE_ABBREV
, HRL.MEANING FILING_STATUS
, SIT.FILING_STATUS_CODE FILING_STATUS_CODE
, SIT.WITHHOLDING_ALLOWANCES ALLOWANCES
, SIT.SECONDARY_WA SECONDARY_ALLOWANCES
, TO_NUMBER(SIT.SIT_ADDITIONAL_TAX) ADDITIONAL_TAX
, SIT.SIT_EXEMPT SIT_EXEMPT_FLAG
, SIT.SUI_EXEMPT SUI_EXEMPT_FLAG
, SIT.SDI_EXEMPT SDI_EXEMPT_FLAG
, DECODE(SIT.WC_EXEMPT
, 'Y'
, 'Y'
, 'N') WC_EXEMPT_FLAG
, SIT.STATE_NON_RESIDENT_CERT STATE_NON_RESIDENT_CERT
, SIT.SUI_WAGE_BASE_OVERRIDE_AMOUNT SUI_WAGE_BASE_OVERRIDE_AMOUNT
, TO_NUMBER(SIT.SIT_OVERRIDE_RATE) OVERRIDE_RATE
, TO_NUMBER(SIT.SIT_OVERRIDE_AMOUNT) OVERRIDE_AMOUNT
, TO_NUMBER(SIT.SUPP_TAX_OVERRIDE_RATE) SUPP_OVERRIDE_RATE
, SIT.ADDITIONAL_WA_AMOUNT EXEMPTION_AMOUNT
, SIT.SIT_OPTIONAL_CALC_IND OPTIONAL_CALCULATION
, NVL(PSTX.SIT_EXISTS
, 'N') SIT_EXISTS
, NVL(SIT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) GREATEST_LAST_UPDATE_DATE
FROM HR_ADP_EMP_REF_V REF
, PER_ALL_PEOPLE_F PP
, PER_ADDRESSES PAD
, PER_PERIODS_OF_SERVICE PPS
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PAY_US_STATES PUST
, HR_LOOKUPS HRL
, PAY_US_EMP_STATE_TAX_RULES_F SIT
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_US_STATE_TAX_INFO_F PSTX
, PAY_STATE_RULES PSR
WHERE PP.EMPLOYEE_NUMBER IS NOT NULL
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PASG.ROWID = REF.ASG_ROWID
AND PPS.PERSON_ID = PASG.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID= PASG.PERIOD_OF_SERVICE_ID
AND PP.PERSON_ID = PAD.PERSON_ID
AND PAD.STYLE = 'US'
AND PAD.PRIMARY_FLAG = 'Y'
AND PPR.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PPR.PAYROLL_ID = PASG.PAYROLL_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.ASSIGNMENT_ID = SIT.ASSIGNMENT_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PAD.DATE_FROM
AND NVL(PAD.DATE_TO
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD'))
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
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 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_ADP.GET_ADP_EXTRACT_DATE)
AND SIT2.EMP_STATE_TAX_RULE_ID = SIT.EMP_STATE_TAX_RULE_ID))
AND TO_NUMBER(SIT.FILING_STATUS_CODE) = TO_NUMBER(HRL.LOOKUP_CODE)
AND PSR.FS_LOOKUP_TYPE = HRL.LOOKUP_TYPE
AND PSR.STATE_CODE = PUST.STATE_ABBREV
AND SIT.STATE_CODE = PUST.STATE_CODE
AND NVL(PAD.ADD_INFORMATION17
, PAD.REGION_2) = PUST.STATE_ABBREV
AND SIT.STATE_CODE = PSTX.STATE_CODE (+)
AND HR_ADP.GET_ADP_EXTRACT_DATE BETWEEN PSTX.EFFECTIVE_START_DATE (+)
AND PSTX.EFFECTIVE_END_DATE (+)
AND DECODE(PUST.STATE_ABBREV
, NVL(PAD.ADD_INFORMATION17
, PAD.REGION_2)
, 'Y'
, 'N') = 'Y' UNION 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
, SIT.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, SIT.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 'N' RESIDENT_FLAG
, 'Y' WORK_FLAG
, PUST.STATE_ABBREV STATE_ABBREV
, HRL.MEANING FILING_STATUS
, SIT.FILING_STATUS_CODE FILING_STATUS_CODE
, SIT.WITHHOLDING_ALLOWANCES ALLOWANCES
, SIT.SECONDARY_WA SECONDARY_ALLOWANCES
, TO_NUMBER(SIT.SIT_ADDITIONAL_TAX) ADDITIONAL_TAX
, SIT.SIT_EXEMPT SIT_EXEMPT_FLAG
, SIT.SUI_EXEMPT SUI_EXEMPT_FLAG
, SIT.SDI_EXEMPT SDI_EXEMPT_FLAG
, DECODE(SIT.WC_EXEMPT
, 'Y'
, 'Y'
, 'N') WC_EXEMPT_FLAG
, SIT.STATE_NON_RESIDENT_CERT STATE_NON_RESIDENT_CERT
, SIT.SUI_WAGE_BASE_OVERRIDE_AMOUNT SUI_WAGE_BASE_OVERRIDE_AMOUNT
, TO_NUMBER(SIT.SIT_OVERRIDE_RATE) OVERRIDE_RATE
, TO_NUMBER(SIT.SIT_OVERRIDE_AMOUNT) OVERRIDE_AMOUNT
, TO_NUMBER(SIT.SUPP_TAX_OVERRIDE_RATE) SUPP_OVERRIDE_RATE
, SIT.ADDITIONAL_WA_AMOUNT EXEMPTION_AMOUNT
, SIT.SIT_OPTIONAL_CALC_IND OPTIONAL_CALCULATION
, NVL(PSTX.SIT_EXISTS
, 'N') SIT_EXISTS
, NVL(SIT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) GREATEST_LAST_UPDATE_DATE
FROM HR_ADP_EMP_REF_V REF
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE PPS
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_LOCATIONS_ALL HL
, HR_SOFT_CODING_KEYFLEX HS
, PAY_US_STATES PUST
, HR_LOOKUPS HRL
, PAY_US_EMP_STATE_TAX_RULES_F SIT
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_US_STATE_TAX_INFO_F PSTX
, PAY_STATE_RULES PSR
WHERE PP.EMPLOYEE_NUMBER IS NOT NULL
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PASG.ROWID = REF.ASG_ROWID
AND PPS.PERSON_ID = PASG.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID= PASG.PERIOD_OF_SERVICE_ID
AND PPR.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PPR.PAYROLL_ID = PASG.PAYROLL_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.ASSIGNMENT_ID = SIT.ASSIGNMENT_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND HL.LOCATION_ID = NVL(HS.SEGMENT18
, PASG.LOCATION_ID)
AND HL.STYLE = 'US'
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
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 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_ADP.GET_ADP_EXTRACT_DATE)
AND SIT2.EMP_STATE_TAX_RULE_ID = SIT.EMP_STATE_TAX_RULE_ID))
AND TO_NUMBER(SIT.FILING_STATUS_CODE) = TO_NUMBER(HRL.LOOKUP_CODE)
AND PSR.FS_LOOKUP_TYPE = HRL.LOOKUP_TYPE
AND PSR.STATE_CODE = PUST.STATE_ABBREV
AND SIT.STATE_CODE = PUST.STATE_CODE
AND SIT.STATE_CODE = PSTX.STATE_CODE (+)
AND HR_ADP.GET_ADP_EXTRACT_DATE BETWEEN PSTX.EFFECTIVE_START_DATE (+)
AND PSTX.EFFECTIVE_END_DATE (+)
AND NVL(HL.LOC_INFORMATION17
, HL.REGION_2) = PUST.STATE_ABBREV
AND DECODE(PUST.STATE_ABBREV
, NVL(HL.LOC_INFORMATION17
, HL.REGION_2)
, 'Y'
, 'N') = 'Y'