Query to get all regional tax information attached to an employee in Fusion HCM
select table1.PAYROLL_RELATIONSHIP_NUMBER,
table1.person_number,
table1.assignment_number,
table1.full_name,
table1.Tax_Reporting_Unit_Name,
table1.Legislative_Data_Group_Name,
table1.effective_start_date AS Effective_Date,
table1.geography_name as state1,
table1.STATE_DISABILITY,
table1.DIR_INFORMATION_CHAR2 as STATE_TAX_EXEMPT,
table1.IRS_Lock_in_Date,
table1.Exemption_for_Military_Spouse,
table1.Nonresident,
table1.Optional_Calculation_Method,
(SELECT HL.MEANING
FROM HR_LOOKUPS HL
WHERE HL.LOOKUP_CODE = table1.DIR_INFORMATION_CHAR1
AND HL.LOOKUP_TYPE = 'HRX_US_STATE_FILING_STATUS_'||table1.geography_name) as SIT_FILING_STATUS,
table1.Voluntary_Tax_Withholding,
table1.Exempt_from_wage_Accumulation,
table1.Resident_Wage_Accumulation,
table1.STATE_UNEMPLOYMENT,
table1.STATE_COMPENSATION,
table1.allowance,
table1.additional_tax,
table2.county,
table3.city,
table1.RESIDENT_PSD_CODE,
table1.WORK_PSD_CODE
from
(select pprd.PAYROLL_RELATIONSHIP_NUMBER,
paa.assignment_number,
pap.person_number,
ppn.full_name,
psu.name as Tax_Reporting_Unit_Name,
'' as Legislative_Data_Group_Name,
hzg.geography_name,
sit_details.DIR_INFORMATION_CHAR1,
sit_details.DIR_INFORMATION_CHAR2,
(SELECT DISABILITY_DETAILS.DIR_INFORMATION_CHAR1
FROM PAY_DIR_CARD_COMPONENTS_F DISABILITY_COMP,
PAY_DIR_COMP_DETAILS_F DISABILITY_DETAILS
WHERE DISABILITY_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND DISABILITY_COMP.DIR_CARD_COMP_ID = DISABILITY_DETAILS.DIR_CARD_COMP_ID
AND DISABILITY_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_DISABILITY'
AND nvl(sit_comp.context_value1,0) = nvl(DISABILITY_COMP.context_value1,0)
AND nvl(sit_comp.context_value2,0) = nvl(DISABILITY_COMP.context_value2,0)
AND nvl(sit_comp.context_value3,0) = nvl(DISABILITY_COMP.context_value3,0)
AND TRUNC(SYSDATE) BETWEEN DISABILITY_COMP.EFFECTIVE_START_DATE AND DISABILITY_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN DISABILITY_DETAILS.EFFECTIVE_START_DATE AND DISABILITY_DETAILS.EFFECTIVE_END_DATE) AS STATE_DISABILITY,
substr(DIR_INFORMATION_DATE1,1,10) AS IRS_Lock_in_Date,
sit_details.DIR_INFORMATION_CHAR7 as Exemption_for_Military_Spouse,
sit_details.DIR_INFORMATION_CHAR4 as Nonresident,
'' as Optional_Calculation_Method,
sit_details.DIR_INFORMATION_CHAR6 as Voluntary_Tax_Withholding,
sit_details.DIR_INFORMATION_CHAR3 as Exempt_from_wage_Accumulation,
hl.meaning as Resident_Wage_Accumulation,
(SELECT UNEMPLOYMENT_DETAILS.DIR_INFORMATION_CHAR1
FROM PAY_DIR_CARD_COMPONENTS_F UNEMPLOYMENT_COMP,
PAY_DIR_COMP_DETAILS_F UNEMPLOYMENT_DETAILS
WHERE UNEMPLOYMENT_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND UNEMPLOYMENT_COMP.DIR_CARD_COMP_ID = UNEMPLOYMENT_DETAILS.DIR_CARD_COMP_ID
AND UNEMPLOYMENT_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_UNEMPLOYMENT'
AND nvl(sit_comp.context_value1,0) = nvl(UNEMPLOYMENT_COMP.context_value1,0)
AND nvl(sit_comp.context_value2,0) = nvl(UNEMPLOYMENT_COMP.context_value2,0)
AND nvl(sit_comp.context_value3,0) = nvl(UNEMPLOYMENT_COMP.context_value3,0)
AND TRUNC(SYSDATE) BETWEEN UNEMPLOYMENT_COMP.EFFECTIVE_START_DATE AND UNEMPLOYMENT_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN UNEMPLOYMENT_DETAILS.EFFECTIVE_START_DATE AND UNEMPLOYMENT_DETAILS.EFFECTIVE_END_DATE) AS STATE_UNEMPLOYMENT,
(SELECT COMPENSATION_DETAILS.DIR_INFORMATION_CHAR1
FROM PAY_DIR_CARD_COMPONENTS_F COMPENSATION_COMP,
PAY_DIR_COMP_DETAILS_F COMPENSATION_DETAILS
WHERE COMPENSATION_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND COMPENSATION_COMP.DIR_CARD_COMP_ID = COMPENSATION_DETAILS.DIR_CARD_COMP_ID
AND COMPENSATION_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_WORKERS_COMPENSATION'
AND nvl(sit_comp.context_value1,0) = nvl(COMPENSATION_COMP.context_value1,0)
AND nvl(sit_comp.context_value2,0) = nvl(COMPENSATION_COMP.context_value2,0)
AND nvl(sit_comp.context_value3,0) = nvl(COMPENSATION_COMP.context_value3,0)
AND TRUNC(SYSDATE) BETWEEN COMPENSATION_COMP.EFFECTIVE_START_DATE AND COMPENSATION_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN COMPENSATION_DETAILS.EFFECTIVE_START_DATE AND COMPENSATION_DETAILS.EFFECTIVE_END_DATE) AS STATE_COMPENSATION,
(SELECT PSD_DETAILS.DIR_INFORMATION_NUMBER1
FROM PAY_DIR_CARD_COMPONENTS_F PSD_COMP,
PAY_DIR_COMP_DETAILS_F PSD_DETAILS
WHERE PSD_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND PSD_COMP.DIR_CARD_COMP_ID = PSD_DETAILS.DIR_CARD_COMP_ID
AND PSD_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_'||hzg.geography_name||'_PSD'
AND TRUNC(SYSDATE) BETWEEN PSD_COMP.EFFECTIVE_START_DATE AND PSD_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PSD_DETAILS.EFFECTIVE_START_DATE AND PSD_DETAILS.EFFECTIVE_END_DATE) AS RESIDENT_PSD_CODE,
(SELECT PSD_DETAILS.DIR_INFORMATION_NUMBER2
FROM PAY_DIR_CARD_COMPONENTS_F PSD_COMP,
PAY_DIR_COMP_DETAILS_F PSD_DETAILS
WHERE PSD_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND PSD_COMP.DIR_CARD_COMP_ID = PSD_DETAILS.DIR_CARD_COMP_ID
AND PSD_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_'||hzg.geography_name||'_PSD'
AND TRUNC(SYSDATE) BETWEEN PSD_COMP.EFFECTIVE_START_DATE AND PSD_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PSD_DETAILS.EFFECTIVE_START_DATE AND PSD_DETAILS.EFFECTIVE_END_DATE) AS WORK_PSD_CODE,
substr(sit_details.effective_start_date,1,10) AS effective_start_date,
allowance_details.value1 as allowance,
add_tax_details.value1 as additional_tax,
pap.person_id,
hzg.geography_id,
sit_details.dir_information_category
from per_all_assignments_m paa,
per_all_people_f pap,
per_person_names_f ppn,
pay_rel_groups_dn prd,
pay_pay_relationships_dn pprd,
pay_dir_cards_f pdcf,
pay_dir_card_components_f sit_comp,
pay_dir_comp_details_f sit_details,
pay_value_definitions_f sit_allowance_valuedefn,
pay_value_definitions_f sit_add_tax_valuedefn,
pay_range_items_f allowance_details,
pay_range_items_f add_tax_details,
hz_geographies hzg ,hz_geography_identifiers hzgi,
hz_geography_identifiers hzgiv,
hcm_lookups hl,
HR_ORGANIZATION_UNITS PSU
where paa.assignment_number =prd.assignment_number
and paa.person_id = pap.person_id
and pap.person_id = ppn.person_id
and ppn.name_type = 'GLOBAL'
AND PAA.ASSIGNMENT_TYPE IN ('E','C')
AND pprd.payroll_relationship_id =prd.payroll_relationship_id
AND prd.group_type ='A'
AND pdcf.dir_card_id =sit_comp.dir_card_id
AND pprd.payroll_relationship_id =pdcf.payroll_relationship_id
AND PPRD.PAYROLL_STAT_UNIT_ID(+) = PSU.ORGANIZATION_ID
AND sit_allowance_valuedefn.source_id =sit_comp.dir_card_comp_id
AND sit_allowance_valuedefn.source_type ='PDCC'
AND sit_add_tax_valuedefn.source_type ='PDCC'
AND sit_allowance_valuedefn.base_name ='US_'||hzg.geography_code||'_ALLOWANCE'
AND sit_add_tax_valuedefn.base_name ='US_'||hzg.geography_code||'_ADDITIONAL_TAX'
AND sit_add_tax_valuedefn.source_id =sit_comp.dir_card_comp_id
AND sit_allowance_valuedefn.value_defn_id =allowance_details.value_defn_id
AND sit_add_tax_valuedefn.value_defn_id =add_tax_details.value_defn_id
AND sit_comp.dir_card_comp_id =sit_details.dir_card_comp_id
AND sit_details.dir_information_category ='HRX_US_WTH_STATE_INCOME_TAX'
and nvl(sit_comp.context_value1,0)||'-'||nvl(sit_comp.context_value2,0)||'-'||nvl(sit_comp.context_value3,0)=hzgi.identifier_value
and hzg.country_code='US'
and hzg.geography_type='STATE'
and hzg.geography_use='MASTER_REF'
and hzg.geography_element1='United States'
and hzg.geography_element1_code='US'
and hzg.geography_id=hzgi.geography_id
and hzgi.identifier_type='CODE'
and hzgi.identifier_subtype='GEO_CODE'
and hzgi.primary_flag='N'
and hzgi.geography_use='MASTER_REF'
and hzgi.geography_type='STATE'
and hzgi.language_code='US'
and hzgi.geography_id= hzgiv.geography_id
and hzgiv.identifier_type='NAME'
and hzgiv.identifier_subtype='STANDARD_NAME'
and hzgiv.primary_flag='Y'
and hzgiv.geography_use='MASTER_REF'
and hzgiv.geography_type='STATE'
and hzgiv.language_code='US'
and paa.assignment_number=prd.assignment_number
and pprd.payroll_relationship_id=prd.payroll_relationship_id
and prd.group_type='A'
and paa.assignment_sequence = ( select max(assignment_sequence) from per_all_assignments_m where person_id = paa.person_id and ASSIGNMENT_TYPE IN ('E','C') and sysdate between effective_start_date and effective_end_date)
and paa.effective_latest_change = 'Y'
and substr(sit_details.effective_end_date,1,10) = '4712-12-31'
and sit_details.DIR_INFORMATION_CHAR8= hl.lookup_code(+)
and hl.lookup_type (+) = 'HRX_US_STATE_WAGE_ACCUM'
and hl.ENABLED_FLAG(+) = 'Y'
and trunc(sysdate) between paa.effective_start_date and paa.effective_end_date
and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
and trunc(sysdate) between ppn.effective_start_date and ppn.effective_end_date
and trunc(sysdate) between pprd.START_DATE(+) and pprd.END_DATE(+)
and trunc(sysdate) between prd.START_DATE(+) and prd.END_DATE(+)
and trunc(sysdate) between sit_comp.effective_start_date(+) and sit_comp.effective_end_date(+)
and trunc(sysdate) between sit_allowance_valuedefn.effective_start_date(+) and sit_allowance_valuedefn.effective_end_date(+)
and trunc(sysdate) between sit_add_tax_valuedefn.effective_start_date(+) and sit_add_tax_valuedefn.effective_end_date(+)
and trunc(sysdate) between sit_details.effective_start_date and sit_details.effective_end_date
and trunc(sysdate) between allowance_details.effective_start_date and allowance_details.effective_end_date
and trunc(sysdate) between add_tax_details.effective_start_date and add_tax_details.effective_end_date
union
select pprd.PAYROLL_RELATIONSHIP_NUMBER,
paa.assignment_number,
pap.person_number,
ppn.full_name,
psu.name as Tax_Reporting_Unit_Name,
'' as Legislative_Data_Group_Name,
hzg.geography_name,
sit_details.DIR_INFORMATION_CHAR1,
sit_details.DIR_INFORMATION_CHAR2,
(SELECT DISABILITY_DETAILS.DIR_INFORMATION_CHAR1
FROM PAY_DIR_CARD_COMPONENTS_F DISABILITY_COMP,
PAY_DIR_COMP_DETAILS_F DISABILITY_DETAILS
WHERE DISABILITY_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND DISABILITY_COMP.DIR_CARD_COMP_ID = DISABILITY_DETAILS.DIR_CARD_COMP_ID
AND DISABILITY_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_DISABILITY'
AND nvl(sit_comp.context_value1,0) = nvl(DISABILITY_COMP.context_value1,0)
AND nvl(sit_comp.context_value2,0) = nvl(DISABILITY_COMP.context_value2,0)
AND nvl(sit_comp.context_value3,0) = nvl(DISABILITY_COMP.context_value3,0)
AND TRUNC(SYSDATE) BETWEEN DISABILITY_COMP.EFFECTIVE_START_DATE AND DISABILITY_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN DISABILITY_DETAILS.EFFECTIVE_START_DATE AND DISABILITY_DETAILS.EFFECTIVE_END_DATE) AS STATE_DISABILITY,
substr(DIR_INFORMATION_DATE1,1,10) AS IRS_Lock_in_Date,
sit_details.DIR_INFORMATION_CHAR7 as Exemption_for_Military_Spouse,
sit_details.DIR_INFORMATION_CHAR4 as Nonresident,
'' as Optional_Calculation_Method,
sit_details.DIR_INFORMATION_CHAR6 as Voluntary_Tax_Withholding,
sit_details.DIR_INFORMATION_CHAR3 as Exempt_from_wage_Accumulation,
hl.meaning as Resident_Wage_Accumulation,
(SELECT UNEMPLOYMENT_DETAILS.DIR_INFORMATION_CHAR1
FROM PAY_DIR_CARD_COMPONENTS_F UNEMPLOYMENT_COMP,
PAY_DIR_COMP_DETAILS_F UNEMPLOYMENT_DETAILS
WHERE UNEMPLOYMENT_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND UNEMPLOYMENT_COMP.DIR_CARD_COMP_ID = UNEMPLOYMENT_DETAILS.DIR_CARD_COMP_ID
AND UNEMPLOYMENT_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_UNEMPLOYMENT'
AND nvl(sit_comp.context_value1,0) = nvl(UNEMPLOYMENT_COMP.context_value1,0)
AND nvl(sit_comp.context_value2,0) = nvl(UNEMPLOYMENT_COMP.context_value2,0)
AND nvl(sit_comp.context_value3,0) = nvl(UNEMPLOYMENT_COMP.context_value3,0)
AND TRUNC(SYSDATE) BETWEEN UNEMPLOYMENT_COMP.EFFECTIVE_START_DATE AND UNEMPLOYMENT_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN UNEMPLOYMENT_DETAILS.EFFECTIVE_START_DATE AND UNEMPLOYMENT_DETAILS.EFFECTIVE_END_DATE) AS STATE_UNEMPLOYMENT,
(SELECT COMPENSATION_DETAILS.DIR_INFORMATION_CHAR1
FROM PAY_DIR_CARD_COMPONENTS_F COMPENSATION_COMP,
PAY_DIR_COMP_DETAILS_F COMPENSATION_DETAILS
WHERE COMPENSATION_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND COMPENSATION_COMP.DIR_CARD_COMP_ID = COMPENSATION_DETAILS.DIR_CARD_COMP_ID
AND COMPENSATION_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_WORKERS_COMPENSATION'
AND nvl(sit_comp.context_value1,0) = nvl(COMPENSATION_COMP.context_value1,0)
AND nvl(sit_comp.context_value2,0) = nvl(COMPENSATION_COMP.context_value2,0)
AND nvl(sit_comp.context_value3,0) = nvl(COMPENSATION_COMP.context_value3,0)
AND TRUNC(SYSDATE) BETWEEN COMPENSATION_COMP.EFFECTIVE_START_DATE AND COMPENSATION_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN COMPENSATION_DETAILS.EFFECTIVE_START_DATE AND COMPENSATION_DETAILS.EFFECTIVE_END_DATE) AS STATE_COMPENSATION,
(SELECT PSD_DETAILS.DIR_INFORMATION_NUMBER1
FROM PAY_DIR_CARD_COMPONENTS_F PSD_COMP,
PAY_DIR_COMP_DETAILS_F PSD_DETAILS
WHERE PSD_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND PSD_COMP.DIR_CARD_COMP_ID = PSD_DETAILS.DIR_CARD_COMP_ID
AND PSD_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_'||hzg.geography_name||'_PSD'
AND TRUNC(SYSDATE) BETWEEN PSD_COMP.EFFECTIVE_START_DATE AND PSD_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PSD_DETAILS.EFFECTIVE_START_DATE AND PSD_DETAILS.EFFECTIVE_END_DATE) AS RESIDENT_PSD_CODE,
(SELECT PSD_DETAILS.DIR_INFORMATION_NUMBER2
FROM PAY_DIR_CARD_COMPONENTS_F PSD_COMP,
PAY_DIR_COMP_DETAILS_F PSD_DETAILS
WHERE PSD_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND PSD_COMP.DIR_CARD_COMP_ID = PSD_DETAILS.DIR_CARD_COMP_ID
AND PSD_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_'||hzg.geography_name||'_PSD'
AND TRUNC(SYSDATE) BETWEEN PSD_COMP.EFFECTIVE_START_DATE AND PSD_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PSD_DETAILS.EFFECTIVE_START_DATE AND PSD_DETAILS.EFFECTIVE_END_DATE) AS WORK_PSD_CODE,
substr(sit_details.effective_start_date,1,10) AS effective_start_date,
allowance_details.value1 as allowance,
'' as additional_tax,
pap.person_id,
hzg.geography_id,
sit_details.dir_information_category
from per_all_assignments_m paa,
per_all_people_f pap,
per_person_names_f ppn,
pay_rel_groups_dn prd,
pay_pay_relationships_dn pprd,
pay_dir_cards_f pdcf,
pay_dir_card_components_f sit_comp,
pay_dir_comp_details_f sit_details,
pay_value_definitions_f sit_allowance_valuedefn,
pay_range_items_f allowance_details,
hz_geographies hzg ,hz_geography_identifiers hzgi,
hz_geography_identifiers hzgiv,
hcm_lookups hl,
HR_ORGANIZATION_UNITS PSU
where paa.assignment_number =prd.assignment_number
and paa.person_id = pap.person_id
and pap.person_id = ppn.person_id
and ppn.name_type = 'GLOBAL'
AND PAA.ASSIGNMENT_TYPE IN ('E','C')
AND pprd.payroll_relationship_id =prd.payroll_relationship_id
AND prd.group_type ='A'
AND pdcf.dir_card_id =sit_comp.dir_card_id
AND pprd.payroll_relationship_id =pdcf.payroll_relationship_id
AND PPRD.PAYROLL_STAT_UNIT_ID(+) = PSU.ORGANIZATION_ID
AND sit_allowance_valuedefn.source_id =sit_comp.dir_card_comp_id
AND sit_allowance_valuedefn.source_type ='PDCC'
AND sit_allowance_valuedefn.base_name ='US_'||hzg.geography_code||'_SUI_ER_RATE'
AND sit_allowance_valuedefn.value_defn_id =allowance_details.value_defn_id
AND sit_comp.dir_card_comp_id =sit_details.dir_card_comp_id
AND sit_details.dir_information_category ='HRX_US_WTH_STATE_INCOME_TAX'
and nvl(sit_comp.context_value1,0)||'-'||nvl(sit_comp.context_value2,0)||'-'||nvl(sit_comp.context_value3,0)=hzgi.identifier_value
and hzg.country_code='US'
and hzg.geography_type='STATE'
and hzg.geography_use='MASTER_REF'
and hzg.geography_element1='United States'
and hzg.geography_element1_code='US'
and hzg.geography_id=hzgi.geography_id
and hzgi.identifier_type='CODE'
and hzgi.identifier_subtype='GEO_CODE'
and hzgi.primary_flag='N'
and hzgi.geography_use='MASTER_REF'
and hzgi.geography_type='STATE'
and hzgi.language_code='US'
and hzgi.geography_id= hzgiv.geography_id
and hzgiv.identifier_type='NAME'
and hzgiv.identifier_subtype='STANDARD_NAME'
and hzgiv.primary_flag='Y'
and hzgiv.geography_use='MASTER_REF'
and hzgiv.geography_type='STATE'
and hzgiv.language_code='US'
and paa.assignment_number=prd.assignment_number
and pprd.payroll_relationship_id=prd.payroll_relationship_id
and prd.group_type='A'
and paa.assignment_sequence = ( select max(assignment_sequence) from per_all_assignments_m where person_id = paa.person_id and ASSIGNMENT_TYPE IN ('E','C') and sysdate between effective_start_date and effective_end_date)
and paa.effective_latest_change = 'Y'
and substr(sit_details.effective_end_date,1,10) = '4712-12-31'
and sit_details.DIR_INFORMATION_CHAR8= hl.lookup_code(+)
and hl.lookup_type (+) = 'HRX_US_STATE_WAGE_ACCUM'
and hl.ENABLED_FLAG(+) = 'Y'
and trunc(sysdate) between paa.effective_start_date and paa.effective_end_date
and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
and trunc(sysdate) between ppn.effective_start_date and ppn.effective_end_date
and trunc(sysdate) between pprd.START_DATE(+) and pprd.END_DATE(+)
and trunc(sysdate) between prd.START_DATE(+) and prd.END_DATE(+)
and trunc(sysdate) between sit_comp.effective_start_date(+) and sit_comp.effective_end_date(+)
and trunc(sysdate) between sit_allowance_valuedefn.effective_start_date(+) and sit_allowance_valuedefn.effective_end_date(+)
and trunc(sysdate) between sit_details.effective_start_date and sit_details.effective_end_date
and trunc(sysdate) between allowance_details.effective_start_date and allowance_details.effective_end_date) TABLE1,
(SELECT DISTINCT hg.geography_name as county,
hg.geography_element2_id,
pap.person_id,
hg.geography_id
FROM pay_dir_comp_details_f pdc,
pay_dir_card_components_f pdcc,
hz_geography_identifiers hgi,
hz_geographies hg,
per_all_people_f pap
WHERE pdc.person_id = pap.person_id
and pdc.dir_information_category = 'HRX_US_WTH_COUNTY_INCOME_TAX'
and pdc.dir_card_comp_id = pdcc.dir_card_comp_id
and nvl(pdcc.context_value1,0)||'-'||nvl(pdcc.context_value2,0)||'-'||'0'=hgi.identifier_value
and hgi.identifier_type='CODE'
and hgi.identifier_subtype='GEO_CODE'
and hgi.primary_flag='N'
and hgi.geography_use='MASTER_REF'
and hgi.geography_type='COUNTY'
and hgi.language_code='US'
and hgi.geography_id = hg.geography_id
and hg.country_code='US'
and hg.geography_type='COUNTY'
and hg.geography_use='MASTER_REF'
and hg.geography_element1='United States'
and hg.geography_element1_code='US'
and substr(pdc.effective_start_date,1,10) = (select substr(max(effective_start_date),1,10) from pay_dir_comp_details_f where person_id = pap.person_id and dir_information_category = 'HRX_US_WTH_COUNTY_INCOME_TAX')
and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
and trunc(sysdate) between pdc.effective_start_date and pdc.effective_end_date
and trunc(sysdate) between pdcc.effective_start_date(+) and pdcc.effective_end_date(+)) table2,
(SELECT DISTINCT hg.geography_name as city,
hg.geography_element2_id,
pap.person_id,
hg.geography_element3_id
FROM pay_dir_comp_details_f pdc,
pay_dir_card_components_f pdcc,
hz_geography_identifiers hgi,
hz_geography_identifiers hgiv,
hz_geographies hg,
per_all_people_f pap
WHERE pdc.person_id = pap.person_id
and pdc.dir_information_category = 'HRX_US_WTH_CITY_INCOME_TAX'
and pdc.dir_card_comp_id = pdcc.dir_card_comp_id
and nvl(pdcc.context_value1,0)||'-'||nvl(pdcc.context_value2,0)||'-'||nvl(pdcc.context_value3,0)=hgi.identifier_value
and hgi.identifier_type='CODE'
and hgi.identifier_subtype='GEO_CODE'
and hgi.primary_flag='N'
and hgi.geography_use='MASTER_REF'
and hgi.geography_type='CITY'
and hgi.language_code='US'
and hgi.geography_id = hg.geography_id
and hg.country_code='US'
and hg.geography_type='CITY'
and hg.geography_use='MASTER_REF'
and hg.geography_element1='United States'
and hg.geography_element1_code='US'
and hgi.geography_id= hgiv.geography_id
and hgiv.identifier_type='CODE'
and hgiv.identifier_subtype='PRIMARY_CITY'
and hgiv.primary_flag='N'
and hgiv.geography_use='MASTER_REF'
and hgiv.geography_type='CITY'
and hgiv.language_code='US'
and substr(pdc.effective_start_date,1,10) = (select substr(max(effective_start_date),1,10) from pay_dir_comp_details_f where person_id = pap.person_id and dir_information_category = 'HRX_US_WTH_CITY_INCOME_TAX')
and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
and trunc(sysdate) between pdc.effective_start_date and pdc.effective_end_date
and trunc(sysdate) between pdcc.effective_start_date(+) and pdcc.effective_end_date(+)) table3
where TABLE1.PERSON_ID = TABLE2.PERSON_ID(+)
and TABLE2.PERSON_ID = TABLE3.PERSON_ID(+)
and table1.geography_id = table2.geography_element2_id(+)
and table2.geography_element2_id = table3.geography_element2_id(+)
and table2.geography_id = table3.geography_element3_id(+)
order by table1.person_id
table1.person_number,
table1.assignment_number,
table1.full_name,
table1.Tax_Reporting_Unit_Name,
table1.Legislative_Data_Group_Name,
table1.effective_start_date AS Effective_Date,
table1.geography_name as state1,
table1.STATE_DISABILITY,
table1.DIR_INFORMATION_CHAR2 as STATE_TAX_EXEMPT,
table1.IRS_Lock_in_Date,
table1.Exemption_for_Military_Spouse,
table1.Nonresident,
table1.Optional_Calculation_Method,
(SELECT HL.MEANING
FROM HR_LOOKUPS HL
WHERE HL.LOOKUP_CODE = table1.DIR_INFORMATION_CHAR1
AND HL.LOOKUP_TYPE = 'HRX_US_STATE_FILING_STATUS_'||table1.geography_name) as SIT_FILING_STATUS,
table1.Voluntary_Tax_Withholding,
table1.Exempt_from_wage_Accumulation,
table1.Resident_Wage_Accumulation,
table1.STATE_UNEMPLOYMENT,
table1.STATE_COMPENSATION,
table1.allowance,
table1.additional_tax,
table2.county,
table3.city,
table1.RESIDENT_PSD_CODE,
table1.WORK_PSD_CODE
from
(select pprd.PAYROLL_RELATIONSHIP_NUMBER,
paa.assignment_number,
pap.person_number,
ppn.full_name,
psu.name as Tax_Reporting_Unit_Name,
'' as Legislative_Data_Group_Name,
hzg.geography_name,
sit_details.DIR_INFORMATION_CHAR1,
sit_details.DIR_INFORMATION_CHAR2,
(SELECT DISABILITY_DETAILS.DIR_INFORMATION_CHAR1
FROM PAY_DIR_CARD_COMPONENTS_F DISABILITY_COMP,
PAY_DIR_COMP_DETAILS_F DISABILITY_DETAILS
WHERE DISABILITY_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND DISABILITY_COMP.DIR_CARD_COMP_ID = DISABILITY_DETAILS.DIR_CARD_COMP_ID
AND DISABILITY_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_DISABILITY'
AND nvl(sit_comp.context_value1,0) = nvl(DISABILITY_COMP.context_value1,0)
AND nvl(sit_comp.context_value2,0) = nvl(DISABILITY_COMP.context_value2,0)
AND nvl(sit_comp.context_value3,0) = nvl(DISABILITY_COMP.context_value3,0)
AND TRUNC(SYSDATE) BETWEEN DISABILITY_COMP.EFFECTIVE_START_DATE AND DISABILITY_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN DISABILITY_DETAILS.EFFECTIVE_START_DATE AND DISABILITY_DETAILS.EFFECTIVE_END_DATE) AS STATE_DISABILITY,
substr(DIR_INFORMATION_DATE1,1,10) AS IRS_Lock_in_Date,
sit_details.DIR_INFORMATION_CHAR7 as Exemption_for_Military_Spouse,
sit_details.DIR_INFORMATION_CHAR4 as Nonresident,
'' as Optional_Calculation_Method,
sit_details.DIR_INFORMATION_CHAR6 as Voluntary_Tax_Withholding,
sit_details.DIR_INFORMATION_CHAR3 as Exempt_from_wage_Accumulation,
hl.meaning as Resident_Wage_Accumulation,
(SELECT UNEMPLOYMENT_DETAILS.DIR_INFORMATION_CHAR1
FROM PAY_DIR_CARD_COMPONENTS_F UNEMPLOYMENT_COMP,
PAY_DIR_COMP_DETAILS_F UNEMPLOYMENT_DETAILS
WHERE UNEMPLOYMENT_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND UNEMPLOYMENT_COMP.DIR_CARD_COMP_ID = UNEMPLOYMENT_DETAILS.DIR_CARD_COMP_ID
AND UNEMPLOYMENT_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_UNEMPLOYMENT'
AND nvl(sit_comp.context_value1,0) = nvl(UNEMPLOYMENT_COMP.context_value1,0)
AND nvl(sit_comp.context_value2,0) = nvl(UNEMPLOYMENT_COMP.context_value2,0)
AND nvl(sit_comp.context_value3,0) = nvl(UNEMPLOYMENT_COMP.context_value3,0)
AND TRUNC(SYSDATE) BETWEEN UNEMPLOYMENT_COMP.EFFECTIVE_START_DATE AND UNEMPLOYMENT_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN UNEMPLOYMENT_DETAILS.EFFECTIVE_START_DATE AND UNEMPLOYMENT_DETAILS.EFFECTIVE_END_DATE) AS STATE_UNEMPLOYMENT,
(SELECT COMPENSATION_DETAILS.DIR_INFORMATION_CHAR1
FROM PAY_DIR_CARD_COMPONENTS_F COMPENSATION_COMP,
PAY_DIR_COMP_DETAILS_F COMPENSATION_DETAILS
WHERE COMPENSATION_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND COMPENSATION_COMP.DIR_CARD_COMP_ID = COMPENSATION_DETAILS.DIR_CARD_COMP_ID
AND COMPENSATION_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_WORKERS_COMPENSATION'
AND nvl(sit_comp.context_value1,0) = nvl(COMPENSATION_COMP.context_value1,0)
AND nvl(sit_comp.context_value2,0) = nvl(COMPENSATION_COMP.context_value2,0)
AND nvl(sit_comp.context_value3,0) = nvl(COMPENSATION_COMP.context_value3,0)
AND TRUNC(SYSDATE) BETWEEN COMPENSATION_COMP.EFFECTIVE_START_DATE AND COMPENSATION_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN COMPENSATION_DETAILS.EFFECTIVE_START_DATE AND COMPENSATION_DETAILS.EFFECTIVE_END_DATE) AS STATE_COMPENSATION,
(SELECT PSD_DETAILS.DIR_INFORMATION_NUMBER1
FROM PAY_DIR_CARD_COMPONENTS_F PSD_COMP,
PAY_DIR_COMP_DETAILS_F PSD_DETAILS
WHERE PSD_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND PSD_COMP.DIR_CARD_COMP_ID = PSD_DETAILS.DIR_CARD_COMP_ID
AND PSD_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_'||hzg.geography_name||'_PSD'
AND TRUNC(SYSDATE) BETWEEN PSD_COMP.EFFECTIVE_START_DATE AND PSD_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PSD_DETAILS.EFFECTIVE_START_DATE AND PSD_DETAILS.EFFECTIVE_END_DATE) AS RESIDENT_PSD_CODE,
(SELECT PSD_DETAILS.DIR_INFORMATION_NUMBER2
FROM PAY_DIR_CARD_COMPONENTS_F PSD_COMP,
PAY_DIR_COMP_DETAILS_F PSD_DETAILS
WHERE PSD_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND PSD_COMP.DIR_CARD_COMP_ID = PSD_DETAILS.DIR_CARD_COMP_ID
AND PSD_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_'||hzg.geography_name||'_PSD'
AND TRUNC(SYSDATE) BETWEEN PSD_COMP.EFFECTIVE_START_DATE AND PSD_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PSD_DETAILS.EFFECTIVE_START_DATE AND PSD_DETAILS.EFFECTIVE_END_DATE) AS WORK_PSD_CODE,
substr(sit_details.effective_start_date,1,10) AS effective_start_date,
allowance_details.value1 as allowance,
add_tax_details.value1 as additional_tax,
pap.person_id,
hzg.geography_id,
sit_details.dir_information_category
from per_all_assignments_m paa,
per_all_people_f pap,
per_person_names_f ppn,
pay_rel_groups_dn prd,
pay_pay_relationships_dn pprd,
pay_dir_cards_f pdcf,
pay_dir_card_components_f sit_comp,
pay_dir_comp_details_f sit_details,
pay_value_definitions_f sit_allowance_valuedefn,
pay_value_definitions_f sit_add_tax_valuedefn,
pay_range_items_f allowance_details,
pay_range_items_f add_tax_details,
hz_geographies hzg ,hz_geography_identifiers hzgi,
hz_geography_identifiers hzgiv,
hcm_lookups hl,
HR_ORGANIZATION_UNITS PSU
where paa.assignment_number =prd.assignment_number
and paa.person_id = pap.person_id
and pap.person_id = ppn.person_id
and ppn.name_type = 'GLOBAL'
AND PAA.ASSIGNMENT_TYPE IN ('E','C')
AND pprd.payroll_relationship_id =prd.payroll_relationship_id
AND prd.group_type ='A'
AND pdcf.dir_card_id =sit_comp.dir_card_id
AND pprd.payroll_relationship_id =pdcf.payroll_relationship_id
AND PPRD.PAYROLL_STAT_UNIT_ID(+) = PSU.ORGANIZATION_ID
AND sit_allowance_valuedefn.source_id =sit_comp.dir_card_comp_id
AND sit_allowance_valuedefn.source_type ='PDCC'
AND sit_add_tax_valuedefn.source_type ='PDCC'
AND sit_allowance_valuedefn.base_name ='US_'||hzg.geography_code||'_ALLOWANCE'
AND sit_add_tax_valuedefn.base_name ='US_'||hzg.geography_code||'_ADDITIONAL_TAX'
AND sit_add_tax_valuedefn.source_id =sit_comp.dir_card_comp_id
AND sit_allowance_valuedefn.value_defn_id =allowance_details.value_defn_id
AND sit_add_tax_valuedefn.value_defn_id =add_tax_details.value_defn_id
AND sit_comp.dir_card_comp_id =sit_details.dir_card_comp_id
AND sit_details.dir_information_category ='HRX_US_WTH_STATE_INCOME_TAX'
and nvl(sit_comp.context_value1,0)||'-'||nvl(sit_comp.context_value2,0)||'-'||nvl(sit_comp.context_value3,0)=hzgi.identifier_value
and hzg.country_code='US'
and hzg.geography_type='STATE'
and hzg.geography_use='MASTER_REF'
and hzg.geography_element1='United States'
and hzg.geography_element1_code='US'
and hzg.geography_id=hzgi.geography_id
and hzgi.identifier_type='CODE'
and hzgi.identifier_subtype='GEO_CODE'
and hzgi.primary_flag='N'
and hzgi.geography_use='MASTER_REF'
and hzgi.geography_type='STATE'
and hzgi.language_code='US'
and hzgi.geography_id= hzgiv.geography_id
and hzgiv.identifier_type='NAME'
and hzgiv.identifier_subtype='STANDARD_NAME'
and hzgiv.primary_flag='Y'
and hzgiv.geography_use='MASTER_REF'
and hzgiv.geography_type='STATE'
and hzgiv.language_code='US'
and paa.assignment_number=prd.assignment_number
and pprd.payroll_relationship_id=prd.payroll_relationship_id
and prd.group_type='A'
and paa.assignment_sequence = ( select max(assignment_sequence) from per_all_assignments_m where person_id = paa.person_id and ASSIGNMENT_TYPE IN ('E','C') and sysdate between effective_start_date and effective_end_date)
and paa.effective_latest_change = 'Y'
and substr(sit_details.effective_end_date,1,10) = '4712-12-31'
and sit_details.DIR_INFORMATION_CHAR8= hl.lookup_code(+)
and hl.lookup_type (+) = 'HRX_US_STATE_WAGE_ACCUM'
and hl.ENABLED_FLAG(+) = 'Y'
and trunc(sysdate) between paa.effective_start_date and paa.effective_end_date
and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
and trunc(sysdate) between ppn.effective_start_date and ppn.effective_end_date
and trunc(sysdate) between pprd.START_DATE(+) and pprd.END_DATE(+)
and trunc(sysdate) between prd.START_DATE(+) and prd.END_DATE(+)
and trunc(sysdate) between sit_comp.effective_start_date(+) and sit_comp.effective_end_date(+)
and trunc(sysdate) between sit_allowance_valuedefn.effective_start_date(+) and sit_allowance_valuedefn.effective_end_date(+)
and trunc(sysdate) between sit_add_tax_valuedefn.effective_start_date(+) and sit_add_tax_valuedefn.effective_end_date(+)
and trunc(sysdate) between sit_details.effective_start_date and sit_details.effective_end_date
and trunc(sysdate) between allowance_details.effective_start_date and allowance_details.effective_end_date
and trunc(sysdate) between add_tax_details.effective_start_date and add_tax_details.effective_end_date
union
select pprd.PAYROLL_RELATIONSHIP_NUMBER,
paa.assignment_number,
pap.person_number,
ppn.full_name,
psu.name as Tax_Reporting_Unit_Name,
'' as Legislative_Data_Group_Name,
hzg.geography_name,
sit_details.DIR_INFORMATION_CHAR1,
sit_details.DIR_INFORMATION_CHAR2,
(SELECT DISABILITY_DETAILS.DIR_INFORMATION_CHAR1
FROM PAY_DIR_CARD_COMPONENTS_F DISABILITY_COMP,
PAY_DIR_COMP_DETAILS_F DISABILITY_DETAILS
WHERE DISABILITY_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND DISABILITY_COMP.DIR_CARD_COMP_ID = DISABILITY_DETAILS.DIR_CARD_COMP_ID
AND DISABILITY_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_DISABILITY'
AND nvl(sit_comp.context_value1,0) = nvl(DISABILITY_COMP.context_value1,0)
AND nvl(sit_comp.context_value2,0) = nvl(DISABILITY_COMP.context_value2,0)
AND nvl(sit_comp.context_value3,0) = nvl(DISABILITY_COMP.context_value3,0)
AND TRUNC(SYSDATE) BETWEEN DISABILITY_COMP.EFFECTIVE_START_DATE AND DISABILITY_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN DISABILITY_DETAILS.EFFECTIVE_START_DATE AND DISABILITY_DETAILS.EFFECTIVE_END_DATE) AS STATE_DISABILITY,
substr(DIR_INFORMATION_DATE1,1,10) AS IRS_Lock_in_Date,
sit_details.DIR_INFORMATION_CHAR7 as Exemption_for_Military_Spouse,
sit_details.DIR_INFORMATION_CHAR4 as Nonresident,
'' as Optional_Calculation_Method,
sit_details.DIR_INFORMATION_CHAR6 as Voluntary_Tax_Withholding,
sit_details.DIR_INFORMATION_CHAR3 as Exempt_from_wage_Accumulation,
hl.meaning as Resident_Wage_Accumulation,
(SELECT UNEMPLOYMENT_DETAILS.DIR_INFORMATION_CHAR1
FROM PAY_DIR_CARD_COMPONENTS_F UNEMPLOYMENT_COMP,
PAY_DIR_COMP_DETAILS_F UNEMPLOYMENT_DETAILS
WHERE UNEMPLOYMENT_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND UNEMPLOYMENT_COMP.DIR_CARD_COMP_ID = UNEMPLOYMENT_DETAILS.DIR_CARD_COMP_ID
AND UNEMPLOYMENT_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_UNEMPLOYMENT'
AND nvl(sit_comp.context_value1,0) = nvl(UNEMPLOYMENT_COMP.context_value1,0)
AND nvl(sit_comp.context_value2,0) = nvl(UNEMPLOYMENT_COMP.context_value2,0)
AND nvl(sit_comp.context_value3,0) = nvl(UNEMPLOYMENT_COMP.context_value3,0)
AND TRUNC(SYSDATE) BETWEEN UNEMPLOYMENT_COMP.EFFECTIVE_START_DATE AND UNEMPLOYMENT_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN UNEMPLOYMENT_DETAILS.EFFECTIVE_START_DATE AND UNEMPLOYMENT_DETAILS.EFFECTIVE_END_DATE) AS STATE_UNEMPLOYMENT,
(SELECT COMPENSATION_DETAILS.DIR_INFORMATION_CHAR1
FROM PAY_DIR_CARD_COMPONENTS_F COMPENSATION_COMP,
PAY_DIR_COMP_DETAILS_F COMPENSATION_DETAILS
WHERE COMPENSATION_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND COMPENSATION_COMP.DIR_CARD_COMP_ID = COMPENSATION_DETAILS.DIR_CARD_COMP_ID
AND COMPENSATION_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_WORKERS_COMPENSATION'
AND nvl(sit_comp.context_value1,0) = nvl(COMPENSATION_COMP.context_value1,0)
AND nvl(sit_comp.context_value2,0) = nvl(COMPENSATION_COMP.context_value2,0)
AND nvl(sit_comp.context_value3,0) = nvl(COMPENSATION_COMP.context_value3,0)
AND TRUNC(SYSDATE) BETWEEN COMPENSATION_COMP.EFFECTIVE_START_DATE AND COMPENSATION_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN COMPENSATION_DETAILS.EFFECTIVE_START_DATE AND COMPENSATION_DETAILS.EFFECTIVE_END_DATE) AS STATE_COMPENSATION,
(SELECT PSD_DETAILS.DIR_INFORMATION_NUMBER1
FROM PAY_DIR_CARD_COMPONENTS_F PSD_COMP,
PAY_DIR_COMP_DETAILS_F PSD_DETAILS
WHERE PSD_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND PSD_COMP.DIR_CARD_COMP_ID = PSD_DETAILS.DIR_CARD_COMP_ID
AND PSD_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_'||hzg.geography_name||'_PSD'
AND TRUNC(SYSDATE) BETWEEN PSD_COMP.EFFECTIVE_START_DATE AND PSD_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PSD_DETAILS.EFFECTIVE_START_DATE AND PSD_DETAILS.EFFECTIVE_END_DATE) AS RESIDENT_PSD_CODE,
(SELECT PSD_DETAILS.DIR_INFORMATION_NUMBER2
FROM PAY_DIR_CARD_COMPONENTS_F PSD_COMP,
PAY_DIR_COMP_DETAILS_F PSD_DETAILS
WHERE PSD_COMP.DIR_CARD_ID = PDCF.DIR_CARD_ID
AND PSD_COMP.DIR_CARD_COMP_ID = PSD_DETAILS.DIR_CARD_COMP_ID
AND PSD_DETAILS.DIR_INFORMATION_CATEGORY = 'HRX_US_WTH_STATE_'||hzg.geography_name||'_PSD'
AND TRUNC(SYSDATE) BETWEEN PSD_COMP.EFFECTIVE_START_DATE AND PSD_COMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PSD_DETAILS.EFFECTIVE_START_DATE AND PSD_DETAILS.EFFECTIVE_END_DATE) AS WORK_PSD_CODE,
substr(sit_details.effective_start_date,1,10) AS effective_start_date,
allowance_details.value1 as allowance,
'' as additional_tax,
pap.person_id,
hzg.geography_id,
sit_details.dir_information_category
from per_all_assignments_m paa,
per_all_people_f pap,
per_person_names_f ppn,
pay_rel_groups_dn prd,
pay_pay_relationships_dn pprd,
pay_dir_cards_f pdcf,
pay_dir_card_components_f sit_comp,
pay_dir_comp_details_f sit_details,
pay_value_definitions_f sit_allowance_valuedefn,
pay_range_items_f allowance_details,
hz_geographies hzg ,hz_geography_identifiers hzgi,
hz_geography_identifiers hzgiv,
hcm_lookups hl,
HR_ORGANIZATION_UNITS PSU
where paa.assignment_number =prd.assignment_number
and paa.person_id = pap.person_id
and pap.person_id = ppn.person_id
and ppn.name_type = 'GLOBAL'
AND PAA.ASSIGNMENT_TYPE IN ('E','C')
AND pprd.payroll_relationship_id =prd.payroll_relationship_id
AND prd.group_type ='A'
AND pdcf.dir_card_id =sit_comp.dir_card_id
AND pprd.payroll_relationship_id =pdcf.payroll_relationship_id
AND PPRD.PAYROLL_STAT_UNIT_ID(+) = PSU.ORGANIZATION_ID
AND sit_allowance_valuedefn.source_id =sit_comp.dir_card_comp_id
AND sit_allowance_valuedefn.source_type ='PDCC'
AND sit_allowance_valuedefn.base_name ='US_'||hzg.geography_code||'_SUI_ER_RATE'
AND sit_allowance_valuedefn.value_defn_id =allowance_details.value_defn_id
AND sit_comp.dir_card_comp_id =sit_details.dir_card_comp_id
AND sit_details.dir_information_category ='HRX_US_WTH_STATE_INCOME_TAX'
and nvl(sit_comp.context_value1,0)||'-'||nvl(sit_comp.context_value2,0)||'-'||nvl(sit_comp.context_value3,0)=hzgi.identifier_value
and hzg.country_code='US'
and hzg.geography_type='STATE'
and hzg.geography_use='MASTER_REF'
and hzg.geography_element1='United States'
and hzg.geography_element1_code='US'
and hzg.geography_id=hzgi.geography_id
and hzgi.identifier_type='CODE'
and hzgi.identifier_subtype='GEO_CODE'
and hzgi.primary_flag='N'
and hzgi.geography_use='MASTER_REF'
and hzgi.geography_type='STATE'
and hzgi.language_code='US'
and hzgi.geography_id= hzgiv.geography_id
and hzgiv.identifier_type='NAME'
and hzgiv.identifier_subtype='STANDARD_NAME'
and hzgiv.primary_flag='Y'
and hzgiv.geography_use='MASTER_REF'
and hzgiv.geography_type='STATE'
and hzgiv.language_code='US'
and paa.assignment_number=prd.assignment_number
and pprd.payroll_relationship_id=prd.payroll_relationship_id
and prd.group_type='A'
and paa.assignment_sequence = ( select max(assignment_sequence) from per_all_assignments_m where person_id = paa.person_id and ASSIGNMENT_TYPE IN ('E','C') and sysdate between effective_start_date and effective_end_date)
and paa.effective_latest_change = 'Y'
and substr(sit_details.effective_end_date,1,10) = '4712-12-31'
and sit_details.DIR_INFORMATION_CHAR8= hl.lookup_code(+)
and hl.lookup_type (+) = 'HRX_US_STATE_WAGE_ACCUM'
and hl.ENABLED_FLAG(+) = 'Y'
and trunc(sysdate) between paa.effective_start_date and paa.effective_end_date
and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
and trunc(sysdate) between ppn.effective_start_date and ppn.effective_end_date
and trunc(sysdate) between pprd.START_DATE(+) and pprd.END_DATE(+)
and trunc(sysdate) between prd.START_DATE(+) and prd.END_DATE(+)
and trunc(sysdate) between sit_comp.effective_start_date(+) and sit_comp.effective_end_date(+)
and trunc(sysdate) between sit_allowance_valuedefn.effective_start_date(+) and sit_allowance_valuedefn.effective_end_date(+)
and trunc(sysdate) between sit_details.effective_start_date and sit_details.effective_end_date
and trunc(sysdate) between allowance_details.effective_start_date and allowance_details.effective_end_date) TABLE1,
(SELECT DISTINCT hg.geography_name as county,
hg.geography_element2_id,
pap.person_id,
hg.geography_id
FROM pay_dir_comp_details_f pdc,
pay_dir_card_components_f pdcc,
hz_geography_identifiers hgi,
hz_geographies hg,
per_all_people_f pap
WHERE pdc.person_id = pap.person_id
and pdc.dir_information_category = 'HRX_US_WTH_COUNTY_INCOME_TAX'
and pdc.dir_card_comp_id = pdcc.dir_card_comp_id
and nvl(pdcc.context_value1,0)||'-'||nvl(pdcc.context_value2,0)||'-'||'0'=hgi.identifier_value
and hgi.identifier_type='CODE'
and hgi.identifier_subtype='GEO_CODE'
and hgi.primary_flag='N'
and hgi.geography_use='MASTER_REF'
and hgi.geography_type='COUNTY'
and hgi.language_code='US'
and hgi.geography_id = hg.geography_id
and hg.country_code='US'
and hg.geography_type='COUNTY'
and hg.geography_use='MASTER_REF'
and hg.geography_element1='United States'
and hg.geography_element1_code='US'
and substr(pdc.effective_start_date,1,10) = (select substr(max(effective_start_date),1,10) from pay_dir_comp_details_f where person_id = pap.person_id and dir_information_category = 'HRX_US_WTH_COUNTY_INCOME_TAX')
and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
and trunc(sysdate) between pdc.effective_start_date and pdc.effective_end_date
and trunc(sysdate) between pdcc.effective_start_date(+) and pdcc.effective_end_date(+)) table2,
(SELECT DISTINCT hg.geography_name as city,
hg.geography_element2_id,
pap.person_id,
hg.geography_element3_id
FROM pay_dir_comp_details_f pdc,
pay_dir_card_components_f pdcc,
hz_geography_identifiers hgi,
hz_geography_identifiers hgiv,
hz_geographies hg,
per_all_people_f pap
WHERE pdc.person_id = pap.person_id
and pdc.dir_information_category = 'HRX_US_WTH_CITY_INCOME_TAX'
and pdc.dir_card_comp_id = pdcc.dir_card_comp_id
and nvl(pdcc.context_value1,0)||'-'||nvl(pdcc.context_value2,0)||'-'||nvl(pdcc.context_value3,0)=hgi.identifier_value
and hgi.identifier_type='CODE'
and hgi.identifier_subtype='GEO_CODE'
and hgi.primary_flag='N'
and hgi.geography_use='MASTER_REF'
and hgi.geography_type='CITY'
and hgi.language_code='US'
and hgi.geography_id = hg.geography_id
and hg.country_code='US'
and hg.geography_type='CITY'
and hg.geography_use='MASTER_REF'
and hg.geography_element1='United States'
and hg.geography_element1_code='US'
and hgi.geography_id= hgiv.geography_id
and hgiv.identifier_type='CODE'
and hgiv.identifier_subtype='PRIMARY_CITY'
and hgiv.primary_flag='N'
and hgiv.geography_use='MASTER_REF'
and hgiv.geography_type='CITY'
and hgiv.language_code='US'
and substr(pdc.effective_start_date,1,10) = (select substr(max(effective_start_date),1,10) from pay_dir_comp_details_f where person_id = pap.person_id and dir_information_category = 'HRX_US_WTH_CITY_INCOME_TAX')
and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
and trunc(sysdate) between pdc.effective_start_date and pdc.effective_end_date
and trunc(sysdate) between pdcc.effective_start_date(+) and pdcc.effective_end_date(+)) table3
where TABLE1.PERSON_ID = TABLE2.PERSON_ID(+)
and TABLE2.PERSON_ID = TABLE3.PERSON_ID(+)
and table1.geography_id = table2.geography_element2_id(+)
and table2.geography_element2_id = table3.geography_element2_id(+)
and table2.geography_id = table3.geography_element3_id(+)
order by table1.person_id
Comments
Post a Comment