eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: HR_ADP_DEDUCTION_V

Product:	PER - Human Resources
Description:	ADP payroll interface view
Implementation/DBA Data:	APPS.HR_ADP_DEDUCTION_V

View Text

SELECT REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, REF.GRE_ID
, PASG.ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER
, PP.PERSON_ID
, PP.BUSINESS_GROUP_ID
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, PASG.PRIMARY_FLAG
, PT.ELEMENT_NAME
, TO_NUMBER (DECODE (PIV1.HOT_DEFAULT_FLAG
, 'N'
, PV1.SCREEN_ENTRY_VALUE
, DECODE (PV1.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV1.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID)
, PV1.SCREEN_ENTRY_VALUE))) DEDUCTION_AMOUNT
, SUBSTR (DECODE (PIV2.HOT_DEFAULT_FLAG
, 'N'
, PV2.SCREEN_ENTRY_VALUE
, DECODE (PV2.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV2.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID)
, PV2.SCREEN_ENTRY_VALUE))
, 1
, 1) DEDUCTION_UNITS
, PT.ELEMENT_INFORMATION4 DEDUCTION_FREQUENCY
, TO_NUMBER (DECODE (PIV3.HOT_DEFAULT_FLAG
, 'N'
, PV3.SCREEN_ENTRY_VALUE
, DECODE (PV3.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV3.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID)
, PV3.SCREEN_ENTRY_VALUE))) DEDUCTION_LIMIT
, DECODE (PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, MIN (PB.EFFECTIVE_START_DATE)
, DECODE (MAX (PB.EFFECTIVE_END_DATE)
, TO_DATE ('31-12-4712'
, 'DD-MM-YYYY')
, TO_DATE (NULL)
, MAX (PB.EFFECTIVE_END_DATE)+ 1 )
, GREATEST (NVL (PP.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST (NVL (PASG.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE)
, GREATEST (NVL (PT.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PT.EFFECTIVE_START_DATE)
, GREATEST ( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, PB.EFFECTIVE_END_DATE)
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST(PB.EFFECTIVE_END_DATE
, NVL(PB.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(PB.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, PB.EFFECTIVE_START_DATE)
, GREATEST ( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, PB.EFFECTIVE_END_DATE)
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST (PB.EFFECTIVE_END_DATE
, 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(PT.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PT.EFFECTIVE_START_DATE
, NVL (PB.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST (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(PT.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PT.EFFECTIVE_START_DATE
, NVL (PB.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))))
, PB.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_TYPES_F PT
, PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
, PAY_INPUT_VALUES_F PIV3
, PAY_ELEMENT_LINKS_F EL
, PAY_ELEMENT_ENTRIES_F PB
, HR_ADP_EMP_REF_V REF
, PER_TIME_PERIOD_TYPES PTP
, PER_ALL_ASSIGNMENTS_F PASG
, PER_PERIODS_OF_SERVICE PPS
, PER_ALL_PEOPLE_F PP
, HR_ALL_ORGANIZATION_UNITS HOU
, PAY_ELEMENT_ENTRY_VALUES_F PV1
, PAY_ELEMENT_ENTRY_VALUES_F PV2
, PAY_ELEMENT_ENTRY_VALUES_F PV3
WHERE REF.PERIOD_TYPE = PTP.PERIOD_TYPE
AND REF.GRE_ID = HOU.ORGANIZATION_ID
AND PPS.PERIOD_OF_SERVICE_ID = PASG.PERIOD_OF_SERVICE_ID
AND PPS.PERSON_ID = PP.PERSON_ID
AND PP.ROWID = REF.PER_ROWID /*
AND PASG.ASSIGNMENT_ID = PB.ASSIGNMENT_ID */
AND REF.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.ROWID = REF.ASG_ROWID
AND EL.ELEMENT_LINK_ID + 0 = PB.ELEMENT_LINK_ID
AND EL.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PT.ATTRIBUTE1 = 'Y'
AND (PV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND PIV1.DISPLAY_SEQUENCE = 1
AND PB.ELEMENT_ENTRY_ID = PV1.ELEMENT_ENTRY_ID
AND PV1.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV1.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PT.ELEMENT_TYPE_ID = PIV1.ELEMENT_TYPE_ID /* + 0 */
AND PIV1.NAME <> 'PAY VALUE')
AND (PV2.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID
AND PIV2.DISPLAY_SEQUENCE = 2
AND PB.ELEMENT_ENTRY_ID = PV2.ELEMENT_ENTRY_ID
AND PV2.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV2.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PT.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID /* + 0 */)
AND (PV3.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID
AND PIV3.DISPLAY_SEQUENCE = 3
AND PB.ELEMENT_ENTRY_ID = PV3.ELEMENT_ENTRY_ID
AND PV3.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV3.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PT.ELEMENT_TYPE_ID = PIV3.ELEMENT_TYPE_ID /* + 0 */)
AND PB.EFFECTIVE_START_DATE = (SELECT MAX(PB2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PB2
WHERE PB2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PB2.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID
AND PB2.ASSIGNMENT_ID = PB.ASSIGNMENT_ID)
AND PT.EFFECTIVE_START_DATE = (SELECT MAX (PT2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_TYPES_F PT2
WHERE PT2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PT2.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID)
AND EL.EFFECTIVE_START_DATE = (SELECT MAX (EL2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_LINKS_F EL2
WHERE EL2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND EL2.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID)
AND PIV1.EFFECTIVE_START_DATE = (SELECT MAX(PIV12.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV12
WHERE PIV12.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PIV12.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID)
AND PIV2.EFFECTIVE_START_DATE = (SELECT MAX (PIV22.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV22
WHERE PIV22.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PIV22.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID)
AND PIV3.EFFECTIVE_START_DATE = (SELECT MAX (PIV32.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV32
WHERE PIV32.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PIV32.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID)
AND PT.ELEMENT_NAME <> 'ADP GTL'
AND PT.BENEFIT_CLASSIFICATION_ID IS NULL GROUP BY REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, REF.GRE_ID
, PASG.ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER
, PP.PERSON_ID
, PP.BUSINESS_GROUP_ID
, PASG.ASSIGNMENT_NUMBER
, PASG.ASSIGNMENT_SEQUENCE
, PASG.PRIMARY_FLAG
, PT.ELEMENT_NAME
, PASG.ASSIGNMENT_ID
, PT.PROCESSING_TYPE
, PV3.SCREEN_ENTRY_VALUE
, PT.ELEMENT_INFORMATION4
, PV2.SCREEN_ENTRY_VALUE
, PV1.SCREEN_ENTRY_VALUE
, PIV3.HOT_DEFAULT_FLAG
, PIV2.HOT_DEFAULT_FLAG
, PIV1.HOT_DEFAULT_FLAG
, PIV3.INPUT_VALUE_ID
, PIV2.INPUT_VALUE_ID
, PIV1.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID
, PB.LAST_UPDATE_DATE
, PB.EFFECTIVE_START_DATE
, PB.EFFECTIVE_END_DATE
, PP.LAST_UPDATE_DATE
, PP.EFFECTIVE_START_DATE
, HOU.LAST_UPDATE_DATE
, HOU.DATE_FROM
, PASG.LAST_UPDATE_DATE
, PASG.EFFECTIVE_START_DATE
, PT.LAST_UPDATE_DATE
, PT.EFFECTIVE_START_DATE
, PTP.NUMBER_PER_FISCAL_YEAR

Columns

Name
COMPANY_CODE_EQUIVALENT
GRE_NAME
GRE_ID
ORGANIZATION_ID
EMPLOYEE_NUMBER
PERSON_ID
BUSINESS_GROUP_ID
ASSIGNMENT_ID
ASSIGNMENT_NUMBER
ASSIGNMENT_SEQUENCE
PRIMARY_FLAG
DEDUCTION_NAME
DEDUCTION_AMOUNT
DEDUCTION_UNITS
DEDUCTION_FREQUENCY
DEDUCTION_LIMIT
PAY_FREQUENCY
BEGIN_DATE
END_DATE
PER_LAST_UPDATE_DATE
ASG_LAST_UPDATE_DATE
DED_LAST_UPDATE_DATE
DDED_LAST_UPDATE_DATE
GREATEST_LAST_UPDATE_DATE