VIEW: APPS.HR_ADP_WORK_STATE_TAX_V

Source

View Text - Preformatted

SELECT /*+ ORDERED PUSH_SUBQ */ 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) pay_frequency , 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 fit_effective_start_date , pft.effective_end_date fit_effective_end_date , pft.filing_status_code fit_filing_status , decode (pft.filing_status_code, '01', 'S', '02', 'M', '03', 'H', '04', 'F', pft.filing_status_code) fit_filing_status_code , TO_NUMBER(pft.withholding_allowances) fit_exemptions , TO_NUMBER(pft.fit_override_amount) fit_override_amount , TO_NUMBER(pft.fit_override_rate) fit_override_rate , pft.statutory_employee statutory_employee_flag , TO_NUMBER(pft.fit_additional_tax) fit_additional_tax , pft.eic_filing_status_code eic_filing_status , pft.fit_exempt fit_exempt_flag , decode(pft.medicare_tax_exempt,'Y','Y','N') medicare_exempt_flag , decode(pft.futa_tax_exempt,'Y','Y','N') futa_exempt_flag , decode(pft.ss_tax_exempt,'Y','Y','N') ss_exempt_flag , decode(pft.cumulative_taxation,'Y','Y','N') cumulative_taxation_flag , psr2.state_code sui_state_name , psr2.state_code sui_state_code , sit.effective_start_date sit_effective_start_date , sit.effective_end_date sit_effective_end_date , TO_NUMBER(sit.withholding_allowances) sit_exemptions , TO_NUMBER(sit.sit_override_amount) sit_override_amount , TO_NUMBER(sit.sit_override_rate) sit_override_rate , hrl.meaning sit_filing_status , sit.filing_status_code sit_filing_status_code , sit.sit_additional_tax sit_additional_tax , sit.sit_exempt sit_exempt_flag , sit.additional_wa_amount sit_exemption_amount , sit.sit_optional_calc_ind sit_optional_calc , sit.secondary_wa sit_secondary_allowances , sit.sui_wage_base_override_amount sui_wage_base_override_amount , TO_NUMBER(pft.supp_tax_override_rate) fit_supp_override_rate , hrl2.meaning lit_filing_status , lit.filing_status_code lit_filing_status_code , lit.effective_start_date lit_effective_start_date , lit.effective_end_date lit_effective_end_date , lit.lit_override_amount lit_override_amount , lit.school_district_code school_district_code , decode(pasg.work_at_home,'N', nvl(hl.loc_information17, hl.region_2), 'Y', nvl(pad.add_information17, pad.region_2)) work_state_code , decode(pasg.work_at_home,'N', nvl(hl.loc_information17, hl.region_2), 'Y', nvl(pad.add_information17, pad.region_2)) work_state_name , pasg.work_at_home work_at_home_flag , TO_NUMBER(lit.lit_additional_tax) lit_additional_tax , TO_NUMBER(lit.lit_override_rate) lit_override_rate , TO_NUMBER(lit.withholding_allowances) lit_exemptions , nvl(lit.sd_exempt,'N') school_district_tax_flag , lit.jurisdiction_code local_tax_code , cn1.city_name local_jurisdiction , decode(pasg.work_at_home,'N',nvl(hl.loc_information19, hl.region_1), 'Y', nvl(pad.add_information19, pad.region_1)) county_name , lit.lit_exempt lit_exempt_flag , decode(sit.sui_exempt,'Y','Y','N') sui_exempt_flag , decode(sit.sdi_exempt,'Y','Y','N') sdi_exempt_flag , decode(sit.wc_exempt,'Y','Y','N') wc_exempt_flag , decode(sit.state_non_resident_cert,'Y','Y','N') state_non_resident_cert , decode(lit.ht_exempt,'Y','Y','N') occ_priv_tax_exempt , sit.supp_tax_override_rate sit_supp_override_rate , lit.additional_wa_rate lit_additional_allowance_rate , nvl(pstx.sit_exists,'N') sit_exists , nvl(pctx.city_tax,'N') lit_exists , greatest ( decode( greatest( trunc(hr_adp.get_adp_extract_date), nvl(ref.final_process_date, to_date ('1900/01/01','YYYY/MM/DD'))), trunc(hr_adp.get_adp_extract_date), greatest(nvl((decode(pasg2.effective_end_date, to_date('31-12-4712','DD-MM-YYYY'), to_date('31-12-1899','DD-MM-YYYY'), pps.final_process_date, pps.final_process_date - 1, pasg2.effective_end_date) + 1), to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pp.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pp.effective_start_date, nvl(pasg.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date, nvl(ref.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), nvl(ref.final_process_date, to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pft.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pft3.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pad.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pad.date_from, to_date('1900/01/01', 'YYYY/MM/DD')), pft.effective_start_date, pft3.effective_start_date, nvl(sit.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), sit.effective_start_date, nvl(sit3.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), sit3.effective_start_date, nvl(lit.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), lit.effective_start_date, nvl(lit3.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), lit3.effective_start_date, nvl(hl.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD'))), greatest(nvl(ref.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pp.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pp.effective_start_date, nvl(pasg.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date, nvl(pft.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pft3.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pad.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pad.date_from, to_date('1900/01/01', 'YYYY/MM/DD')), pft.effective_start_date, pft3.effective_start_date, nvl(sit.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), sit.effective_start_date, nvl(sit3.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), sit3.effective_start_date, nvl(lit.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), lit.effective_start_date, nvl(lit3.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), lit3.effective_start_date, nvl(hl.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')))), ref.date_start) greatest_last_update_date FROM /* PLEASE DO NOT CHANGE THE TABLE ORDER */ /* Note: The cbo for 8.1.6 seems to be choosing the wrong table order, hence the forced ORDERED hint. This is a culmination of a known CBO bug which is fixed in 9i and the secure view PAY_PAYROLLS_F. Because of the nature of the view, and the way it is used, this should not cause problems, however, for 9i should re-evaluate the hints. The use of the PUSH_SUBQ hint helps the view to filter out rows earlier, and again should be re-evaluated. */ pay_us_emp_fed_tax_rules_f pft, hr_adp_emp_ref_v ref, pay_us_emp_state_tax_rules_f sit, pay_us_emp_city_tax_rules_f lit, per_all_assignments_f pasg, per_periods_of_service pps, hr_soft_coding_keyflex hs, per_addresses pad, hr_locations_all hl, pay_us_city_names cn1, pay_us_states pust, pay_us_states pust2, pay_state_rules psr, per_time_period_types ptp, per_all_people_f pp, hr_lookups hrl, hr_lookups hrl2, pay_us_counties c, hr_all_organization_units hou, pay_us_emp_fed_tax_rules_f pft3, pay_us_emp_state_tax_rules_f sit3, pay_us_emp_city_tax_rules_f lit3, per_all_assignments_f pasg2, pay_us_city_names cn4, pay_us_city_names cn3, pay_us_states pust3, pay_state_rules psr3, hr_lookups hrl3, hr_lookups hrl4, pay_us_counties c2, pay_us_city_names cn2, pay_us_state_tax_info_f pstx, pay_us_city_tax_info_f pctx, pay_state_rules psr2, pay_state_rules psr4 WHERE REF.period_type = ptp.period_type AND hl.style = 'US' 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.rowid = REF.asg_rowid AND pp.ROWID = REF.per_rowid AND hl.location_id = NVL(hs.segment18, pasg.location_id) AND pad.person_id = REF.person_id AND pad.primary_flag = 'Y' AND pad.style = 'US' AND (pad.date_from <= TRUNC(hr_adp.get_adp_extract_date) AND (pad.date_to IS NULL OR pad.date_to >= TRUNC(hr_adp.get_adp_extract_date))) AND pft.assignment_id = ref.assignment_id AND sit.assignment_id = ref.assignment_id AND lit.assignment_id = sit.assignment_id AND pasg2.assignment_id = pasg.assignment_id AND pasg2.assignment_type = 'E' AND pasg2.effective_start_date = (SELECT MAX(pasg3.effective_start_date) FROM per_all_assignments_f pasg3 WHERE pasg3.assignment_id = pasg.assignment_id AND pasg3.effective_start_date <= pasg.effective_start_date AND ((pasg3.location_id <> pasg.location_id) OR (pasg3.location_id = pasg.location_id AND pasg3.effective_start_date = (SELECT MIN(pasg4.effective_start_date) FROM per_all_assignments_f pasg4 WHERE pasg4.assignment_id = pasg.assignment_id AND pasg4.assignment_type = 'E')))) 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 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 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_adp.get_adp_extract_date) AND lit2.emp_city_tax_rule_id = lit.emp_city_tax_rule_id) AND sit.state_code = pust.state_code AND pust.state_abbrev = (SELECT DECODE(pasg.work_at_home,'N', nvl(hl.loc_information17, hl.region_2), 'Y', nvl(pad.add_information17, pad.region_2)) FROM dual) AND hrl.lookup_code = TO_CHAR(TO_NUMBER(sit.filing_status_code)) AND hrl.lookup_type = psr.fs_lookup_type AND hrl2.lookup_code = lit.filing_status_code AND hrl2.lookup_type = 'US_LIT_FILING_STATUS' AND sit.state_code = pust2.state_code AND pust2.state_abbrev = psr.state_code /* fts here - payroll advised that they should index pay_state_rules.jurisdiction_code */ AND psr2.jurisdiction_code = pft.sui_jurisdiction_code AND c.county_name = (SELECT DECODE(pasg.work_at_home,'N',nvl(hl.loc_information19, hl.region_1), 'Y', nvl(pad.add_information19, pad.region_1)) FROM dual) AND c.county_code = SUBSTR(lit.jurisdiction_code, 4, 3) AND c.state_code = SUBSTR(lit.jurisdiction_code, 1, 2) AND SUBSTR(lit.jurisdiction_code, 8, 4) <> '0000' AND cn2.city_name = (SELECT DECODE(pasg.work_at_home,'N',NVL(hl.loc_information18, hl.town_or_city), 'Y',nvl(pad.add_information18,pad.town_or_city)) FROM dual) AND cn2.city_code = lit.city_code AND cn2.county_code = lit.county_code AND cn2.state_code = lit.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 lit.jurisdiction_code = pctx.jurisdiction_code (+) AND hr_adp.get_adp_extract_date BETWEEN pctx.effective_start_date (+) AND pctx.effective_end_date (+) 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 pft3.assignment_id = ref.assignment_id AND pft3.effective_start_date = (SELECT MAX(pft4.effective_start_date) FROM pay_us_emp_fed_tax_rules_f pft4 WHERE pft4.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND pft4.emp_fed_tax_rule_id = pft.emp_fed_tax_rule_id) AND /* fts here - payroll advised that they should index pay_state_rules.jurisdiction_code */ psr4.jurisdiction_code = pft3.sui_jurisdiction_code AND sit3.assignment_id = pft3.assignment_id AND sit3.effective_start_date = (SELECT MAX(sit4.effective_start_date) FROM pay_us_emp_state_tax_rules_f sit4 WHERE sit4.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND sit4.emp_state_tax_rule_id = sit3.emp_state_tax_rule_id) AND pust3.state_code = sit3.state_code AND pust3.state_abbrev = NVL(pad.add_information17,pad.region_2) AND c2.county_name = NVL(pad.add_information19,pad.region_1) AND c2.county_code = SUBSTR(lit3.jurisdiction_code, 4, 3) AND c2.state_code = SUBSTR(lit3.jurisdiction_code, 1, 2) AND SUBSTR(lit3.jurisdiction_code, 8, 4) <> '0000' AND lit3.assignment_id = sit3.assignment_id AND lit3.effective_start_date = (SELECT MAX(lit4.effective_start_date) FROM pay_us_emp_city_tax_rules_f lit4 WHERE lit4.effective_start_date <= TRUNC(hr_adp.get_adp_extract_date) AND lit4.emp_city_tax_rule_id = lit3.emp_city_tax_rule_id) AND cn4.city_name = NVL(pad.add_information18, pad.town_or_city) AND cn4.city_code = lit3.city_code AND cn4.county_code = lit3.county_code AND cn4.state_code = lit3.state_code AND cn3.state_code = lit3.state_code AND cn3.county_code = lit3.county_code AND cn3.city_code = lit3.city_code AND ((cn3.primary_flag = 'Y') OR (cn3.primary_flag = 'N' AND cn3.city_code LIKE 'U%')) AND psr3.state_code = pust3.state_abbrev AND hrl3.lookup_type = psr3.fs_lookup_type AND hrl3.lookup_code = TO_CHAR(TO_NUMBER(sit3.filing_status_code)) AND hrl4.lookup_type = 'US_LIT_FILING_STATUS' AND hrl4.lookup_code = lit3.filing_status_code

View Text - HTML Formatted

SELECT /*+ ORDERED PUSH_SUBQ */ 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) PAY_FREQUENCY
, 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 FIT_EFFECTIVE_START_DATE
, PFT.EFFECTIVE_END_DATE FIT_EFFECTIVE_END_DATE
, PFT.FILING_STATUS_CODE FIT_FILING_STATUS
, DECODE (PFT.FILING_STATUS_CODE
, '01'
, 'S'
, '02'
, 'M'
, '03'
, 'H'
, '04'
, 'F'
, PFT.FILING_STATUS_CODE) FIT_FILING_STATUS_CODE
, TO_NUMBER(PFT.WITHHOLDING_ALLOWANCES) FIT_EXEMPTIONS
, TO_NUMBER(PFT.FIT_OVERRIDE_AMOUNT) FIT_OVERRIDE_AMOUNT
, TO_NUMBER(PFT.FIT_OVERRIDE_RATE) FIT_OVERRIDE_RATE
, PFT.STATUTORY_EMPLOYEE STATUTORY_EMPLOYEE_FLAG
, TO_NUMBER(PFT.FIT_ADDITIONAL_TAX) FIT_ADDITIONAL_TAX
, PFT.EIC_FILING_STATUS_CODE EIC_FILING_STATUS
, PFT.FIT_EXEMPT FIT_EXEMPT_FLAG
, DECODE(PFT.MEDICARE_TAX_EXEMPT
, 'Y'
, 'Y'
, 'N') MEDICARE_EXEMPT_FLAG
, DECODE(PFT.FUTA_TAX_EXEMPT
, 'Y'
, 'Y'
, 'N') FUTA_EXEMPT_FLAG
, DECODE(PFT.SS_TAX_EXEMPT
, 'Y'
, 'Y'
, 'N') SS_EXEMPT_FLAG
, DECODE(PFT.CUMULATIVE_TAXATION
, 'Y'
, 'Y'
, 'N') CUMULATIVE_TAXATION_FLAG
, PSR2.STATE_CODE SUI_STATE_NAME
, PSR2.STATE_CODE SUI_STATE_CODE
, SIT.EFFECTIVE_START_DATE SIT_EFFECTIVE_START_DATE
, SIT.EFFECTIVE_END_DATE SIT_EFFECTIVE_END_DATE
, TO_NUMBER(SIT.WITHHOLDING_ALLOWANCES) SIT_EXEMPTIONS
, TO_NUMBER(SIT.SIT_OVERRIDE_AMOUNT) SIT_OVERRIDE_AMOUNT
, TO_NUMBER(SIT.SIT_OVERRIDE_RATE) SIT_OVERRIDE_RATE
, HRL.MEANING SIT_FILING_STATUS
, SIT.FILING_STATUS_CODE SIT_FILING_STATUS_CODE
, SIT.SIT_ADDITIONAL_TAX SIT_ADDITIONAL_TAX
, SIT.SIT_EXEMPT SIT_EXEMPT_FLAG
, SIT.ADDITIONAL_WA_AMOUNT SIT_EXEMPTION_AMOUNT
, SIT.SIT_OPTIONAL_CALC_IND SIT_OPTIONAL_CALC
, SIT.SECONDARY_WA SIT_SECONDARY_ALLOWANCES
, SIT.SUI_WAGE_BASE_OVERRIDE_AMOUNT SUI_WAGE_BASE_OVERRIDE_AMOUNT
, TO_NUMBER(PFT.SUPP_TAX_OVERRIDE_RATE) FIT_SUPP_OVERRIDE_RATE
, HRL2.MEANING LIT_FILING_STATUS
, LIT.FILING_STATUS_CODE LIT_FILING_STATUS_CODE
, LIT.EFFECTIVE_START_DATE LIT_EFFECTIVE_START_DATE
, LIT.EFFECTIVE_END_DATE LIT_EFFECTIVE_END_DATE
, LIT.LIT_OVERRIDE_AMOUNT LIT_OVERRIDE_AMOUNT
, LIT.SCHOOL_DISTRICT_CODE SCHOOL_DISTRICT_CODE
, DECODE(PASG.WORK_AT_HOME
, 'N'
, NVL(HL.LOC_INFORMATION17
, HL.REGION_2)
, 'Y'
, NVL(PAD.ADD_INFORMATION17
, PAD.REGION_2)) WORK_STATE_CODE
, DECODE(PASG.WORK_AT_HOME
, 'N'
, NVL(HL.LOC_INFORMATION17
, HL.REGION_2)
, 'Y'
, NVL(PAD.ADD_INFORMATION17
, PAD.REGION_2)) WORK_STATE_NAME
, PASG.WORK_AT_HOME WORK_AT_HOME_FLAG
, TO_NUMBER(LIT.LIT_ADDITIONAL_TAX) LIT_ADDITIONAL_TAX
, TO_NUMBER(LIT.LIT_OVERRIDE_RATE) LIT_OVERRIDE_RATE
, TO_NUMBER(LIT.WITHHOLDING_ALLOWANCES) LIT_EXEMPTIONS
, NVL(LIT.SD_EXEMPT
, 'N') SCHOOL_DISTRICT_TAX_FLAG
, LIT.JURISDICTION_CODE LOCAL_TAX_CODE
, CN1.CITY_NAME LOCAL_JURISDICTION
, DECODE(PASG.WORK_AT_HOME
, 'N'
, NVL(HL.LOC_INFORMATION19
, HL.REGION_1)
, 'Y'
, NVL(PAD.ADD_INFORMATION19
, PAD.REGION_1)) COUNTY_NAME
, LIT.LIT_EXEMPT LIT_EXEMPT_FLAG
, DECODE(SIT.SUI_EXEMPT
, 'Y'
, 'Y'
, 'N') SUI_EXEMPT_FLAG
, DECODE(SIT.SDI_EXEMPT
, 'Y'
, 'Y'
, 'N') SDI_EXEMPT_FLAG
, DECODE(SIT.WC_EXEMPT
, 'Y'
, 'Y'
, 'N') WC_EXEMPT_FLAG
, DECODE(SIT.STATE_NON_RESIDENT_CERT
, 'Y'
, 'Y'
, 'N') STATE_NON_RESIDENT_CERT
, DECODE(LIT.HT_EXEMPT
, 'Y'
, 'Y'
, 'N') OCC_PRIV_TAX_EXEMPT
, SIT.SUPP_TAX_OVERRIDE_RATE SIT_SUPP_OVERRIDE_RATE
, LIT.ADDITIONAL_WA_RATE LIT_ADDITIONAL_ALLOWANCE_RATE
, NVL(PSTX.SIT_EXISTS
, 'N') SIT_EXISTS
, NVL(PCTX.CITY_TAX
, 'N') LIT_EXISTS
, GREATEST ( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, NVL(REF.FINAL_PROCESS_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST(NVL((DECODE(PASG2.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, TO_DATE('31-12-1899'
, 'DD-MM-YYYY')
, PPS.FINAL_PROCESS_DATE
, PPS.FINAL_PROCESS_DATE - 1
, PASG2.EFFECTIVE_END_DATE) + 1)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(REF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(REF.FINAL_PROCESS_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PFT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PFT3.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PAD.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PAD.DATE_FROM
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PFT.EFFECTIVE_START_DATE
, PFT3.EFFECTIVE_START_DATE
, NVL(SIT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, SIT.EFFECTIVE_START_DATE
, NVL(SIT3.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, SIT3.EFFECTIVE_START_DATE
, NVL(LIT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, LIT.EFFECTIVE_START_DATE
, NVL(LIT3.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, LIT3.EFFECTIVE_START_DATE
, NVL(HL.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST(NVL(REF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PFT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PFT3.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PAD.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PAD.DATE_FROM
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PFT.EFFECTIVE_START_DATE
, PFT3.EFFECTIVE_START_DATE
, NVL(SIT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, SIT.EFFECTIVE_START_DATE
, NVL(SIT3.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, SIT3.EFFECTIVE_START_DATE
, NVL(LIT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, LIT.EFFECTIVE_START_DATE
, NVL(LIT3.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, LIT3.EFFECTIVE_START_DATE
, NVL(HL.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, REF.DATE_START) GREATEST_LAST_UPDATE_DATE
FROM /* PLEASE DO NOT CHANGE THE TABLE ORDER */ /* NOTE: THE CBO FOR 8.1.6 SEEMS TO BE CHOOSING THE WRONG TABLE ORDER
, HENCE THE FORCED ORDERED HINT. THIS IS A CULMINATION OF A KNOWN CBO BUG WHICH IS FIXED IN 9I
AND THE SECURE VIEW PAY_PAYROLLS_F. BECAUSE OF THE NATURE OF THE VIEW
,
AND THE WAY IT IS USED
, THIS SHOULD NOT CAUSE PROBLEMS
, HOWEVER
, FOR 9I SHOULD RE-EVALUATE THE HINTS. THE USE OF THE PUSH_SUBQ HINT HELPS THE VIEW TO FILTER OUT ROWS EARLIER
,
AND AGAIN SHOULD BE RE-EVALUATED. */ PAY_US_EMP_FED_TAX_RULES_F PFT
, HR_ADP_EMP_REF_V REF
, PAY_US_EMP_STATE_TAX_RULES_F SIT
, PAY_US_EMP_CITY_TAX_RULES_F LIT
, PER_ALL_ASSIGNMENTS_F PASG
, PER_PERIODS_OF_SERVICE PPS
, HR_SOFT_CODING_KEYFLEX HS
, PER_ADDRESSES PAD
, HR_LOCATIONS_ALL HL
, PAY_US_CITY_NAMES CN1
, PAY_US_STATES PUST
, PAY_US_STATES PUST2
, PAY_STATE_RULES PSR
, PER_TIME_PERIOD_TYPES PTP
, PER_ALL_PEOPLE_F PP
, HR_LOOKUPS HRL
, HR_LOOKUPS HRL2
, PAY_US_COUNTIES C
, HR_ALL_ORGANIZATION_UNITS HOU
, PAY_US_EMP_FED_TAX_RULES_F PFT3
, PAY_US_EMP_STATE_TAX_RULES_F SIT3
, PAY_US_EMP_CITY_TAX_RULES_F LIT3
, PER_ALL_ASSIGNMENTS_F PASG2
, PAY_US_CITY_NAMES CN4
, PAY_US_CITY_NAMES CN3
, PAY_US_STATES PUST3
, PAY_STATE_RULES PSR3
, HR_LOOKUPS HRL3
, HR_LOOKUPS HRL4
, PAY_US_COUNTIES C2
, PAY_US_CITY_NAMES CN2
, PAY_US_STATE_TAX_INFO_F PSTX
, PAY_US_CITY_TAX_INFO_F PCTX
, PAY_STATE_RULES PSR2
, PAY_STATE_RULES PSR4
WHERE REF.PERIOD_TYPE = PTP.PERIOD_TYPE
AND HL.STYLE = 'US'
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.ROWID = REF.ASG_ROWID
AND PP.ROWID = REF.PER_ROWID
AND HL.LOCATION_ID = NVL(HS.SEGMENT18
, PASG.LOCATION_ID)
AND PAD.PERSON_ID = REF.PERSON_ID
AND PAD.PRIMARY_FLAG = 'Y'
AND PAD.STYLE = 'US'
AND (PAD.DATE_FROM <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND (PAD.DATE_TO IS NULL OR PAD.DATE_TO >= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)))
AND PFT.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND SIT.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND LIT.ASSIGNMENT_ID = SIT.ASSIGNMENT_ID
AND PASG2.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND PASG2.ASSIGNMENT_TYPE = 'E'
AND PASG2.EFFECTIVE_START_DATE = (SELECT MAX(PASG3.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F PASG3
WHERE PASG3.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND PASG3.EFFECTIVE_START_DATE <= PASG.EFFECTIVE_START_DATE
AND ((PASG3.LOCATION_ID <> PASG.LOCATION_ID) OR (PASG3.LOCATION_ID = PASG.LOCATION_ID
AND PASG3.EFFECTIVE_START_DATE = (SELECT MIN(PASG4.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F PASG4
WHERE PASG4.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND PASG4.ASSIGNMENT_TYPE = 'E'))))
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 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 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_ADP.GET_ADP_EXTRACT_DATE)
AND LIT2.EMP_CITY_TAX_RULE_ID = LIT.EMP_CITY_TAX_RULE_ID)
AND SIT.STATE_CODE = PUST.STATE_CODE
AND PUST.STATE_ABBREV = (SELECT DECODE(PASG.WORK_AT_HOME
, 'N'
, NVL(HL.LOC_INFORMATION17
, HL.REGION_2)
, 'Y'
, NVL(PAD.ADD_INFORMATION17
, PAD.REGION_2))
FROM DUAL)
AND HRL.LOOKUP_CODE = TO_CHAR(TO_NUMBER(SIT.FILING_STATUS_CODE))
AND HRL.LOOKUP_TYPE = PSR.FS_LOOKUP_TYPE
AND HRL2.LOOKUP_CODE = LIT.FILING_STATUS_CODE
AND HRL2.LOOKUP_TYPE = 'US_LIT_FILING_STATUS'
AND SIT.STATE_CODE = PUST2.STATE_CODE
AND PUST2.STATE_ABBREV = PSR.STATE_CODE /* FTS HERE - PAYROLL ADVISED THAT THEY SHOULD INDEX PAY_STATE_RULES.JURISDICTION_CODE */
AND PSR2.JURISDICTION_CODE = PFT.SUI_JURISDICTION_CODE
AND C.COUNTY_NAME = (SELECT DECODE(PASG.WORK_AT_HOME
, 'N'
, NVL(HL.LOC_INFORMATION19
, HL.REGION_1)
, 'Y'
, NVL(PAD.ADD_INFORMATION19
, PAD.REGION_1))
FROM DUAL)
AND C.COUNTY_CODE = SUBSTR(LIT.JURISDICTION_CODE
, 4
, 3)
AND C.STATE_CODE = SUBSTR(LIT.JURISDICTION_CODE
, 1
, 2)
AND SUBSTR(LIT.JURISDICTION_CODE
, 8
, 4) <> '0000'
AND CN2.CITY_NAME = (SELECT DECODE(PASG.WORK_AT_HOME
, 'N'
, NVL(HL.LOC_INFORMATION18
, HL.TOWN_OR_CITY)
, 'Y'
, NVL(PAD.ADD_INFORMATION18
, PAD.TOWN_OR_CITY))
FROM DUAL)
AND CN2.CITY_CODE = LIT.CITY_CODE
AND CN2.COUNTY_CODE = LIT.COUNTY_CODE
AND CN2.STATE_CODE = LIT.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 LIT.JURISDICTION_CODE = PCTX.JURISDICTION_CODE (+)
AND HR_ADP.GET_ADP_EXTRACT_DATE BETWEEN PCTX.EFFECTIVE_START_DATE (+)
AND PCTX.EFFECTIVE_END_DATE (+)
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 PFT3.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PFT3.EFFECTIVE_START_DATE = (SELECT MAX(PFT4.EFFECTIVE_START_DATE)
FROM PAY_US_EMP_FED_TAX_RULES_F PFT4
WHERE PFT4.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PFT4.EMP_FED_TAX_RULE_ID = PFT.EMP_FED_TAX_RULE_ID)
AND /* FTS HERE - PAYROLL ADVISED THAT THEY SHOULD INDEX PAY_STATE_RULES.JURISDICTION_CODE */ PSR4.JURISDICTION_CODE = PFT3.SUI_JURISDICTION_CODE
AND SIT3.ASSIGNMENT_ID = PFT3.ASSIGNMENT_ID
AND SIT3.EFFECTIVE_START_DATE = (SELECT MAX(SIT4.EFFECTIVE_START_DATE)
FROM PAY_US_EMP_STATE_TAX_RULES_F SIT4
WHERE SIT4.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND SIT4.EMP_STATE_TAX_RULE_ID = SIT3.EMP_STATE_TAX_RULE_ID)
AND PUST3.STATE_CODE = SIT3.STATE_CODE
AND PUST3.STATE_ABBREV = NVL(PAD.ADD_INFORMATION17
, PAD.REGION_2)
AND C2.COUNTY_NAME = NVL(PAD.ADD_INFORMATION19
, PAD.REGION_1)
AND C2.COUNTY_CODE = SUBSTR(LIT3.JURISDICTION_CODE
, 4
, 3)
AND C2.STATE_CODE = SUBSTR(LIT3.JURISDICTION_CODE
, 1
, 2)
AND SUBSTR(LIT3.JURISDICTION_CODE
, 8
, 4) <> '0000'
AND LIT3.ASSIGNMENT_ID = SIT3.ASSIGNMENT_ID
AND LIT3.EFFECTIVE_START_DATE = (SELECT MAX(LIT4.EFFECTIVE_START_DATE)
FROM PAY_US_EMP_CITY_TAX_RULES_F LIT4
WHERE LIT4.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND LIT4.EMP_CITY_TAX_RULE_ID = LIT3.EMP_CITY_TAX_RULE_ID)
AND CN4.CITY_NAME = NVL(PAD.ADD_INFORMATION18
, PAD.TOWN_OR_CITY)
AND CN4.CITY_CODE = LIT3.CITY_CODE
AND CN4.COUNTY_CODE = LIT3.COUNTY_CODE
AND CN4.STATE_CODE = LIT3.STATE_CODE
AND CN3.STATE_CODE = LIT3.STATE_CODE
AND CN3.COUNTY_CODE = LIT3.COUNTY_CODE
AND CN3.CITY_CODE = LIT3.CITY_CODE
AND ((CN3.PRIMARY_FLAG = 'Y') OR (CN3.PRIMARY_FLAG = 'N'
AND CN3.CITY_CODE LIKE 'U%'))
AND PSR3.STATE_CODE = PUST3.STATE_ABBREV
AND HRL3.LOOKUP_TYPE = PSR3.FS_LOOKUP_TYPE
AND HRL3.LOOKUP_CODE = TO_CHAR(TO_NUMBER(SIT3.FILING_STATUS_CODE))
AND HRL4.LOOKUP_TYPE = 'US_LIT_FILING_STATUS'
AND HRL4.LOOKUP_CODE = LIT3.FILING_STATUS_CODE