VIEW: APPS.PAY_EMPLOYEE_ACTION_INFO_V

Source

View Text - Preformatted

SELECT /*+ ORDERED USE_NL(PAA) */ PAI.ACTION_CONTEXT_ID, PAI.TAX_UNIT_ID, PAI.EFFECTIVE_DATE, PAI.ASSIGNMENT_ID, PAI.ACTION_INFORMATION1 FULL_NAME, PAI.ACTION_INFORMATION2 ORGANIZATION_ID, PAI.ACTION_INFORMATION4 NATIONAL_IDENTIFIER, PAI.ACTION_INFORMATION5 PAY_BASIS, PAI.ACTION_INFORMATION6 FREQUENCY, PAI.ACTION_INFORMATION7 GRADE, PAI.ACTION_INFORMATION8 BARGAINING_UNIT, PAI.ACTION_INFORMATION9 COLLECTIVE_AGREEMENT, PAI.ACTION_INFORMATION10 EMPLOYEE_NUMBER, PAY_US_EMPLOYEE_PAYSLIP_WEB.FORMAT_TO_DATE( PAI.ACTION_INFORMATION11) EMPLOYEE_DATE_START, PAY_US_EMPLOYEE_PAYSLIP_WEB.FORMAT_TO_DATE(PAI.ACTION_INFORMATION12) ORIGINAL_HIRE_DATE, PAY_US_EMPLOYEE_PAYSLIP_WEB.FORMAT_TO_DATE( PAI.ACTION_INFORMATION13) ADJUSTED_SERVICE_DATE, PAI.ACTION_INFORMATION14 ASSIGNMENT_NUMBER, PAI.ACTION_INFORMATION15 ORGANIZATION_NAME, PAI_OADDR.ACTION_INFORMATION5 EMPLOYER_ADDRESS1, PAI_OADDR.ACTION_INFORMATION6 EMPLOYER_ADDRESS2, PAI_OADDR.ACTION_INFORMATION7 EMPLOYER_ADDRESS3, PAI_OADDR.ACTION_INFORMATION8 EMPLOYER_CITY, /* DECODE( PAI_OADDR.ACTION_INFORMATION13, 'CA', PAI_OADDR.ACTION_INFORMATION9, PAI_OADDR.ACTION_INFORMATION10) */ substr(decode(PAY_PAYSLIP_UTIL.get_leg_rule_state(PAY.BUSINESS_GROUP_ID), 'ACTION_INFORMATION9', PAI_OADDR.ACTION_INFORMATION9, PAI_OADDR.ACTION_INFORMATION10),1,250) EMPLOYER_STATE, PAI_OADDR.ACTION_INFORMATION12 EMPLOYER_ZIP_CODE, PAI_OADDR.ACTION_INFORMATION13 EMPLOYER_COUNTRY, PAY.PAYROLL_NAME PAYROLL_NAME, PAI.ACTION_INFORMATION17 JOB, PAI.ACTION_INFORMATION18 GRE_NAME, PAI.ACTION_INFORMATION19 POSITION, PAI.ACTION_INFORMATION30 LOCATION_NAME, PAI.ACTION_INFORMATION20 CONTRACT, PAI.ACTION_INFORMATION21 WORKING_HOURS_START, PAI.ACTION_INFORMATION22 WORKING_HOURS_END, PAI.ACTION_INFORMATION23 PAY_CALCULATION_METHOD, PAI.ACTION_INFORMATION24 SHIFT, PAI.ACTION_INFORMATION25 EMPLOYER_PHONE_NUMBER, PAI.ACTION_INFORMATION26 HOURLY_SALARIED_CODE, PAI.ACTION_INFORMATION27 AGREEMENT_GRADE, PAI_EADDR.ACTION_INFORMATION5 EMPLOYEE_ADDRESS1, PAI_EADDR.ACTION_INFORMATION6 EMPLOYEE_ADDRESS2, PAI_EADDR.ACTION_INFORMATION7 EMPLOYEE_ADDRESS3, PAI_EADDR.ACTION_INFORMATION8 EMPLOYEE_CITY, /* DECODE( PAI_EADDR.ACTION_INFORMATION13, 'CA', PAI_EADDR.ACTION_INFORMATION9, PAI_EADDR.ACTION_INFORMATION10) */ substr(decode(PAY_PAYSLIP_UTIL.get_leg_rule_state(PAY.BUSINESS_GROUP_ID), 'ACTION_INFORMATION9', PAI_EADDR.ACTION_INFORMATION9, PAI_EADDR.ACTION_INFORMATION10),1,250) EMPLOYEE_STATE, PAI_EADDR.ACTION_INFORMATION12 EMPLOYEE_ZIP_CODE, PAI_EADDR.ACTION_INFORMATION13 EMPLOYEE_COUNTRY, NVL(FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION28),0) PAY_RATE, (NVL(FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION28),0) * NVL(FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION29),0)) ANNUAL_SALARY, PTPV.DISPLAY_PERIOD_TYPE PERIOD_TYPE, PTP.PERIOD_NUM PERIOD_NUM, PTP.START_DATE BEGINNING_DATE, PTP.END_DATE ENDING_DATE, PAI.EFFECTIVE_DATE PAYMENT_DATE ,PTP.regular_payment_date REGULAR_PAYMENT_DATE , DECODE (PAY_PAYSLIP_UTIL.get_legislation_code (PAY.BUSINESS_GROUP_ID), 'US' , nvl(pai_oaddr.action_information15,pai.action_information18), PAI.ACTION_INFORMATION18) PAYSLIP_EMPLOYER_NAME , DECODE (PAY_PAYSLIP_UTIL.get_legislation_code (PAY.BUSINESS_GROUP_ID), 'US' , nvl(pai_oaddr.action_information16,pai.action_information25), PAI.ACTION_INFORMATION25) PAYSLIP_EMPLOYER_PHONE FROM PAY_ACTION_INFORMATION PAI, PAY_ASSIGNMENT_ACTIONS PAA, PAY_ACTION_INFORMATION PAI_EADDR, PER_TIME_PERIODS PTP, PAY_ALL_PAYROLLS_F PAY, PAY_ACTION_INFORMATION PAI_OADDR, PER_TIME_PERIOD_TYPES_VL PTPV WHERE PAI.ACTION_CONTEXT_TYPE = 'AAP' AND PAI.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE DETAILS' AND PAA.ASSIGNMENT_ACTION_ID = PAI.ACTION_CONTEXT_ID AND PTP.TIME_PERIOD_ID = PAI.ACTION_INFORMATION16 AND PAY.PAYROLL_ID = PTP.PAYROLL_ID AND PAI.EFFECTIVE_DATE BETWEEN PAY.EFFECTIVE_START_DATE AND PAY.EFFECTIVE_END_DATE AND PAI.ACTION_CONTEXT_ID = PAI_EADDR.ACTION_CONTEXT_ID AND PAI_EADDR.ACTION_CONTEXT_TYPE = 'AAP' AND PAI_EADDR.ACTION_INFORMATION_CATEGORY = 'ADDRESS DETAILS' AND PAI_EADDR.ACTION_INFORMATION14 = 'Employee Address' AND PAI_OADDR.ACTION_CONTEXT_ID = PAA.PAYROLL_ACTION_ID AND PAI_OADDR.ACTION_CONTEXT_TYPE = 'PA' AND PAI_OADDR.ACTION_INFORMATION_CATEGORY = 'ADDRESS DETAILS' AND PAI_OADDR.ACTION_INFORMATION14 = 'Employer Address' AND PAI_OADDR.ACTION_INFORMATION1 = PAY_PAYSLIP_UTIL.GET_ID_FOR_EMPLOYER_ADDRESS ( PAY.BUSINESS_GROUP_ID, PAI.TAX_UNIT_ID, PAI.ACTION_INFORMATION2, PAI.EFFECTIVE_DATE) AND NVL(PAI_EADDR.ASSIGNMENT_ID, -1) = NVL(PAI.ASSIGNMENT_ID, -1) AND PTP.PERIOD_TYPE = PTPV.PERIOD_TYPE

