VIEW: APPS.PAY_US_EMPLOYEE_ACTION_INFO_V

Source

View Text - Preformatted

SELECT paa.assignment_action_id action_number, ppa2.effective_date, ppf.person_id, ppf.last_name, ppf.first_name, ppf.middle_names, ppf.employee_number, ppf.national_identifier, pa.address_line1||pa.address_line2||pa.address_line3 address_line, pa.region_1 county, pa.town_or_city, pa.region_2 state, pa.postal_code, pa.country, paf.assignment_number, paf.payroll_id, ppa.business_group_id, Pay_Mag_Utils.get_parameter('TRANSFER_CONSOLIDATION_SET_ID', null,ppa.legislative_parameters) consolidation_set_id, hl.location_id, hl.location_code, paa.tax_unit_id tax_unit_id, hou.name gre_name, paa.assignment_id, paf.organization_id, hou2.name org_name, hoi.org_information1 fed_ein, fcl.meaning action_type from fnd_common_lookups fcl, per_people_f ppf, hr_locations_all hl, per_addresses pa, per_assignments_f paf, /*bug 10296882*/ hr_organization_information hoi, hr_all_organization_units hou, hr_all_organization_units hou2, pay_payroll_actions ppa2, pay_assignment_actions paa2, pay_assignment_actions paa, pay_payroll_actions ppa WHERE ppa.report_type = 'XFR_INTERFACE' and ppa.report_category = 'RT' and ppa.report_qualifier = 'FED' and ppa.payroll_action_id = paa.payroll_action_id and paa2.assignment_action_id = substr(paa.serial_number, 3) and ppa2.payroll_action_id = paa2.payroll_action_id and paa.tax_unit_id = hou.organization_id and paa.tax_unit_id = hoi.organization_id and hoi.org_information_context = 'Employer Identification' and paa.assignment_id = paf.assignment_id and substr(paa.serial_number,1,1) = fcl.lookup_code and lookup_type = 'ACTION_TYPE' and paf.effective_start_date = (select max(paf1.effective_start_date) from per_all_assignments_f paf1 where paf1.assignment_id = paf.assignment_id and paf1.effective_start_date <= ppa.effective_date) and paf.location_id = hl.location_id and paf.person_id = pa.person_id and pa.primary_flag = 'Y' and ppa2.effective_date between pa.date_from and nvl(pa.date_to,to_date('4712/12/31','YYYY/MM/DD')) and paf.organization_id = hou2.organization_id and paf.person_id = ppf.person_id and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ACTION_ID ACTION_NUMBER
, PPA2.EFFECTIVE_DATE
, PPF.PERSON_ID
, PPF.LAST_NAME
, PPF.FIRST_NAME
, PPF.MIDDLE_NAMES
, PPF.EMPLOYEE_NUMBER
, PPF.NATIONAL_IDENTIFIER
, PA.ADDRESS_LINE1||PA.ADDRESS_LINE2||PA.ADDRESS_LINE3 ADDRESS_LINE
, PA.REGION_1 COUNTY
, PA.TOWN_OR_CITY
, PA.REGION_2 STATE
, PA.POSTAL_CODE
, PA.COUNTRY
, PAF.ASSIGNMENT_NUMBER
, PAF.PAYROLL_ID
, PPA.BUSINESS_GROUP_ID
, PAY_MAG_UTILS.GET_PARAMETER('TRANSFER_CONSOLIDATION_SET_ID'
, NULL
, PPA.LEGISLATIVE_PARAMETERS) CONSOLIDATION_SET_ID
, HL.LOCATION_ID
, HL.LOCATION_CODE
, PAA.TAX_UNIT_ID TAX_UNIT_ID
, HOU.NAME GRE_NAME
, PAA.ASSIGNMENT_ID
, PAF.ORGANIZATION_ID
, HOU2.NAME ORG_NAME
, HOI.ORG_INFORMATION1 FED_EIN
, FCL.MEANING ACTION_TYPE
FROM FND_COMMON_LOOKUPS FCL
, PER_PEOPLE_F PPF
, HR_LOCATIONS_ALL HL
, PER_ADDRESSES PA
, PER_ASSIGNMENTS_F PAF
, /*BUG 10296882*/ HR_ORGANIZATION_INFORMATION HOI
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ALL_ORGANIZATION_UNITS HOU2
, PAY_PAYROLL_ACTIONS PPA2
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'XFR_INTERFACE'
AND PPA.REPORT_CATEGORY = 'RT'
AND PPA.REPORT_QUALIFIER = 'FED'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA2.ASSIGNMENT_ACTION_ID = SUBSTR(PAA.SERIAL_NUMBER
, 3)
AND PPA2.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID
AND PAA.TAX_UNIT_ID = HOU.ORGANIZATION_ID
AND PAA.TAX_UNIT_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'EMPLOYER IDENTIFICATION'
AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND SUBSTR(PAA.SERIAL_NUMBER
, 1
, 1) = FCL.LOOKUP_CODE
AND LOOKUP_TYPE = 'ACTION_TYPE'
AND PAF.EFFECTIVE_START_DATE = (SELECT MAX(PAF1.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F PAF1
WHERE PAF1.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PAF1.EFFECTIVE_START_DATE <= PPA.EFFECTIVE_DATE)
AND PAF.LOCATION_ID = HL.LOCATION_ID
AND PAF.PERSON_ID = PA.PERSON_ID
AND PA.PRIMARY_FLAG = 'Y'
AND PPA2.EFFECTIVE_DATE BETWEEN PA.DATE_FROM
AND NVL(PA.DATE_TO
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD'))
AND PAF.ORGANIZATION_ID = HOU2.ORGANIZATION_ID
AND PAF.PERSON_ID = PPF.PERSON_ID
AND PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE