View: HR_ADP_ELE_COSTING_V

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

View Text

SELECT 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)
, 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
, PBEE.ELEMENT_ENTRY_ID ELEMENT_ENTRY_ID
, PBET.ELEMENT_NAME DEDUCTION_NAME
, PBEE.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PBEE.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, PCA.SEGMENT1 COST_SEGMENT1
, PCA.SEGMENT2 COST_SEGMENT2
, PCA.SEGMENT3 COST_SEGMENT3
, PCA.SEGMENT4 COST_SEGMENT4
, PCA.SEGMENT5 COST_SEGMENT5
, PCA.SEGMENT6 COST_SEGMENT6
, PCA.SEGMENT7 COST_SEGMENT7
, PCA.SEGMENT8 COST_SEGMENT8
, PCA.SEGMENT9 COST_SEGMENT9
, PCA.SEGMENT10 COST_SEGMENT10
, PCA.SEGMENT11 COST_SEGMENT11
, PCA.SEGMENT12 COST_SEGMENT12
, PCA.SEGMENT13 COST_SEGMENT13
, PCA.SEGMENT14 COST_SEGMENT14
, PCA.SEGMENT15 COST_SEGMENT15
, PCA.SEGMENT16 COST_SEGMENT16
, PCA.SEGMENT17 COST_SEGMENT17
, PCA.SEGMENT18 COST_SEGMENT18
, PCA.SEGMENT19 COST_SEGMENT19
, PCA.SEGMENT20 COST_SEGMENT20
, PCA.SEGMENT21 COST_SEGMENT21
, PCA.SEGMENT22 COST_SEGMENT22
, PCA.SEGMENT23 COST_SEGMENT23
, PCA.SEGMENT24 COST_SEGMENT24
, PCA.SEGMENT25 COST_SEGMENT25
, PCA.SEGMENT26 COST_SEGMENT26
, PCA.SEGMENT27 COST_SEGMENT27
, PCA.SEGMENT28 COST_SEGMENT28
, PCA.SEGMENT29 COST_SEGMENT29
, PCA.SEGMENT30 COST_SEGMENT30
, GREATEST(NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PBEE.EFFECTIVE_START_DATE) GREATEST_LAST_UPDATE_DATE
FROM HR_ADP_EMP_REF_V REF
, PER_ALL_PEOPLE_F PP
, PAY_COST_ALLOCATION_KEYFLEX PCA
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, PAY_ELEMENT_ENTRIES_F PBEE
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_ELEMENT_LINKS_X PBEL
, PAY_ELEMENT_TYPES_X PBET
WHERE PASG.PERSON_ID = PP.PERSON_ID
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PPR.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PPR.PAYROLL_ID = PASG.PAYROLL_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PCA.COST_ALLOCATION_KEYFLEX_ID = PBEE.COST_ALLOCATION_KEYFLEX_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND A2.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID))
AND PBEL.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID
AND PBEE.ELEMENT_LINK_ID = PBEL.ELEMENT_LINK_ID
AND PASG.ASSIGNMENT_ID = PBEE.ASSIGNMENT_ID
AND PBEE.EFFECTIVE_START_DATE = (SELECT MAX(PBEE2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PBEE2
, PAY_ELEMENT_LINKS_X PBEL2
WHERE (PBEE2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PBEE2.ASSIGNMENT_ID = PBEE.ASSIGNMENT_ID
AND PBEE2.ELEMENT_LINK_ID = PBEL2.ELEMENT_LINK_ID
AND PBEL2.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID))

Columns