View Text - HTML Formatted

SELECT /*+ ORDERED USE_NL(PAA) */ PAI.ACTION_CONTEXT_ID
, PAI.TAX_UNIT_ID
, PAI.EFFECTIVE_DATE
, PAI.ASSIGNMENT_ID
, PAI.ACTION_INFORMATION1 FULL_NAME
, PAI.ACTION_INFORMATION2 ORGANIZATION_ID
, PAI.ACTION_INFORMATION4 NATIONAL_IDENTIFIER
, PAI.ACTION_INFORMATION5 PAY_BASIS
, PAI.ACTION_INFORMATION6 FREQUENCY
, PAI.ACTION_INFORMATION7 GRADE
, PAI.ACTION_INFORMATION8 BARGAINING_UNIT
, PAI.ACTION_INFORMATION9 COLLECTIVE_AGREEMENT
, PAI.ACTION_INFORMATION10 EMPLOYEE_NUMBER
, PAY_US_EMPLOYEE_PAYSLIP_WEB.FORMAT_TO_DATE( PAI.ACTION_INFORMATION11) EMPLOYEE_DATE_START
, PAY_US_EMPLOYEE_PAYSLIP_WEB.FORMAT_TO_DATE(PAI.ACTION_INFORMATION12) ORIGINAL_HIRE_DATE
, PAY_US_EMPLOYEE_PAYSLIP_WEB.FORMAT_TO_DATE( PAI.ACTION_INFORMATION13) ADJUSTED_SERVICE_DATE
, PAI.ACTION_INFORMATION14 ASSIGNMENT_NUMBER
, PAI.ACTION_INFORMATION15 ORGANIZATION_NAME
, PAI_OADDR.ACTION_INFORMATION5 EMPLOYER_ADDRESS1
, PAI_OADDR.ACTION_INFORMATION6 EMPLOYER_ADDRESS2
, PAI_OADDR.ACTION_INFORMATION7 EMPLOYER_ADDRESS3
, PAI_OADDR.ACTION_INFORMATION8 EMPLOYER_CITY
, /* DECODE( PAI_OADDR.ACTION_INFORMATION13
, 'CA'
, PAI_OADDR.ACTION_INFORMATION9
, PAI_OADDR.ACTION_INFORMATION10) */ SUBSTR(DECODE(PAY_PAYSLIP_UTIL.GET_LEG_RULE_STATE(PAY.BUSINESS_GROUP_ID)
, 'ACTION_INFORMATION9'
, PAI_OADDR.ACTION_INFORMATION9
, PAI_OADDR.ACTION_INFORMATION10)
, 1
, 250) EMPLOYER_STATE
, PAI_OADDR.ACTION_INFORMATION12 EMPLOYER_ZIP_CODE
, PAI_OADDR.ACTION_INFORMATION13 EMPLOYER_COUNTRY
, PAY.PAYROLL_NAME PAYROLL_NAME
, PAI.ACTION_INFORMATION17 JOB
, PAI.ACTION_INFORMATION18 GRE_NAME
, PAI.ACTION_INFORMATION19 POSITION
, PAI.ACTION_INFORMATION30 LOCATION_NAME
, PAI.ACTION_INFORMATION20 CONTRACT
, PAI.ACTION_INFORMATION21 WORKING_HOURS_START
, PAI.ACTION_INFORMATION22 WORKING_HOURS_END
, PAI.ACTION_INFORMATION23 PAY_CALCULATION_METHOD
, PAI.ACTION_INFORMATION24 SHIFT
, PAI.ACTION_INFORMATION25 EMPLOYER_PHONE_NUMBER
, PAI.ACTION_INFORMATION26 HOURLY_SALARIED_CODE
, PAI.ACTION_INFORMATION27 AGREEMENT_GRADE
, PAI_EADDR.ACTION_INFORMATION5 EMPLOYEE_ADDRESS1
, PAI_EADDR.ACTION_INFORMATION6 EMPLOYEE_ADDRESS2
, PAI_EADDR.ACTION_INFORMATION7 EMPLOYEE_ADDRESS3
, PAI_EADDR.ACTION_INFORMATION8 EMPLOYEE_CITY
, /* DECODE( PAI_EADDR.ACTION_INFORMATION13
, 'CA'
, PAI_EADDR.ACTION_INFORMATION9
, PAI_EADDR.ACTION_INFORMATION10) */ SUBSTR(DECODE(PAY_PAYSLIP_UTIL.GET_LEG_RULE_STATE(PAY.BUSINESS_GROUP_ID)
, 'ACTION_INFORMATION9'
, PAI_EADDR.ACTION_INFORMATION9
, PAI_EADDR.ACTION_INFORMATION10)
, 1
, 250) EMPLOYEE_STATE
, PAI_EADDR.ACTION_INFORMATION12 EMPLOYEE_ZIP_CODE
, PAI_EADDR.ACTION_INFORMATION13 EMPLOYEE_COUNTRY
, NVL(FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION28)
, 0) PAY_RATE
, (NVL(FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION28)
, 0) * NVL(FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION29)
, 0)) ANNUAL_SALARY
, PTPV.DISPLAY_PERIOD_TYPE PERIOD_TYPE
, PTP.PERIOD_NUM PERIOD_NUM
, PTP.START_DATE BEGINNING_DATE
, PTP.END_DATE ENDING_DATE
, PAI.EFFECTIVE_DATE PAYMENT_DATE
, PTP.REGULAR_PAYMENT_DATE REGULAR_PAYMENT_DATE
, DECODE (PAY_PAYSLIP_UTIL.GET_LEGISLATION_CODE (PAY.BUSINESS_GROUP_ID)
, 'US'
, NVL(PAI_OADDR.ACTION_INFORMATION15
, PAI.ACTION_INFORMATION18)
, PAI.ACTION_INFORMATION18) PAYSLIP_EMPLOYER_NAME
, DECODE (PAY_PAYSLIP_UTIL.GET_LEGISLATION_CODE (PAY.BUSINESS_GROUP_ID)
, 'US'
, NVL(PAI_OADDR.ACTION_INFORMATION16
, PAI.ACTION_INFORMATION25)
, PAI.ACTION_INFORMATION25) PAYSLIP_EMPLOYER_PHONE
FROM PAY_ACTION_INFORMATION PAI
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ACTION_INFORMATION PAI_EADDR
, PER_TIME_PERIODS PTP
, PAY_ALL_PAYROLLS_F PAY
, PAY_ACTION_INFORMATION PAI_OADDR
, PER_TIME_PERIOD_TYPES_VL PTPV
WHERE PAI.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE DETAILS'
AND PAA.ASSIGNMENT_ACTION_ID = PAI.ACTION_CONTEXT_ID
AND PTP.TIME_PERIOD_ID = PAI.ACTION_INFORMATION16
AND PAY.PAYROLL_ID = PTP.PAYROLL_ID
AND PAI.EFFECTIVE_DATE BETWEEN PAY.EFFECTIVE_START_DATE
AND PAY.EFFECTIVE_END_DATE
AND PAI.ACTION_CONTEXT_ID = PAI_EADDR.ACTION_CONTEXT_ID
AND PAI_EADDR.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI_EADDR.ACTION_INFORMATION_CATEGORY = 'ADDRESS DETAILS'
AND PAI_EADDR.ACTION_INFORMATION14 = 'EMPLOYEE ADDRESS'
AND PAI_OADDR.ACTION_CONTEXT_ID = PAA.PAYROLL_ACTION_ID
AND PAI_OADDR.ACTION_CONTEXT_TYPE = 'PA'
AND PAI_OADDR.ACTION_INFORMATION_CATEGORY = 'ADDRESS DETAILS'
AND PAI_OADDR.ACTION_INFORMATION14 = 'EMPLOYER ADDRESS'
AND PAI_OADDR.ACTION_INFORMATION1 = PAY_PAYSLIP_UTIL.GET_ID_FOR_EMPLOYER_ADDRESS ( PAY.BUSINESS_GROUP_ID
, PAI.TAX_UNIT_ID
, PAI.ACTION_INFORMATION2
, PAI.EFFECTIVE_DATE)
AND NVL(PAI_EADDR.ASSIGNMENT_ID
, -1) = NVL(PAI.ASSIGNMENT_ID
, -1)
AND PTP.PERIOD_TYPE = PTPV.PERIOD_TYPE