View: HR_ADP_COSTING_V

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

View Text

SELECT REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, HS.SEGMENT1
, PASG.ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, PASG.PRIMARY_FLAG
, PCF.EFFECTIVE_START_DATE
, PCF.EFFECTIVE_END_DATE
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, (PCF.PROPORTION*100)
, PCA.SEGMENT1
, PCA.SEGMENT2
, PCA.SEGMENT3
, PCA.SEGMENT4
, PCA.SEGMENT5
, PCA.SEGMENT6
, PCA.SEGMENT7
, PCA.SEGMENT8
, PCA.SEGMENT9
, PCA.SEGMENT10
, PCA.SEGMENT11
, PCA.SEGMENT12
, PCA.SEGMENT13
, PCA.SEGMENT14
, PCA.SEGMENT15
, PCA.SEGMENT16
, PCA.SEGMENT17
, PCA.SEGMENT18
, PCA.SEGMENT19
, PCA.SEGMENT20
, PCA.SEGMENT21
, PCA.SEGMENT22
, PCA.SEGMENT23
, PCA.SEGMENT24
, PCA.SEGMENT25
, PCA.SEGMENT26
, PCA.SEGMENT27
, PCA.SEGMENT28
, PCA.SEGMENT29
, PCA.SEGMENT30
, PCA.SEGMENT1||PCA.SEGMENT2||PCA.SEGMENT3
, GREATEST ( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, NVL(REF.FINAL_PROCESS_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST(NVL(REF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(REF.FINAL_PROCESS_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, 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
, PCF.EFFECTIVE_START_DATE
, NVL(PCF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST(NVL(REF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, 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
, PCF.EFFECTIVE_START_DATE
, NVL(PCF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, REF.DATE_START)
FROM HR_ADP_EMP_REF_V REF
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE PPS
, PAY_COST_ALLOCATION_KEYFLEX PCA
, PAY_COST_ALLOCATIONS_F PCF
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_ASSIGNMENTS_F PASG
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
WHERE PASG.PERSON_ID = PP.PERSON_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PPS.PERSON_ID = PASG.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID= PASG.PERIOD_OF_SERVICE_ID
AND PTP.PERIOD_TYPE = PPR.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 = PCF.COST_ALLOCATION_KEYFLEX_ID
AND PCF.ASSIGNMENT_ID = PASG.ASSIGNMENT_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 PCF.EFFECTIVE_START_DATE=(SELECT MAX(PCF2.EFFECTIVE_START_DATE)
FROM PAY_COST_ALLOCATIONS_F PCF2
WHERE (PCF2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PCF2.ASSIGNMENT_ID = PCF.ASSIGNMENT_ID))
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))

Columns